Blog
DB2utor

Categories

August 04, 2009

A Morning Jolt for Response Times

Have end users ever told you that response times are slow early in the day, but then return to normal later on? Or perhaps the problem recurs on Monday mornings in shops that bring DB2 down for maintenance each Sunday. After some research, you find that the first SQL statement is taking a hit to wait for DB2 to open the datasets. If you're working with an application like PeopleSoft or SAP that opens thousands of datasets, you can see the significance of this issue.

Some shops just live with this situation and know that Monday mornings will be bad for the first few users starting out the day. Others have built home-grown solutions to open all of the datasets before people start working.

However, with DB2 V9 for z/OS, IBM now has a solution to this long-standing issue: the access database command. You might not have heard much about it, but if your database is taking an early-morning performance hit, you should know about this enhancement.

The access database command command is very simple:

-ACCESS DATABASE (database-name) SPACENAM(space-name)
      MODE(open|ngbpdep) part(integer | integer1:integer2)

When using the MODE(open) option, DB2 opens the datasets on the local member of a data sharing group. Another option, MODE(ngbpdep), removes the group buffer pool-dependent status. When scheduling batch jobs, you'll want to run this command on the member just before the batch stream runs to avoid the performance impact associated with group buffer pool locks.

The bad news is you cannot "wild card" or use a pattern to open all datasets (table space and index space) within a database. You must list each and every page set. I suggest using SPUFI to generate the statements, and then putting them in a batch job that can be run on your scheduler.

SELECT '-ACCESS DATABASE('
                CONCAT RTRIM(TS.DBNAME)
                CONCAT ') SPACENAM('
                CONCAT RTRIM(TS.NAME)
                CONCAT ') MODE(OPEN)'
FROM SYSIBM.SYSTABLESPACE TS
WHERE TS.DBNAME = 'DSNDB06'
WITH UR;

Sample generated script:

-ACCESS DATABASE(DSNDB06) SPACENAM(SYSCOPY) MODE(OPEN)
-ACCESS DATABASE(DSNDB06) SPACENAM(SYSDBASE) MODE(OPEN)

For indexes I would use this SQL:

SELECT '-ACCESS DATABASE('
           CONCAT RTRIM(I.DBNAME)
           CONCAT ') SPACENAM('
           CONCAT RTRIM(I.INDEXSPACE)
           CONCAT ') MODE(OPEN)'
 FROM SYSIBM.SYSINDEXES I
 WHERE I.DBNAME = 'DSNDB06'
 WITH UR;

Sample generated script:

-ACCESS DATABASE(DSNDB06) SPACENAM(DSNACX01) MODE(OPEN)
-ACCESS DATABASE(DSNDB06) SPACENAM(DSNADH01) MODE(OPEN)

If you've used this feature, please post a comment so others can learn how it worked for you.