Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Unanswered: Which User is running which SQL..

    Hello All,

    We have a Production DB2 Database running in AIX (8.1) Fixpak 11. Is there anyway i can find which user runs which Dynamic sql ? I could see 1000's of dynamic SQL's executed at a time in the server. Also Please let me know is there anyway to find the SQL that uses more CPU time.

    Thanks & regards,
    Senthil.T

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    with db2 get snapshot you can see the userid that has ben identified to db2
    alse with another db2 get snapshot command .. dynamic sql... you can see the cpu usage by executed query in the dynamic sql cache.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Mar 2007
    Posts
    30
    You could try using Spotlight from Quest Central for DB2.

    Thanks

  4. #4
    Join Date
    Mar 2007
    Posts
    4
    Spotlight from Quest Central for DB2, is very useful. I hope this is just like a Query patroller.
    As per policy we were not allowed to install any of additional products in our environments.
    Dynamic SQL snapshot does not have the UserID's under which it runs, also it has only USER CPU and SYSTEM CPU..

    Please let me know how can i see the dynamic SQL Cache..

    Thanks & regards,
    Senthil.T

  5. #5
    Join Date
    Jun 2006
    Posts
    471
    this command will show user and connectid
    C:\Documents and Settings\GUY>db2 get snapshot for application agentid 127

    Application Snapshot

    Application handle = 127
    Application status = Connect Completed
    Status change time = Not Collected
    Application code page = 1252
    Application country/region code = 0
    DUOW correlation token = *LOCAL.DB2.070323125333
    Application name = db2bp.exe
    Application ID = *LOCAL.DB2.070323125333
    Sequence number = 0001
    TP Monitor client user ID =
    TP Monitor client workstation name =
    TP Monitor client application name =
    TP Monitor client accounting string =

    Connection request start timestamp = 23/03/2007 13:53:33.797228
    Connect request completion timestamp = 23/03/2007 13:53:33.798058
    Application idle time = Not Collected
    CONNECT Authorization ID = DB2ADMIN
    Client login ID = GUY
    Configuration NNAME of client = DBSS_PC
    Client database manager product ID = SQL08025

    db2 get snapshot for dynamic sql on db2nt
    will list the dynamic sql cache
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  6. #6
    Join Date
    Mar 2007
    Posts
    4
    Thanks Guy Przytula.

    But the dynamic SQL snapshot doesn't have the UserID. Eventhough the application snapshot gives the UserID, Dynamic SQL shows a number of queries being executed, and where i could see the CPU time consumed by a single query also.

    Number of executions = 1
    Number of compilations = 1
    Worst preparation time (ms) = 0
    Best preparation time (ms) = 0
    Internal rows deleted = 0
    Internal rows inserted = 0
    Rows read = 0
    Internal rows updated = 0
    Rows written = 0
    Statement sorts = 0
    Statement sort overflows = 0
    Total sort time = 0
    Buffer pool data logical reads = 0
    Buffer pool data physical reads = 0
    Buffer pool temporary data logical reads = 0
    Buffer pool temporary data physical reads = 0
    Buffer pool index logical reads = 0
    Buffer pool index physical reads = 0
    Buffer pool temporary index logical reads = 0
    Buffer pool temporary index physical reads = 0
    Total execution time (sec.ms) = 0.000000
    Total user cpu time (sec.ms) = 0.000000
    Total system cpu time (sec.ms) = 0.000000
    Statement text = CALL REORGCHK_IX_STATS ('T', '"PSEPM "."PS_WFA_WKFRCE1_T7"')

    The only thing that i could not find here is the USER ID under which the query is being executed. Please let me know if there is any other way to get the User ID info.

    Thanks & regards,
    Senthil.T

  7. #7
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    "db2 get snapshot for application agentid xxx"
    should give you some user information, I get these 3 values on my 8.1 FP 14 instance:

    CONNECT Authorization ID
    Client login ID
    Configuration NNAME of client

  8. #8
    Join Date
    Mar 2007
    Posts
    4
    Thankyou for the informations

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •