Taking DB2 Traces

Debbie Bargas, Advisory Programmer , IBM Corporation

Debra Bargas is an Advisory Programmer and has been with IBM for 14 years. She has spent the last 8 years working with DB2 and is DB2 UDB certified in Application Development. She works in Developer Relations supporting vendors who are porting their applications to DB2. Debbie can be reached at debbo@us.ibm.com.

Summary: The intent of this document is to provide basic information about what tools are available for troubleshooting, when to use them, and how to generate the necessary files for diagnosing problems.

Tag this!

Date: 09 May 2002
Level: Introductory
Activity: 976 views

Introduction

The purpose of this document is to provide some very basic troubleshooting techniques to help self-diagnose problems that may be encountered while using IBM DB2 ® Universal Database. This document will cover information such as how to check the maintenance level or fixpak level of the DB2 server and client. It will go over some basic troubleshooting logging tools, such as the db2diag.log file and how to create various types of traces, including a CLI trace, JDBC trace, and a DB2 trace.

This document is not intended to replace the DB2 UDB Troubleshooting Guide , but rather to be a quick reference for gathering diagnostic data. This data, when used in conjunction with the DB2 UDB Message Reference and Troubleshooting Guide, can lead users to resolve DB2 problems on their own. Although this document does not guarantee the resolution of problems, it may be helpful to users that do problem determination before contacting DB2 service for assistance.

Checking the fixpak level for DB2

When encountering problems with DB2, one of the first things that should be done is to make sure that clients and servers are at the latest fixpak of DB2. Many times, simply applying the latest fixpak will take care of problems that have already been corrected. The latest fixpaks can be found at the DB2 UDB and DB2 Connect Online Support page.

When a problem is reported to IBM by an outside group or customer, an APAR is typically generated to track the defect. When IBM externalizes a new fixpak level of DB2, an APAR list is generated with the fixpak, giving very brief descriptions of the APARs included in the fixpak. Although all APARs are listed, not all defects are included on this list. Many times, internally reported defects are corrected within the code without a formal APAR associated with it. Even though a particular problem is not listed, it is still a good idea to apply the fixpak as underlying changes may have corrected your problem.

To check whether or not the system is at the latest fixpak, you can run the db2level command on the client (to check the DB2 level of your client code) and then run this same command on the DB2 server where the database resides. This command can be executed either from a DB2 command window or from the login prompt of your instance userid under UNIX ® platforms. This command will not run in the Command Line Processor (CLP):

E:\SQLLIB\BIN>db2level

Db2level will return information similiar to the following:

DB21085I Instance "DB2" uses DB2 code release "SQL07020" with level identifier 
"03010105" and informational tokens "DB2 v7.1.0.41", "n010426" and "WR21254".

The most important piece of information is the last token passed. In this case, WR21254 represents the fixpak level of the system and WR21254 is FP3 of DB2 version 7.1.

After determining the fixpak level of your system, the Web url above should be checked to ensure that this is the latest fixpak of DB2. If it is not, then we recommend that you apply the latest fixpak and re-run your scenario to determine if the problem still exists before calling DB2 service. Since each fixpak has numerous fixes, it will be one of the first things recommended by DB2 service before proceeding to the next step.

Using db2diag level

Whether the problem is occurring on the client or on the server, DB2 will generate a diagnostic file called db2diag.log . This is the first file that should be used for problem determination as it can be used to capture warnings and severe errors. In order to get a better feel for what is happening with DB2, it is best to start with this db2diag.log file. If the problem is encountered on the client machine, start by using the db2diag.log file located on the client to see what is being sent by the DB2 server. If the problem is occurring on the DB2 server, then this file will also be generated on the server.

This file is generated to capture informational warnings and critical errors depending on the setting of the database manager configuration parameter, diaglevel . The default value for this is 3, indicating to capture all errors and warnings. If you are trying to capture all possible diagnostic information that is written by DB2, then it is best to set this value to 4. Since having DB2 diaglevel set to 4 is resource intensive, this should be done only for problem determination. Once the problem has been fixed, then the diaglevel should be set back to 3. The db2diag.log file is cumulative. Before capturing the information pertaining to your problem scenario, it is best to rename the existing db2diag.log file and then reproduce the problem with a limited sequence of steps. This will help eliminate any extraneous information that may be written to the file.

To change the diaglevel value that controls the extent of information that is being written by DB2, issue the following command:

db2 update dbm cfg using diaglevel 4 db2stop

Then restart DB2 with:

db2start

The db2diag.log file that is generated is placed in different locations for UNIX and Intel ® platforms. For the 32-bit Windows ® platform, it is placed in the following directory structure by default:

