Sunday, November 15, 2009

REORG SHRLEVEL CHANGE – aiming at 24x7 – Part II

In the previous post I started talking about the REORG utility with the shrlevel change option. I told you about two problems that one would probably encounter when running online REORG with the default parameter values:
1. The default value of MAXRO is 300 seconds, while the default timeout interval (IRLMRWT) is 60 seconds which means, once DB2 will get to the last log iteration, most likely some concurrent transactions will time out.
2. If we have a long running reader and we are running a concurrent REORG, on top of problem number 1, the REORG might time out waiting for the DRAINING of the tablespace (since the long reader holds a claim), either leaving the tablespace in RO state, preventing other transactions from updating the table, or terminating completely causing all the work it did to be rolled back (it is also important to note that when the REORG is waiting for the DRAIN, other transactions wait as well).

So what can we do? The REORG utility provides several parameters to allow us to deal with the problem:
MAXRO               - maximum time for last log apply iteration
DRAIN                 - specifies drain behavior when the last iteration is to be applied
DRAIN_WAIT     - number of seconds the utility will wait when draining
RETRY                - number of retries if the drain failed
RETRY_DELAY - number of seconds to wait between retries

By setting MAXRO to a low value, we can make sure no users transactions (which try to update the table) will be timed out during the last log apply iteration. By setting DRAIN_WAIT we make sure no users transactions (which try to read the table) will be timed out during the switch phase (update activity is not allowed either way).

If DB2 is unable to drain the object, the utility will abend and leave the objects in UTRO access, so update operations fail with –904 reason 00C90083.

So this gets us to the million dollar question: How can we be sure that if the REORG utility managed to get the DRAIN for the last log iteration, it will also be able to get the DRAIN for the SWITCH phase, and complete the processing as fast as possible?
Or put differently – how can we minimize the impact of the REORG utility on other transactions.

