Accessing current thread activity information

To access current thread information

1. On EZDSSI, hyperlink on SSI Status - List DB2s.

2. On STDB2, hyperlink on Act Thrd to view a list of all active threads in the selected Db2.If you have the proper Db2 authorization, you can use the C line command to issue the DB2 CANCEL THREAD command.

   >W1=THDACTV==========(ALL======*=======)ddmmmyyyy===17:14:56====MVDB2====D====6
    +  Active Threads                                             + Show Header    
   CMD Correlation  Db2   Elapsed             Plan     Package                    
   --- ID           ID     Time      Auth ID  Name     Name      %CPU Locks Warn U
       DOMQEV54     DEDM 10:04:04.53 BOLDJW1  - none -            0.0     0 No   B
       DMRPASWN     DEDM 08:13:48.82 BOLSTC   - none -            0.0     0 No   B
       db2bp.exe    DEDM 06:15:10.49 BOLHHH1  DISTSERV SQLC2E03   0.0    12 No   D
       DMRPA91X     DEDM 03:52:07.83 BOLSTC   - none -            0.0     0 No   B
       JXOPAS81     DEDM 01:55:09.51 BOLSTC   - none -            0.0     0 No   B
       DMRPASTP     DEDM 00:43:13.56 BOLSTC   - none -            0.0     0 No   B

   A hidden header beneath the window information line provides direct navigation to related views and identifies the row hyperlinks in the view.

3. To display and review the header options:
   1. Place the cursor on the Show Header field and press Enter.
   2. Select Hide Header and press Enter when you are finished.

4. Hyperlink on one thread by placing your cursor in the Correlation ID column to see a detail display for that thread.

   >W1 =DUSER=============ATDENA==*========ddmmmyyyy==1:18=3M====MVDB2====D==119
   -  Detail User Status PARM : DMRPASAT                         - Hide Header   
      Command:           CANCEL Thread        Section Locates                     
      Hyperlinks:                             (Section Field Help)                
      . Current Locks                         BASE       SQLCNTS    ACC           
      . EXPLAIN                               SQLSTMT    BFRPOOL    RTN           
      . START TRACE                           ENV        LOCKS      DDF           
      . UTRAC(Actv Dtl)                       ELAPSED    PRLL       PKG           
      . Monitors (USER)                                                           
                                                                                 
      ** Scrollable View Lines **                                                 
   ------------------------------------------------------------------------------
   RUNTIME ANALYSIS   IN DB2     IN APPL.      TOTAL      %IN DB2(=)     TOTAL(*)
   ----------------   --------   --------   --------     0 ...25...50...75..100%  
   ELAPSED TIME       3,935 ms   02:04:36    02:04:40     | <******************* |
   CPU TIME             593 ms     334 ms     927 ms      | <                    |
   DB2 WAIT TIME        268 ms                            | ==                   |
   - Specialty Engine CPU times - -                                               
     - In DB2 CPU         0 us               - - - -      |                      |
     - STORED PROC        0 us                                                    
     - UDF                0 us                                                    
     - TRIGGER            0 us                                                    
   - Accel-elig elpsd     0 us                                                    
     - Eligible CPU       0 us                                                    
     - Eligible zIIP      0 us                                                    
   - - - - - - ACTIVITY  - - - - - -     - - - - - - KEY INDICATORS  - - - - - - -
   TOTAL SQL.......................0               
   GETPAGES........................0     
   SYNC READS (PRLL=00)............0     
   PREFETCH PAGES READ.............0                                              
   UPDATES/COMMIT................0.0                                              
   BFR HIT RATIOS:...........VP=   %                                              
   LOG RECORDS WRITTEN.............0
   - - - - - - - - - - - - - - - - - -WORKFILE- - - - - - - - - - - - - - - - - -
   Max Workfile Blocks.............0
   Curr Workfile Blocks............0

   The DUSER view is a hybrid free-form view. The data in the scrollable section of the view is provided by the full-screen service DUSER. You cannot customize or sort the view.

   You can scroll down to see more data, or put the cursor on a field in the Section Locates area and press Enter to display that section at the top of the view. To display Help for a section, put the cursor on a field in the Section Locates area and press F1.

   The view shows all available accounting detail information about that thread. A summary of the most critical data, including the key indicators of failures or potential problems is shown in the base section.

   Notice that the SQL Statement Analysis section contains:

   • The full text of the current SQL statement that is being executed
   • The current package/DBRM name
   • The amount of time this SQL statement has been active
   • The last page accessed

   If the SQL is dynamic, the decimal value of the current SQL Cache Token is also shown. If the statement has been executing for a long time, you might want to use the decimal value to issue an EXPLAIN STMTCACHE :stmt-token to see information about the access path in use for the cached SQL statement. In contrast, the EXPLAIN hyperlink in the DUSER header (and the Explain function described in Step 7) performs a dynamic Explain based on the SQL text. If the text has been used multiple times from the cache, the access paths might be different. zIIP CPU times are unavailable for the DUSER because they do not exist in the IFCID 148, which is why the value is displayed as N/A. These times are only recorded in the IFCID 003 LTRAC after the thread has terminated. 

5. To view a breakdown of all SQL executed by this thread, place the cursor on the SQLCNTS field in the Section Locates area and press Enter.This view contains many other sections of detail data with which you will become acquainted over time. For example, if you are concerned about locking, you can use the # Locks hyperlink in DUSER header to see all of the locks that this thread is holding or waiting on, and any threads that are in contention with it. Alternatively, you could use the LOCKS field in the Section Locates area to see the counts of all lock activity for this thread.
6. Press F3 as needed to return to THDACTV.This view contains several other hyperlinks that can be useful in analyzing a thread. Scroll right and hyperlink on User Status to see a subset of the DUSER information in THDDETL. Staying in windows mode might be valuable if you need to 'freeze' the threads to look at several at the same point in time.

7.  Hyperlink on Current Activity if it shows a currently active SQL statement.

   >W1 =EXPLAIN===========DEFGWTN==*========ddmmmyyyy==15:18:26====MVDB2====D====7
   C      Actions: S H R W P                                                      
   -                                                                              
     LBL  STMTNO     COST*RATE SQL-STATEMENT                                      
     XD01    215    547.428024  SELECT DKEY1K0, COUNT(*) AS HOW_MANY FROM BOLDJW1
          COST*RATE QB PL MIX ME ACC MTCH IX TBNAME             IXNAME            
     XD01 245.00355  1  1   0  0 I      1 N  DWQATABA           DWQAX05A          
     XD01 69.246201  1  2   0  4 I      1 N  DWQATABA           DWQAX01A          
     XD01  2.330811  1  3   0  3        0 N                                       
     XD01 230.84745  3  1   0  0 R      0 N  SYSDUMMY1

   You can also access the Explain application from the MVDB2/DC Admin/Archive hyperlink on the EZDB2 easy menu (shown in Analyzing-a-specific-Db2) to Explain a plan or package, or view existing Plan_Table Explain data. For more information, see Explaining-SQL-Statements.

8. Press F3 until you return to EZDSSI.