2 Replies Latest reply on Aug 24, 2016 10:38 AM by m60freeman

    Historically Associate Queries

    awickham10

      We have a dynamically generated query that runs every day with a new number in it.

       

      Day 1 - Hash X

         SELECT T.ID, 

            1000 (dynamically generated) AS ETL_RUNID 

         FROM TABLENAME T

       

      Day 2 - Hash Y

         SELECT T.ID, 

            1001 (dynamically generated) AS ETL_RUNID 

         FROM TABLENAME T

       

      Every day that number increments by one. This causes us to lose historical tracking because DPA does not recognize them as similar queries since it's not parameterized.

       

      Is there anyway that I can associate these daily queries together and make them appear to DPA that they are the same query? Even if it's something I have to manually run on the repository database I would be OK with that.

       

      Thanks!

        • Re: Historically Associate Queries
          mandevil

          We're creating the hash within DPA based on output from the RDBMS. For example, we'll create the hash in SQL Server from parts of the sql_handle, and some other factors. For Oracle, we actually get the hash from Oracle itself.

          Thus the problem - if the RDBMS sees it as a new query, so do we.

           

          Recommendation: You can create a report that will look for specific SQLtext (some identifying pattern) that will pull back all of the hashes that match that pattern.

          Now, to actually change the hash in the repo would not be recommended as there are a LOT of dependencies that don't necessarily follow RI (think summary tables, dimensions, plans, etc.).

           

          I think you may be limited by the report for pulling like SQL...

          Anyone else have any ideas?