Dynamic SQL Cache by Authid

This panel provides information about the dynamic SQL cache by Authid. You can also look at the dynamic SQL cache by end user ID, transaction ID, or by workstation name.

With this panel, you can identify problem statements in the dynamic SQL cache.

One line of output is produced for each authorization identifier (Auth ID) that is located in the cache when the displayed data is collected.

To sort the output by column, move the cursor under a column heading and press the sort key (PF10).


 ________________ ZEDDS    VTM     O2       V540./I SE11 06/13/13 15:11:44   2 
>  Help PF1    Back PF3     Up PF7     Down PF8     Sort PF10     Zoom PF11    
>                                                                              
> *-BY AUTHID   B-ALL STMTS   C-BY TXID   D-BY WSNM   E-BY EUID                
===============================================================================
>             Dynamic SQL Cache Statistics by Authid                           
>                                                                              
 EDDS                                                                          
+ *                                                                            
+ Authorization id   Entries CPU Time     Elasped Time Wait Time    GetPages   
+ ------------------ ------- ------------ ------------ ------------ --------   
+ HONG                    14 01-18:08     03-01:35     00:00:01.759 2989115K   
+ HONG1                   14 00:00:15.384 00:00:23.059 00:00:00.013    22068   
+ TAM                      1 00:00:18.860 00:00:20.398 00:00:00.037    48078   
===============================================================================

Navigation

For additional information about
  • detailed dynamic SQL cache information for an authorization ID, move the cursor to the required line and press F11 (Zoom).
  • other topics, use the PF keys.

Fields

Each field reflects the data that was available when collection was executed. OMEGAMON® XE for DB2 PE collects data and refreshes the panel each time you navigate to the panel.

Authorization ID
The current SQLID of the user who did the initial PREPARE.
Entries
The number of dynamic SQL statements that are cached for each authorization ID.
CPU Time
The accumulated CPU time of all statements that are associated with the authorization ID. This time includes CPU that is consumed on an IBM® specialty engine for all statements associated with this authorization ID.
Elapsed Time
The accumulated elapsed time that is used for all statements that are associated with this authorization ID.
Wait Time
The accumulated wait time that is used for all statements that are associated with the authorization ID including the following items:
  • Wait for Synch I/O (QW0316W1)
  • Wait for Lock/Latch (QW0316W2)
  • Wait Synch Exec Switch (QW0316W3)
  • Wait for Global Locks (QW0316W4)
  • Wait Other Thread Read (QW0316W5)
  • Wait Other Thread Write (QW0316W6)
  • Wait for Latch Req (QW0316W7)
  • Wait Time Page Latch (QW0316W8)
  • Wait for Drain Lock (QW0316W9)
  • Wait for Drain/Claim (QW0316WA)
  • Wait for Log Writer (QW0316WB)
  • Wait global child L-Locks (QW0316WC)
  • Wait global other L-Locks (QW0316WD)
  • Wait global P/P P-Locks (QW0316WE)
  • Wait global page P-Locks (QW0316WF)
  • Wait global other P-Locks (QW0316WG)
  • Wait for PQS (QW0316_PQS_WAIT)
  • Wait for PIPE (QW0316_PIPE_WAIT)
GetPages
The accumulated GET PAGES for all statements that are associated with the authorization ID.