Shulou(Shulou.com)05/31 Report--

How to analyze the daily operation and maintenance of DB2, I believe that many inexperienced people do not know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Daily maintenance operation of DB2

1. Start, stop and activate the database

Db2 list active databases

Db2 active db database name

Db2start-start

Db2stop [force]-- stop

two。 Connection and disconnection of database

Db2 CONNECT TO DBName [user UserID using PWD]-- establish a connection

Db2 CONNECT reset / db2 disconnect CURRENT-disconnect

Db2 terminate-disconnect from the database

3. Creation and deletion of instances

Db2icrt

< 实例名称 >

-- create a db2 instance

Db2idrop

< 实例名称 >

-- Delete a db2 instance

Set db2intance=db2-sets the current db2 instance

Db2ilist-displays instances owned by db2

4. Cataloging of nodes and databases

Db2 list db directory-View the local node directory

Db2 catalog tcpip node remote ostype-cataloging one

TCP/IP node

Db2 uncatalog node-cancel node cataloging

Db2 catalog DATABASE AS AT node-cataloging database

Db2 uncatalog DATABASE-cancel database cataloging

5. Check whether the DB2 instance has been started

Use the ps command to see if there is a db2sysc background process

# ps-ef | db2sysc

You can also log in as the owner of the DB2 instance and issue the db2start command to ensure that the instance is started (if the instance has been started, it will inform the SQL1026N database manager that it has been activated; otherwise, the instance will be started)

6. Check whether the status of the tablespace is normal

Log in as the db2 instance owner

# db2 list tablespaces show detail / / check the status of the tablespace on a single partition and return 0x0000 normally

# db2_all list tablespaces show detail / / View the status of tablespaces on all partitions

You can use the LIST TABLESPACES command to determine the current state of the tablespace in the connection database, and 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 status return value is 0x0000. In this case, you can use db2tbst to view the status meaning of the status number. The syntax is as follows:

Db2tbst can view the status represented by the number

Db2inst1@local:~ > db2tbst 0x0000

State = Normal

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 externally visible state of the tablespace consists of the hexadecimal sum of a single state value. For example, if the state of the tablespace is Backup Pending and Load in Progress, the hexadecimal value returned is 0x20020

(0x00020 + 0x20000)

7. View the status of the table

Query the system catalog view for status information about the tables in the database. For example, the following statement returns the names of all tables with entries in SYSCAT.TABLES, as well as the number of columns and the status of each table (N = normal; C = check pending)

# db2 select tabname, colcount, status FROM syscat.tables ORDER BY tabname

You can also use the load query command to view the status of a single table. For example, for the table TEST1, we can issue the following command:

# db2 load query table test1

8. Check disk space

Check whether the database activity log directory is full. The location of the activity log directory can be checked using db2 get db cfg. Be careful not to delete the activity log manually.

# df-k

Check whether the container directory space corresponding to the SMS tablespace is full

# df-k

Check to see if there are any pages available in the DMS tablespace

# db2 list tablespaces show detail / / check whether there are any available pages in the tablespace on a single partition

# db2_all list tablespaces show detail / / check if there are any available pages in the tablespace on all partitions

9. Check whether the storage management software is normal

Check that TSM or other storage management software is working, and that the tape drive is functioning properly.

10. 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. If you want to check the backup on DB2, you can use the LIST command

# db2 list history backup all for database name

11. Check whether the archive log is archived correctly

Make sure that the log files that are not in the active log directory have been properly archived to the tape machine (see TSM or third-party storage management software).

12. Check whether the hit rate of the most frequently running SQL is normal.

# db2 get snapshot for bufferpools on database name > log.txt

# db2 "select * from SYSCAT.BUFFERPOOLS" to check buffer pool size information

Hit rate calculation:

(1-(Buffer pool data physical reads + Buffer pool index physical reads) /

(Buffer pool data logical reads + Buffer pool index logical reads)) * 100%

Use the grep command to check the statements that "Number of executions" executes most frequently to see if their hit rates are normal.

For example:

Grep-n "Number of executions" log.txt | grep-v "= 0" | sort-k 5pm 5rn | more

13. Check the currently connected application to see if there is any illegal connection

# db2 list applications show detail

Look at these connections to see if there are inappropriate IP connections, or third-party tools that are not allowed to connect. For example, some third-party tools will lock the table and affect the normal operation of the business system. At this time, you can use db2 "force application" to stop a single handle.

