IBM Books

Troubleshooting Guide

Troubleshooting Tips

This chapter describes how to deal with some frequently encountered problems faced by developers of applications that access a DB2 server. These tips to prevent or overcome problems with applications were created from information available from DB2 Customer Service. DB2 Customer Service hears about customer problems and concerns, and so is ideally suited to provide the tips found here. This chapter presents the following topics:

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

Important: 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. For more information, see Diagnosing locking problems.

[  ]
Your application is bound before you issue any static SQL statements. For information on the sqlabndx bind API, refer to the Administrative 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 Application Development Guide, and use the following sample programs:

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. Refer to the Application Development 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 Application Development Guide and CLI Guide and Reference manuals.

[  ]
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, Design and Implementation.

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. Refer to the Application Building Guide book for information specific to a supported compiler.

[  ]
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, refer to the Application Building Guide book 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 looked for information about Java related applications, JDBC (Java Database Connectivity) and SQLJ (Embedded SQL for Java), on the IBM DB2 and Java page at http://www.software.ibm.com/data/db2/java.

[  ]
You have investigated any error messages you received. For example, embedded SQL statements, DB2 API, or CLI statements may return error 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, for more information.

[  ]
If you are using CLI/ODBC/JDBC applications, review the considerations in the section Using CLI/ODBC/JDBC Applications.

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/JDBC Applications

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

Developing CLI/ODBC/JDBC Applications

When developing CLI/ODBC/JDBC applications, note the following:

[  ]
You must use the PATCH1/PATCH2 keyword to specify a work-around for any known problems with CLI/ODBC/JDBC applications. Set this keyword by editing the db2cli.ini file or (for OS/2, Windows 95, Windows 98, and Windows NT) by clicking the Service tab of the CLI/ODBC Settings in the Client Configuration Assistant. See the Installation and Configuration Supplement guide for information on this keyword.

CLI/ODBC/JDBC 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 Properties push button. The Database Properties window appears.
  3. Select the Settings push button. The CLI/ODBC Settings 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.

Mixed case names created in ODBC applications causing problems

Symptom
Table or field names in DB2 are in mixed case. They could created in one type of application such as an ODBC application like Lotus Approach, and then accessed using another method such as the DB2 Command Line Processor. For example, the problem with accessing tables could be seen when the following error message is returned from an SQL statement such as: SELECT * FROM table1 
   SQL0204N "USERID.TABLE1" is an undefined name. SQLSTATE=42704

This error message is given even though the table exists, and is shown in the Control Center list of tables or in a LIST TABLES command.

For example, the problem with accessing columns could be seen when the following error message is returned from an SQL statement such as: SELECT column1 FROM table2 

   SQL0206N "COLUMN1" is not a column in an inserted table, updated table, or any
   table identified in a FROM clause or is not a valid transition variable for
   the subject table of a trigger. SQLSTATE=42703

This error message is give even though:

  1. The column "column1" exists, and shows up in the Control Center

    or

    the SQL statement: SELECT tabname, colname FROM SYSCAT.COLUMNS WHERE tabname='table2' returns the appropriate information about the "column1" column.

  2. The SQL statement: SELECT * FROM table2 returns all the columns, including the "column1" column.

This problem can also appear if the object is created with a name that uses all uppercase characters, and the object name is specified using mixed case and quotation marks. For instance, the statement: SELECT * FROM "Org" would also return SQL0204N "USERID.Org" is an undefined name. SQLSTATE=42704.

Possible Cause
The DB2 object name, including table and column names, is in mixed case. The object name must be specified in the mixed case, enclosed in double quotes, or DB2 will convert them to upper case.

This affects all SQL statements, including SELECT statements as well as CREATE TABLE and DROP TABLE statements.

Many ODBC applications, including Lotus Approach, preserve the case of table and column names. If you specify the table name and the column name in mixed case when the table is created in the ODBC application then the following SQL statement is sent to DB2: 

   CREATE TABLE "test1" ("col1" CHAR(5))

In this case DB2 preserves the lower case name because of the double quotation marks around the table and column names. Any attempt to access this table without enclosing the name with double quotation marks will fail.

Objects in the Control Center with mixed case names are enclosed with double quotation marks.

Action
If you are using an ODBC application to create DB2 tables then specify the table and column names in upper case. They can then be accessed either with or without the double quotation marks.

Enclose all object names in double quotes if you need to work with tables and columns in mixed case.

For instance, if the following table is created, both SELECT statements would succeed: 

   CREATE TABLE "User1"."Table1" ("column1" CHAR(5))
   SELECT * FROM "User1"."Table1"
   SELECT "column1" FROM "User1"."Table1"

Several error messages and warnings are seen when running ODBC applications

Symptom
Several dialog boxes appear with error messages or warnings when running ODBC (or CLI) applications such as Lotus Approach, Microsoft Access, or Powerbuilder. The title of each dialog box indicates which function returned the error or warning.

