inplace reorg (on-line reorg) on LUW

grofaty · #1 (**permalink**) 10-21-04, 05:24

Hi,

I wrote script on db2 v8.1 fp6 on Linux to make inplace reorg.

Sample script
reorg table admin.table1 index admin.table1 inplace allow write access start
reorg table admin.table2 index admin.table2 inplace allow write access start
reorg table admin.table3 index admin.table3 inplace allow write access start
etc.

Return info
Each command returs the info:

DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.

Problem
Because each command executes in background, script triggers multiple db2agents. So script occupyes all the db2agents.

I would like to write a script which would occupy only one db2agent.
How to write a reorg command to execute "reorg table ... inplace" after first one has finished?

Thanks,
Grofaty

sathyaram_s · #2 (**permalink**) 10-21-04, 06:01

If doing within a korn shell

nohup db2 reorg .... &
reorg_pid=$!
wait $reorg_pid

The wait will wait for the reorg to finish ... The sucess/failure of reorg can be known by checking the $? value following the wait command

HTH

Sathyaram

grofaty · #3 (**permalink**) 10-21-04, 07:35

Hi,

I have written the script:

Code:

nohup db2 connect to ISQ user db2inst1 using password &
reorg_pid=$!
wait $reorg_pid
nohup db2 REORG TABLE ADM08.UPORABNIKI INPLACE ALLOW WRITE ACCESS START &
reorg_pid=$!
wait $reorg_pid


nohup db2 REORG TABLE CEV.CEVBLOK INPLACE ALLOW WRITE ACCESS START &
reorg_pid=$!
wait $reorg_pid


nohup db2 REORG TABLE CEV.CEVDEFORM INPLACE ALLOW WRITE ACCESS START &
reorg_pid=$!
wait $reorg_pid

... and executed script with command:
./run.sh

But above script is not working properly.

I have also executed db2 command:
db2 list application
... and the result is:

Code:

Auth Id  Application    Appl.      Application Id                 DB       # of
         Name           Handle                                    Name    Agents
-------- -------------- ---------- ------------------------------ -------- -----
DB2INST1 db2reorg       432        *LOCAL.db2inst1.041021134745   ISQ      1
DB2INST1 db2reorg       431        *LOCAL.db2inst1.041021134744   ISQ      1
DB2INST1 db2reorg       407        *LOCAL.db2inst1.041021134720   ISQ      1
DB2INST1 db2reorg       396        *LOCAL.db2inst1.041021134711   ISQ      1
DB2INST1 db2reorg       393        *LOCAL.db2inst1.041021134710   ISQ      1
DB2INST1 db2reorg       350        *LOCAL.db2inst1.041021134709   ISQ      1
DB2INST1 db2bp          357        *LOCAL.db2inst1.02FC31134626   ISQ      1
DB2INST1 db2bp          329        *LOCAL.db2inst1.02FAC1134611   ISQ      1
DB2INST1 javaw.exe      346        C0A8051E.A811.012101113210     ISQ      1
DB2INST1 db2bp.exe      152        C0A8051E.P711.013581112716     ISQ      1
DB2INST1 java.exe       10         C0A8052D.K50A.01C4C1084341     ISQ      1
DB2INST1 java.exe       9          C0A8052D.K30A.024041084330     ISQ      1

Noticed "db2reorg" multiple time? I would like to have only one connection working with "reorg inplace"! How to do this?

Thanks,
Grofaty

grofaty · #4 (**permalink**) 10-25-04, 04:21

Hi,

Does anyone has some unix script knowleadge to solve my problem?

Thanks,
Grofaty

n_i · #5 (**permalink**) 10-26-04, 00:02

The problem as I see it is that the "db2 reorg ..." command returns successfully while the process actually continues in the background. You'll need to find a way to check whether the reorg is still running and continue with the second "db2 reorg... " when the first one finishes. A straightforward way to do it could be:

Code:

db2 connect ...
db2 reorg ...
# check if reorg is running
db2 list applications | grep -q db2reorg
RC=$? 
# loop while it's running
while [[ $RC -eq 0 ]]
do
    sleep 300 #seconds
   db2 list applications | grep -q db2reorg
   RC=$? 
done
# continue with another reorg
db2 reorg ...

bmujeeb · #6 (**permalink**) 10-26-04, 13:10

I have done this , don't have the script

1. nohup .... &
2. use the job command and the awk for Done.. sleep certain time.
3. Then start the other job

regards & thanks

Mujeeb

Internet Services

Web Development

Digital Marketing

Consumer Tech