d:\sqllib\db2\db2diag.log

where db2 is the instance name. For UNIX platforms, it is located by default under:

$/home/db2inst1/sqllib/db2dump/db2diag.log

where db2inst1 is the instance name.

If you want to relocate this file to another directory structure, you can do so with the following command:

db2 update dbm cfg using diagpath d:\myerrors\

where d:\myerrors represents the file location for db2diag.log . Once again, any changes made to the database manager configuration file ( dbm cfg ) are made effective by stopping ( db2stop ) and starting DB2 ( db2start ).

Sample db2diag.log

The following sample of db2diag.log file shows a disk-full error condition:

 
2001-10-05-13.22.03.467322   Instance:db2v72   Node:000 
PID:23520(db2agent (SAMPLE))   Appid:*LOCAL.db2v72.011005182037 
oper_system_services  sqloSetFileAccessMode   Probe:36   Database:SAMPLE 
errno: 0000 0002                                     .... 
 
2001-10-05-19.15.59.394579   Instance:db2v72   Node:000 
PID:5648(db2wdog)   Appid:none 
oper_system_services  sqloRunInstance   Probe:102 
DiagData 
4372 6974 6963 616c 2045 7272 6f72 3a20       Critical Error: 
5368 7574 7469 6e67 2064 6f77 6e20 7379       Shutting down sy 
7374 656d 00                                  stem. 
 
2001-10-05-19.15.59.739726   Instance:db2v72   Node:000 
PID:5648(db2wdog)   Appid:none 
oper_system_services  sqloRunInstance   Probe:105 
DiagData 
0000 0102 ffff ffff                           ........ 
 
2001-10-05-19.15.59.907519   Instance:db2v72   Node:000 
PID:5648(db2wdog)   Appid:none 
base_sys_utilities  sqleCleanupResources   Probe:5 
DiagData 
0000 0102                                     .... 
 
2001-10-10-12.53.21.459787   Instance:db2v72   Node:000 
PID:17146(db2bp)   Appid: 
oper_system_services  sqlo_access_listen_queue   Probe:1 
0000 0002                                     .... 
 
2001-10-10-13.02.12.980199   Instance:db2v72   Node:000 
PID:20640(db2agent (instance))   Appid:none 
oper_system_services  sqloopenp   Probe:36 
errno: 0000 001a                                     .... 
 
2001-10-10-13.02.14.628298   Instance:db2v72   Node:000 
PID:20640(db2agent (instance))   Appid:none 
oper_system_services  sqloopenp   Probe:36 
errno: 0000 001a                                     .... 
 
2001-10-10-13.02.14.723173   Instance:db2v72   Node:000 
PID:20640(db2agent (instance))   Appid:none 
oper_system_services  sqloopenp   Probe:36 
errno: 0000 001a                                     .... 
 
2001-10-10-19.06.56.163289   Instance:db2v72   Node:000 
PID:25338(db2bp)   Appid:*LOCAL.db2v72.011010234522 
oper_system_services  sqlowque   Probe:5 
0000 0004                                     .... 
 
2001-10-10-19.31.24.731619   Instance:db2v72   Node:000 
PID:20752(db2agent (BLOBTEST))   Appid:*LOCAL.db2v72.011010204440 
buffer_pool_services  sqlbSMSDirectWrite   Probe:99   Database:BLOBTEST 
SMS Tablespace 2(USERSPACE1) is full.  Detected on Container 0. 
2001-10-10-19.31.24.851541   Instance:db2v72   Node:000 
PID:20752(db2agent (BLOBTEST))   Appid:*LOCAL.db2v72.011010204440 
buffer_pool_services  sqlbSMSDirectWrite   Probe:825   Database:BLOBTEST 
DIA3612C Disk was full. 
ZRC=FFFFD60C

Interpreting return codes with no messages

To interpret what is written in the db2diag.log file, the file should be scanned for any error messages indicating that something is wrong with the system (either DIA or SQL errors). In most cases, there is an error message associated with the DIAxxxxx messages, but this is not always the case. Under the DIA error, there may be a RC=xxxxxxxx with a hexadecimal value. Once this value is converted to decimal, it may map to a SQL code. If it does not, then the hexadecimal value is a DB2 Internal Return Code. The SQL code values can be found in the Message Reference and the DB2 Internal Return Codes can be found in the Troubleshooting Guide. The general rule is to convert this value to decimal, then see if it equates to a SQL code. If it does not, then the hexadecimal value should map to a DB2 Internal Return Code. If it is not in the list in the Troubleshooting Guide, you will need to contact DB2 support.

