2 Replies Latest reply on Apr 25, 2019 10:44 AM by mmuller

    DPA table that relates the DBUSER name with the SQLHASH

    mmuller

      I'm attempting to get the sql wait times for a specific DB User (on the DB User tab) via the DPA DB.

       

      In the UI I can navigate to the DB User in question and drill down to the related SQLHASH and use that to create a query to get the wait times.

      What I'm currently unable to find is a means to query the SQLHASH from the DB User name (in my case it's PIMSdbSvcRpt200d).

       

      Thank you for your help ... I'm almost there

       

      Mike

        • Re: DPA table that relates the DBUSER name with the SQLHASH
          mmuller

          I was able to dig a bit more here is what I've got so far (not quite there yet):

           

          select * from CONU_2 where NAME ='PIMSdbSvcRpt200d'

           

          which returns a number which coincides with the url parameter &dId which is used in returning the page to the UI

          http://server.as.local:8123/iwc/database.iwc?repo_id=1

          &db_id=2

          &day=Apr+16!991555390800000

          &time=1PM-2PM!991555437600000

          &dType=6

          &dId=314

          &dName=PIMSdbSvcRpt200d

          &bc=

          &action=0

          &ts=3600

          &tab=31

          &iorw=r

          &pm=P

          &sdTab=

          &fullSQLText=

          &chartType=

           

          but I'm not sure what other tables that is used in and as what column name to get me closer to the SQLHASH.

           

          Still need help

           

          Thank you.

           

          Mike

            • Re: DPA table that relates the DBUSER name with the SQLHASH
              mmuller

              Well, I'm able to go from DB User name to DBUSERID and then use that in a table to get the wait times with the following

               

              select * from CONU_2 cu

              join CON_DBUSER_SUM_2 c2 on c2.DBUSERID = cu.ID

              join CON_DBUSER_TEN_MINUTE_2 c10 on c10.DBUSERID = cu.ID

              where NAME ='PIMSdbSvcRpt200d'

               

              however, the DATEHOUR I'm interested in is earlier than is available so, I'm close, but not sure yet how to get historical data.

               

              Still need help lol

               

              Mike