Improving system performance on DB2

[jay_b2]

Dears

I have set up a new system and increased the number of WP. Will increasing the number of update process help to improve performance.

Is there any way I can speed up my DB2 to handle more load or more requests per unit time ?

Thanks,

[Former Member]

Hi,

Whatever may be your OS platform, first step towards performance tuning would be to identify whether you have any bottlenecks. Try to dig out the root cause of the bottleneck

1) Monitor SAP buffer utilization using ST02

2) Monitor database and SAP response time using ST03N.

3) Check database fill level using db02.

After observing above mentioned parameters you can get a way to fine tune your system with the SAP notes mentioned in above response.

Hope this helps.

Regards,

Deepak Kori

[jay_b2]

I have issues with speeding up D/B Inserts / Updates. Its definitely not about buffer as I'm dealing with upgrades and load generation. Any clue ?

Second, what is the database fill level and where do we measure it ?

This is what I can see in topas... Does this mean my upgrade is slow because of the disk utilization ? Is 2000 Kbps speed of fetching data from disk good in IBM Power 7 machines ? Any clue here?

Topas Monitor for host:    ailpe40dbcs          EVENTS/QUEUES    FILE/TTY                                                                     
Mon Nov 21 06:50:31 2011   Interval:  2         Cswitch    2425  Readch  1789.8K                                                                                
Syscall    7046  Writech  716.0K                                                              
CPU  User%  Kern%  Wait%  Idle%                 Reads       462  Rawin         0                                                              
ALL    6.4    0.8    6.7   86.1                 Writes      338  Ttyout      449                                                                                
Forks         0  Igets         0                                                              
Network  KBPS   I-Pack  O-Pack   KB-In  KB-Out  Execs         0  Namei       104                                                              
Total   712.4    869.5   870.0   355.9   356.5  Runqueue    0.0  Dirblk        0                                                                                
Waitqueue   0.5                                                                                
Disk    Busy%     KBPS     TPS KB-Read KB-Writ                   MEMORY                                                                       
Total    96.0   2286.0   188.0  1442.0   844.0  PAGING           Real,MB   31616                                                                                
Faults      652  % Comp     59                                                                
FileSystem        KBPS     TPS KB-Read KB-Writ  Steals        0  % Noncomp  40                                                                
Total              2.4K  781.0    1.7K 715.3    PgspIn        0  % Client   40                                                                                
PgspOut       0                                                                                
Name            PID  CPU%  PgSp Owner           PageIn      363  PAGING SPACE                                                                 
R3trans    25166018   3.5   3.4 e40adm          PageOut      31  Size,MB   42496                                                              
R3trans    24838360   1.3  10.8 e40adm          Sios        331  % Used      0                                                                

[Former Member]

Hi,

Disk    Busy%     KBPS     TPS KB-Read KB-Writ
Total    96.0   2286.0   188.0  1442.0   844.0 

It definitely seems issue with your disks orientation and layout. Utilization is 96% percent.

1) What is the RAID configuration you have done ?

2) How is the stripping done in the storage ?

3) Is the SAN storage design done based on number of IOPs required ?

4) Whats the RPM for your disk ?

Regards,

Deepak Kori

[Former Member]

> I have set up a new system and increased the number of WP. Will increasing the number of update process help to improve performance.

Not Exactly.

Performance tuning is really a wide concept, try to go through the guide -

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/60fe97e1-12e3-2c10-2f8d-ccb9a5450...

Thanks

[Former Member]

Hi Jay,

assuming your DB2 is running on Linux Unix or Windows, you have to set up your system according to note

1329179 DB2 v9.7 standard parameter settings (if your release is 9.5/9.1 check the linked notes)

You should also check the DBA guide

http://service.sap.com/operationsNW70 -> Database-Specific Guides

this note might be useful as well

25528 Parameter rdisp/max_wprun_time

your system should perform fine with this setup. If not, then you will have to make further adjustments depending on your requirements.

Let me know if your DB2 is running on mainframe (z/OS ,OS390 ), because the above recommendations wouldn't apply in this case.

regards, Javier