Use Thread Detail
attributes to create situations that monitor DB2 threads. This
information is used locally for data sharing.
Authorization
ID The primary authorization ID from
connection or sign-on that identifies the execution privileges an
application has to a DB2 object or service. Valid format is alphanumeric
with a maximum of 8 characters.
Authorization
ID (Unicode) The primary authorization
ID from connection or sign-on that identifies the execution privileges
an application has to a DB2 object or service. Valid format is
alphanumeric with a maximum of 8 characters.
Collection
ID The collection ID.
This
field is blank for DBRM. Valid format is alphanumeric, with a maximum
of 18 characters
Connection ID Identifies the connection of an application
to a DB2 system.
Valid values are:
Value |
Description |
BATCH |
Batch job |
CICS |
CICS attach |
DB2CALL |
DB2 call facility |
DIST |
Distributed |
IMS |
IMS thread |
TSO/E |
TSO/E foreground and background |
UTILITY |
DB2 utility |
Connection Type The type of DB2 connection.
Valid
values are:
Value |
Description |
APPLCTN |
Application directed access |
BATCH |
Batch job |
CICS |
CICS attach |
DB2CAF |
DB2 call attach |
DLIBATCH |
DL/I batch |
IMS |
IMS thread |
IMS BMP |
IMS attach BMP |
IMS TBMP |
IMS transaction BMP |
IMS CTL |
IMS control region |
IMS MPP |
IMS attach MPP |
SYSTEM |
System directed access |
TSO/E |
TSO/E foreground and background |
UNKNOWN |
Unrecognizable connection type |
UTILITY |
DB2 utility |
Correlation ID The correlation between an application and
the connection to a DB2 system. Valid format is alphanumeric,
with a maximum of 12 characters; for example DLKEX212.
CPU
Rate The thread CPU rate.
DB2 ID The name of a
DB2 subsystem.
Display CPU Time The total amount of CPU time that DB2 has accumulated
for a thread.
Valid format is HH:MM:SS:mmm, where:
- H = Hour
- M = Minute
- S = Second
- m = Millisecond
If the time goes over 24 hours, the format is DD-HH:MM where:
- D = Day
- H = Hour
- M = Minute
For example, 00:14:59 indicates that the display CPU time is
14 minutes and 59 seconds.
Display Elapsed Time The total amount of elapsed time since thread
creation or DB2 sign-on.
Valid format is DD-HH:MM:SS, where:
- D = Day
- H = Hour
- M = Minute
- S = Second
For example, 00-20:16:18 indicates that the display elapsed time
is 20 hours, 16 minutes, and 18 seconds.
Display
Wait Time The total amount of thread
wait time. The collection of thread wait time requires activation
of Accounting Class 2 in the monitored DB2 subsystem.
Valid format is HH:MM:SS:mmm, where:
- H = Hour
- M = Minute
- S = Second
- m = Millisecond
If the time goes over 24 hours, the format is DD-HH:MM where:
- D = Day
- H = Hour
- M = Minute
For example, 00:14:59 indicates that the display CPU time is
14 minutes and 59 seconds.
ID Name An internal attribute used for navigation,
it contains either the Data Sharing Group Name or the DB2ID.
IIP CPU The accumulated
CPU time consumed while executing on an IBM zIIP in all environments.
In DB2 CP CPU Time The
CP CPU time accumulated in DB2 include the current in-DB2 CPU time
for the SQL statement in progress.
In
DB2 Elapsed Time The elapsed time accumulated
in DB2 include the current in-DB2 time for the SQL statement in progress.
MVS System The MVS System
Management Facility (SMF) ID. Valid format is alphanumeric with
a maximum of 4 characters; for example SP11.
Originating
System ID The managed system name of
the agent. Valid format is alphanumeric, with a maximum of 32
characters; for example, DB91:SYS1:DB2.
Package
Name Identifies the package name with
which a thread is associated. Valid format is alphanumeric, with
a maximum of 8 characters.
Plan Name The name of an application plan that DB2 produces
during the bind process and uses for processing SQL statements during
execution. Valid format is alphanumeric, with a maximum of 8
characters. For example, PLANLCK2 is the name of an application plan.
*SYSTEM* indicates DB2 subsystem-generated threads that own or are
waiting on a lock.
Thread CPU Time The total amount of CPU time that DB2 has accumulated
for a thread. Valid value is an integer in the range 0 - 99999999
seconds.
Thread Elapsed Time The total amount of elapsed time since thread
creation or DB2 sign-on. Valid value is an integer in the range
0 - 99999999 seconds.
Thread Group Name The name of a data sharing group for a thread. Valid
format is alphanumeric, with a maximum of 8 characters; for example,
TDDB241G.
Thread Group Member Name The name of the member within a data sharing
group for a thread.
Thread Status The current status of a thread.
Valid
values are:
- Status
- Description
- IN-ABORT
- The thread is in abort processing.
- IN-ACCEL
- The thread is executing in accelerator.
- IN-AUTO-PROC
- The thread is processing an autonomous procedure.
- IN_BIND_DYNM
- The thread is in dynamic bind processing.
- IN-BIND-STAT
- The thread is in static bind processing.
- IN-COMMAND
- Command threads display this status when they are active in DB2
and executing within the DB2 command processor. (This type of thread
always has a blank.)
- IN-COMMIT
- The thread is in commit processing. It applies only to threads
that originate from an attachment that does not use a two-phase commit
protocol.
- IN-COMT-PHS1
- The thread is in commit phase 1 processing.
- IN-COMT-PHS2
- The thread is in commit phase 2 processing.
- IN-CRTE-THRD
- The thread is in create thread processing.
- IN_DB2
- The thread is executing in DB2. A more descriptive status could
not be determined.
- INDOUBT
- The thread is in doubt.
- IN-SIGNON
- The thread is in signon processing. This status applies only
to threads originating from CICS or IMS attachments.
- IN_SQL_CALL
- The thread is processing an SQL call.
- IN_SQL_SORT
- The thread is executing an SQL call and is doing the sort processing
required to satisfy the request of the call.
- IN-STOR-PROC
- The thread is currently running in a stored procedure.
- IN-TERM-THRD
- The thread is in termination as a result of allied task termination.
This status corresponds to the DB2 DISPLAY THREAD=D.
- IN-TRIGGER
- The thread is currently running in a trigger.
- IN-USER-FUNC
- The thread is currently running a user-defined function.
- NOT_AVAIL
- The thread is not available
- NOT_IN_DB2
- The thread is currently not running in DB2.
- SP/UDF-INACT
- The thread is trying to but cannot run in a stored procedure or
user-defined function.
- SWAPPED_OUT
- The thread is currently not running in DB2. The thread originating
address space is swapped out.
- WAIT_ARCHIVE
- The thread is waiting for an archive log tape mount necessary
during thread abort processing.
- WAIT_ARCREAD
- The thread is currently waiting for read of archive log from tape.
- WAIT_ASYNCRD
- The thread is currently waiting for completion of a read I/O
that is done under a thread other than this one (such as sequential
or list prefetch).
- WAIT_ASYNCWR
- The thread is currently waiting for completion of write I/O that
is done under a thread other than this one (such as deferred writes).
- WAIT_CLAIMER
- The thread is currently waiting for claimers to be released after
acquiring DRAIN lock.
- WAIT_CONVLIM
- The distributed thread is in a synchronous wait because the conversation
limit has been reached for its designated logmode.
- WAIT_CTHREAD
- The thread is queued in create thread processing because DB2 reached
the CTHREAD value. This status corresponds to DB2 DISPLAY THREAD=QD
status.
- WAIT_DRNLOCK
- The thread is currently waiting to acquire DRAIN lock.
- WAIT_GLBLOCK
- The thread is currently waiting for one of the following:
- A lock held by another subsystem in the data sharing group.
- Inter-system communication within the data sharing group to determine
if there is lock contention.
- WAIT_LOCK
- The thread is waiting for a lock
- WAIT_LOGQSCE
- The thread is currently suspended because of an ARCHIVE LOG MODE(QUIESCE)
command.
- WAIT_MSGSEND
- The thread is currently waiting because of inter-system message
sending contention.
- WAIT_PGLATCH
- The thread is currently waiting for page latch.
- WAIT_REMREQ
- The database access thread is in a synchronous wait (waiting for
a response or a request from the originating DB2 subsystem).
- WAIT_REMSQL
- The distributed allied thread is in a synchronous wait (waiting
for a response from the remote DB2 subsystem being accessed).
- WAIT_REUSE
- The thread is not currently in use and is waiting to be reused.
This status applies only to CICS and IMS threads.
- WAIT_SERVICE
- The thread is currently waiting for completion of a DB2 service.
Types of DB2 services include open/close of data set, DFHSM recall
of a data set, SYSLGRNG update, or define/extend/delete of a data
set, rollback, and commit phase 2 for read only threads.
- WAIT_SP_STOP
- The thread is queued waiting for a stopped stored procedure.
- WAIT_SPSCHD
- The thread is waiting for a TCB to become available in the stored
procedures address space to schedule a stored procedure.
- WAIT_SYNC_IO
- The thread is currently waiting for completion of a synchronous
Read I/O or Write I/O.
Note: This status can indicate that a DB2 resource
is not large enough. You can use the OMEGAMON XE for DB2 PE resource
manager or object analysis displays to further isolate the problem.
- WAIT_TERM_TH
- The thread is queued and waiting for thread termination as a result
of allied task termination. This status corresponds to the DB2 DISPLAY
THREAD=QD status.
- UTIL-STOP
- The DB2 utility was started but not completed because of abnormal
termination.
Thread Stored Procedure Name The name of the stored procedure that a thread
invokes. Valid format is alphanumeric, with a maximum of 18 characters;
for example, SPCALC_TAX.
Thread Type The type of thread.
It can be:
Type |
Description |
A-LIST |
Allied-distributed thread starts at one server location;
processes at another. |
ALLIED |
Thread starts and processes at one server location. |
DBACCESS |
Database access thread, requested by an allied-distributed
thread. |
UNKNOWN |
Unknown. |
Thread Wait Time The
total amount of thread wait time. Valid format is an integer
in the range 09 - 99999999 seconds. Note: collection of thread time
requires activation of Accounting Class 2 in the Monitored DB2 subsystem.
Time Stamp Represents
the end of data collection or the end of an interval, and is the local
time where the system is running.
The timestamp format for SCAN and STR functions is
CYYMMDDHHMMSSmmm (as
in 1120315064501000 for 03/15/12 06:45:01) where:
- C = Century (0 for 20th, 1 for 21st)
- Y = Year
- M = Month
- D = Day
- H = Hour
- M = Minute
- S = Second
- m = Millisecond