Summary of daily DB2 O & M

1. Routine DB2 maintenance operations 1. Database Start and Stop db2start -- start db2stop [force] -- stop 2. database connection, disconnection db2CONNECTTODBName [userUserIDusingPWD] -- establish connection &..

1. Routine DB2 maintenance operations 1. Database Start and Stop db2start -- start db2stop [force] -- stop 2. database connection, disconnection db2CONNECTTODBName [userUserIDusingPWD] -- establish connection &..

I. Routine DB2 maintenance operations

1. Start and Stop a database

Db2start -- start

Db2stop [force] -- stop

2. database connection and disconnection

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. Create and delete instances

Db2icrt <Instance name> -- create a db2 instance

Db2idrop <Instance name> -- delete a db2 instance

Set db2intance = db2 -- set the current db2 instance

Db2ilist -- display instances owned by db2

4. node and database Cataloguing

Db2 list db directory -- view the local node directory

Db2 catalog tcpip node Remote Ostype -- Cataloguing

TCP/IP Node

Db2 uncatalog node -- Cancel node Cataloguing

Db2 catalog DATABASE AS AT node -- Cataloguing Database

Db2 uncatalog DATABASE -- Cancel database Cataloguing

5. 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)

6. 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 status value is 0x0000. In this case, the website space can use db2tbst to view the status meaning of the Status Number, the syntax is as follows:

Db2tbst You can view the status of the serial 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 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)

7. view the table status

Query the directory view to obtain the status information about the database table. For example, the following statement is returned in SYSCAT. the names of all TABLES 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 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

8. view disk space

Check whether the active log directory of the database is full. You can use db2 get db cfg to check the location of 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

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

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. To view the backup information on DB2, use the LIST command in Hong Kong Space.

# Db2 list history backup all for Database Name

11. 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 ).

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

13. Check whether the application currently connected has an illegal connection.

# Db2 list applications show detail

Looking at these connections, the Hong Kong virtual host can see if there are any inappropriate IP connections or are not allowed to connect to 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 db2 "force application (application handle number)" to stop a separate handle.

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

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

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

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.

17. database backup and recovery

-- Offline backup

Db2 force applications all -- disconnect all connections

Db2 force application (h1, h2 ......) -- Kill a process connected to the database

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

-- Online and Incremental Backup

-- Premise: Change the database parameter logretain, userexit, and trackmod to on. After that, the database is in the backup pending state. You must back up the database offline once and then perform online and Online Incremental backup.

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

-- Archive path

DB2 update database configuration using LOGARCHMETH1 DISK:/backup

-- Change the LOGARCHMETH1 parameter 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

-- View the DBName backup status of [from YYYYMMDDHHMMSS]

Db2 rollforward database DBName to end of logs and complete noretrieve -- restores temporary data TO the roll-forward status

Db2 rollforward database DBName to end of logs and complete overflow log path ("/home/db2inst1/SQLOGDIR ")

Db2 connect to DBName

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

18. Data Export and Import

-- 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 -- convert csv TO excel

Db2 export to d: \ TableName. ixf OF ixf SELECT * from schema. TableName

-- Export data (IXF integrated universal 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 DB2 logs

Check the db2diag. log file at least once a day to see if any exception exists.

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

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

22. Database Patch Level

# Db2level

23. Obtain database configuration parameters

2. Routine O & M precautions for DB2

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 is used

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 stops working.

In both cases, you need to add space to the file system where the log is located to restore normal, to avoid the problem of full transaction logs.

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 = (main Log File + auxiliary Log File) * log file size

Where:

1) the master log file is controlled by the parameter LOGPRIMARY,

2) Auxiliary log files are controlled by the parameter LOGSECOND

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.

Check license content

$ Db2licm-l

Execute the following installation authorization

#/Opt/ibm/db2/V9.7/adm/db2licm-a/mnt/cdrom/db2/license/db2ese. lic

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, the type with compatible independent variables cannot be found as" <例程类型> " <例程名> "Authorization routine

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 Able to connect to the database

2) db2 connect User... using... cannot connect

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

This article is from the "dripping water and stone" blog. Please keep this source