Troubleshooting Guide

Troubleshooting Tips

This chapter describes some problems frequently encountered 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 Support. DB2 Customer Support 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 on the Web, at http://www-4.ibm.com/software/data/db2/library/ (Note that this information is in English only.)

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, see 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 SQLCODE or the SQLSTATE element of the SQLCA data structure following each SQL statement. If it encounters a negative SQLCODE, it should take appropriate actions. For example, it should capture the SQLCODE 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, see the Application Development Guide, and use the following sample programs:

Negative SQLCODEs that result in a terminated connection are logged in the DB2 diagnostic log (db2diag.log) file. 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 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: Planning.

Compiling and Linking Applications

At compile time, ensure that:

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

[  ]
Your compiler options are correct. See 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, see the instructions for you operating system in the Application Building Guide.

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-4.ibm.com/software/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 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:

You might also consider modifying your application so that it writes to an application log file to probe where the code is suspended or looping.

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 compatibility 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 on 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. Click Properties to display the Database Properties window.
  3. Click Settings to display the CLI/ODBC Settings window.
  4. Click Advanced. 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.
Note:Customizing the CLI Driver may cause some applications to fail, including some DB2 Java-based utilities. It is a good practice to create multiple database aliases for different CLI/ODBC/JDBC-based applications so that the driver settings can be modified independently for each application.

[  ]
The DB2 CLI/ODBC driver is optimized for Lotus Approach, Microsoft Access, or Visual Basic.

You can optimize the driver using the DB2 Client Configuration Assistant:

  1. Select the DB2 database alias you are configuring.
  2. Click Properties to display the Database Properties window.
  3. Click Settings to display the CLI/ODBC Settings window.
  4. Select the Optimize push button. A window appears where you select one of applications shown above. The CLI driver will be automatically configured for that application using pre-defined patch values and configuration options.

[  ]
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 may have been 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 happens 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 given even though:

  1. 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 COLUMN1.

  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" also returns 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 uppercase.

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 quotation marks 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 1 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 "Error popup" parameter 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, see "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 Properties to open the Database Properties window.
  4. Click Settings to open the CLI/ODBC Settings window.
  5. Click Advanced.
  6. Click 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, see the Application Development Guide and the DB2/Lotus Approach Web page at http://www-4.ibm.com/software/data/db2/db2lotus/ Enter 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.

Notes:

  1. The db2ocat tool will not prevent an application from querying the system tables if it explicitly requests preparing an SQL statement.

  2. The db2ocat tool only affects driver-generated queries against the system tables that use the CLI APIs like SQLTables, SQLColumns, and SQLFunctions. It will not prevent the optimizer from accessing system catalogs to generate an access plan.

Using db2ocat for Performance Improvement

To improve performance using the db2ocat utility, do the following:

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 API calls during the "information gathering" stage after the database is first connected.

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

  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 separately 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. See the compiler documentation for information on using the supplied debugger.

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

Error Received When Calling a Stored Procedure

Symptom
Received an error (Error: Expected stored procedure name. Found: ?) when attempting to call a stored procedure where the name of the stored procedure is in a host variable.

Possible Cause
The use of host variables for the procedure name is not an SQL92 standard. SQLJ is based on SQL92 standards and therefore does not support the use of host variables in the CALL statement for the name of the procedure.

Action
You cannot use host variables in the CALL statement for the name of the procedure.

[ Top of Page | Previous Page | Next Page ]