Maintaining the DB2 database

Edit online

To ensure that the DB2® database works properly, you need to regularly back up your data and perform maintenance actions.

About this task

The configuration of DB2 has a significant impact on performance. You should perform some standard actions to administer the database correctly. If you have DB2 administration professionals, they can monitor the DB2 health and performance. In other cases, you should follow the procedures that are described in this topic to keep your database healthy.

Procedure

  1. Perform regular backups of the data that is stored in the database. It is advisable to back up the database before upgrading the server to facilitate recovery in case of failure.
    1. Stop the server.
    2. In the DB2 command-line interface, run the following command: db2 backup database TEMADB.
      Note: TEMADB is the default database name. If you are unsure whether it applies to your database, see: Checking the database name.
    3. Start the server

    The backup is created in the current working directory, and the file name contains the instance name and time stamp of the backup procedure (for example: TLMA.0.db2inst1.NODE0000.CATN0000.20101105000715.001).

    For more information about database backup strategies, see: Developing a backup and recovery strategy.

  2. Reorganize the table to match the index and to reclaim space:
    1. Stop the License Metric Tool server.
    2. In the DB2 command-line interface, run the following commands:
      • db2 connect to TEMADB
      • db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' allow no access;'from syscat.tables where type = 'T' and tabschema in ('ADM','SAM','DBO') order by tabschema,tabname " > reorgs.sql
      • db2 -tvf reorgs.sql
      • db2 terminate
    3. Start the License Metric Tool server.
  3. Keep the statistics up-to-date. By default, DB2 statistics are run automatically. If this option is disabled, you must manually run the following commands:
    1. Stop the License Metric Tool server.
    2. In the DB2 command-line interface, run the following commands:
      • db2 connect to TEMADB
      • db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,50),' and indexes all;'from syscat.tables where type = 'T' and tabschema in ('DBO','SAM','ADM') order by tabschema,tabname " > runstats.sql
      • db2 -tvf runstats.sql
      • db2 terminate
    3. Start the server.

What to do next

For a more advanced investigation of queries that are used by the License Metric Tool server, you should use the DB2 design advisor command (db2advis). It helps in finding new indexes that can improve the database performance. The Design Advisor uses the output from the DB2 monitors to suggest the creation of new indexes. The suggestions are based on the queries that are found in monitors. For more information about Design Advisor, consult the DB2 documentation: