IBM Books

Troubleshooting Guide

Troubleshooting Tips

This section describes how to deal with some frequently encountered problems faced by developers of applications that access a DB2 server. It addresses the following topics:

In addition, see "Developing Applications in DB2 Extended Enterprise Edition".

Important: This section represents a small sampling of the information available from DB2 Customer Service. For a complete and up-to-date source of DB2 information, use the DB2 Product and Service Technical Library at http://www.software.ibm.com/data/db2/library/.

Developing Applications

When developing applications, ensure that:

[  ]
Embedded SQL statements are syntactically and semantically correct. If possible, try to step through SQL statements manually using the command line processor before embedding them in an application. This method ensures that you are using valid SQL statements that perform the required function.

[  ]
You have the authorities and privileges needed to execute the SQL statements.

[  ]
The application is not holding locks for excessive periods of time. (See "Diagnosing locking problems".)

[  ]
Your application is bound before you issue any static SQL statements. For information on the sqlabndx bind API, see the API Reference.

[  ]
You provide code to handle SQL errors in applications that connect to DB2.

A DB2 application should check the value of the SQL code or the SQL state element of the SQLCA data structure following each SQL statement. If it encounters a negative SQL code, it should take appropriate actions. For example, it should capture the SQL code and the SQLCA structure for later analysis.

For debugging purposes, the sqlaintp() API provides a formatted error message that you can display. For examples on how to use this API, refer to the Embedded SQL Programming Guide, and use the following sample programs: util.c for C, checkerr.cbl for COBOL, and util.f for FORTRAN.

Those negative SQL codes that result in a terminated connection are logged in the DB2 diagnostic log (db2diag.log). The database monitor application ID is also logged with the SQLCA on the server to enable you to identify the client and application.

[  ]
You provide enough memory for the variables you are using.

[  ]
You consider code page conversions.

If you are writing programs to handle data across various operating systems, you must consider the various code pages you will be using. See the Embedded SQL Programming Guide for details.

[  ]
If your applications use user-defined functions, stored procedures, distributed unit of work or multi-threading, you follow the guidelines in the Embedded SQL Programming Guide.

[  ]
There are no incompatibilities with previous releases.

If the application is legacy code for a previous version of DB2, review the lists of incompatibilities in the Administration Guide.

Compiling and Linking Applications

At compile time, ensure that:

[  ]
Your compiler environment is correct and all include files and environment variables are current. Refer to your compiler's documentation for details.

[  ]
Your compiler options are correct. See the Building Your Applications book for your platform.

[  ]
No compiler error messages are received. (Correct any syntax errors before proceeding.)

At link time, ensure that:

[  ]
The link options and link libraries are defined properly. For information, see the Building Applications guide for your platform.

Running Applications

When you experience problems at run time, ensure that:

[  ]
You have EXECUTE privilege on packages that contain embedded SQL statements, and the required authorities and privileges for all dynamic SQL statements.

[  ]
You have investigated any error messages you received. (For example, embedded SQL statements, DB2 API, or CLI statements may return messages.)

[  ]
You step through embedded SQL statements manually from the command line processor to find problems. This technique is recommended when statements in an application run but produce the wrong results.

[  ]
You solve all application errors. A debugger may assist you.

[  ]
The application is not holding locks for excessive periods of time. (See "Diagnosing locking problems".)

Diagnosing a suspended or looping application

After you start a query or application, you may suspect that it is either suspended (because it does not show any activity) or looping (because it shows activity, but no results are returned to the application). See "Responding to Suspensions and Loops" for information on how to restart your system in this situation.

After you restart your system, you may find the following tools helpful to diagnose problems with applications that cause loops or suspensions:

Using CLI/ODBC Applications

This section covers some common troubleshooting tips related to CLI/ODBC applications.

Developing ODBC applications

When developing ODBC applications, note the following:

[  ]
You must use the PATCH1 keyword to specify a workaround for any known problems with CLI/ODBC applications. Set this keyword by editing the db2cli.ini file, or (for OS/2, Windows 95, and Windows NT) by accessing the Service tab of the CLI/ODBC Settings in the Client Configuration Assistant. See the Installing and Configuring DB2 Clients online guide for information on this keyword.

[  ]
16-bit applications are supported by the 32-bit IBM DB2 ODBC driver shipped with Windows 95 and Windows NT clients, with the Microsoft ODBC Driver Manager performing the required conversion between the 16-bit application and the 32-bit ODBC driver.

16-bit applications shipped before 1995 use an old set of 16-bit Driver Manager DLLs that do not function well in the 32-bit environment. For the old set of DLLs, problems include:

If (and only if) you are experiencing these problems, replace the 16-bit ODBC Driver Manager DLLs in the system subdirectory of the Windows operating system directory with the files in DB2's SQLLIB\MSODBC16 subdirectory.

CLI/ODBC applications fail when connecting to server

Symptom
An application such as Microsoft Query, Microsoft Access or Lotus Approach fails when connecting to a DB2 server. The SQL1003N message is typically received or a General Protection Fault (GPF) occurs.

Action
Ensure that:

[  ]
The DB2 CLI/ODBC driver is configured correctly.

You can configure the driver by using the DB2 Client Configuration Assistant:

  1. Select the DB2 database alias you are configuring.

  2. Select the Settings push button. The Settings window appears.

  3. Select the Settings push button. The ODBC Data Source Configuration window appears.

  4. Select the Advanced push button. A window appears where you can set the configuration keywords. In particular, ensure that:

    • LONGDATACOMPAT=1 if you are accessing Large Object Binary (LOB) data.

    • UNDERSCORE=0 if there are underscore characters (_) in table names.

(Alternatively, edit the db2cli.ini file and ensure that the keywords appear in the list of parameters listed after the alias of the database you are connecting to. If they are not there, edit the file and add them on separate line.)

[  ]
You have compatible Microsoft ODBC driver manager files. (Updating these files can solve GPFs.)

Can't perform update

Some ODBC applications (such as Lotus Approach) require unique indexes to be defined for any tables that are updated. For these applications, you must create a primary key or a unique index on any table that you are trying to update, or you will receive an error that you cannot perform the operation.

For information, see the DB2/Lotus Approach web page at http://www.software.ibm.com/data/db2/db2lotus. (Select the question, "Why does Approach open my DB2 table in read only mode?")

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]