Question & Answer
Collecting data for DB2® for Linux, UNIX®, and Windows® CLI Applications. Gathering this information before calling IBM® support helps familiarize you with the troubleshooting process and saves you time.
- Is this a reoccurring issue or the first time experiencing the issue?
- Have there been any recent changes to the instance or database cfg files?
- Have there been any recent changes to your operating system?
- Are you able to reproduce this behavior? If so, can a testcase be provided?
This section lists questions regarding the effects of the problem.
- Is this a production, development, or test environment?
- How many users are affected by this problem?
- What is the business impact of this problem?
- Are there other repercussions to the problem occurring?
Information to Collect
This section lists the required information DB2 Support would like to have gathered:
The most useful information for diagnosing a problem with a CLI/ODBC application is the CLI trace. CLI trace shows what the application is doing when the problem occurs and this will be very helpful when diagnosing the problem. Sometimes the problem can be from the server or the internal layers lower than DB2 CLI APIs, so it will be better to collect both CLI and DB2 trace simultaneously. DB2 trace will trace every component from the API layer to the lowest communication layer.
To collect the CLI and DB2 trace, you can follow the steps below:
1) Turn on CLI trace:
- db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACE 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACEFLUSH 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACECOMM 1
db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACETIMESTAMP 3
db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACEPATHNAME <existing path>
where the <existing path> must be an existing path that does not contain any files and that should be writable by the userid that runs the application. For example, C:\clitrace for Windows or /home/clitrace for UNIX/Linux. You can verify the above keywords settings with the following command:
- db2 GET CLI CFG FOR SECTION COMMON
2) Turn on the DB2 trace:
- db2trc on -t -f db2trc.dmp
Note: For CLI traces to be generated properly the application needs to be shut down and restarted. This is true for applications running in an application server environment as well. In that specific case the application server should be recycled for CLI traces to be collected properly.
4) Turn off the DB2 trace and CLI trace:
- db2trc off
db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 0
db2trc fmt db2trc.dmp db2trc.fmt
db2trc fmt -c db2trc.dmp db2trc.fmtc
db2trc flw -t db2trc.dmp db2trc.flw
- db2support <output path>
Note: Turning on CLI trace can cause significant performance impact on all the CLI applications running on the same system, so you need to turn off the CLI trace as soon as the problem reproduces. Minimizing the number of running CLI applications can reduce the performance impact and the size of CLI trace files.
If the problem is not reproducible on demand or within a short period of time, turning on the CLI trace can cause the disk space to be filled up by the large CLI trace files that are created. In this case, you may consider turning on the CLI trace dynamically at a time close to the expected occurrence of the problem. To turn on the trace dynamically, you can set the CLI keyword TraceRefreshInterval to a positive integer. This keyword sets the frequency at which the Trace and TracePIDList keywords are re-read from the [COMMON] section of the db2cli.ini file. The positive integer value set to the keyword indicates the number of seconds between re-reads of the file. You can add this keyword to the above CLI keyword list for collecting CLI trace with Trace = 0 and restart the CLI application. You can then turn on the CLI trace later by setting the TRACE keyword to 1. Note, setting TraceRefreshInterval to a small integer may cause a performance problem because DB2 CLI will frequently check the db2cli.ini file for the possible changes made to either Trace or TracePIDList keyword.
The TracePIDList keyword can be set for tracing specific problematic processes of DB2 CLI applications.
For example, the following command will set this keyword to 60 seconds, meaning that DB2 will re-read the setting of TRACE and TRACEPIDLIST every 60 seconds onwards and any changes made to CLI keyword TRACE and TRACEPIDLIST will be effective within the next 60 seconds:
db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACEREFRESHINTERVAL 60
<restart CLI application>
To disable dynamic CLI tracing, you can simply set TraceRefreshInterval to zero and restart your CLI application:
db2 UPDATE CLI CFG FOR SECTION COMMON USING TRACEREFRESHINTERVAL 0
<restart CLI application>
Submitting information to IBM Support
Once you have collected your information, you can begin Problem Determination through the product Support web page, or simply submit the diagnostic information to IBM support. Use the document below for submitting information to IBM Support.
Submitting diagnostic information to IBM Technical Support for problem determination
16 June 2018