DB2 Performance tunning

[vallish]

Hi All,

I am struggling because of the update performance of db2 . Is it possible to improve update performance using the bufferpool size of tablespace?? I read it in some document but they didn't say how to change or how to relate a tablespace with the perticular table.Also can we disable logging in db2 level.(not the NOT LOGGED INITIALLY!! any other tech) .

Also How to improve my db2 database update performance its becoming poorer every day!!!!.

Advance Thanks And Hoping all will help me to improve the db2 update perf.
Vallish

[tsaul]

What DB2 environment do you mean? LUW or z/OS. I'm mostly z/OS, so I will give that view.
In z/OS you generally specify the Tablespace when defining the Table. You can also find by Selecting from SYSIBM.SYSTABLE S there is a column TSNAME.

As to improving INSERT performance, check your Freespace and Freepage. Also how many Indexes are defined. Each Index needs to be updated for every INSERT. In DB2 V8 and V9 there are Muliple Inserts available.

The NO LOGGING is only available from DB2 V9 (z/OS) and can be switched by issuing the ALTER TABLESPACE LOGGED / NOT LOGGED. This can leave the Tablespace in Copypending status, so it needs to be tested.

[vallish]

What DB2 environment do you mean? LUW or z/OS. I'm mostly z/OS, so I will give that view.
In z/OS you generally specify the Tablespace when defining the Table. You can also find by Selecting from SYSIBM.SYSTABLE S there is a column TSNAME.

As to improving INSERT performance, check your Freespace and Freepage. Also how many Indexes are defined. Each Index needs to be updated for every INSERT. In DB2 V8 and V9 there are Muliple Inserts available.

The NO LOGGING is only available from DB2 V9 (z/OS) and can be switched by issuing the ALTER TABLESPACE LOGGED / NOT LOGGED. This can leave the Tablespace in Copypending status, so it needs to be tested.

I am using db2 8.1 version in suse Linux .

[docdiesel]

Hi,

increasing the bufferpool size is always the first attempt and usually shows significant impact, esp. if you started a new database with default parameters. But increase the bufferpool with caution and check the resukting performance because every database has a point when adding memory to the bufferpool doesn't show much effect any more.

Another parameter you may play with is CHNGPGS_THRESH which usually is 60%. Try setting it to 80 which means the page cleaners are starting their work later. Additionally, you should increase their numbers (NUM_IOCLEANERS ) so the db keeps using asynchronous page cleaning on the bufferpool once CHNGPGS_THRESH is reached. This can have high impact on databases with many write operations.

If your table gets lots of inserts but few updates, then the APPEND option could be interesting for you. It tells DB2 to write new records always to the end of the table and never to look for free space in between existing records (e.g. space freed after deleting records). Use "ALTER TABLE schema.tablenam e APPEND ON/OFF" to enable/disable this option.

Regards,

Bernd

[contactsiva14]

I think otherwise.Setti ng CHNGPGS_THRESH to higer value may not be beneficiel in this case where we have frequent updates.This is because the DB has to wait until 80% of bufferpool to be filled up before activating num_iocleaners. Setting to a lower value might be beneficial becuse the dirty pages can be cleared well in advance.I would say you can try 20%.

Wiht Regards
Siva
IBM Certified DB2 DBA

[vallish]

Hi all,
Thanks for your valuable suggestion.

In my Scenario i need to do both read and update the database (not necessarily same record ).I am getting good read performance.But Update performance is very poor. I will try the thrashing parameter.
Thanks
Vallish

[niladri123]

Please try the following:


CREATE REGULAR TABLESPACE MYTBSP PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL MYDEFBPOOL DROPPED TABLE RECOVERY ON;



CREATE TABLE TAB1 (...........) IN MYTBSP

This most of the time improves ur performance