Troubleshooting of DB2 faults and solutions to general problems

In my opinion, the key to solving the problem lies in identifying the types of the problem and clarifying the solutions to each problem. In addition, many database problems are caused by incorrect operations and incorrect configuration. Therefore, this article will give some good suggestions when explaining how to solve the problem to avoid the problem. This article focuses on practical methods.

There are many ways to classify problems. I have adopted two classification schemes in this article.

The first solution is whether there is an error code. That is, whether an error code is returned at the same time when an error occurs. The error code includes both the return code for executing the command and the return code for expanding the application.

An error solution with a return code is to run db2 in db2 CLP? SQLXXXX, and then take the corresponding solution based on the explanation of the problem. There are no error codes, such as database hang and high CPU usage. The experience in solving the problem will be very important and will be described in detail in this article.

The following example uses the error code to solve the problem ):

If an error occurs during database connection

db2 connect to sample SQL0332N There is no available conversion for the source code page "1386" to the target code page "819". Reason Code "1". SQLSTATE=57017

Error codes include the return Code SQL0332N) and the Reason Code "1"). Different solutions are available for different causes codes.

Run db2? Sql0332

From the output, we can see that the reason code 1 is interpreted

…… 1 source and target code page combination is not supported by the database manager. ……

You can solve this problem by setting the code page.

db2set db2codepage=1386 db2 terminate db2 connect to sample

The connection is successful.

The second classification scheme is to classify according to the scope and nature of the problem. Categories:

1. database instance Problems

2. Database Problems

3. database performance problems

4. Database-related issues in Application Development

The following describes each type of problem in detail.

I. database instance Problems

Database instance problems can be divided into two situations

1. The instance cannot be started. After running db2start, an error code, such as sql00002c, is directly returned.

If the Error Code cannot be resolved, try the following solution:

Update the instance again and log on as root,

cd /usr/opt/db2_08_01/instance/ ./db2iupdt

Tip: common causes of instance startup failure

The database does not run db2iupdt after a new patch is installed.

The Database File Permission is changed to 777, and the database file permission is required. Therefore, you cannot change all files to 777.

Database instance File deleted or damaged

The host name is inconsistent with that recorded in db2nodes. cfg.

2. When running db2start, hang is there and neither an error nor an instance can be started

This is generally because the instance is not stopped normally. You can solve this problem by running the following command:

su - db2_kill ipclean su – root

(Kill all db2 processes related to this instance to kill-9)

Then restart the instance.

3. database instance crash

When an instance crashes, first check db2diag. log and analyze the cause of database downtime based on the information in it. Check whether the db2dump directory contains the trap file. The cause can be analyzed based on the information. Generally, such problems require assistance from IBM engineers.

The causes of downtime can be divided into two categories: Database bugs, that is, database defects. Generally, if a database defect is encountered, there are temporary solutions, or you can install the latest patch to solve the problem, and IBM also provides temporary revisions to solve some problems ). The other is caused by operating systems, misoperations, and other non-product problems. Avoid downtime caused by non-product problems as much as possible.

Tip: common causes of database downtime

The system's swap space paging space is exhausted.

A process in the database is killed.

Ii. Database Problems

1. Data connection problems

The database cannot be connected. Common Errors include code page errors, communication protocol errors, and database status errors.

For code page errors, you can set db2codepage and db2country. These two variables must be set to the same value as the database using db2set.

When a communication error occurs, first check whether the environment variable DB2COMM = TCPIP has been set, and then check the SVCENAME of dbm cfg. The variable can be directly set to the port number or service name, the service name must be set to the corresponding port number in the services file. Check whether the port number is occupied by other services. After starting the database, run netstat-an | grep to check the status of the port.

TCP 0.0.0.0:50000 0.0.0.0:0 LISTENING

In another case, when you connect to the database, the database is in the backup pending status and cannot be connected. You only need to back up the database.

Tip: the cause of the database being backed up and liked

When a database is changed from cyclic logs to archive logs, the database requires an offline backup. After the database is restarted, the database is in the backup like-up state.

For a database that uses linear logs, when used as load, the tablespace will be in the backup like-up state. To avoid this situation, the load Command needs to use the copy yes or nonrecoverable parameter.

2. database corruption

The most serious problem with the database is that the database is damaged. When the database is damaged, the best way is to restore the database from the backup.

If the data cannot be recovered from the backup, you can try the corresponding solution based on the cause of the damage.

