Troubleshooting Guide

Table Space Usability

After fixing the problem that caused DB2 Universal Database to revert to an OFFLINE table space state, perform the following steps to return the state to ONLINE:

1. Disconnect all applications and connect to the database again. You will see that the table space is now fine and can be taken out of the OFFLINE state.
2. Use the ALTER TABLESPACE ... SWITCH ONLINE statement to bring the table space up while the rest of the database is still up and deployed.
   • If the table space can be brought up successfully after issuing the command, or if the table space was not in the OFFLINE state to begin with, DB2 will return a SQLCODE of 0.
   • If the table space cannot be brought up successfully because there are still problems with one or more of the containers, DB2 will return a SQLCODE of --293.

RESTART ... DROP PENDING TABLESPACE

Making a successful database restart

RESTART DATABASE database-alias 
   [USER user-name [USING password]] 
   [DROP PENDING TABLESPACES tablespace-name1, 
      tablespace-name2, tablespace-nameN]

In case of circular logging, a damaged or missing table space (either temporary or permanent) causes a restart database failure. Option DROP PENDING TABLESPACES can be used by explicitly telling the database manager to make the database restart successfully, even if you may lose these table spaces.

A list of troubled table space names can be found in db2diag.log if a restart database fails because of container problems.

If there is only one temporary table space in the database and it is in DROP PENDING state, a new temporary table space must be created right after a successful database restart.

Restarting the database on a single node

If the database is only restarted on a single node within an MPP system, a message may be returned on a subsequent database query indicating that the database needs to be restarted on other nodes. This requirement occurs because the database partition on a node on which the query depends must also be restarted. Restarting the database on all nodes solves the problem.

RESTART ... DROP PENDING Table Space State

If a problem occurs with containers of table spaces, specified in DROP PENDING option of RESTART DATABASE command, during the restart process, the corresponding table space is in DROP PENDING state after the successful restart.

Once a table space is in DROP PENDING state, the only operation available is to drop it.

DROP PENDING table space state applies only to circular logging.

Table Space Usability

DROP TABLESPACE allows you to drop a table space that shares a table with other table spaces.

Dropping the table space

You can drop the table space that has a table spanned across several DMS table spaces. For example, if long data, indexes, and regular data of the same table are stored in different DMS table spaces, this command allows you to drop the related tables spaces at one time.

Accepting a list of one ore more table space names

The DROP TABLESPACE statement accepts a list of one or more table space names (separated by commas). If one of the table spaces in the list to be dropped contains data that spans another table space, then that other table space must also be in the list. You cannot drop them, and an error is returned.

RESTART ... DROP PENDING

In DB2 Universal Database Version 6, new table space status is valuable to circular logging. For log retain, the current logic is applied without change. However, for circular logging, there are two situations to be considered.

Crash recovery is necessary

If the user can fix the table space, a later restart is successful. Otherwise, the table space is in DROP PENDING state right after the restart. This DROP PENDING state may apply to multiple table spaces if necessary. This change requires new options for the RESTART command:

db2 RESTART sample [USER user1 [USING passwd]]
   [DROP PENDING TABLESPACES (tsname1, tsname2, ...tsnameN)]

Crash recovery is not necessary

If the table space is in a consistent state, a new CONNECT tries to bring the table space online as usual. A successful table space rollforward drops its OFFLINE state if applicable.