DB2 maintenance manual

I. Daily DB2 maintenance operations 3
1. Check whether the management server is enabled. 3.
2. Check whether the DB2 instance has been started. 3.
3. Check whether the tablespace status is normal. 3.
4. view the table status 4
5. view disk space 4
6. Check whether the storage management software is normal. 4
7. Check whether the database backup is normal. 5
8. Check whether the archived logs are correctly archived. 5
9. View buffer pool hit rate 5
10. Check whether the hit rate of the SQL statements that run most frequently is normal.
11. Check whether the application currently connected has an illegal connection. 5
12. Check for deadlocks. 6.
13. Perform RUNSTATS 6 on tables and Indexes
14. Check whether the table needs to be reorganized. 6
15. reorganize the table to be reorganized.
2. DB2 daily maintenance operations 7
1. View DB2 logs 7
2. Check whether the backup and log are saved.
Iii. DB2 routine maintenance operations 7
1. view the system performance through the snapshot monitor 7
2. Database Patch Level 8
Iv. Note 8
1. Do not delete activity log files 8
2. Pay attention to the transaction log storage space 8
3. Configure the log space according to the actual workload of the system 8
4. Set the correct database code page 9
5. check LICENSE installation 9
6. Adjust the system time before creating the database 9
7. Do not run CHOWN (CHMOD)-R (UNIX/LINUX) 9.
8. Use LOAD in archive log mode and add NONRECOVERABLE parameter 9.
V. Appendix: reorganize table 9 offline
Vi. Appendix: Index restructuring 10
VII. Appendix: guidelines for collecting and updating statistical information 11
VIII. Appendix: Use CLP to capture database health snapshots 13
 
I. Daily DB2 maintenance operations
1. Check whether the management server is started
Run the ps command to check whether the dausr1 background process exists.
# Ps-ef | dasusr1
Make sure that the management server has been started. If not, follow these steps to start the Management Server:
Login to Manage Server users (DASUSR1 by default for UNIX)
 Issues the db2admin start command
In the HA environment, make sure that the startup command is correctly configured in the script.
2. Check whether the DB2 instance has been started.
Run the ps command to check whether the db2sysc background process exists.
# Ps-ef | db2sysc
You can also log on to the DB2 instance owner and run the db2start command to ensure that the instance has been started. (If the instance has been started, the SQL1026N Database Manager is notified to be activated. Otherwise, the instance will be started)
3. Check whether the tablespace status is normal.
Log On As the db2 instance owner
# Db2 list tablespaces show detail // check the status of the tablespace in a single partition. Normally, 0x0000 is returned.
# Db2_all list tablespaces show detail // view the tablespace status on all partitions
You can use the list tablespaces command to determine the current status of the tablespace connected to the database. You can use the show detail option to view the details of the tablespace. For example, if we connect to the SAMPLE database and execute list tablespaces show detail, we can see that the returned state value is 0x0000. In this case, we can use db2tbst to view the status meaning of the Status number. The syntax is as follows:
Db2tbst <tablespace state> allows you to view the status represented by the number.
The db2tbst command receives the hexadecimal status value and returns the corresponding tablespace status. For example, the command db2tbst 0x0008 returns State = Load Pending. The hexadecimal status value, in turn, is part of the output of the list tablespaces command. The external visible state of a tablespace is composed of the hexadecimal total of a single state value. For example, if the tablespace status is Backup Pending and Load in Progress, the hexadecimal value returned is 0x20020 (0x00020 + 0x20000)
4. view the table status
Query the directory view to obtain useful information about the database. For example, the following statement uses not like assertions and returns the result in SYSCAT. all user-defined table names of items in TABLES, as well as the number of columns and status of each table (N = normal; C = check pending ))
# Db2 select tabname, colcount, status FROM syscat. tables WHERE tabschema not like sys % order by tabname
You can also run the load query command to view the status of a single table. For example, to view the table TEST1, run the following command:
# Db2 load query table test1
5. view disk space
Check whether the active log directory of the database is full. You can use get db cfg to check the active log directory. Be sure not to manually delete the active log.
# Df-k
Check whether the container directory space corresponding to the SMS tablespace is full
# Df-k
Check whether there are available pages in the DMS tablespace.
# Db2 list tablespaces show detail // check whether there are available pages in the tablespace on a single Partition
# Db2_all list tablespaces show detail // check whether there are available pages in the tablespace on all partitions
6. Check whether the storage management software is normal.
Check whether TSM or other storage management software is normal and whether the tape drive is running normally.
7. Check whether the database backup is normal
Please check TSM or third-party storage management software to see if the backup image file is completely saved to the tape drive. To view the backup information on DB2, you can use the LIST Command
# Db2 list history backup all for Database Name
8. Check whether the archived logs are correctly archived.
Make sure that all the log files in the active log directory are correctly archived to the on-board (view TSM or third-party storage management software ).
View the log files in the Active Directory:
# Ls-l
9. view the buffer pool hit rate
# Db2 get snapshot for bufferpools on Database Name
Check the buffer pool hit rate to see if it is lower than 95% (the higher the hit rate, the better)
10. Check whether the hit rate of the SQL statements that run most frequently is normal.
# Db2 get snapshot for bufferpools on Database Name> log.txt
Run the grep command to view the statements with the most frequent execution times of "Number of executions" and check whether the hit rate is normal.
For example:
Grep-n "Number of executions" snap. out | grep-v "= 0" | sort-k 5, 5rn | more
11. Check whether the application currently connected has an illegal connection.
# Db2 list applications show detail
Check the connection status to see if there are any inappropriate IP addresses or prohibited third-party tools. For example, if some third-party tools are connected, the table will be locked, this affects the normal operation of the business system. In this case, you can use force applications (application handle) to stop.
12. Check for deadlocks.
# Db2 get snapshot for all on Database Name> log.txt
Run the grep command to check whether there is a deadlock record in the output file, such
Grep-n "Deadlocks detected" log.txt | grep-v "= 0" | more
13. Perform runstats on tables and Indexes
# Db2 runstats on table name and index all
We recommend that you write a shell script to automatically run system tables and frequently-changed tables.
14. Check whether the table needs to be reorganized
Use the REORGCHK command to check whether data needs to be reorganized using the statistical data table. The syntax is as follows:
REORGCHK [UPDATE | CURRENT] statistics on [table system | table user | table all | TABLE table_name | SCHEMA schema_name]
Update statistics: UPDATE the statistical data of a table and determine whether to reorganize the table based on the statistical data.
Current statistics: determines whether to reorganize the table based on the STATISTICS of the CURRENT table.
TABLE table_name: analyze a single TABLE
Table all: analyzes ALL tables in the database.
Table system: Analyze SYSTEM tables
Table user: analyzes all tables in the current USER mode.
# Db2 reorgchk update statistics on table all
15. reorganize the table to be reorganized.
# Db2 reorg table name // remove "fragment" data by restructuring rows
# Db2 reorg indexes all for table name // only reorganize the index
For example:
Reorg table db2inst1.org index by_id
Based on the INDEX by_id, if the INDEX option is not added, the table and all indexes will be reorganized.
Reorg table db2inst1.org index by_id use tempspace1
Reorganize a table using the specified temporary tablespace

