IBM Books

Troubleshooting Guide

Troubleshooting Tips

This section describes how to deal with some frequently encountered problems faced by users working with DB2 Enterprise - Extended Edition. It addresses the following topics:

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, refer to the DB2 Product and Service Technical Library at http://www.software.ibm.com/data/db2/library/.

DB2 Universal Database Enterprise - Extended Edition Will Not Install

Symptom
The DB2 Universal Database Enterprise - Extended Edition product will not install.

Action
Ensure that:

[  ]
The DB2 product directory on each node has enough free disk space.

[  ]
All prerequisite software products are installed on the system.

[  ]
The same version of the product is not already installed on the system.

Severe Error Occurs on System

There are two distinct types of severe error:

The severe error SQL code -1224 (SQL state 55032) can occur for a variety of reasons. If you receive this message, check the SQLCA to determine which node failed, and then the db2diag.log file file for additional details. For information, see Determining Failing Node and Interpreting the db2diag.log file.

Note: In the event of a severe error where multiple machines were involved, you may find diagnostic information in their syslog files rather than in the db2diag.log file file, if they were not able to obtain an NFS lock on the db2diag.log file file.

Determining Failing Node

To find out which node failed, check the db2diag.log file on the coordinating node for the application. If a node returns an error or warning, the node number is identified in the SQLERRD(6) field of the SQLCA. This number corresponds to the node number in the db2nodes.cfg file. (If an SQL statement or API call is successful, the node number in the SQLERRD(6) field is that of the coordinator agent.)

For more information on the db2diag.log file file and SQLCA structures, see Interpreting the db2diag.log file.

Database Manager Will Not Start or Hangs

db2start fails

Symptom
The db2start command fails.

Action
Ensure that:

[  ]
The product was successfully installed.

[  ]
Your user ID has SYSADM, SYSCTRL, or SYSMAINT authority. Refer to the Administration Guide, Design and Implementation for details.

[  ]
You have the same user ID, group identifier, and password set for the instance owners on all nodes.

[  ]
You can use the rsh command (on UNIX platforms) from the DB2 instance ID on all nodes defined in the db2nodes.cfg file. (To do this, add an entry for the node in the $HOME/.rhosts file or the hosts.equiv file. Ensure that the file permissions on these files allow read access for all users.)

[  ]
If you are using the high-performance switch (HPS) on RS/6000 SP systems, ensure that it is up on all nodes. See the description for spmon in Commands for DB2 Enterprise - Extended Edition.

[  ]
The db2nodes.cfg file, located in the sqllib subdirectory under the $HOME directory, contains the correct information for all nodes defined in the system. Ensure that the host names and net names are valid.

[  ]
FCM communication is properly enabled. You must create an entry for each DB2 logical port in the /etc/services file. Refer to the DB2 Enterprise - Extended Edition for Windows NT Quick Beginnings or the DB2 Enterprise - Extended Edition for UNIX Quick Beginnings for details.

[  ]
The value for the DB2INSTANCE environment variable is the same for every node and it matches the instance name that you are trying to start.

[  ]
You have the appropriate permissions. Check the permissions of the tmp subdirectory under the sqllib subdirectory and, if required, update them so that the instance ID has write permission for the directory. If the permissions are not correct, a problem may have occurred during installation and setup of the system.

[  ]
You have checked the contents of the db2diag.log file for possible reasons for the db2start failure.

db2start does not respond

Symptom
The db2start command invokes no response.

Action
Ensure that the $HOME directory of the instance owner is NFS-mounted across all nodes, and that NFS is running. If the lockd and statd daemons are not running, db2start may hang. To start the daemons, execute rc.nfs, and ensure that this command is in the inittab subdirectory under the etc subdirectory.

Nodes do not start or are very slow

Symptom
Some nodes cannot be started, or run extremely slowly.

Action
Ensure that:

[  ]
You installed the DB2 Enterprise - Extended Edition product on all nodes.

Although you can install the product on an NFS-mounted file system in a RISC/6000 cluster, performance will be compromised. We recommend that you install the product on each node.

[  ]
All nodes are installed at the same level of DB2 Enterprise - Extended Edition.

[  ]
NFS is running and there are enough NFS daemon (nfsd) processes assigned on the machine where the home directory of the instance owner resides.

Cannot Create Database

Symptom
A database cannot be created.

Action

Determine which node is experiencing the problem. The failing node number is stored in the sqlerrd(6) field of the SQLCA returned with the CREATE DATABASE command. Once you have determined the failing node, ensure that:

[  ]
Permission on the database directory path is correct.

The instance must have permission to write into the database directory path.

[  ]
You are creating the database on a path that exists on all nodes.

[  ]
The file systems are mounted.

The file systems for the database may have been unmounted. Mount all file systems again, and then try to create the database. You should check for unmounted file systems on all nodes.

[  ]
There is enough disk space.

You may have created a small test database and decided that you needed very little disk space. However, when a database is created, a certain amount of disk space is required for the default table spaces and default logs. Refer to the Administration Guide, Design and Implementation for details.

Commands Cannot Be Issued

Symptom
DB2 or operating system commands are not recognized.

Action
Ensure that:

[  ]
Your DB2 Enterprise - Extended Edition system is properly installed.

[  ]
You have sufficient authority to run the commands.

[  ]
If the home file system of the instance owner ID is mounted over Ethernet or a high performance switch (on a RS/6000 SP machine), Ethernet or the switch are running.

To determine if this is the problem, log onto the system as root and try to access a file in the instance owner's home file system, or use the netstat command to determine the status of the high performance switch.

Problems Backing Up On Database Partitions

Symptom
Problems have been encountered while attempting to backup data on all of the database partitions.