If the error codes are in the form ffff nnnn , they can be interpreted as is. If they are in the form nnnn ffff , they must be byte-reversed before they are meaningful. To do this, switch the first four characters with the last four characters, and then the fifth and sixth characters with the seventh and eighth characters. For example, if the error code received is 0ae6 ffff , then it would be translated to ffff e60a . If the error received is ZRC=FFFFE60A in the above diagnostic file with no text message, the value should be converted to a decimal value.

Any hexadecimal conversion tool can be used. In this example, the calculator found under Windows NT ® or Windows 2000 ® was used. From Programs -> Accessories -> Calculator, follow the steps to convert ffffe60a to check if it is a SQL code or DB2 Internal Return Code:

Verify the calculator is set to View->Scientific
Select Hex
Enter 8 F's
Enter '-'
Enter 'FFFFE60A'
Enter '=' will result in 19F5
Enter '+'
Enter '1'
Enter '=' will result in 19F6
Select Dec will result in 6646

From a DB2 command window, the following can now be executed on the value 6646 to determine if it is a SQL code:

db2 ? SQL6646

Since this is not a valid SQL code, E60A can be located in the DB2 Internal Return Code table in the Troubleshooting Guide. In this case, E60A indicates that a file does not exist.

Taking a CLI trace

If developing or running a DB2 application that uses CLI, ODBC or SQLJ, then it uses the DB2 CLI driver. When developing this type of application, it is sometimes useful to look at what data your application is sending and what the DB2 server is returning to your application. This can be accomplished by taking a CLI trace. In order to take a CLI trace, the db2cli.ini file located under the \sqllib directory for Intel platforms and under the sqllib/cfg path for UNIX platforms will need to be updated to turn on tracing.

In the db2cli.ini file, tracing should be turned on either under the [COMMON] section so that it captures the CLI trace for all databases or under a specific database [DBNAME] . For example, to take a CLI trace for all databases, the following update would be made under db2cli.ini :

[COMMON] Trace=1 TraceFileName=d:\temp\clitrace.txt TraceFlush=1

If the trace is only going to be generated for the sample database, then the update would be made as follows:

[SAMPLE] Trace=1 TraceFileName=d:\temp\clitrace.txt TraceFlush=1

After the updates to the db2cli.ini file have been made, then the application can be executed again to capture the information. Once debugging of the application is completed, be sure to turn tracing off by setting Trace=0 as tracing consumes extra system resources.

Sample CLI trace

Below is a sample of a CLI trace file. Notice that the

--->

indicates what is being sent to the DB2 server by the application. The

<---

indicates what is being sent back by the DB2 server. This type of trace is helpful in determining whether the application is failing because of the way the application is sending the data across or the way data it is being returned from the DB2 server.

; 
[ Process: 1428, Thread: 632 ] 
[ Date & Time:          09-06-2001 09:31:27.000007 ] 
[ Product:              QDB2/NT 7.1.0.41 ] 
[ Level Identifier:     03010105 ] 
[ CLI Driver Version:   07.02.0000 ] 
[ Informational Tokens: "DB2 v7.1.0.41","n010426","WR21254" ] 
 
SQLAllocHandle( fHandleType=SQL_HANDLE_ENV, hInput=0:0, phOutput=&001289ec ) 
    ---> Time elapsed - 0 seconds 
 
SQLAllocHandle( phOutput=0:1 ) 
    <--- SQL_SUCCESS   Time elapsed - +1.771300E-002 seconds 
 
SQLSetEnvAttr( hEnv=0:1, fAttribute=SQL_ATTR_USE_LIGHT_OUTPUT_SQLDA, vParam=0, cbParam=0 ) 
    ---> Time elapsed - +5.200000E-005 seconds 
 
SQLSetEnvAttr( ) 
    <--- SQL_SUCCESS   Time elapsed - +1.386500E-002 seconds 
 
SQLAllocHandle( fHandleType=SQL_HANDLE_DBC, hInput=0:1, phOutput=&00128a44 ) 
    ---> Time elapsed - +1.070000E-004 seconds 
 
SQLAllocHandle( phOutput=0:1 ) 
    <--- SQL_SUCCESS   Time elapsed - +2.480000E-004 seconds 
 
SQLSetConnectAttr( hDbc=0:1, fOption=SQL_ATTR_AUTOCOMMIT, pvParam=NULL, iStrLen=-3 ) 
    ---> Time elapsed - +3.300000E-005 seconds 
 