14. Check for deadlocks

# db2 get snapshot for all on database name > log.txt

Use the grep command to see if there is a record of deadlocks in the output file, such as

Grep-n "Deadlocks detected" log.txt | grep-v "= 0" | more

15. Runstats tables and indexes

# db2 runstats on table table name and index all

Running statistics on system tables and tables that change frequently is recommended to be written as a shell script to run automatically.

16. Check if the list needs to be reorganized

Use the REORGCHK command to check whether the table needs to be reorganized through statistics, 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 statistics of the table to determine whether the table needs to be reorganized or not

CURRENT STATISTICS: judge whether the table needs to be reorganized according to the current table statistics

TABLE table_name: analyze a single table

TABLE ALL: analyze all the tables in the database

TABLE SYSTEM: analyze the system table

TABLE USER: analyze all tables in the current user mode

# db2 reorgchk update statistics on table all

Reorganize the tables that need to be reorganized

# db2 reorg table table name / / remove "fragmented" data by restructuring rows

# db2 reorg indexes all for table table name / / only reorganize the index

For example:

Reorg table db2inst1.org index by_id

The table and all indexes will be reorganized according to the index by_id, if the INDEX option is not added

Reorg table db2inst1.org index by_id use tempspace1

Reorganize tables using the specified temporary tablespace

RUNSTATS is required after the table reorganization is completed. Also, remember that in a partitioned database environment, you need to use the db2_all command if you want to run the command on all nodes.

17. Backup and recovery of database

-- offline backup

Db2 force applications all-- disconnects all connections

Db2 force application (H2 and H3). )-- kills a process connected to the database

Db2 backup db DBName [to / backup] [use TSM]-offline backup

-- online, incremental backup

-- premise: change the database parameters logretain, userexit, trackmod to on, and then the database is in a state of backup pending. You need to back up the database completely offline, and then you can perform online and online incremental backups.

Db2 update db cfg using logretain on userexit on trackmod on-- enables related parameters

-- Archive path

DB2 UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 DISK:/backup

-- change the parameter LOGARCHMETH1 to TSM

DB2 UPDATE DATABASE CONFIGURATION USING LOGARCHMETH1 TSM IMMEDIATE

Db2 backup db DBName [to / backup] [use TSM]-offline backup

Db2 backup db DBName online [to / backup] [use TSM] include logs-online backup

Db2 backup db DBName online incremental [to / backup] [use TSM]-online incremental backup

-- Database recovery

Db2 restore db DBname [incremental] [FROM / backup] [use TSM] taken AT YYYYMMDDHHMMSS

-restore (timestamp: YYYYMMDDHHMMSS)

Db2 list history backup [since YYYYMMDDHHMMSS] ALL FOR DBName

-- check the backup of DBName [from YYYYMMDDHHMMSS]

Db2 ROLLFORWARD DATABASE DBName TO END OF LOGS AND COMPLETE NORETRIEVE-- restores suspended data to roll forward state

Db2 ROLLFORWARD DATABASE DBName TO END OF LOGS AND COMPLETE OVERFLOW LOG PATH ("/ home/db2inst1/SQLOGDIR")

Db2 CONNECT TO DBName

Db2 prune history YYYYMMDDHHMMSS-- deletes backup records before DBName YYYYMMDDHHMMSS

18. Export and import of data

-- Export

Db2 EXPORT TO D:\ TableName.txt OF del SELECT * FROM SCHEMA.TableName-- text format

Db2 EXPORT TO D:\ TableName.csv OF del SELECT * FROM SCHEMA.TableName--csv can be converted to excel

Db2 EXPORT TO D:\ TableName.ixf OF ixf SELECT * FROM SCHEMA.TableName

-- Export data (IXF integrated common exchange format)

Db2 EXPORT TO "D:\ TableName.data" OF IXF MESSAGES "D:\ TableName.msg" SELECT * FROM SCHEMA.TableName

-- Import

Db2 IMPORT FROM D:\ TableName.txt OF del INSERT INTO SCHEMA.TableName

Db2 IMPORT FROM "D:\ TableName.data" OF IXF [MESSAGES "D:\ TableName.msg"] [COMMITCOUNT 1000] INSERT/CREATE INTO SCHEMA.TableName

Db2 IMPORT FROM "D:\ TableName.ixf" OF IXF [COMMITCOUNT 1000] INSERT/INSERT_UPDATE/CREATE/REPLACE/REPLACE_CREATE INTO SCHEMA.TableName

