TPC basic database maintenance steps - tutorial

Database maintenance

TPC database maintenance consists of three tasks:

1. updating statistics - DB2 uses this information to determine the most efficient way to store and retrieve data.

2. reorganization (reorg) - this is comparable to defragmentation of a disk, and improves the efficiency of data retrieval operations.

3. backup - this step is essential to protect your TPC data and provide recovery capability in the event of a system failure or other problem that could lead to loss of data.

DO I NEED TO STOP TPC SERVICES?

TPC data and device server services should be stopped while performing database maintenance. Instructions to stop TPC services can be found in the TPC Infocenter.

RUNNING DB2 COMMANDS, CONNECTING TO THE DATABASE

Database maintenance commands must be run from a user account that has database administrator authority, and must be run from a DB2 command environment:

Windows: Start -> All Programs -> IBM DB2 -> DB2COPY1 (default) -> Command Line Tools -> Command Window

Unix: login as the DB2 administrator user (typical default: 'su - db2inst1')

(All steps shown below assume a DB2 command environment.)

Establish a connection to the database prior to running your commands:

db2 connect to TPCDB

STEP 1 - UPDATING STATISTICS

The easiest way to update DB2 statistics is to use the DB2 'reorgchk' command:

db2 reorgchk update statistics on schema tpc >reorgchk.log

Redirecting output to a file as shown here is optional, but recommended so that the file can be reviewed to determine where reorg is needed.

STEP 2 - REORGANIZATION (REORG)

There are two ways to reorganize the TPC database. You can reorganize all tables in the database, or you can reorganize only the tables and indexes that require it. If you want to reorganize all tables, use this command to create a list of the table names:

unix example: db2 list tables for schema tpc show detail | grep T_ >tables.log

windows example: db2 list tables for schema tpc show detail | find "T_" >tables.log

You will now create a command file for DB2 by editting the output file of table names, and changing each line to this format:

REORG TABLE TPC.tablename;

where 'tablename' is the name of the table (don't forget the semi-colon ';' at the end). For example:

REORG TABLE TPC.T_RES_CONFIG_DATA;

To reorganize only the tables and indexes that need it, first complete step #1 to update statistics and create a log file from the reorgchk command.

The log file lists each table, and at the end of the line there are flags that will indicate reorg is needed with the presence of a '*' character. If reorg is not needed, you will see only a series of hyphens and no '*' such as:

---

You will create a command file for DB2 with a line for each table that needs to be reorganized, based on the reorgchk report, using the format/syntax shown in the example above.

After the list of tables in the report, the database indexes are listed. Similar to the tables, each index entry line ends with a set of flags indicating if reorganization is needed. If the flags include one or more asterisk (star) '*' characters, you should add an entry to your command file to reorganize the indexes for that table:

REORG INDEXES ALL FOR TABLE TPC.tablename;

After you have assembled your command input file listing all tables and indexes to be reorganized, you are ready to run the commands (in this example the command input file has been saved as reorg.db2):

db2 -tvf reorg.db2 >reorg.log 2>&1

After completing the reorg, you should perform step #1 again to update statistics so that the statistics reflect the reorganized database.

STEP 3 - BACKUP YOUR DATABASE

Here are the commands to run to do a simple database backup:

Windows:

db2 CONNECT TO TPCDB
db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS
db2 CONNECT RESET
db2 BACKUP DATABASE TPCDB TO c:\backups WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
db2 CONNECT TO TPCDB
db2 UNQUIESCE DATABASE
db2 CONNECT RESET

Unix:

db2 "CONNECT TO TPCDB"
db2 "QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS"
db2 "CONNECT RESET"
db2 "BACKUP DATABASE TPCDB TO /backups WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING"
db2 "CONNECT TO TPCDB"
db2 "UNQUIESCE DATABASE"
db2 "CONNECT RESET"

These examples show c:\backups and /backups as the target directory for the backup, and these can be changed to a safe/suitable existing location for your environment.

There are other options to backup your database, including using the Tivoli Storage Manager. Please refer to chapter 3 of the TPC Advanced Topics Guide for more information:

http://www.redbooks.ibm.com/redpieces/abstracts/sg247348.html?Open