Some data files are damaged due to storage problems, but the database can still be connected. In this case, you can restore the database by exporting the table results and data of the database. Of course, the export of corrupted tables cannot be completed. You can use the export data function of db2dart to export the data of these damaged tables.

If the database is damaged to a level that cannot be connected, the only way except from Backup recovery is to use db2dart to export all the data.

Tip: How to Use db2dart to export data

Run commands

db2dart /DDEL ＃Table object data formatting start. ＃Please enter ＃Table ID or name, tablespace ID, first page, num of pages: ＃(suffic page number with 'p' for pool relative)，

Enter the table name, tablespace id, start page number, and number of pages to be exported as prompted.

3. The activity log of the database is deleted.

This problem is often encountered. It is also a situation where the database is damaged. And the database cannot be connected.

First, consider whether there is a recoverable backup. If yes, you can recover from the backup, and then roll back to the end of the log to completely recover the database. If no backup is available for recovery, you can use the IBM technical support center to help solve the problem. If you want to solve the problem by yourself, you only need to use the db2dart tool.

Tip: How to Avoid deletion of database activity logs

Enable the Image Log function of the database

Enable the log export program of the database to avoid manual deletion of logs in the active log directory.

When you must manually delete the archived logs in the active log directory, run the following command:

PRUNE LOGFILE PRIOR TO log-file-name，]

You can avoid mistakes to delete activity logs.

Iii. database performance problems

The database performance problem is generally not a fault, but when the performance problem becomes very serious, it becomes a fault.

To solve database performance problems, you can check the database configuration, such as the buffer pool and sorting heap, and check whether the database collects statistics, accurate statistics play an important role in statement optimization. Optimize SQL statements and check whether there are system resource bottlenecks.

To confirm performance problems, you must first analyze the system resource consumption. Generally, you can use operating system tools, such as the aix topas command. The performance problem of databases is generally caused by slow applications or even no response.

Tip: How to quickly locate the problem

If the CPU usage of the system is very high and IO is very small, there will be more database sorting.

If the system is busy with I/O and many CPUs are wait, it indicates that the database has too many I/O operations.

If the CPU and I/O of the system are idle, it indicates a lock problem.

If the system IO and CPU usage are very busy, it indicates that the SQL statement is executed at a very high cost.

There are generally three types of database performance problems: one is excessive CPU usage, the other is too busy IO, and the other is lock wait.

1. Quickly find SQL statements with high execution costs

First, enable the monitor switch.

db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on

When the system is busiest, run

db2 get snapshot for all applications > app.out

Find the application in the Executing state in the file and find the corresponding SQL statement to be executed.

If this method cannot be found, you can collect SQL snapshots.

db2 get snapshot for dynamic sql on > sql.out

This snapshot records the snapshot information of the dynamic statement.

Total execution time (sec.ms) = 0.000000 Total user cpu time (sec.ms) = 0.000000 Total system cpu time (sec.ms) = 0.000000

This information is used to find the most time-consuming statement.

2. How to optimize SQL statements

DB2 provides good tools for SQL statement optimization. First, analyze the SQL statement to see if it causes performance problems. We can use db2expln to view the SQL statement access plan and execution cost.

First, write the SQL statement found to SQL. in a text file, end with ";", and then run

db2expln –d -f -z “;” –g –o sql.exp

Check SQL. exp to see the execution cost of this SQL statement.

If you confirm that the statement has a problem, you can use db2advis to optimize the statement by creating an index.

db2advis –d -i sql.in

If the statement cannot be optimized by creating an index, it can only be optimized from the business perspective.

3. What should I do if the lock problem occurs?

There are two general locks: Lock wait and deadlock. First, check the database configuration parameter locktimeout. This parameter cannot be set to-1, because it may cause some applications to wait for an indefinite period of time.

You can use snapshots to determine which database issues occur.

db2 get snapshot for db on

View the following content in the output:

Deadlocks detected = 0 Lock Timeouts = 0

If a deadlock occurs, you can create a deadlock monitor to analyze the cause of the deadlock. The command is as follows:

mkdir /tmp/dlmon db2 connect to db2 create event monitor dlmon for deadlocks with detail write to file ‘/tmp/dlmon’ replace

1. Using functions to solve the problem of date and time in DB2
2. DBase: Common commands and skills DB2 must understand
3. Use tablespace backup to quickly restore the IBM DB2 database