Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Question Unanswered: "db2 get snapshot" linking SQL with application handle / ID

    How could i be using "db2 get snapshot" to get all SQLs run in 15 mins with ID showing who runs it?

    Currently i am using the following command:

    db2 get snapshot for all on DB123

    The problem is, I found that all SQLs found in a session called "Dynamic SQL Snapshot Result" under "Statement text" but there is no ID in this session for each SQL to link back to who run the SQL, linkage like "application handle" or "Application ID" or "Agent process/thread ID" would be fine for this purpose

    example of my Dynamic SQL

    Dynamic SQL Snapshot Result

    Database name = DB123

    Database path = /db2data/basit/ssit/NODE0000/SQL0004/

    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 = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Buffer pool xda logical reads = Not Collected
    Buffer pool xda physical reads = Not Collected
    Buffer pool temporary xda logical reads = Not Collected
    Buffer pool temporary xda physical reads = Not Collected
    Total execution time (sec.microsec)= 0.000908
    Total user cpu time (sec.microsec) = 0.000539
    Total system cpu time (sec.microsec)= 0.000009
    Total statistic fabrication time (milliseconds) = 0
    Total synchronous runstats time (milliseconds) = 0
    Statement text = SET CCCC_TX_ID = '13859471113519'

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That's not possible because any statement in the package cache may be executed by multiple applications. If you really need to know what applications execute what statements, use the audit facility or continuously run an event monitor for statements or activities.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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