Action
If you want to use the db2_all command to back up a list of database partitions, you must first back up the catalog node for the database. Following taking this back up, you can back up the remaining database partitions. For example, after the catalog node is backed up, you can issue the following db2_all command to back up the remaining database partitions: 
   db2_all '<<-n< db2 backup db <database-alias>'

Where n is the node number of the catalog node.

Problems with LOAD and with Splitting and Loading Data

For more information on using the LOAD utility or on using the AutoLoader to split data and load it on a node, refer to the Data Movement Utilities Guide and Reference.

For more information on the db2atld command, type db2atld -h from the misc subdirectory under the sqllib subdirectory.

Data not loaded

Symptom
LOAD does not load data

Action
Ensure that:

[  ]
LOAD was run by a user with the correct authority.

Users in the same group as the instance owner who have SYSADM or DBADM authority can execute LOAD.

[  ]
The table being loaded is not already being used by another application.

LOAD cannot share tables, so it will not execute until it can lock all required tables. Determine why the tables are already locked, and ensure that the locks are released.

[  ]
LOAD was started on all nodes.

You must execute LOAD on all nodes for data to be loaded in parallel. If you have a script that sends a shell to all nodes, ensure that the LOAD execution is not serialized by the remote shell.

To check if LOAD failed on a node, look at the message files:

  • If you issue the LOAD command directly, you can specify the name and location of a message file using its MESSAGES option.
  • If you use the db2atld command, message files are located in the current working directory. There is a file for each node, and each file is named load_log.nnn, where nnn is the node number as specified in the db2nodes.cfg file.

[  ]
The format of the data is correct.

Refer to the Command Reference for more information on using correct data formats. Note that:

  • Data cannot contain non-delimited blanks. LOAD processes non-delimited blanks in a delimited ASCII (DEL) file as null, and cannot load this data into a non-nullable column. You must use a string delimiter for blank columns.

    For non-delimited data, use the null indicator to indicate columns with null values.

  • Data must contain decimal points in the correct position.

    For data being loaded from MVS systems, you must explicitly put the decimal points in the data before loading it. Otherwise, they are assumed to be at the end of the column.

All rows rejected

Symptom
LOAD rejected all rows.

Possible Cause
Column definitions are not correct.

Action
When using METHOD L, ensure that the data column specification is correct. Shifting columns can cause truncation errors or data that does not match the table column definition.

Rows not loaded

Symptom
LOAD completed but no rows are loaded.

Possible Cause
LOAD rejected all rows.

Action
Check the db2load file in the temporary directory where you ran db2atld to see if all the rows were rejected. If some rows were loaded, refer to the Data Movement Utilities Guide and Reference.

Data is not split

Symptom
The db2atld program completed successfully, but the data is not split.

Possible Cause
The db2atld program was set only to analyze the data.

Action
Check the AutoLoader configuration file and ensure that the Mode parameter is not set to analyze data. This option does not split data. It only analyzes the data and suggests a new partitioning map. Refer to the Data Movement Utilities Guide and Reference for details.

Data is not split correctly

Symptom
The db2atld program completed successfully, but the data is not split correctly.

Action
Ensure that:

[  ]
Binary data is not in a character column.

The db2atld program cannot detect binary values in a column of any data type, unless the BINARYNUMERICS or PACKEDDECIMAL options are specified on the load command.

[  ]
The SplitNodes and OutputNodes parameters are set correctly.

Otherwise, output data files may not be correct. Refer to the Data Movement Utilities Guide and Reference for details.

Data does not match the select count

Symptom
The data that is loaded does not match the select count.

Action
Ensure that:

[  ]
The data is split correctly by your conversion program.

The db2atld program creates a header file to prevent data from being loaded to the wrong node. The header information is checked by LOAD.

If you use a conversion program to convert binary columns to the character format, the conversion may not be the same as that done by the db2atld program. Data will be split for the correct node, but during the conversion data may be converted to a different value and may not be hashed the same way.

[  ]
The partitioning columns match the header.

If the data is split using one set of partitioning keys, and is loaded into a table that was partitioned on other columns, the LOAD operation will fail. Partition column information is in the header of the split data file. If you manually alter the header, this compromises the validation measure built into the AutoLoader.

Problems with Indexing Data

Symptom
The CREATE INDEX statement fails or hangs.

Possible Cause
The table space is full, or there is insufficient log space.

Action
Ensure that there is sufficient disk space for the index pages and for sorting; and that there is sufficient log space.

For information on calculating disk size and on determining log size, refer to the Administration Guide, Design and Implementation.

Problems during Recovery

Connection appears suspended

Symptom
A connection appears suspended.

Possible Cause
The database was restarted and is still recovering.

When crash recovery is in progress, it can take a long time to recover the database from a failure. This occurs when operations that require a large volume of logging activities were running before the database crashed. This is a normal situation.

Action
Check the db2diag.log file file of the coordinator node to see if crash recovery is completed. Entries in the db2diag.log file will indicate when recovery is started, and when it is finished.

The connection will finish once recovery is complete on all nodes.

SQL1061W received

Symptom
The SQL1061W message is received during recovery.

Possible Cause
There are indoubt transactions that cannot complete, typically because one or more nodes cannot be started. The database is recovered and open for user connections, but the indoubt transactions are taking up memory and other resources.

Action
Check the db2diag.log file file of the coordinator node to see if all nodes are started.

Resolve the indoubt transactions as soon as possible. For information, refer to the discussion on heuristics in the Administration Guide, Design and Implementation.

Developing Applications in DB2 Enterprise - Extended Edition

In a partitioned database environment, DB2 breaks up SQL statements into subsections, each of which is processed on the node that contains the relevant data. As a result, an error may occur on a node that does not have access to the application.

When developing applications for multiple nodes, consider the following:

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

[ DB2 List of Books | Search the DB2 Books ]