Routine maintenance DB

DB2 maintenance manual 1
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:

Log on as a server user (dasusr1 by default for UNIX)
Issue 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, You must select the correct code page when creating the database.
The wrong database code page may cause Chinese fields to be truncated (including the control center) during JDBC/ODBC access. In this case, the database must be rebuilt to modify the database code page.

For global planning, if the application needs to access multiple databases, the code pages of these databases should be consistent.

5. check license installation
Expired licenses may cause unnecessary service interruptions. Therefore, after the DB2 installation is complete, we recommend that you check the license installation information.

6. Adjust the system time before creating the database
After the database is created, adjusting the system time will cause an exception in the internal timestamp of the database. Some objects in the database are time-related. You need to be careful when the time is not accurate. Incorrect time adjustment may cause many problems, such:
1) Some objects are invalid, for example:
Sql0440n, unable to find the authorized routine named "<routine Name>" with the compatible independent variable type as "<routine type>"
2) Database Log logic error-> downtime
3) Common Errors-only adjust the time, not the time zone
7. Do not run chown (chmod)-R (Unix/Linux)
Chown (chmod)-R under the instance directory will cause
1) on the database server, DB2 connect to <dbname> can connect to the database.
2) DB2 connect to <dbname> User... using... unable to connect
8. Remember to add the nonrecoverable parameter when using load in archive log mode.

V. Appendix: reorganize the table offline
Reorganizing a table offline is the fastest way to organize table fragments. Restructuring can reduce the amount of space required by the table and improve data access and query performance.
You must have sysadm, sysctrl, sysmaint, or dbadm permissions, or you must have control permissions on the table to reorganize the table. You must have a database connection to reorganize the table.
After identifying the tables to be reorganized, you can run the reorg utility on these tables, and you can choose to run the utility on any indexes defined on these tables.
1. To use CLP to reorganize a table, run the reorg table command:
DB2 reorg table test. Employee
To use the temporary tablespace mytemp to reorganize the table, enter:
DB2 reorg table test. employee use mytemp
To reorganize the table and re-sort the rows based on the index myindex, enter:
DB2 reorg table test. Employee index myindex
2. To reorganize a table using an SQL CALL statement, run the reorg table command in admin_cmd:
Call sysproc. admin_cmd ('reorg table employee index myindex ')
3. To use the DB2 management API to reorganize the table, use the db2reorg API.
After reorganizing the table, collect statistical information about the table so that the optimizer has the most accurate data to evaluate and query the ACCESS scheme.
Vi. Appendix: Index Reorganization
After the table is updated through the delete and insert operations, the index performance will decrease as follows:
• Leaf Page Segmentation
After a leaf page is segmented, the I/O operation cost increases because more leaf pages must be read to access the Table Page.
• The Order of physical index pages does not match the key order on these pages (this is called poor cluster index ).
When a leaf page suffers from a poor cluster, the efficiency of sequential prefetch operations will be reduced, resulting in more I/O waits.
• The index is larger than the most effective index.
In this case, the index should be reorganized.
If the minpctused parameter is set during index creation, the database server automatically merges the index leaf pages when a key is deleted and the available space is smaller than the specified percentage. This process is called online index fragmentation. However, to restore the Index Cluster and available space and reduce the leaf level, use one of the following methods:
• Delete and re-create an index.
• Use the reorg indexes command to reorganize indexes online.
This method allows you to perform read and write operations on the table during table index reconstruction, so you may need to select this method in the production environment.
• Run the reorg table command using the option that allows offline table reorganization and Its indexes.
Online Index Reorganization
When you run the reorg indexes command using the allow write access option, if you allow both read and write access to the specified table, all indexes of the table will be rebuilt. During the reorganization, any changes made to the basic table that will affect the index will be recorded in the DB2 log. In addition, if any internal memory buffer space is available, these changes will also be placed in this memory space. Restructuring will process the changes recorded so that the rebuild index can be synchronized with the current Write activity. The internal memory buffer space is the specified memory area allocated from the utility heap as needed. It is used to store changes to the indexes being created or restructured. Use the memory buffer space to enable the index reorganization operation to handle changes in this way, that is, directly read from the memory and then read the logs (if necessary ), however, the log reading time is much later. After the reorganization is completed, the allocated memory is released. After the reorganization, the re-built index may not be the index of the best cluster. If pctfree is specified for the index, the percentage of space on each page is retained during the reorganization.
For partitioned tables, you can reorganize and clear indexes online. To restructure each index, specify the index name: reorg index index_name for table table_name
For spatial indexes or multi-dimensional cluster (MDC) tables, online index reorganization using allow write is not supported.
Note: The cleanup only option of the reorg indexes command cannot completely reorganize the index. The cleanup only all option removes keys marked as "deleted" and considered to be implemented. In addition, it releases the pages of all keys marked as "deleted" and considered to be implemented. After the page is released, the adjacent leaf pages will be merged, provided that at least pctfree space can be set up on the merged page. Pctfree refers to the percentage of available space defined when an index is created. The cleanup only pages option only deletes the pages of all keys marked as "deleted" and considered to be implemented.
Any access level is supported when you use the cleanup only option to reorganize the index of a partitioned table. If the cleanup only option is not specified, the default access level allow no access is the only supported access level.
Reorg Indexes has the following requirements:
• Sysadm, sysmaint, sysctrl, or dbadm permissions on indexes and tables, or control permissions.
• The number of available spaces for the tablespace used to store the index is equal to the current size of the index.
When issuing the create table statement, consider reorganizing the index in a large tablespace.
• Other log Spaces
Reorg indexes needs to record its activities. Therefore, restructuring may fail, especially when the system is busy and other concurrent activities are recorded.
Note: If the reorg indexes all command with the allow no access option fails to run, it indicates that the index is invalid and this operation cannot be undone. However, if the reorg command with the allow read access option or the reorg command with the allow write access option fails to run, the original index object can be restored.