SQLSetConnectAttr( ) 
    <--- SQL_SUCCESS   Time elapsed - +6.400000E-005 seconds 
 
SQLConnect( hDbc=0:1, szDSN="sample", cbDSN=-3, szUID="", cbUID=-3, szAuthStr="", 
cbAuthStr=-3 ) 
    ---> Time elapsed - +7.045000E-003 seconds 
( DBMS NAME="DB2/NT", Version="07.02.0000", Fixpack="0x23010105" ) 
 
( Application Codepage=1252, Database  Codepage=1252, Char Send/Recv Codepage=1252, 
Graphic Send/Recv Codepage=1252, iGraphic Codepage=1252 ) 
 
 
SQLConnect( ) 
    <--- SQL_SUCCESS   Time elapsed - +4.130200E-002 seconds 
( DSN=""SAMPLE"" ) 
 
( UID=" " ) 
 
( PWD="*" ) 
 
( DBALIAS="SAMPLE" ) 
 
SQLAllocHandle( fHandleType=SQL_HANDLE_STMT, hInput=0:1, phOutput=&0012ff34 ) 
    ---> Time elapsed - +4.455000E-003 seconds 
 
SQLAllocHandle( phOutput=1:1 ) 
    <--- SQL_SUCCESS   Time elapsed - +4.100000E-004 seconds 
 
SQLProcedureColumns( hStmt=1:1, szProcCatalog=Null Pointer, cbProcCatalog=0, 
szProcSchema="%",cbProcSchema=-3, szProcName="RICH1()", cbProcName=-3, 
szColumnName="%", cbColumnName=-3 ) 
    ---> Time elapsed - +3.800000E-005 seconds 