RUNSTATS is required after table reorganization. In addition, remember to use the db2_all command to run commands on all nodes in the partitioned database environment.
2. Daily DB2 maintenance monthly operations
1. View DB2 logs
Check the db2diag. log file at least once a month to see if any exception exists.
2. Check whether the backup and log are saved.
You can use TSM or third-party storage management software to check whether the backup and archive logs are saved. You can view the backup at the database level and use:
# Db2 list history backup all for Database Name
Iii. Quarterly DB2 maintenance operations
1. view the system performance through the snapshot Monitor
Through the snapshot monitor, capture the database information and analyze whether the database performance is reasonable:
# Db2 get snapshot for all on Database Name> log.txt
2. Database Patch Level
# Db2level
Iv. Notes
1. Do not delete activity log files
The DB2 activity log file cannot be deleted. Once the active log file of DB2 is deleted, or the storage device where it is stored has problems, the DB2 database system will inevitably crash.
2. Pay attention to the transaction log storage space
In the archive log mode, if the automatic archiving method is not used, the number of log files stored increases continuously, which may cause the file system space of the log to be full. In this case, the BLK_LOG_DSK_FUL parameter may vary depending on the Configuration:
1) if this parameter is enabled, the DB2 database can continue the read operation, but the write operation will be suspended.
2) If this parameter is not enabled, the DB2 database stops working.
In both cases, you need to add space to the file system where the log is located to restore normal.
3. Configure the log space according to the actual workload of the system.
DB2 databases maintain data integrity and consistency through log files. The log space of the DB2 database can be calculated using the following formula:
Log Space = (primary Log File + secondary Log File) * log file size
Where:
1) the master log file is controlled by the parameter LOGPRIMARY,
2) The second-level log file is controlled by the LOGSECOND parameter.
3) the log file size is controlled by the LOGFILSIZ parameter.
4) LOGPRIMARY + LOGSECOND <256 (different DB2 versions are slightly different. Please refer to the DB2 manual of the same version for confirmation)
4. Set the correct database code page
Because the database code page cannot be modified after the database is created