Hi EveryBody,
Recently I have been asked to review an application batch. The batch is a fairly simple one which runs a series of Insert Statements. After running db2top in background mode for bufferpools, I found that the Average Execute Time is almost 10 times the Average CPU time. There are no lock escalations. After narrowing the suspects to I/O, I ran a snapshot on the db. The snapshot shows the that the Asynchronous Write Percentage (AWP) hovering between 25%-35%.
My first question is:-
1. Is the low AWP the sole cause of the high Execution time? If No what are the other suspects.
2. If I need to increase the AWP are there any guidelines. I know some of the parameters affecting this are hardware dependent, so I am not expecting a definitive answer .

All advice is welcome.

Ashwin

Here are some things to look at:

1. Check the following in "get db cfg"
   • log file size at least 10000
   • log buffer size 256. If you have LOB data, then 512
   • number of page cleaners set to automatic
2. Make sure all LOB's in separate tablespace with File Caching On.
3. Setup multiple disk mount points (with no overlapping spindles between them) and use each of them when defining automatic storage paths (or for containers on non-automatic storage tablespace definitions)
4. Make sure bufferpool is sufficiently large for tablespaces used for the data and indexes being inserted. I would avoid "-2" automatic setting.
5. db2set DB2_SKIPINSERTED=ON (instance restart required)
6. db2set DB2_USE_ALTERNATE_PAGE_CLEANING (instance restart required)
7. Eliminate any unnecessary or questionable indexes.
8. If you have LOB data, and using DB2 9.7, use 32K page size for table and define INLINE LENGTH (as large as possible) for LOB columns.