VII. Appendix: guidelines for collecting and updating statistical information

The runstats command collects statistics of tables, indexes, and Statistical Information views to provide the optimizer with accurate information for selection of access solutions.
Use the runstats utility to collect statistics in the following cases:
• When the data has been loaded into the table and an appropriate index has been created.
• When creating a new index in the table. If the table has not been modified since the last runstats operation in the table, you only need to execute runstats on the new index.
• When a table is restructured using the reorg utility.
• When a large number of updated tables and their indexes are modified, deleted, and inserted through data. (The "large number" mentioned here may indicate that 10% to 20% of the table and index data are affected .)
• Before binding an application with critical performance
• When you want to compare current and previous statistics. If you regularly update statistics, you can detect performance problems as early as possible.
• When the prefetch volume changes.
• When the redistribute database partition group utility is used.
Note:
In earlier versions of DB2, this command uses the nodegroup keyword instead of the database partition group keyword.
• Use the runstats utility to collect statistics about XML columns. When runstats is used to collect statistics for only XML columns, the existing statistics for non-XML columns that have been collected by the load or last runstats utility are retained. If statistics on some XML columns have been collected previously, the statistics of the previously collected XML columns will be deleted if the current command does not collect statistics on this XML column; when the current command collects statistics about this XML column, it replaces the statistics of the previously collected XML column.
To improve runstats performance and save the disk space used to store statistics, you must specify only the columns whose data distribution statistics should be collected.
Theoretically, you should re-bind the application after running the statistics. If the query optimizer has new statistics, you can select different access schemes.
If you do not have enough time to collect all the statistics at a time, you can run runstats to update the statistics of only a few tables, indexes, or statistics views at a time, and complete the group of objects in turn. If table activity causes inconsistency during running of runstats for some selective updates, a warning message (sql0437w, cause code 6) is sent during query optimization ). For example, if you execute runstats to collect the table distribution statistics and execute runstats again after a table activity to collect the index statistics of the table, this may happen. This warning message is sent if the table activity is inconsistent and the inconsistency is detected during query optimization. In this case, run runstats again to update the distribution statistics.
Make sure that the index statistics are synchronized with the table. Run runstats to collect both the table and index statistics. Index statistics retain the statistics of most tables and columns collected since the last runstats operation. If a large number of modifications have been made to the table since the statistical information of the table was collected last time, only the index statistics of the table are collected, so that the two groups of statistics cannot be synchronized on all nodes.
Calling runstats on the production system may negatively affect the performance of the production workload. The runstats utility now supports the speed control option. During the execution of high-level database activities, you can use the speed control option to limit the performance impact of runstats execution.
When collecting table statistics in the partitioned database environment, runstats only collects statistics on the tables on the database partition where the command is executed. Promote the runstats results of this database partition to other database partitions. If the database partition executing runstats does not contain a part of a specific table, the request is sent to the first database partition that contains a part of the table in the database partition group.
When the Statistical Information view is collected, the statistics of all database partitions that contain the basic tables referenced by the view are collected.
Consider the following tips to improve runstats efficiency and the effectiveness of collected statistics:
• Collect statistics only for the columns used to connect to the table, where, group by, and query similar clauses. If you have created an index for these columns, you can use the only on key columns clause of the runstats command to specify the columns.
• Customize num_freqvalues and num_quantiles values for specific tables and columns.
• Use the sample detailed clause to collect the detailed index statistics to reduce the background computing workload for detailed index statistics. The sample detailed clause reduces the time required to collect statistics and produces sufficient accuracy in most cases.
• When creating an index for a filled table, add the collect statistics clause to create statistics when creating the index.
• When a large number of table rows are added or removed, or if data in the columns that collect statistics are updated, run runstats again to update statistics.
• Since runstats only collects statistics for a single database partition, if the data is not uniformly distributed across all database partitions, the statistics are inaccurate. If you suspect that there is deformation data distribution, you may want to use the redistribute database partition group command before running runstats to distribute data between database partitions.
VIII. Appendix: Use CLP to capture database operation status snapshots

You can use the get health snapshot command from the CLP to capture running state snapshots. This command syntax allows you to retrieve the running status snapshot information of different object types monitored by the running status monitor.
Prerequisites
You must have an instance connection to capture a running snapshot. If no instance connection exists, a default instance connection is created. To obtain a snapshot of a remote instance, you must connect to the instance first.
Process
Use CLP to capture database health snapshots
1. Issue the get health snapshot command with the expected parameters from the CLP.
In the following example, a snapshot of the Database Manager running status is captured immediately after the Database Manager is started.
DB2 get health snapshot for dBm

2. For a partitioned database system, you can capture a specific database snapshot for a specific partition, or capture a global database snapshot for all partitions. To capture a running snapshot of a database in a specific partition (such as Partition Number 2), run the following command:
DB2 get health snapshot for db on sample at dbpartitionnum 2
To Capture Database snapshots for all applications in all partitions, run the following command:
DB2 get health snapshot for db on sample global
The running status snapshot captured by the following command carries additional details, including the formula, additional information, and running status indicator history:
DB2 get health snapshot for db on sample show detail

3. For a set-state-based running indicator, you can capture database snapshots for all collection objects, regardless of the status of these objects. The conventional get health snapshot for DB command returns all set objects that need alarms for all set-state-based health indicators.
To capture running state snapshots of databases listing all set objects, run the following command:
DB2 get health snapshot for db on sample with full collection