IBM Support

Collecting Data: Read First for DB2 JDBC Issues

Question & Answer


Question

How do I investigate issues with applications using the DB2 JDBC Type 2 Driver or IBM Data Server Driver for JDBC and SQLJ? What information should be collected when I experience a problem with with a JDBC application connecting to DB2? Collecting this information before calling IBM support will help you understand the problem and save time analyzing the data. This document outlines the preferred methods for collecting traces for JDBC issues involving DB2 on Distributed (Linux, Unix, Windows) platforms, and provides tips on preliminary problem analysis and searching for known issues. Collecting this documentation, and checking whether your issue has an existing solution, before calling DB2 support will allow the analyst to more quickly troubleshoot and help resolve your issue.

Cause

Situation
This section lists questions to consider regarding the conditions under which the problem occurred. Providing answers to these questions will help IBM to better unsterstand the problem environment and scenario, and narrow the scope of the investigation.

  • List the exact product versions and levels involved in the problem.
  • Have there been any recent changes to your environment (Hardware, OS, Software version, configuration)?
  • Has this problem occurred before?
  • How often does this problem occur?
  • Can the problem be reproduced, or is it intermittent?

Impact
This section lists questions to consider regarding the effects of the problem.
Providing answers to these questions will help IBM to better understand the current business impact 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 due to the problem occurring?

Answer

Files to collect
This section lists the required data to collect for problem analysis.

First, determine which type of driver you are using. If the driver is from DB2 it will be one of the following two types:

1) DB2 Legacy (CLI-based) JDBC driver

This driver is also known as the DB2 JDBC Type 2 Driver. It has been deprecated since version 8.
The connection string or error stack traceback will show classes of the following type:

    COM.ibm.db2.jdbc
This driver makes use of the archive file db2java.zip.
If the driver is of this type, see the document Collecting Data: Tracing for the DB2 JDBC Type 2 Driver
for details on tracing this driver.

2) DB2 Universal JDBC Driver (Type 2 or Type 4)

This driver is known as IBM Data Server Driver for JDBC and SQLJ

The connection string or error stack traceback will show classes of the following type:
    com.ibm.db2.jcc

This driver makes use of the archive file db2jcc.jar, and/or the archive files sqlj.zip (SQLJ applications) and db2jcc4.jar (JDBC 4.0 applications).

Check the driver class path in case it's still unclear as to which driver is being used. If the driver is of this type, see the document Collecting Data: Tracing the IBM Data Server Driver for JDBC and SQLJ for tracing instructions.

NATURE OF THE PROBLEM

Now that you have determined which driver you are using, let's ensure that you're able to accurately describe the nature of the problem.

What is the exact error message

Most JDBC error messages are in the form of an exception. Try to provide the exact exception you are receiving.

For example, this SQLException from a JCC trace:
    [2/26/09 10:24:40:249 CST] 00000085 logwriter     3   [jcc][Time:2009-02-26-10:24:40.249][Thread:WebContainer : 3][ResultSet@1f951f95] findColumn (GRP_ELENR_CD) called
    [2/26/09 10:24:40:349 CST] 00000085 logwriter     3   [jcc] BEGIN TRACE_DIAGNOSTICS
    [2/26/09 10:24:40:376 CST] 00000085 logwriter     3   [jcc][Thread:WebContainer : 3][SQLException@449e449e] java.sql.SQLException
    [2/26/09 10:24:40:377 CST] 00000085 logwriter     3   [jcc][Thread:WebContainer : 3][SQLException@449e449e] SQL state  = null
    [2/26/09 10:24:40:377 CST] 00000085 logwriter     3   [jcc][Thread:WebContainer : 3][SQLException@449e449e] Error code = -4460
    [2/26/09 10:24:40:385 CST] 00000085 logwriter     3   [jcc][Thread:WebContainer : 3][SQLException@449e449e] Message    = [jcc][10150][10300][3.51.90] Invalid parameter: Unknown column name GRP_ELENR_CD. ERRORCODE=-4460, SQLSTATE=null
    [2/26/09 10:24:40:385 CST] 00000085 logwriter     3   [jcc][Thread:WebContainer : 3][SQLException@449e449e] Stack trace follows
    [2/26/09 10:24:40:385 CST] 00000085 logwriter     3   com.ibm.db2.jcc.a.SqlException: [jcc][10150][10300][3.51.90] Invalid parameter: Unknown column name GRP_ELENR_CD. ERRORCODE=-4460, SQLSTATE=null
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.db2.jcc.a.wc.a(wc.java:576)
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.db2.jcc.a.wc.a(wc.java:60)
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.db2.jcc.a.wc.a(wc.java:103)
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.db2.jcc.a.cb.a(cb.java:1575)
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.db2.jcc.a.uk.a(uk.java:1569)
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.db2.jcc.a.uk.findColumn(uk.java:1558)
    [2/26/09 10:24:40:386 CST] 00000085 logwriter     3   at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.findColumn(WSJdbcResultSet.java:393)
    ...

