Folks,
My shop has a huge application under which about 45 sub applications are running which is critical to running the business.The platform is OS390 2.8,DB2 V6.1 ,CICS,NATURAL.

There had been significant increase in the Application and DB2 response times for my DB2/CICS transactions.The benchmark was 0.20sec (avg resptime) and since the start of this year it has been dwindling between 0.21s to 0.24s.

The are few sub applications for which the average response time is about a minute.

The Bool n Baggage DB2 monitor metrics showed SYNC I/O for this DB2 applications as a whole is showing Uptrend.

We tried with dymanic cacheing for few of the objects but it also didnot reduce the response time.


Tuning work that I did so far:
------------------------------
I took a sample high response time application selected the costliest package then SQL in that package, did some maintenance work on it (reorg,runstats,rebind).the response time reduced for that package for 2-3 weeks.But it creeped up again after 2 weeks.

We generate a weekly report about the avg. response times.The report shows 2 to 3 sub application that has consistently high resp. time. But other applications does not consistently figure in the report every week.It changes every week.

Folks,I need your suggestions me what kind of approch should I make for this performance tuning task.I am thinking of bufferpool tuning as SYNC I/O is increasing.Let me know if this wll help.

Thanks!!!
Prasad Mani

Hi!
B&B is pretty nice monitor, we have it too! Of course we have other tools also...

But some questions. Are those slow transactions doing updates? How good is your bufferpool hit ratio? How much inserts you are doing in this 2 week time? And are there some old data you can move to history tables (or tape)?

If there are constantly many new rows coming in and some old rows going out now and then (by some cleaning program), then actually it's a very good idea to do REORG first, then run your cleaning program and only then RUNSTATS and REBIND. This way you can have clearly more freespace than it's possible otherwise.

Cheers, Bill

It is hard to know from the scant information provided, but I suspect it may be related to lack of reorgs combined with too little percent free on the table and/or indexes.

There should be enough percent free after a reorg to handle all inserted data on the table and indexes before the next reorg. So either you have too little percent free or do not reorg frequently enough. When figuring out the correct percent free to use on the table and indexes, you need to understand where new rows are inserted (in the middle or at the end) depending on the nature of the data, and then customize the percent free accordingly. Some indexes may need very little percent free, and some need a lot.

Personally, I prefer to reorg on a regular schedule during a defined maintenance period (like weekends maybe) rather than wait until a problem occurs. However, if there is not enough time to do regular reorgs, or you are charged for CPU time, then that may be a different story.

Of course, this is just a guess based on your description of the symptoms and there could be many other issues that are contributing to the problem.

Hi folks,
Adding on to the details,

1) My shop gives only 1 hour of down time per week for this very critical application.

2) I was analysing bufferpool stats through platinum subsystem analyser and the details for 06/07/2004 is attached in xls.Clearly the bufferpool Hit ratio is very low for some of the BP's.

Should I start looking into BP performance first or Reorg/percent free stuff ?Please suggest.

You need to look at both. You must coordinate your reorgs with your percent free. One hour per week may be enough to do reorgs if you do them simultaneously.

OMG, you have way too many bufferpools. This is major hit on performance and hit ratio. Not counting your 32K bufferpools, you should have the following:

BP0 bufferpool for the catalog, small tables, and small to medium size indexes.
BP1 bufferpool for medium to large tables and large indexes.
BP2 bufferpool for very large tables, especially those with ad-hoc queries.

You currently have 147,000 pages for BP0 through BP22. Divide that amount approximately as follows:

BP0 - 30,000
BP1 - 60,000
BP2 - 57,000

Combine BP32K with BP32K1.

You currently have 1,180 MB for 32K bufferpools and only 602 MB for 4K buffer pools (I am assuming that BP0-22 are 4K). This is probably way off. You probably should cut the 32K bufferpools to about 200 MB (or possibly less) and add 900 MB to 4K bufferpools. But I don't really know much about your application other than it is a high volume transactions system.

So the revised allocation (with same total memory) would be (in pages):

BP0 - 90,000 4K pages
BP1 - 180,000 4K pages
BP2 - 175,00 4K pages
BP32 - 6,000 32K pages

Marcus has it right!

You cannot focus on one problem only. However, since after a reorg you have a timeperiod with good responce time, I'd personally like to take closer look into reorganisations. We are in DB2 V7, so we run online-reorg quite offen, but I think that DB2 V6 does not support that.

We had a big bufferpool review 1 year ago. Overall performance did not improve that much, but number of superslow transactions fell down. Since we have used SQL-sentences using INDEX ONLY quite a lot, we found that having separate pool for indexes was (in our case) very usefull.

But as DB2 guru Richard Yevitch used to say about performance: "It depends"

Cheers, Bill