Tips to Improve DB2 Performance!

By admin News

db2-performance-6480828 Apart from several problems, the issue of performance is also one of the most concerned problems for DB2 users. And if you are Database Administrator then this concerned becomes more important for you. You have to look at such problems more carefully and you have to take all the appropriate measures to solve this. It means being a DBA, you have to look for all the potential fields where there are probability of improvements. Hence, in order to increase the performance of your DB2, here are some tips that you can follow:

1 – Make sure you have enough DB2 agents: First of all you have to ensure that you have enough DB2 agents for handling your workload. Give the following command to find the number of agents

db2 “get snapshot for database manager”

After giving the above commands 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

From the above command, if you notice that your agents are stolen or waiting from the application such as Agents missing or stolen from another application or agents is waiting for token, it is recommended that you must increase the number of agents available for your database manager.

2 – Turn-on Monitor Switches: It is advised that you must turn ON the monitor switches. If they are not switched ON, then you will not have the detail information regarding performance that you want. In order to turn On switches follow the below command:

db2 “update monitor switches using lock ON sort ON bufferpool ON uow ON

table ON statement ON”

3 – Look at Maximum Open Files: The DB2 is built in such a way that it can easily manage several open files at one time. You can give the MAXFILOP command, by which you will come to know about the upper limit of open files that DB2 can handle at same time. And when this maximum limits is reached then DB2 tablespace files starts to open and close. And this issue slows down the performance of db2. Hence it is important to find out whether DB2 is closing files or not. You can check that by following the below command:

“get snapshot for database on DBNAME”

After this, just check for the lines that reads – Database files closed = 0.

If you notice that your files are being closed then to stop closing and opening of tablespace you must increase the value of ‘MAXFILOP’. Give the below command for the same:

db2 “update db cfg for DBNAME using MAXFILOP N”

However, if you want to really keep your DB2 performance free of errors and corruption then you use DB2 file repair tool. It will scan the entire DB2 file and help you in fixing the errors and corruption and will keep your DB2 application run smooth and efficiently.

By keeping the above mentioned points in mind and checking them in regular interval of time, you can improve and maintain the performance of your DB2 and work quickly and smoothly.

Steps to Improve DB2 Performance:

Step 1: Download, Install and Run DB2 File Repair software. It will provides two options to repair db2 file, first option is for if the database is attached to DB2, then you can select the option ‘Repair the database stored at default location‘, and if the database is not attached to DB2 then choose the manual selection of database.

Step 2: It will provide you the list of database attached to DB2. Select the particular database you want to repair and recover corresponding database objects.

2-2-3360590