Another parameter which will take part in our solution is the DRAIN parameter. This parameter tells the REORG utility which type of DRAIN to ask for before the last log iteration phase. The default value is writers, meaning, only write activity should be drained. By setting this parameter to ALL, we tell REORG to drain all activity to the tablespace before entering the last log iteration. Once the REORG utility gets the DRAIN, the last log iteration will take no more than MAXRO seconds (or at least it shouldn't), and the SWITCH phase should be rather instantaneous (since no DRAIN is necessary).

If on the other hand we have a long running UOW, the REORG utility will not get the DRAIN and will never start the last log apply iteration and abend, leaving the objects with UTRW status, and allow concurrent user activity.

By setting the DRAIN_WAIT parameter we tell REORG how long to wait for that drain, so we can make sure no concurrent transactions will time out because the DRAIN request.

And now, for some test results…
I wrote 2 REXX scripts, one of which performs insert operations in a loop, and the other one fetches a single record from the database with RS isolation, and then goes to sleep for a given interval.

In the first test case, I executed both REXX scripts concurrently on a table with row level locking. Both of them were running, and then I tried to execute REORG SHRLEVEL CHANGE with the following parameters:

MAXRO 10
DRAIN ALL
DRAIN_WAIT 10
RETRY 5
RETRY_DELAY 5

The REORG utility SYSPRINT looked like this

DSNU394I -DB2T DSNURBXA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=80 FOR INDE
DSNU391I DSNURPTB - SORTBLD PHASE STATISTICS. NUMBER OF INDEXES = 2
DSNU392I DSNURPTB - SORTBLD PHASE COMPLETE, ELAPSED TIME = 00:00:00
DSNU1122I -DB2T DSNURLOG - JOB REORGJOB PERFORMING REORG
WITH UTILID REOUTIL UNABLE TO DRAIN DBMOMI.TSMOMI.
RETRY 1 OF 5 WILL BE ATTEMPTED IN 5 SECONDS
DSNU1122I -DB2T DSNURLOG - JOB REORGJOB PERFORMING REORG
WITH UTILID REOUTIL UNABLE TO DRAIN DBMOMI.TSMOMI.
RETRY 2 OF 5 WILL BE ATTEMPTED IN 5 SECONDS
DSNU1122I -DB2T DSNURLOG - JOB REORGJOB PERFORMING REORG
WITH UTILID REOUTIL UNABLE TO DRAIN DBMOMI.TSMOMI.
RETRY 3 OF 5 WILL BE ATTEMPTED IN 5 SECONDS
DSNU1122I -DB2T DSNURLOG - JOB REORGJOB PERFORMING REORG
WITH UTILID REOUTIL UNABLE TO DRAIN DBMOMI.TSMOMI.
RETRY 4 OF 5 WILL BE ATTEMPTED IN 5 SECONDS
DSNU1122I -DB2T DSNURLOG - JOB REORGJOB PERFORMING REORG
WITH UTILID REOUTIL UNABLE TO DRAIN DBMOMI.TSMOMI.
RETRY 5 OF 5 WILL BE ATTEMPTED IN 5 SECONDS
DSNU590I -DB2T DSNURDRN - RESOURCE NOT AVAILABLE, REASON=X'00C200EA', ON DBMOMI
DSNT360I -DB2T ***********************************
DSNT361I -DB2T * DISPLAY DATABASE SUMMARY
* GLOBAL CLAIMERS
DSNT360I -DB2T ***********************************
DSNT362I -DB2T DATABASE = DBMOMI STATUS = RW
DBD LENGTH = 197856
DSNT397I -DB2T
NAME TYPE PART STATUS CONNID CORRID CLAIMINFO
-------- ---- ----- ----------------- -------- ------------ --------
TSMOMI TS RW,UTRW DB2CALL DB2RXINS (WR,C)
TSMOMI TS RW,UTRW DB2CALL DB2RXINS (CS,C)
TSMOMI TS RW,UTRW DB2CALL DB2RXLCK (CS,C)
******* DISPLAY OF DATABASE DBMOMI ENDED **********************
DSN9022I -DB2T DSNTDDIS 'DISPLAY DATABASE' NORMAL COMPLETION
DSNT500I DSNUGBAC - RESOURCE UNAVAILABLE
REASON 00C200EA
TYPE 00000200
NAME DBMOMI .TSMOMI

The REXX script that performed the insert loop kept on going without interruption, so, this combination of parameters provided a solution to the long running reader problem.

The following tests were conducted in order to decide which values one should choose for the above mentioned parameters. In these tests, I executed the REXX that performs inserts concurrently using 5 jobs, thus creating a greater load on the table.

The table that was used was created with the following SQL:
CREATE TABLE MOMI.MOMITEST (
COL1 INT NOT NULL,
COL2 TIMESTAMP NOT NULL,
COL3 CHAR(40) NOT NULL
) IN DBMOMI.TSMOMI;

The table had 5 indexes on COL1 (clustered), COL1+COL2, COL1+COL2+COL3, COL2+COL3, COL1+COL3

After several seconds of insert processing (by the 5 jobs running the insert script), I executed the REORG utility with the following parameters:
MAXRO 1 (this is 1 intentionally, not 10)
DRAIN ALL
DRAIN_WAIT 10
RETRY 5
RETRY_DELAY 5

During the time of this test, the insert rate was (each task performed 200k inserts):

Task ID Min Inserts/Sec Avg Inserts/Sec Max Inserts/Sec
1 44194 564
2 44 194 557
3 16 194 566
4 23 194 570
549194573

So by average, the table had 973 inserts per second (although, in later testing I was able to get an insert rate of about 1600 records per second).

Here are the results of the REORG utility:

Records in Table
Log records To apply
# Log iterations
Log phase Elapsed time
152369
13141
2
00:00:07
184795
18170
3
00:00:23
273933
27400
3
00:00:13
354644
70771
4
00:00:35
445782
82947
4
00:00:43
531495
87132
3
00:00:36
742060
138428
5
00:01:13
1275055
256657
5
00:01:55
1535205
148244
4
00:01:03
1760847*
232682
6
00:01:50
2001291**
216785
5
00:01:23
2218251**
405000
5
00:02:54
2637665*
320874
7
00:01:37
2965924*
407336
8
00:01:52

During the tests, none of the inserting processes timed out.

* - this REORG was executed with MAXRO 1, and received the LONGLOG message
** - this REORG was executed with MAXRO 3, and received the LONGLOG message

The LONGLOG message (DSNU377I) indicates REORG believes it will not be able to catch up with the log, that is, the rate at which records are being updated is higher than the rate REORG is able to apply them. If such a situation occurs, you can take several actions in order to fix it (although it might just solve itself eventually) like increasing the size of MAXRO.

When identifying a LONGLOG condition, the REORG utility will write a message to SYSPRINT and will continue to execute indefinitely (this is the default behavior which can be changed by the alter utility command or by specifying other behavior using the LONGLOG parameter of the REORG control statement).
Conclusion; Even though the insert rate that was tested was not very high, it does prove that the REORG utility can be executed online without causing other transactions to time out. For higher insert rates, one can increase the value of MAXRO, or use partitioning in order to reduce the load on any single file.

I hope this post helped clearing things out,
Till next time…

3 comments:

  1. finally , a post worth reading.

    yellowg.

    ReplyDelete
  2. arg...
    i meant , finally a blog worth reading,
    to imply that most of the stuff you see on the db2planet is a waste of time,


    ... not that this is the first good post of this blog.

    yellowg

    ReplyDelete
  3. Ome other item to keep in mind is to make use of MAXRO DEFER. I use this qute often for those heavy hit tables or very large partitioned tables. This way I can start the reorg well ahead of my maintenance window, then when window time arrives I can alter the maxro and let the utility complete or go into the build2 phase(not V9 yet).
    Dave Nance

    ReplyDelete