Identifying db2diag log entries for a load operation
Identifying diagnostic information
for a load operation and finding the db2diag command
log messages associated with that load operation are important steps
in troubleshooting problematic load operations.
Symptoms
When issuing multiple load operations you might notice that a load operation is taking longer to complete than normal or appears to be hanging.Resolving the problem
To determine which of the load operations are being problematic:- Issue the db2pd -utilities command to display
the IDs of all load operations. In the following example output, there
is only one load operation that is taking place, with an ID of LOADID:
16.2011-05-13-12.44.34.638811.0 (3;5):
Database Partition 0 -- Active -- Up 0 days 00:16:15 -- Date 05/13/2011 13:00:33 Utilities: Address ID Type State Invoker Priority StartTime DBName NumPhases CurPhase Description 0x000000020120E2A0 1 LOAD 0 0 0 Fri May 13 12:44:34 SAMPLE 2 2 [LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5)] [APPID: *LOCAL.vivmak.110513164421] OFFLINE LOAD DEL AUTOMATIC INDEXING INSERT COPY NO VIVMAK .T1 Progress: Address ID PhaseNum CompletedWork TotalWork StartTime Description 0x000000020120E600 1 1 0 bytes 0 bytes Fri May 13 12:44:34 SETUP 0x000000020120E7E0 1 2 0 rows 0 rows Fri May 13 12:44:34 LOAD
- Issue the db2diag -g 'dataobj:=loadID' command,
where loadID is the ID of the load operation found
in the previous step. This command displays all the diagnostic log
messages from the db2diag command log related to
the specified load operation. The following example shows what is
displayed when this command is issued with the load operation ID identified
previously:
$ db2diag -g 'dataobj:= LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5)' 2011-05-13-14.27.03.134598-240 I1700E525 LEVEL: Warning PID : 29615 TID : 47039995963712PROC : db2sysc INSTANCE: vivmak NODE : 000 DB : SAMPLE APPHDL : 0-7 APPID: *LOCAL.vivmak.110513164421 AUTHID : VIVMAK EDUID : 44 EDUNAME: db2lrid FUNCTION: Db2, database utilities, sqlulPrintPhaseMsg, probe:314 DATA #1 : String, 99 bytes LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) Completed LOAD phase at 05/13/2011 14:27:03.134463.
After you complete these steps, you have enough information
to identify the problematic load operation. However, if you need more
information about the load operation, issue the db2pd -db <dbname> -load
loadID="LOADID" stacks command
to obtain a stack trace. The stacks option is available
on UNIX and Linux® operating systems only. The following
example shows what is displayed when this command is issued on a sample
database with the load operation ID identified previously:
$ db2pd -db sample -load loadID="LOADID: 16.2011-05-13-12.34.34.638811.0 (3;5)" stacks
Attempting to produce stack traces for LOAD edu 40
Attempting to produce stack traces for LOAD edu 41
Attempting to produce stack traces for LOAD edu 42
Attempting to produce stack traces for LOAD edu 44
Attempting to produce stack traces for LOAD edu 45
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:00:27 -- Date 05/13/2011 14:28:32
Node Number : 0
Database Name : SAMPLE
LoadID EDUID EDUNAME TableName SchemaName AppHandl [nod-index] Application ID StartTime LoadPhase
LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) 40 db2lfrm0 T1 VIVMAK 7 [000-00007] *LOCAL.vivmak.110513164421 2011-05-13-12.44.34.638811 LOAD
LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) 41 db2lfrm1 T1 VIVMAK 7 [000-00007] *LOCAL.vivmak.110513164421 2011-05-13-12.44.34.638811 LOAD
LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) 42 db2lfrm2 T1 VIVMAK 7 [000-00007] *LOCAL.vivmak.110513164421 2011-05-13-12.44.34.638811 LOAD
LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) 44 db2lrid T1 VIVMAK 7 [000-00007] *LOCAL.vivmak.110513164421 2011-05-13-12.44.34.638811 LOAD
LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) 45 db2lbm0 T1 VIVMAK 7 [000-00007] *LOCAL.vivmak.110513164421 2011-05-13-12.44.34.638811 LOAD
The db2pd -db <dbname> -load loadID="LOADID" stacks command displays all the EDU information related to the load operation specified and produces stack trace files in the diagpath directory.
You can use all the information retrieved to perform further troubleshooting techniques, such as monitoring or terminating the load operation. Also, the information gathered might be requested by IBM technical support to troubleshoot problematic load operations.
You can also use the collected
information to run the db2trc command for further
troubleshooting. To run the db2trc command for
a specific load operation by using the retrieved information:
- Run the db2pd -load command to retrieve the application ID of the specific load operation that you are interested in.
- Run the db2trc -appid or db2trc -apphdl command to record further information about the load operation.
$ db2trc on -appid *LOCAL.vivmak.110513164421
Trace is turned on
$ db2trc info
Marker : @TRACE@
Trace version : 7.0
Platform : Linux/X
Build level : n110612
maxBufferSize : 33554432 bytes (32 MB)
auxBufferSize : 524288 bytes (0 MB)
allocationCount : 2
DB2TRCD pid : 0
Trace destination : <shared memory buffer>
numSuspended : 0
Trace starting time : 2011-06-16-10.07.52.209999-240
Buffer size : 33554432 bytes (32 MB)
Allow buffer to wrap : yes
Mask : *.*.*.*.*
Timestamps : disabled
PID.TID mask : all
Fixed data mask #1 : all
Fixed data mask #2 : all
Max system errors : infinite
Treat this rc as sys err: none
Member mask : none
Application handle mask : none
Application ID mask : *LOCAL.vivmak.110513164421
In
the next example, the application handle obtained from the output
of the db2pd -load command is used to change the
trace options that are in effect for the db2trc command:$ db2trc chg -apphdl 7
Trace has been changed
$ db2trc info
Marker : @TRACE@
Trace version : 7.0
Platform : Linux/X
Build level : n110612
maxBufferSize : 33554432 bytes (32 MB)
auxBufferSize : 524288 bytes (0 MB)
allocationCount : 2
DB2TRCD pid : 0
Trace destination : <shared memory buffer>
numSuspended : 0
Trace starting time : 2011-06-16-10.10.11.816264-240
Buffer size : 33554432 bytes (32 MB)
Allow buffer to wrap : yes
Mask : *.*.*.*.*
Timestamps : disabled
PID.TID mask : all
Fixed data mask #1 : all
Fixed data mask #2 : all
Max system errors : infinite
Treat this rc as sys err: none
Member mask : none
Application handle mask : 7
Application ID mask : none