Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: reorg is a cursor killer?

    Hi, I am performing a POC with the ATS scheduler because the "Task Centre" is becomming obsolete, right?

    If i understand it right my housekeeping scripts have to be transformed into housekeeping stored procedures first.
    Looks easy enough but ..... when you execute a reorg in a loop the loop is broken. No matter how you specify it. Please look at my code:
    Code:
    create or replace 
    procedure runstats_schema3 () 
    language sql
    specific  runstats_schema3
    begin
         declare global temporary table housekeeptabs 
             (   table_schema   varchar(128) not null
               , table_name     varchar(128) not null
             )   on commit  preserve rows 
                 not logged
                 on rollback preserve rows
             ;
         insert into session.housekeeptabs 
                select table_schema , table_name 
                from sysibm.tables a 
                where a.table_type   = ucase('base table') 
                  and a.table_schema = current_schema  
         ;
         for L1 as
             select varchar ('reorg table '       || rtrim (h.table_schema) || '.' || rtrim (h.table_name)) as db2_reorg_line   
                  , varchar ('runstats on table ' || rtrim (h.table_schema) || '.' || rtrim (h.table_name)) as db2_runstats_line 
             from session.housekeeptabs h
             order by h.table_name
         do
             call  sysproc.admin_cmd (db2_reorg_line)    ;
             call  sysproc.admin_cmd (db2_runstats_line) ;
         end for;     
    end  ~
    As long as I only perform the "runstats" call everything works fine, but when I excute the reorg line the loops fails with a SQL0501. A cursor WITH HOLD does not do the job either.

    This is basic stuff, what am I missing here?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by dr_te_z View Post
    Hi, I am performing a POC with the ATS scheduler because the "Task Centre" is becomming obsolete, right?

    If i understand it right my housekeeping scripts have to be transformed into housekeeping stored procedures first.
    I don't know much about ATS... I tested it with an online backup once in the past. I don't recall creating any stored procedure for it. Reorg should be the same, I think.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by db2girl View Post
    Reorg should be the same, I think.
    Yes. 1 REORG works fine. But when I select a set of tables to be REORG'd.... it does not work.

    All the "lessons learned" about the cursor WITH HOLD or temp table on commit preserve and things like that. It just is not true after you performed 1 REORG. The next row from your selection will not be fetched, the cursor/loop is broken beyond repair.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    A simple explanation could be that REORG executes a ROLLBACK internally. That would close all cursors on the connection, of course. (I don't know if this is actually the case here!)

    If I read the manual (REORG INDEXES/TABLE using ADMIN_CMD - IBM DB2 9.7 for Linux, UNIX, and Windows), I find this:
    Tables that have been modified so many times that data is fragmented and access performance is noticeably slow are candidates for the REORG TABLE command. [...] Be sure to complete all database operations and release all locks before invoking REORG TABLE. This can be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. After reorganizing a table, use RUNSTATS to update the table statistics, and REBIND to rebind the packages that use this table. The reorganize utility will implicitly close all the cursors.
    So I'd say that things are working exactly as designed and you'll have to revisit your approach to not rely on a cursor. You could, for example, insert all the tables/statements into a temp table. Then select the first record from that table, delete it, and run the reorg+runstats on it. Once you're done with the table, do the same with the next table until your temp table is empty.

    p.s: You may want to enclose your table names in double-quotes and work with delimited identifiers.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by stolze View Post
    You could, for example, insert all the tables/statements into a temp table. Then select the first record from that table, delete it, and run the reorg+runstats on it. Once you're done with the table, do the same with the next table until your temp table is empty.
    Excellent advice! Thank you very much
    This works:
    Code:
    create or replace procedure runstats_bank_schema3 () 
    language sql      specific  runstats_bank_schema3
    begin     
         declare #tbl_cnt int ;
         declare global temporary table housekeeptabs 
             (   table_schema   varchar(128) not null
               , table_name     varchar(128) not null
             )   on commit   preserve rows  not logged
                 on rollback preserve rows   with replace 
             ;         
         insert into session.housekeeptabs 
                select table_schema 
                     , table_name 
                from sysibm.tables a 
                where a.table_type    = ucase('base table')       
                  and a.table_schema != ucase('sysibm')
                ;
         commit ;
         set #tbl_cnt = (select count(*) from session.housekeeptabs);
         loop_outer: while #tbl_cnt > 1
         do loop_inner: for tbl as 
                        select table_schema                                                                        as #schema
                             , table_name                                                                          as #table 
                             , varchar ('reorg table '       || rtrim (table_schema) || '.' || rtrim (table_name)) as #db2_reorg_line   
                             , varchar ('runstats on table ' || rtrim (table_schema) || '.' || rtrim (table_name)) as #db2_runstats_line 
                       from session.housekeeptabs  
                       order by table_name
            do delete session.housekeeptabs 
               where  table_schema = #schema
                 and  table_name   = #table                 ;
               commit                                       ;
               call  sysproc.admin_cmd (#db2_reorg_line)    ;
               call  sysproc.admin_cmd (#db2_runstats_line) ;
               set   #tbl_cnt = #tbl_cnt - 1                ;
               leave loop_inner                             ;
            end for  loop_inner                             ;       
         end  while  loop_outer                             ;     
    end  ~
    You need the "leave" here. I tried the "fetch first 1 row only" and omitted the "leave" but that still caused errors on the "end for". That may never be executed

Posting Permissions

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