This article mainly tells you about the DB2 database optimization experience. If you are interested in the DB2 database optimization experience, you can click the following article to view it, the following is a detailed description of the relevant content, hoping to help you in this regard.
The system was launched for about two months. IBM engineers were asked to perform a DB2 database Optimization on 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 of optimizing DB2 databases.