Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: inplace reorg (on-line reorg) on LUW

    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
    Last edited by grofaty; 10-21-04 at 07:28.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 10-21-04 at 08:52.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

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

    Thanks,
    Grofaty

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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 ...

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •