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.

In the following example, the application ID *LOCAL.vivmak.110513164421 of the load ID LOADID: 16.2011-05-13-12.44.34.638811.0 (3;5) from the previous example in this topic, is used to run the db2trc command:

$ 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