Results 1 to 4 of 4

Thread: Reorg in a loop

  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Reorg in a loop

    Hi,

    I am trying to perform Reorg inside a loop. But according to the docs, REORG executes a ROLLBACK internally.
    In my procedure, there is a cursor & after the first fetch due to the roerg, the cursor is closing. Even if the cursor is defined with HOLD option.

    My reorg inside the loop is necessary . As data is changing frequently & it is over a bulk database.

    My requirement is I have to use a reorg inside a loop , which should not affect my cursor at all.

    kindly give me a suggestion regarding this.

  2. #2
    Join Date
    Dec 2014
    Posts
    2
    use a shell script.

    1. generate a SQL using your loop, the script reorg_tab.sql will like this:

    reorg table1;
    reorg table2;

    2. then run the SQL

    db2 -tvf reorg_tab.sql

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    reorg-index or reorg-table requires that you close all cursors before running reorg, and reorg implicitly closes all cursors. IBM documents this clearly.

    Your design is incorrect for db2 luw, and the requirement may be nonsensical for present versions of db2 luw. Analyse the requirement carefully.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you can use arrays for that.

    Code:
    begin
      declare a dbms_output.chararr;
      declare card int default 0;
      declare i int default 0;
      
      for c as
        select tabschema, tabname
        from syscat.tables
        where type='T' and tabschema=user
        fetch first 10 rows only
      do
        set card=card+1;
        set a[card] = 'reorg table "'||c.tabschema||'"."'||c.tabname||'"';
      end for;
      while i<card do
        set i=i+1;
        --call dbms_output.put_line(a[i]);
        call admin_cmd(a[i]);
      end while;
    end@
    Regards,
    Mark.

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
  •