Experience in optimizing DB2 Databases

The following articles mainly describe the experience of optimizing the DB2 database. I saw the information about the experience of optimizing the DB2 database on the relevant website a few days ago, I will share it with you. The following is a detailed description of the article. I hope you can learn from it.

Database, experience, original database, experience, original

About two months after the system was launched, IBM engineers were asked to tune the database. The main gains were as follows:

1. The application server must be separated from the Database Server

2. If multiple databases exist, separate the hard disk (I/O busy)

3. Data and logs in each database must be separated by hard disks (I/O busy)

4. There are mainly the following points for databases:

View hit rate:

Turn on the instance-level switch: mainly the following parameters:

 
 

  
  
DB2 update dbm cfg using DFT_MON_BUFPOOL ON;  
  
  
DB2 update dbm cfg using DFT_MON_TABLE ON;  
  
  
DB2 update dbm cfg using DFT_MON_STMT ON;  
  
  
update monitor switches using bufferpool on lock on sort on statement on table on uow on; 
 
 

Snapshot Capture:

 
 

  
  
DB2 get snapshot for dynamic sql on dbname;  
  
  
DB2 get snapshot for table sql on dbname;  
  
  
DB2 get snapshot for bufferpools on dbname; 
 
 

Check the row read, row write, and hit rate in the snapshot file to make corresponding adjustments.

5. Modify the buffer pool memory size:

First, view the details of the buffer pool:

 
 

  
  
select * from syscat.bufferpools; 
 
 

Modify the buffer pool size:

Alter bufferpool bufferpool_name immediate size change the size of the automatic;

6. dynamically monitor the usage of the buffer pool:

 
 

  
  
DB2mtrk -i -d 
 
 

7. view database logs for a certain period of time

 
 

  
  
DB2diag -time 2009-05-23.00.00.00:2009-05-23.00.30.00  
 
 

The above content is an introduction to the experience in optimizing DB2 databases. I hope you will gain some benefits.