Top Tips to Improve DB2 Performance

Along with various other issues, the performance issue is one of the major concerns for DB2 users. If you are a Database Administrator (DBA), then you have to look at this problem more carefully and take all the measures to rectify it. That means being DBA; you have to look for all the possible areas where there are chances of improvements. Therefore, in order to help you out, we are providing you some tips to get back the performance of your DB2:

• Turn-on Monitor Switches: It is suggested to turn on the monitor switches. If they are not, then you will not have the information regarding performance that you require. Issue the following command to turn-on the monitor switches:

db2 “update monitor switches using   
lock ON sort ON bufferpool ON uow ON   
table ON statement ON”

• Ensure You Have Sufficient DB2 Agents: Make sure that you have sufficient DB2 agents for handling your workload. Issue the following command to find the number of agents:

db2 “get snapshot for database manager”

After that look for the following lines:

High water mark for agents registered = 7  
High water mark for agents waiting for a token = 0  
Agents registered= 7  
Agents waiting for a token= 0  
Idle agents= 5  
Agents assigned from pool= 158  
Agents created from empty Pool = 7  
Agents stolen from another application= 0  
High water mark for coordinating agents= 7  
Max agents overflow= 0

In these commands, if you find that your agents are waiting or stolen from the application like Agents waiting for token or Agents stolen from another application, then increasing the number of agents available for database manager is recommended.

• Watch out Maximum Open Files: The DB2 is designed in a way that it can manage multiple open files at a time. With MAXFILOP command, you can get to know about the upper limit of opened files that DB2 can handle concurrently. However, when this maximum limit is reached, DB2 starts closing and opening tablespace files. This slows its performance. Issue the following command to find out if DB2 is closing files or not: db2 “get snapshot for database on DBNAME”. After that, look for the line that reads: Database files closed = 0. If you find that your files are being closed, then increase the value of ‘MAXFILOP’ till this closing and opening stops. Issue the following command for the same:

db2 “update db cfg for DBNAME using MAXFILOP N”

By keeping these points in mind and checking them on a regular basis, you can improve the performance of DB2 and work more efficiently and quickly.