Troubleshooting Guide

DB2 Administrative Tools

The following sections outline some of the various tools available to help you perform database administrative tasks and DB2 troubleshooting. See "Improving Database Performance" for additional performance-related tools.

Graphical Tools

The following graphical user interface (GUI) tools are available from the DB2 folder on OS/2, and from the Start -> Programs menu choice on the Windows NT and Windows 95 operating systems. For information on these tools, see their online help and the Administration Getting Started.

Information Center

Provides a central source to access and search help, books, and up-to-date World Wide Web information.

Control Center

Displays local and remote database objects (such as databases, tables, packages, and table spaces), and lets you perform operations on them.

Use the Control Center to:

• Perform administrative tasks such as configuring databases, managing directories, backing up and recovering databases, scheduling jobs, collecting statistics for data, and managing media.

• Use SmartGuides to help you:
  • Create databases, table spaces, and tables
  • Back up and restore databases
  • Tune the performance of databases by optimizing their configuration

• Use graphical interfaces to perform the following tasks:

  • Analyze the access plans chosen by the DB2 optimizer for SQL statements and packages. This functionality is called "Visual Explain". To invoke it, click mouse button 2 on a database object in the Control Center, and select Explain SQL from the pop-up menu. See also "Improving Database Performance".

  • Monitor the performance of the database manager. You can capture point-in-time information at specified intervals, and send alerts if performance falls outside of a desired range. To invoke it, click mouse button 2 on a database object, and select Snapshot monitoring from the pop-up menu to see the monitoring choices.

  • Record information over the duration of an event, such as a connection. To invoke it, click mouse button 2 on a database object, and select Monitor events from a pop-up menu.

• For Extended Enterprise Edition, identify database nodes that are down, run user exits, or display alerts.

Miscellaneous Troubleshooting Tools

There are standalone tools provided for troubleshooting. You can find these tools in the misc, bin, or adm subdirectories of the sqllib directory. In UNIX-based systems, the sqllib directory is a subdirectory of the instance owner's home directory.

These tools provide syntax help, which you can access by typing the command and following it with a question mark. (Example: db2look ?)

The following list highlights some of the important tools available to you:

db2bfd

Provides a bind file description. For more information on binding, see the Embedded SQL Programming Guide, the Command Reference, or the API Reference.

db2cat

Dumps the contents of packed descriptors for tables and formats them in a readable form. (A packed descriptor is a column within the system catalog tables that DB2 uses to identify the details of a database object.)

db2dart

Verifies that the architectural integrity of a database is correct. For example, this tool confirms that:

• The control information is correct
• There are no discrepancies in the format of the data
• The data pages are the correct size and contain the correct column types
• Indexes are valid

If db2dart reports a problem with an index, use the tool's /MI option to mark the index as invalid. The index is rebuilt based on the value of the indexrec database and database manager configuration parameters (see the Administration Guide for details).

You must run this tool on the DB2 server where the database resides. Also ensure that there are no active connections to the database. (Use the LIST APPLICATIONS FOR DATABASE database-alias command and disconnect any applications that are listed.)

For information on db2dart options, type db2dart without any options.

db2look

Lets you mimic a production database on your test database. Use the -m option against a production database to generate the update statements that will match the catalog statistics of a test database with those of the production database. Use the -e option to generate the DDL for one or more tables from the database catalogs.

For introductory information on db2look, type db2look ?. For detailed information, type db2look -h.

For information on using system catalog statistics, see the Administration Guide.

db2recr

Re-creates indexes that were marked as inconsistent during a database restart or use of the db2dart tool.

db2resdb

Required by DB2 for OS/2 to restore a Version 1 database image to a target drive, and then migrate it to the current level.

db2tbst

Provides a textual description for a table space state.

db2untag

Removes the DB2 tag from a table space container. The tag is used to prevent DB2 from using a container for more than one table space. If a table space or database is destroyed, this tag may be left behind, preventing future DB2 use of the resource.

Use this tool if a DROP TABLESPACE command does not work. Typically, the SQL0294N message is received.

Attention: Use this tool in consultation with DB2 Customer Service, and only if you are an experienced database administrator. You must be completely sure that the container is not used by any other database, because this command is equivalent to dropping the container.

The Database System Monitor

The DB2 database manager maintains data about its operation and performance as it runs. This data can provide important troubleshooting information. For example, you can find out:

• The number of applications connected to a database, their status, and which SQL statements each application is executing, if any.

• Counts that show how well the database manager is configured.

• When deadlocks occurred for a specified database, which applications were involved, and which locks were in contention.

• The list of locks held by an application or a database. If the application cannot proceed because it is waiting for a lock there is additional information on the lock, including which application is holding it.

Because collecting some of this data introduces overhead on the operation of DB2, monitor switches are available to control which information is collected. To set monitor switches explicitly, use the UPDATE MONITOR SWITCHES command or the sqlmmon() API. (You must have SYSADM, SYSCTRL, or SYSMAINT authority.)

There are two ways to access the data maintained by the database manager:

• Taking a snapshot. Use the GET SNAPSHOT command from the command line; the Control Center on the OS/2, Windows 95, or Windows NT operating systems for a graphical interface; or write your own application, using the sqlmonss() API call.

• Using an event monitor. An event monitor captures system monitor information after particular events have occurred, such as the end of a transaction, the end of a statement, or the detection of a deadlock. This information can be written to files or to a named pipe.

To use an event monitor:

1. Create its definition with the Control Center or the SQL statement CREATE EVENT MONITOR. This statement stores the definition in the database system catalog.

2. Activate the event monitor with the Control Center or the SQL statement:

   SET EVENT MONITOR evname STATE 1
   

   If writing to a named pipe, start the application reading from the named pipe before activating the event monitor. You can either write your own application to do this, or use db2evmon. Once the event monitor is active and starts writing events to the pipe, db2evmon will read them as they are being generated and write them to standard output.

3. If using a file event monitor, you can view the binary trace that it created in either of the following ways:

   • Use the db2evmon tool to format the trace to standard output.

   • Click on the Event Analyzer icon in the Control Center (on the Windows 95, Windows NT, or OS/2 systems) to use a graphical interface to view the trace, search for keywords, and filter out unwanted data.

For information on the system database monitor and the event monitor, see the System Monitor Guide and Reference. For a scenario of how to use them from the Control Center, see the Administration Getting Started.

[ DB2 List of Books | Search the DB2 Books ]