Discussion on DB2 database troubleshooting and best practices (1)

When using a database, you may encounter different 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"

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 <inst_name>

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-<inst_owner>

Db2_kill

Ipclean

Su-root

(Kill all db2 processes related to this instance by killing kill-9 <pid>)

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, you can run netstat-an | grep <port> to view 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.