Possible Cause
These errors and warnings are a normal part of the communication that occurs between DB2 and the applications. They do not necessarily indicate a problem with DB2 or the application.

The errors and warnings are seen when the CLI/ODBC configuration keyword POPUPMESSAGE is set to one in the db2cli.ini file. This keyword is also accessible in the CLI/ODBC Settings notebook which is available from the Client Configuration Assistant. The parameter "Error popup" is located on the Service tab.

Action
The CLI/ODBC configuration keyword POPUPMESSAGE should be set to zero for production systems.

For more information on the POPUPMESSAGE keyword, and the db2cli.ini file, refer to "POPUPMESSAGE" in the "DB2 CLI/ODBC Configuration Keywords Listing" section of the CLI Guide and Reference manual.

To configure the DB2 CLI/ODBC driver using the DB2 Client Configuration Assistant (if available on your operating system):

  1. Start the DB2 Client Configuration Assistant.
  2. Select the DB2 database alias you want to configure.
  3. Click on the Properties push button. The Database Properties window opens.
  4. Click on the Settings push button. The CLI/ODBC Settings window opens.
  5. Click on the Advanced push button.
  6. Click on the Service Tab and select the "Error popup" parameter.
  7. Ensure that there is no check mark next to Popup error message in the Value section.

Cannot 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, refer to the Application Development Guide and 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?"

Using SQLJ Applications

To run Java stored procedures or user-defined functions (UDFs) on an OS/2 server, you must install DB2 on an HPFS drive on the server. This permits the stored procedure or UDF ".class" files to be placed in the %DB2PATH%\function directory without truncating the filename extension.

To build SQLJ programs, you must turn off the just-in-time compiler feature of the Java Development Kit using the following command: 

SET JAVA_COMPILER=NONE

Note:SQLJ source files may include an end-of-file character (EOF) causing the SQLJ translator 'sqlj' to fail with an exception similar to the following: 

Cursor.sqlj:122.2: Error: Java Parsing. Encountered: \u001a
Expected: > ; "abstract" ...; "public" ...; "interface" ...; 
"#sql" ...; ";" ...;
Total 1 error.

To remove the offending EOF, open the SQLJ source file in a text editor that does not automatically append an EOF, modify the file by adding a blank line to the end of the file, and resave the file.

Using the db2ocat Tool

The DB2 ODBC Catalog Optimizer tool creates optimized catalog tables for ODBC, CLI, and JDBC access.

The tool results in fewer locks on base system catalog tables for the following reasons:

Dealing with Performance Degradation
Many applications written using ODBC or DB2 CLI interfaces make heavy use of the system catalog. While this does not usually present a problem for databases with a small number of database objects, it can lead to performance problems when using these applications with larger DB2 databases. This performance degradation can be attributed to two main factors:

Performance Enhancement with the db2ocat Tool
The DB2 ODBC Catalog Optimizer Tool solves the problem of performance degradation by creating alternative catalog tables that are optimized for ODBC access by a particular application. This utility helps database administrators to identify the subset of catalog information that is needed for a particular application, and it creates an ODBC optimized catalog that the application can use. As a result, few locks are placed on the base system catalog tables, and catalog query times can be reduced substantially, together with the amount of data returned as a result of such queries.

Using db2ocat for Performance Improvement

Step 1: Create an ODBC optimized catalog(s) on the host

Step 2: Configure each workstation to point to the right ODBC-optimized catalog(s).

Step 3: Keep the ODBC optimized catalog(s) current.

Notes:

  1. DB2 Universal Database provides several CLI/ODBC initialization keywords that can be used to limit the amount of data that is returned by the initial PAI calls during the "information gathering" stage after the database is first connected.

  2. CLISHEMA keywords in db2cli.ini file should be set to an ODBC catalog schema. The CLISCHEMA option indicates an alternative schema, tables, and index set to be searched instead of the SYSIBM (or SYSTEM, QSYS2) schemas when the DB2 CLI and ODBC Catalog function calls are issued to obtain catalog information.

  3. If you specify CLISCHEMA=`SERGE', the internal CLI/ODBC API calls that normally reference system tables will reference the following user tables instead:

Stored Procedures

A stored procedure is a type of application stored on a database server. A calling procedure (or client application) is run on the database client that calls the stored procedure using an available API method.

The stored procedure, after being called or invoked by the client application, runs on the database server and returns any results to the client application.

The calling procedure and stored procedure can be written in any language supported by DB2 on your operating system. They do not need to be written in the same language.

To debug a stored procedure, run the stored procedure in debug mode. Testing of the stored procedure can be done separate from the calling application program. You can use the debugger supplied with your language compiler to debug local FENCED stored procedures as you would with other applications. Refer to the compiler documentation for information on using the supplied debugger.

Refer to the Application Development Guide in the "Resolving Problems" section of the "Stored Procedures" chapter for more information on troubleshooting stored procedures.

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

[ DB2 List of Books | Search the DB2 Books ]