In the above case, the exception occurred due to the query containing an invalid column name, so the query needs to be modified.

Here is an example from a Websphere Application Server SystemOut.log, for the DB2 JDBC Type 2 driver:
    [4/26/09 1:07:20:644 PDT] 0000000e ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl initialize FFDC0009I: FFDC opened incident stream file /usr/WebSphere/AppServer/profiles/Custom01/logs/ffdc/EPMCloneB1_0000000e_09.04.26_01.07.20_0.txt
    [4/26/09 1:07:21:783 PDT] 0000000e WSRdbManagedC W   DSRA0080E: An exception was received by the Data Store Adapter. See original exception message: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -1131, SQLSTATE: 38503, SQLERRMC: null
    at com.ibm.db2.jcc.b.SqlException.a(SqlException.java:238)
    at com.ibm.db2.jcc.b.hg.a(hg.java:345)
    at com.ibm.db2.jcc.b.hg.a(hg.java:329)
    at com.ibm.db2.jcc.b.SqlException.getMessage(SqlException.java:203)
    at java.lang.Throwable.getLocalizedMessage(Throwable.java:131)
    at java.lang.Throwable.toString(Throwable.java:275)
    at java.lang.Throwable.printStackTrace(Throwable.java:211)
    at com.ibm.ws.ffdc.CallStack.callStack(CallStack.java:229)
    at com.ibm.ws.ffdc.CallStack.getCallStack(CallStack.java:91)
    at com.ibm.ws.ffdc.FFDCFilter.filterEngine(FFDCFilter.java:430)
    at com.ibm.ws.ffdc.FFDCFilter.processException(FFDCFilter.java:201)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteQuery(WSJdbcPreparedStatement.java:882)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:559)
    ...

In the above example, the exception was caused by SQLCODE -1131 (SQL1131N), abnormal termination of a stored procedure. Collecting db2support for the server and reviewing the instance's db2diag.log may shed light on the cause.


Is the application crashing?

In the event of a JDBC application crash, there should be a javacore.txt file generated (on platforms other than Solaris). Open up the javacore.txt and look at the failing library to determine which component is causing the problem.
A core file may also be generated. On Windows, a Dr. Watson log file entry as well as a user dump file may be generated.

If the application runs in the Websphere environment, it is recommended to refer to the appropriate MustGather doc for crash.

Is the application hanging?

Try to attach a debugger to the hanging process.
e.g. on AIX,
    dbx <pid>

then type,
    where

This would provide a stack traceback for the hanging code.

For data collection and analysis of application hang issues, it is recommended to refer to appropriate documentation for your product on how to respond to a hang or non-responsive application. A search of 'MustGather hang <product> <Operating System>' will return appropriate results for many IBM products.


HINTS
  • It is recommended to search the diagnostics collected, to confirm that the error message or condition being experienced, was captured. Doing this before sending files to IBM will reduce time and duplication of effort.
  • Use the IBM Technical Support site and other search tools to see whether the error message has already been discovered and is a common problem that has been documented. IBM also provides a search toolbar which can be installed to make problem searches quicker and easier.
  • Concurrent traces (application-level, and driver traces) should be taken if feasible to avoid potential duplication of effort if it's determnined that application traces are necessary. If in doubt, consult IBM before tracing if the problem is not easily reproduceable or tracing cannot readily be performed.
  • Check the DB2 product level. For a DB2 server or client, this can be accomplished using the 'db2level' command.For an application server using the Type 4 driver, with DB2 otherwise uninstalled, this can be accomplished using the command:

    • java -classpath <path>/db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version

    The JCC version can be mapped to the corresponding DB2 fix pack level here:
    DB2 JDBC/JCC Driver Versions

    In most cases, if you are at a lower fix pack level there is a good possibility that the problem you are trying to fix is already in a recent fix pack. If possible, apply the latest fix pack first and attempt to see if that resolves the problem.
      You can download fixes for DB2 from this site.
  • Look at the 'Troubleshooting and Support' section in the DB2 Information Center. Check the message reference for detail on specific error messages. Typically the message detail provides explanation for what the message means and potential reasons it occurred. Often the documentation will suggest a potential resolution.

Overall, try to be clear on what error message you are trying to diagnose. Be comprehensive and consistent in your description and try to gather the necessary information listed above to aid the investigation. This will save you and the support team time and help to make this a more pleasant experience.

What to do next Once you have collected the preceding information, you can begin analysing the data 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.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - JDBC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21388639

Page Feedback