Maintaining the DB2 database

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.

Perform regular backups

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.

Procedure

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.

DB2 Maintenance: Reorganize Table and Refresh Indexes statistics

Reorganize the table to match the index and to reclaim space:
1. Stop the BigFix Inventory 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 BigFix Inventory server.
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 BigFix Inventory 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.
Reclaim storage / reduce database size
To reduce tablespace size and avoid huge .LRG files please follow the below instructions.
- How to identify used pages in a tablespace
  - db2 inspect check tablespace name USERSPACE1 results keep inspect.log
    - This will generate a file inspect.log on the instance's diagnostic directory (usually <instance directory>/sqllib/db2dump). If multinode, it will generate a file for each node, adding the node number at the end of the filename. ie: inspect.log.000, inspect.log.001, etc. or it will generate separate folders for the nodes ie: DIAG000, DIAG0001, etc.
- To format the output, you must navigate to where the output file is located and run:
  - db2inspf inspect.log inspect.out
    - This will generate inspect.out in the current directory
Please note that you may have to wait a while for the below commands to execute so please execute these commands a few minutes apart
- To recover the space, please run:
  - db2 "ALTER TABLESPACE USERSPACE1 LOWER HIGH WATER MARK "
- To reduce the size of USERSPACE1 please run:
  - db2 "ALTER TABLESPACE USERSPACE1 REDUCE MAX
- To check for any change in "High water mark (pages)"
- db2 "LIST TABLESPACES SHOW DETAIL"
Checking space occupied by individual tables
- To get the size of the tables in temadb please run:
  - ```
  db2 “select char(date(TAB.STATS_TIME))||'-'||char(time(TAB.STATS_TIME))
                      as STATSTIME, substr(TAB.TABSCHEMA,1,3) as TABSCHEMA, substr(TAB.TABNAME,1,35)
                      as TABNAME, CARD as ROWS, (COL_OBJECT_P_SIZE + DATA_OBJECT_P_SIZE + INDEX_OBJECT_P_SIZE
                      + LONG_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE) as TAB_ALLOC_KB, 
                      (COL_OBJECT_L_SIZE + DATA_OBJECT_L_SIZE + INDEX_OBJECT_L_SIZE + LONG_OBJECT_L_SIZE
                      + LOB_OBJECT_L_SIZE + XML_OBJECT_L_SIZE) as TAB_USED_KB, DICTIONARY_SIZE AS DICT_P_SIZE,
                      DATA_OBJECT_P_SIZE AS DATA_P_SIZE, INDEX_OBJECT_P_SIZE AS INDEX_P_SIZE, 
                      LOB_OBJECT_P_SIZE AS LOB_P_SIZE, LONG_OBJECT_P_SIZE AS LONG_P_SIZE, 
                      XML_OBJECT_P_SIZE AS XML_P_SIZE, DATA_OBJECT_L_SIZE AS DATA_L_SIZE, INDEX_OBJECT_L_SIZE AS INDEX_L_SIZE,
                      LOB_OBJECT_L_SIZE AS LOB_L_SIZE, LONG_OBJECT_L_SIZE AS LONG_L_SIZE, XML_OBJECT_L_SIZE AS XML_L_SIZE 
                      from syscat.tables TAB join sysibmadm.admintabinfo ADMTI on TAB.tabname=ADMTI.tabname and 
                      TAB.tabschema=ADMTI.tabschema where TAB.TABSCHEMA IN ('DBO','SAM','ADM') order by TABSCHEMA,
                      TABNAME with ur"
```

What to do next

For a more advanced investigation of queries that are used by the BigFix Inventory 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:

DB2 11.5