Troubleshooting Guide

DB2 Administrative Tools

The following sections outline some of the tools available to help you perform database administration 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 for 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 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 Visual Explain, left-click 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 Database Version 7, 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 the monitoring option, left-click a database object, and select Snapshot monitoring from the pop-up menu.
• Record information over the duration of an event, such as a connection. To invoke this ability to record information, left-click a database object, and select Monitor events from the pop-up menu.

DB2 Wizards

DB2 wizards are invoked from the pop-up menus in the Control Center.

Use DB2 wizards 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

DB2 wizards prompt you step-by-step on how to fill in the information necessary for the task you are doing. Calculations and recommendations based on the information you supply are done by the wizards.

All of the tools mentioned above apply to the DB2 Universal Database Enterprise - Extended Edition (EEE) environment. In addition, you can identify database nodes that are down, run user exits, or display alerts.

Miscellaneous Troubleshooting Tools

Standalone tools are 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. On other operating systems, you can specify the directory where you would like the sqllib directory to be placed. Typically, this is under the drive where you install DB2.

These tools provide syntax help, which you can access by typing the command followed by 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

Note:

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: Performance for details).

You must run this tool on the DB2 server where the database resides. You must 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.)

In a DB2 UDB Enterprise - Extended Edition (EEE) environment, db2dart must be run on each database partition server.

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 file 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 pack level, that is currently installed.

db2look

Extracts the DDL necessary to re-create a database or database objects. This tool allows you to, for example, 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 more information on the options available on db2look, see the Command Reference.

db2recr

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

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. Several parameters are associated with this command. See the Command Reference manual for additional information.

db2tbst

Provides a text 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 Support, 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. Running this command on a container that has data results in the loss of all the data from that 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:

1. 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.
2. 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:

1. Define it 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 you are 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. When 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 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, see the System Monitor Guide and Reference. For a scenario of how to use them from the Control Center, see the Administration Guide: Implementation.