Analysis on DB2 troubleshooting and best practices

Therefore, this article will give some suggestions when explaining how to solve the problem in DB2 to avoid the problem. When using a database, you may encounter different problems. I personally think that the most important thing to solve the problem is to distinguish the types of problems and understand 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 in DB2 to avoid problems. 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.

Based on the error code to solve the problem (in the following article, when this method is required to solve the problem, it will not be repeated ):

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 are divided into return codes (SQL0332N) and Reason codes (Reason Code "1"). Different solutions are available for different cause 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

Let's talk about the troubleshooting and best practices of DB2, and explain 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, by installing the latest patch, IBM also provides temporary revisions to some issues (pay-as-you-go ). 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 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.

The above content is a brief introduction to DB2 troubleshooting and best practices. I hope you will have some gains.