19. View the DB2 log

Please check the db2diag.log file at least once a day to see if there are any anomalies.

20. Check whether the backup and log are saved

Through TSM or third-party storage management software to see if the backup and archive logs are saved, and to view backups at the database level, you can use:

# db2 list history backup all for database name

21. Check the performance of the system through the snapshot monitor

Through the snapshot monitor, grab the information of the database and analyze whether the performance of the database is reasonable:

# db2 get snapshot for all on database name > log.txt

22. Database patch level

# db2level

23. Get the configuration parameters of the database

To view the current values of the configuration parameters in the existing database manager configuration file, you can use the following command:

Db2get database manager configuration

Can be abbreviated as: db2 get dbm cfg

To update the value of a single configuration parameter in the database manager configuration file, you can use the following command:

Db2update database manager configuration using...

Can be abbreviated as: db2 upadte dbm cfg using...

To view the current values of the configuration parameters in the database configuration file, you can use the following command:

Db2 get database configuration for database name

Can be abbreviated to: db2 get db cfg for database name

To update the value of a single configuration parameter in the database configuration file, you can use the following command:

Db2 update database configuration for database name using parameter name parameter value...

Can be abbreviated as: db2 upadte db cfg for database name using parameter value.

24. View the parameters set

$db2set

DB2_INLIST_TO_NLJN=YES

DB2_EVALUNCOMMITTED=YES

DB2_RR_TO_RS=YES

DB2COUNTRY=86

DB2COMM=TCPIP

DB2CODEPAGE=1386

Daily matters needing attention in DB2 operation and maintenance

1. Do not delete the activity log file

The activity log file for DB2 cannot be deleted. Once the activity log file of DB2 is deleted, or there is a problem with the storage device, it will inevitably cause DB2 database system downtime.

2. Pay attention to the storage space of the transaction log

In archived log mode, if automatic archiving is not used, the number of log files stored will continue to increase, which may cause the file system where the logs are located to be full. When this happens, it will vary depending on the configuration of the parameter BLK_LOG_DSK_FUL

The same phenomenon:

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 will stop working

In both cases, you need to add space to the file system where the log resides to return to normal, so as to avoid the problem of transaction log being full.

3. Configure the log space according to the actual workload of the system

DB2 database maintains data integrity and consistency through log files. The log space of the DB2 database can be calculated by the following formula:

Log space = (primary log file + secondary log file) * log file size

Where:

1) the main log file is controlled by the parameter LOGPRIMARY

2) the auxiliary log file is controlled by the parameter LOGSECOND

3) the log file size is controlled by the parameter LOGFILSIZ

4) LOGPRIMARY + LOGSECOND

< 256 （不同的 DB2 版本略有不同，请参看相同版本的 DB2 手册确认） 4、 设置正确数据库代码页 由于数据库的代码页在数据库创建之后是无法修改的，所以在创建数据库时一定要选择正确的代码页。 错误的数据库代码页会造成 JDBC/ODBC 访问时中文字段被截断（包括控制中心），这种情况需要重建数据库以修改数据库代码页。 从全局规划来说，如果应用需要访问多个数据库，那么这多个数据库的代码页应该是一致的。 5、 检查许可证（License）安装情况 许可证过期会造成不必要的服务中断，所以在 DB2 安装完毕后，建议检察许可的安装情况 检查license的内容 $db2licm -l 执行如下安装授权许可 #/opt/ibm/db2/V9.7/adm/db2licm -a /mnt/cdrom/db2/license/db2ese.lic 6、 创建数据库前调整好系统时间 在数据库创建好之后，调整系统时间会造成数据库内部时间戳的异常。数据库中一些对象和时间相关，一旦时间不准确要调整需要很小心。错误的时间调整可能会造成很多问题，如： 1）某些对象失效，例如 ： SQL0440N，找不到具有兼容自变量的类型为 "" 的名为 "" 的已授权例程 2）数据库日志逻辑错误 ->

Downtime

3) Common errors-only adjust the time, not the time zone

7. Do not execute chown (chmod)-R (UNIX/Linux) casually

In the instance directory, chown (chmod)-R will cause

1) db2 connect to can connect to the database on the database server

2) db2 connect to user... Using... Unable to connect

8. Remember to add the NONRECOVERABLE parameter when using LOAD in archive log mode

After reading the above, have you mastered how to analyze the daily operation and maintenance of DB2? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.