Post reorg Backup bloated about 20 GB

itsonlyme44 · #1 (**permalink**) 02-11-09, 13:26

running DB2 9.5 on Windows. Once a month I run reorgs on all of the Tables in our Datamart. The first time I run a Full-Offline AFTER the reorgs, my 65gb backup bloats to over 95gb - then goes back to normal (65gb) until the next time I run a full set of reorgs.

Can anyone explain why this is happening and what I can do to pretent it???

here is my reorg script:

Quote:

CONNECT TO PRODDM;
REORG TABLE KCDWHPRC.T8ACT ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8CP ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8CPA ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8CVG ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8PCL ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8PLP ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8POL ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T8VDV ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9ACB ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9ACM ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9ACU ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9ADE ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9AGC ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9APB ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9ASR ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9CFG ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9CLI ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9CTC ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9DAT ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9DVP ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9ERL ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9LOC ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9PH ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9PSC ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9PSR ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9PST ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9WF2 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TAF35 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TAF37 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TAMEX ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TBNDV ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCATX ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCDSA ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCDSD ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCFLW ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCHDL ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCHTX ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCM13 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCRCY ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TCVDC ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TDMAV ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TEDIT ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TFR09 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TFR11 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TFR26 ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TIR ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TMVTX ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TPD ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TPH ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TPOLL ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TPOLN ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TREQT ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TSPND ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TUSRP ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZACA ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZAGY ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZAPX ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZCCR ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZCFR ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZLDD ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZLDH ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZLMD ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZLMF ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZLMH ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZLMP ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZPCN ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZPVL ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZSGA ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZVDV ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.TZVRT ALLOW READ ACCESS;
REORG TABLE KCDWHPRC.T9EXC ALLOW READ ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8CP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8CPA ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8CVG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8PCL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8PLP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8POL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T8VDV ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9ACB ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9ACM ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9ACU ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9ADE ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9AGC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9APB ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9ASR ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9CFG ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9CLI ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9CTC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9DAT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9DVP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9ERL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9LOC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9PH ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9PSC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9PSR ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9PST ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9WF2 ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TAMEX ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TBNDV ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCATX ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCDSA ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCDSD ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCFLW ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCHDL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCHTX ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCRCY ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TCVDC ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TDMAV ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TEDIT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TIR ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TMVTX ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TPD ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TPH ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TPOLL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TPOLN ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TREQT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TSPND ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TUSRP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZACA ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZAGY ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZAPX ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZCCR ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZCFR ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZLDD ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZLDH ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZLMD ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZLMF ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZLMH ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZLMP ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZPCN ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZPVL ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZSGA ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZVDV ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.TZVRT ALLOW WRITE ACCESS;
REORG INDEXES ALL FOR TABLE KCDWHPRC.T9EXC ALLOW WRITE ACCESS;
COMMIT WORK;
CONNECT RESET;

n_i · #2 (**permalink**) 02-11-09, 13:56

Are you making sure that reorg fully completes before the backup? The backup image includes temporary tablespaces too, so if they are utilized the image size will be increased.

itsonlyme44 · #3 (**permalink**) 02-11-09, 14:04

Yes, the backup runs after the reorgs, but not immediately after. Should I be quiescing the Database in between? or restarting the DB?

Marcus_A · #4 (**permalink**) 02-11-09, 14:31

I can understand how a database gets larger after a reorg in some cases, because a reorg re-creates free space that may have been used by inserts. But I don't see how it could shrink back to a lower size by itself unless you deleteting some rows, or as mentioned above, that the reorgs have not released their temp space yet.

Marcus_A · #5 (**permalink**) 02-11-09, 14:34

BTW, when you reorg a table off-line, it automatically reorgs all the indexes. The reorg indexes with allowing write access is suspicious.

itsonlyme44 · #6 (**permalink**) 02-11-09, 14:46

suspicious maybe.. definitely extraneous!!! I'll remove them.

db2girl · #7 (**permalink**) 02-11-09, 14:53

check if the tablespaces are DMS or SMS. With automatic storage, I believe they're DMS. In case of an offline reorg, the shadow object is created in the same tablespace as the table being reorg by default. This can raise the high-water mark for DMS. What you can do is to check the tablespace sizes before and after the reorg and also after db backup. If it's DMS, look at the "High water mark" value.

dav1mo · #8 (**permalink**) 02-11-09, 14:55

also th reorgs on the indexes, would be an asynchronous command, wouldn't it? Meaning the command has completed, but the reorg is now running.

dr_te_z · #9 (**permalink**) 02-13-09, 02:29

Quote:

Originally Posted by dav1mo

also th reorgs on the indexes, would be an asynchronous command, wouldn't it? Meaning the command has completed, but the reorg is now running.

I made this little script to deal with that problem

Code:

db2 set current schema %1
db2 Reorg table %2 inplace allow write access           START
:BackHome
d:\utils\sleep 32
db2 Reorg table %2 inplace                              PAUSE
if %errorlevel% equ 0 (
    db2 Reorg table %2 inplace allow write access       RESUME
    goto  :%BackHome
    )

So it starts the online-reorg (an async task) and after a period of time I try to PAUSE that reorg-task. When that works (rc=0) then I RESUME the task and wait again for that same period of time....
When the PAUSE did not succeed, the reorg must be finished and you can continue for that table (reorg indexes, do runstats etc etc).
Now you can build a scritp which launches the reorg 1-by-1 and they will execute only 1 at the time.

Internet Services

Web Development

Digital Marketing

Consumer Tech