reorg is a cursor killer?

dr_te_z · #1 (**permalink**) 10-25-11, 04:36

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?

db2girl · #2 (**permalink**) 10-25-11, 06:20

Quote:

Originally Posted by dr_te_z

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.

dr_te_z · #3 (**permalink**) 10-26-11, 03:42

Quote:

Originally Posted by db2girl

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.

stolze · #4 (**permalink**) 10-26-11, 16:38

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:

Quote:

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.

dr_te_z · #5 (**permalink**) 10-27-11, 08:02

Quote:

Originally Posted by stolze

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

Internet Services

Web Development

Digital Marketing

Consumer Tech