IBM Support

Spectrum Control / TPC basic database maintenance steps - tutorial

Question & Answer


Question

The Spectrum Control / TPC database needs periodic maintenance to run efficiently. This document describes how to perform this maintenance.

Cause

Database maintenance

Answer

Spectrum Control (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, device, and alert server services should be stopped while performing database maintenance. Instructions to stop TPC services can be found in the Knowledge Center (see 'Related Information' below).

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 administrator 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.

ALTERNATIVE STEP 1 AND STEP 2 - TPC DATABASE MAINTENANCE TOOL

Current releases of Spectrum Control / TPC are shipped with a database maintenance tool that will run reorg and update statistics for you. More information about this tool can be found in the Knowledge Center (see link below in 'Related Information').

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 Beyond the Basics Redbook for more information.

[{"Product":{"code":"SS5R93","label":"IBM Spectrum Control"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}},{"Product":{"code":"SS5R93","label":"IBM Spectrum Control"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":" ","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Document Information

Modified date:
22 February 2022

UID

swg21408580