Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Reorg Statements

    Hi ,

    I am doing the REORG on all the tables under a particular database by using the following statements.

    db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T' " > reorg.out

    db2 -tvf reorg.out

    My question is I have to run these two statements individually from the command prompt.

    How can i made these two statements into a single call. I mean Can i implant these two statements in a single procedure & by calling that procedure alone, can get the same functionality.

    With out manual intervention I have to run these two commands at a time.

    Can some one help me out plz

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Encase the statements is an OS script/batch file.

    Andy

  3. #3
    Join Date
    Oct 2002
    Posts
    15
    Not sure I would want to do this, however .. you could write a SP that uses a cursor with the basic select tabschema, tabname query, fetch those values into schema and name defined variables, looping through each fetch using something like SET REO_TXT = 'call admin_cmd(''reorg table '||v_schema||'.'||v_tabname||')'; and EXECUTE IMMEDIATE reo_txt; before moving on to the next in the loop.

    Not tested but you should get the idea, again not sure I would want to do this but the logic should work.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Frankly, reorging all the tables all at once is overkill. Many may not need a reorg at all. In my opinion, there should be a particular need to perform a reorg. You realize that because this process reorgs every table, the the system is essentially offline while this is happening.

    Andy

  5. #5
    Join Date
    Feb 2015
    Posts
    1

    Reorg Statements

    Try this out:

    db2 -x "select 'reorg table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),';'from syscat.tables where type = 'T' fetch first 1 row only " | db2 +p -txv

    Regards/Gre Stefan M. Mihokovic

  6. #6
    Join Date
    Aug 2012
    Posts
    70
    I agree with ARWinner. Nevertheless if the database is very small and if you are sure that no activity is running on it, you can try. You can also run an online reorg, which allows you to run the application in parallel, but the performance will suffer.
    Don't forget, after the reorg is done, to run the runstats and if you have statics sql you need to rebind the packages using the reorganized tables.

Tags for this Thread

Posting Permissions

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