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 95, Windows 98, and Windows NT operating systems. UNIX platforms also have GUI tools. For information on these tools, see the online help on each platform and the Administration Guide.

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 simplify some monotonous jobs:

Create databases, table spaces, and tables
Back up and restore databases
Tune the performance of databases by optimizing their configuration
Create indexes

Use graphical interfaces to perform the following tasks:

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

Note:

With IBM DB2 Universal DatabaseVersion 6, Visual Explain is no longer invoked from the command line. It can still, however, be invoked from various database objects in the Control Center. For this version, the documentation continues to use the name, Visual Explain.

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, use the left mouse button 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 the left mouse button on a database object, and select Monitor events from a pop-up menu.

For DB2 Universal Database Enterprise - Extended 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 bin, misc, and 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 tools available to you:

db2bfd

Provides a bind file description. For more information on binding, see the Application Development Guide, the Command Reference, or the Administrative API Reference.

db2cat

Dumps the contents of packed descriptors for tables and formats them in a readable form.

Note:

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.

db2flsn

Returns the name of the file that contains the log record identified by a specified log sequence number (LSN).

The log header control fil sqlogctl.lfh must reside in the current directory before using this tool. The tool also uses the logfilsiz database configuration parameter. This tool can only be used with recoverable databases.

db2ipxad

Returns the DB2 server's IPX/SPX internetwork address. This command must be issued locally from the DB2 server machine. Issuing the command from a remote client is not supported.

db2level

Displays detailed output about the level of DB2 Universal Database code, including the Fix Pak level, that is currently installed.

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.

db2sql92

Reads SQL statements from either a flat file or standard input, dynamically describes and prepares the statements, and returns an answer set. Supports concurrent connections to multiple databases. There are several parameters associated with this command. Refer to the Command Reference manual for additional information.

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, or the Control Center on the OS/2, Windows 95, Windows 98, and Windows NT operating systems. You can also write your own application, using the sqlmonss() API call.
Using an event monitor. The 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:

Create its definition with the Control Center or the SQL statement CREATE EVENT MONITOR. This statement stores the definition in the database system catalog.
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.
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 the Event Analyzer icon in the Control Center to view the trace, search for keywords, and filter out unwanted data.

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

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

[ DB2 List of Books | Search the DB2 Books ]