Accessing current thread activity information
From the DB2 status overview (STDB2), you can access current thread information.
| For more information, view the Quick Course Displaying Active Threads. |
To access current thread information
- On EZDSSI, hyperlink on SSI Status - List DB2s.
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.
10MAR2011 17:15:37 ------------ INFORMATION DISPLAY -------------------------
COMMAND ===> SCROLL ===> CSR
CURR WIN ===> 1 ALT WIN ===>
>W1=THDACTV==========(ALL======*=======)10MAR2011==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 BA hidden header beneath the window information line provides direct navigation to related views and identifies the row hyperlinks in the view.
- To display and review the header options:
- Place the cursor on the Show Header field and press Enter.
- Select Hide Header and press Enter when you are finished.
Hyperlink on one thread by placing your cursor in the Correlation ID column to see a detail display for that thread.
07JUL2016 10:16:51 ------ MAINVIEW WINDOW INTERFACE (V6.1.01) ----------------
COMMAND ===> SCROLL ===> CSR
CURR WIN ===> 1 ALT WIN ===>
>W1 =DUSER=============JXODEHF==*========07JUL2016==10:16:51====MVDB2====D==236
- Detail User Status PARM : BOLJXOAD - 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 **
CURRENT........10:16:51.59 PLAN...............DSNTEP2 TYPE.........ACCELERATOR
START..........10:16:43.73 AUTHID............BOLJXO1 CONNECT........BATCH/TSO
ELAPSED...........7,857 ms ORIG PRIM AUTH....BOLJXO1 CORR ID.....BOLJXOAD
STATUS.........ACCELERATOR COMMITS..................0 ROLLBACKS..............0
------------------------------------------------------------------------------
RUNTIME ANALYSIS IN DB2 IN APPL. TOTAL %IN DB2(=) TOTAL(*)
---------------- -------- -------- -------- 0 ...25...50...75..100%
ELAPSED TIME 7,852 ms 4,712 us 7,857 ms | ===================* |
CPU TIME 16 ms 2,141 us 18 ms | < |
DB2 WAIT TIME 804 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.......................9 SQL: DYNAMIC(PREPARE)= 3
GETPAGES......................115 I/O RSP: SYNC= 2,182 us, ASYNC= 12 ms
SYNC READS (PRLL=00)...........59 NUMBER OF DISTRIBUTED LOCATIONS = 1
PREFETCH PAGES READ.............3
UPDATES/COMMIT................0.0
BFR HIT RATIOS:...........VP= 46%
LOG RECORDS WRITTEN.............0The 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.
- 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.
- 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.
Hyperlink on Current Activity if it shows a currently active SQL statement.
10MAR2011 15:18:26 ------ MainView WINDOW INTERFACE (V6.0.00) ----------------
COMMAND ===> SCROLL ===> CSR
CURR WIN ===> 1 ALT WIN ===>
>W1 =EXPLAIN===========DEFGWTN==*========10MAR2011==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 SYSDUMMY1You 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.
- Press F3 until you return to EZDSSI.
Related topic