( StmtOut="SELECT PROCSCHEMA, PROCNAME, PARMNAME, TYPESCHEMA, TYPENAME, LENGTH, 
SCALE, PARM_MODE, ORDINAL, CODEPAGE FROM "SYSIBM".SYSPROCPARMS WHERE PROCNAME = 
'RICH1()' ORDER BY 1, 2, 9" ) 
 
 
SQLProcedureColumns( ) 
    <--- SQL_SUCCESS   Time elapsed - +4.703370E-001 seconds 
 
SQLBindCol( hStmt=1:1, iCol=2, fCType=SQL_C_CHAR, rgbValue=&001287d4, cbValueMax=129, 
pcbValue=&001287d0 ) 
    ---> Time elapsed - +4.500000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +6.700000E-005 seconds 
 
SQLBindCol( hStmt=1:1, iCol=4, fCType=SQL_C_CHAR, rgbValue=&0012886c, cbValueMax=129, 
pcbValue=&00128868 ) 
    ---> Time elapsed - +2.100000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.100000E-005 seconds 
 
SQLBindCol( hStmt=1:1, iCol=5, fCType=SQL_C_SHORT, rgbValue=&001287c8, cbValueMax=2, 
pcbValue=&001287c4 ) 
    ---> Time elapsed - +2.000000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.000000E-005 seconds 
 
SQLBindCol( hStmt=1:1, iCol=6, fCType=SQL_C_SHORT, rgbValue=&00128a34, cbValueMax=2, 
pcbValue=&00128a30 ) 
    ---> Time elapsed - +2.000000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.000000E-005 seconds 
 
SQLBindCol( hStmt=1:1, iCol=8, fCType=SQL_C_LONG, rgbValue=&0012ff40, cbValueMax=4, 
pcbValue=&0012ff3c ) 
    ---> Time elapsed - +1.900000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.100000E-005 seconds 
 
SQLBindCol( hStmt=1:1, iCol=10, fCType=SQL_C_SHORT, rgbValue=&00129ae8, cbValueMax=2, 
pcbValue=&00129ae4 ) 
    ---> Time elapsed - +1.900000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.100000E-005 seconds 
 
SQLBindCol( hStmt=1:1, iCol=18, fCType=SQL_C_LONG, rgbValue=&0012885c, cbValueMax=4, 
pcbValue=&00128858 ) 
    ---> Time elapsed - +1.900000E-005 seconds 
 
SQLBindCol( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.000000E-005 seconds 
 
SQLAllocHandle( fHandleType=SQL_HANDLE_STMT, hInput=0:1, phOutput=&0012879c ) 
    ---> Time elapsed - +1.490000E-004 seconds 
 
SQLAllocHandle( phOutput=1:3 ) 
    <--- SQL_SUCCESS   Time elapsed - +2.387000E-003 seconds 
 
SQLFetch( hStmt=1:1 ) 
    ---> Time elapsed - +9.400000E-005 seconds 
SQLFetch( ) 
    <--- SQL_NO_DATA_FOUND   Time elapsed - +2.153700E-002 seconds 
 
SQLFreeHandle( fHandleType=SQL_HANDLE_STMT, hHandle=1:1 ) 
    ---> Time elapsed - +5.800000E-005 seconds 
 
SQLFreeHandle( ) 
    <--- SQL_SUCCESS   Time elapsed - +5.780000E-004 seconds 
 
SQLPrepare( hStmt=1:3, pszSqlStr="CALL RICH1() )", cbSqlStr=-3 ) 
    ---> Time elapsed - +4.300000E-005 seconds 
( StmtOut="CALL RICH1() )" ) 
 
SQLPrepare( ) 
    <--- SQL_SUCCESS   Time elapsed - +2.100000E-004 seconds 
 
SQLExecute( hStmt=1:3 ) 
    ---> Time elapsed - +3.000000E-005 seconds 
( return=0 ) 
 
SQLExecute( ) 
    <--- SQL_SUCCESS   Time elapsed - +7.857780E-001 seconds 
 
SQLNumResultCols( hStmt=1:3, pcCol=&00128768 ) 
    ---> Time elapsed - +4.600000E-005 seconds 
 
SQLNumResultCols( pcCol=0 ) 
    <--- SQL_SUCCESS   Time elapsed - +4.700000E-005 seconds 
 
SQLFetch( hStmt=1:3 ) 
    ---> Time elapsed - +7.206000E-003 seconds 
 
SQLFetch( ) 
    <--- SQL_ERROR   Time elapsed - +1.956500E-002 seconds 
 
SQLMoreResults( hStmt=1:3 ) 
    ---> Time elapsed - +7.000000E-005 seconds 
( Unretrieved error message="CLI0115E  Invalid cursor state. SQLSTATE=24000" )

Taking a JDBC trace

If developing a JDBC application, then additional information can be retrieved by using a JDBC trace. This trace is also enabled in the db2cli.ini file.

The entries would once again be either under the [COMMON] section or under the specific database name. For example:

[COMMON] JDBCTrace=1 JDBCTracePathname=d:\temp JDBCFlush=1

If the option of TRACEFILENAME keyword in the db2cli.ini file is used for the fully qualified filename, then the trace files will be easy to locate by the name given to that keyword. If using the JDBCTRACEPATHNAME keyword, then the files will be created in the directory specified under that keyword. It will create a set of files with the name set to the process id of the application and an extension that is a sequence number for each unique thread. For example, it may create a file 65442.0, 65442.1, 65443.2 , etc. where 65442 is the process id of the application.

Sample JDBC trace

Below is a sample of a JDBC trace with the last error indicating that the database manager has not been started.

======================================================== 
|    Trace beginning on 2001-3-1 15:20:51.608 
======================================================== 
System Properties: 
------------------ 
java.runtime.name = Java(TM) 2 Runtime Environment, Standard Edition 
sun.boot.library.path = /usr/local/jdk/130/jre/lib/sparc 
java.vm.version = 1.3.0 
java.vm.vendor = Sun Microsystems Inc. 
java.vendor.url = http://java.sun.com/ 
path.separator = : 
java.vm.name = Java HotSpot(TM) Client VM 
file.encoding.pkg = sun.io 
java.vm.specification.name = Java Virtual Machine Specification 
user.dir = /export/home/pace2 
java.runtime.version = 1.3.0 
java.awt.graphicsenv = sun.awt.X11GraphicsEnvironment 
os.arch = sparc 
java.io.tmpdir = /var/tmp/ 
line.separator = 
java.vm.specification.vendor = Sun Microsystems Inc. 
java.awt.fonts = 
os.name = SunOS 
java.library.path = 
/usr/local/jdk/130/bin/../jre/lib/sparc/client:/usr/local/jdk/130/bin/../jre/lib/sparc: 
:/usr/local/jdk/130/bin/../jre/../lib/sparc:/usr/local/oracle/product/8.0.6/lib:/usr 
/local/OptimizeItDemo/lib:/usr/local/jdk/130/bin:/usr/lib:/usr/ucblib:/usr/openwin/lib: 
/usr/local/lib:/export/home/db2inst1/sqllib/lib:/usr/lib 
java.specification.name = Java Platform API Specification 
java.class.version = 47.0 
os.version = 5.6 
user.home = /export/home/pace2 
user.timezone = America/Chicago 
java.awt.printerjob = sun.awt.motif.PSPrinterJob 
file.encoding = ISO8859-1 
java.specification.version = 1.3 
user.name = pace2 
java.class.path = 
/export/home/pace2/classes/HRLoaderProps.jar:.:/export/home/pace2/customcode:/export/home 
/pace2/conv_classes:/export/home/pace2/classes/pace2.jar:/export/home/pace2/classes 
/pace2_ca_engine.jar:/export/home/pace2/classes/CompAdminRes.jar:/export/home/pace2 
/classes:/export/home/pace2/classes/Loader.jar:/export/home/pace2/classes 
/engwrapperclasses.zip:/export/home/pace2/classes/CMParallelAPI.zip:/export/home/pace2 
/classes/CompAdminExt.jar:/export/home/pace2/classes/xml4j.jar:/export/home/pace2/classes 
/xml.jar:/export/home/pace2/classes/classes111.zip:/opt/IBMdb2/V6.1/java/db2java.zip:/export 
/home/pace2/classes/Chart.jar:/export/home/pace2/classes/CMEngine.zip:/export/home/pace2 
/classes/ffchtml.jar:/export/home/pace2/classes/ffchtmlsamples.jar:/export/home/pace2 
/classes/IReport.zip:/export/home/pace2/classes/jgl3.1.0.jar:/export/home/pace2/classes 
/jndi.jar:/export/home/pace2/classes/repl.jar:/export/home/pace2/classes/scbb.jar:/export 
/home/pace2/classes/scbbconv.jar:/export/home/pace2/classes/SCFormula.zip:/export/home 
/pace2/classes/servlet.jar:/export/home/pace2/classes/SFDashboard.zip:/export/home/pace2 
/classes/UIObjects.zip:/export/home/pace2/classes/UIObjectsResources.zip:/export/home/pace2 
/classes/updatemd.jar:/export/home/pace2/classes/WebControlStore.zip:/export/home/pace2 
/classes/WebControlStoreRes.zip:/export/home/pace2/classes/WebGUIControls.zip:/export/home 
/pace2/classes/WebGUIFiles.zip:/export/home/pace2/classes/WebGUIResources.zip:/usr/local 
/OptimizeItDemo/lib/optit.jar:/export/home/db2inst1/sqllib/java/sqlj.zip:/export/home 
/db2inst1/sqllib/java/db2java.zip:/export/home/db2inst1/sqllib/java/runtime.zip:. 
java.vm.specification.version = 1.0 
java.home = /usr/local/jdk/130/jre 
user.language = en 
java.specification.vendor = Sun Microsystems Inc. 
java.vm.info = interpreted mode 
java.version = 1.3.0 
java.ext.dirs = /usr/local/jdk/130/jre/lib/ext 
sun.boot.class.path = 
/usr/local/jdk/130/jre/lib/rt.jar:/usr/local/jdk/130/jre/lib/i18n.jar:/usr/local/jdk 
/130/jre/lib/sunrsasign.jar:/usr/local/jdk/130/jre/classes 
java.vendor = Sun Microsystems Inc. 
file.separator = / 
java.vendor.url.bug = http://java.sun.com/cgi-bin/bugreport.cgi 
sun.cpu.endian = big 
sun.io.unicode.encoding = UnicodeBig 
user.region = US 
sun.cpu.isalist = sparcv8plus+vis sparcv8plus sparcv8 sparcv8-fsmuld sparcv7 sparc 
---------------------------------------- 
 
jdbc.app.DB2ConnectionTrace -> prepareStatement( SELECT COUNT(*) from MVSTAGEI.MVTPERSON) 
(2001-3-1 15:20:51.630) 
| Connection handle = 3 
| jdbc.app.DB2PreparedStatementTrace -> DB2Statement( con, 1003, 1007 ) 
(2001-3-1 15:20:51.631) 
| jdbc.app.DB2PreparedStatementTrace <- DB2Statement() [Time Elapsed = 0.019] 
| jdbc.app.DB2PreparedStatementTrace -> DB2PreparedStatement( " SELECT COUNT(*) from 
MVSTAGEI.MVTPERSON", con, 1003, 1007 ) (2001-3-1 15:20:51.672) 
| jdbc.app.DB2PreparedStatementTrace <- DB2PreparedStatement() [Time Elapsed = 0.0030] 
| jdbc.app.DB2PreparedStatementTrace -> DB2PreparedStatement( SELECT COUNT(*) from 
MVSTAGEI.MVTPERSON) (2001-3-1 15:20:51.676) 
| | Statement handle = 3:1 
| jdbc.app.DB2PreparedStatementTrace <- DB2PreparedStatement 
jdbc.app.DB2ConnectionTrace <- prepareStatement - Time Elapsed = 0.047 
 
jdbc.app.DB2PreparedStatementTrace -> executeQuery() (2001-3-1 15:20:51.685) 
| Statement handle = 3:1 
| jdbc.app.DB2PreparedStatementTrace -> execute2() (2001-3-1 15:20:51.687) 
| | 20: rowCount = -1 
| | 20: colCount = 1 
| jdbc.app.DB2PreparedStatementTrace <- execute2() [Time Elapsed = 0.0040] 
| jdbc.app.DB2ResultSetTrace -> DB2ResultSetTrace( stmt,1 ) (2001-3-1 15:20:51.691) 
| | Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace <- DB2ResultSetTrace 
jdbc.app.DB2PreparedStatementTrace <- executeQuery - Time Elapsed = 0.0060 
 
jdbc.app.DB2ResultSetTrace -> next (2001-3-1 15:20:51.693) 
| Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace -> clearIsList (2001-3-1 15:20:51.694) 
| | Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace <- clearIsList - Time Elapsed = 0.0 
jdbc.app.DB2ResultSetTrace <- next - Time Elapsed = 0.0020 
 
jdbc.app.DB2ResultSetTrace -> getObject( 1 ) (2001-3-1 15:20:51.697) 
| Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace -> getColType( 1, true ) (2001-3-1 15:20:51.698) 
| | Statement handle = 3:1 
| | 10: sqlType = 4 
| jdbc.app.DB2ResultSetTrace <- getColType - Time Elapsed = 0.0010 
| jdbc.app.DB2ResultSetTrace -> getObject( 1, 4 ) (2001-3-1 15:20:51.701) 
| | Statement handle = 3:1 
| | jdbc.app.DB2ResultSetTrace -> getObject( 1, 4, -1 ) (2001-3-1 15:20:51.702) 
| | | Statement handle = 3:1 
| | | jdbc.app.DB2ResultSetTrace -> getInt( 1 ) (2001-3-1 15:20:51.703) 
| | | | Statement handle = 3:1 
| | | | jdbc.app.DB2ResultSetTrace -> getInt2( 1 ) (2001-3-1 15:20:51.705) 
| | | | | Statement handle = 3:1 
| | | | | Return value = 8 
| | | | jdbc.app.DB2ResultSetTrace <- getInt2 - Time Elapsed = 0.0 
| | | jdbc.app.DB2ResultSetTrace <- getInt - Time Elapsed = 0.0030 
| | | jdbc.app.DB2ResultSetTrace -> wasNull (2001-3-1 15:20:51.708) 
| | | | Statement handle = 3:1 
| | | jdbc.app.DB2ResultSetTrace <- wasNull - Time Elapsed = 0.0 
| | jdbc.app.DB2ResultSetTrace <- getObject - Time Elapsed = 0.0060 
| jdbc.app.DB2ResultSetTrace <- getObject - Time Elapsed = 0.0080 
jdbc.app.DB2ResultSetTrace <- getObject - Time Elapsed = 0.012 
 
jdbc.app.DB2ResultSetTrace -> close (2001-3-1 15:20:51.711) 
| Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace -> close2( true ) (2001-3-1 15:20:51.712) 
| | Statement handle = 3:1 
| | jdbc.app.DB2ResultSetTrace -> clearIsList (2001-3-1 15:20:51.714) 
| | | Statement handle = 3:1 
| | jdbc.app.DB2ResultSetTrace <- clearIsList - Time Elapsed = 0.0 
| jdbc.app.DB2ResultSetTrace <- close2 - Time Elapsed = 0.0020 
jdbc.app.DB2ResultSetTrace <- close - Time Elapsed = 0.0040 
 
jdbc.app.DB2PreparedStatementTrace -> close() (2001-3-1 15:20:51.717) 
| Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace -> clearIsList (2001-3-1 15:20:51.718) 
| | Statement handle = 3:1 
| jdbc.app.DB2ResultSetTrace <- clearIsList - Time Elapsed = 0.0 
jdbc.app.DB2PreparedStatementTrace <- close - Time Elapsed = 0.0020 
 
DB2Driver - connect(jdbc:db2:pace2) 
 
jdbc.app.DB2ConnectionTrace -> connect( pace2, info, db2driver, 0, false ) 
(2001-3-1 15:20:51.758) 
| 10: connectionHandle = 11 
| jdbc.DB2Exception -> DB2Exception() (2001-3-1 15:20:52.342) 
| | 10: SQLError = [IBM][CLI Driver] SQL1032N  No start database manager command was issued.  
SQLSTATE=57019 
 
| |     SQLState = 08001 
| |     SQLNativeCode = -1032 
| jdbc.DB2Exception <- DB2Exception() [Time Elapsed = 0.0020] 
| jdbc.app.DB2ConnectionTrace -> close2() (2001-3-1 15:20:52.345) 
| | 10: connectionHandle = 11 
| | jdbc.DB2Exception -> DB2Exception() (2001-3-1 15:20:52.348) 
| | | 10: SQLError = [IBM][CLI Driver] CLI0106E  Connection is closed. SQLSTATE=08003 
| | |     SQLState = 08003 
| | |     SQLNativeCode = -99999 
| | jdbc.DB2Exception <- DB2Exception() [Time Elapsed = 0.0020]

Taking a DB2 trace

A DB2 trace is another trace that is often used by DB2 service for problem determination. In most cases, this information is more helpful to those in DB2 service. This section will go over how to capture this information. This information should only be gathered at the request of DB2 service, as this significantly impacts the performance of the DB2 server and in most cases, can only be analyzed by DB2 service.

In order to take a DB2 trace, you should shut down all your other applications and then turn the trace on with the following command:

db2trc on -l 4000000 -e -l -f db2trc.dmp

After turning the trace on, you can execute your problem scenario and a dump file, db2trc.dmp , will be created. This file can then be formatted to be viewed by DB2 service. To format the file, the following commands can be issued:

db2trc flw db2trc.dmp > db2trc.flw db2trc fmt db2trc.dmp > db2trc.fmt

The trace can then be turned off with the following command:

db2trc off

Sample DB2 trace

Below is a small snippet of a db2trc.fmt generated:

1 DB2 cei_entry     oper_system_services sqlogmblk (1.20.15.60) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 0 
2 DB2 cei_data      oper_system_services sqlogmblk (1.25.15.60) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 1 
 018c 4d38 0000 0064 0000 0000           ..M8...d.... 
3 DB2 cei_data      oper_system_services sqlogmblk (1.25.15.60) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 250 
 01be 0508                               .... 
4 DB2 cei_retcode   oper_system_services sqlogmblk (1.21.15.60) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 254 
 rc = 0 
5 DB2 fnc_retcode   SW- query graph      sqlnq_keych__new (1.31.60.384) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 254 
 rc = 0 
6 DB2 fnc_retcode   SW- query graph      sqlnq_qtb::cr8_key_col_struct (1.31.60.353) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 254 
 rc = 0 
7 DB2 fnc_entry     SW- query graph      sqlnq_pid::prd_eligibility (1.30.60.382) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 0 
8 DB2 fnc_retcode   SW- query graph      sqlnq_pid::prd_eligibility (1.31.60.382) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 254 
 rc = 0 
9 DB2 fnc_entry     SW- query rewrite    sqlnr_pid_bexp (1.30.64.34) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 0 
10 DB2 fnc_retcode   SW- query rewrite    sqlnr_pid_bexp (1.31.64.34) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 254 
 rc = 0 
11 DB2 fnc_entry     SW- query graph      sqlnq_pid::prd_eligibility (1.30.60.382) 
 pid 7066; tid 1; node 0; cpid 18395; sec 0; nsec 0; tpoint 0 

Below is a sample of the corresponding db2trc.fmt file: 
 
pid = 7066; tid = 1; node = 0; 
1 sqlogmblk  cei_entry 
2 sqlogmblk  cei_data     ... 
3 sqlogmblk  cei_data     ... 
4 sqlogmblk  cei_retcode  0 
5 sqlnq_keych__new fnc_retcode  0 
6 sqlnq_qtb::cr8_key_col_struct fnc_retcode  0 
7 sqlnq_pid::prd_eligibility fnc_entry 
8 sqlnq_pid::prd_eligibility fnc_retcode  0 
9 sqlnr_pid_bexp fnc_entry 
10 sqlnr_pid_bexp fnc_retcode  0 
11 sqlnq_pid::prd_eligibility fnc_entry

Conclusion

In conclusion, depending on the nature of the error you encounter, there are several pieces of diagnostic information that can be used when troubleshooting problems with your DB2 clients or servers. There will be cases when it will be necessary to contact DB2 service, but the intent of this document is to provide basic information about what tools are available for troubleshooting, when to use them, and how to generate the necessary files for diagnosing problems. For more detailed information on these tools and other troubleshooting tools available with DB2, see the DB2 UDB Troubleshooting Guide .

About the author

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=13279

ArticleTitle=Taking DB2 Traces

publish-date=05092002

author1-email=

author1-email-cc=