Process Each row in stored procedure or SQL in DB2

Question

I am basically from Oracle background and trying to create a SQL code or stored procedure which will execute REORG for each table which is in reorg pending status

I have already figured out way to get list of such tables as given below

SELECT 'CALL SYSPROC.ADMIN_CMD(''REORG TABLE', substr(rtrim(TABSCHEMA)||'.'||rtrim(TABNAME),1,20) ||''')', ';' from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y' AND TABSCHEMA=CURRENT SCHEMA

Now my requirement is to run this result set as a DB2 SQL command for each row found, I don't have option to use shell script or batch file , it has to be done using SQL or stored procedure. I tried creating proc but lots of issues with that , can someone pl help

mao · Accepted Answer · 2018-01-28 16:23:16Z

1

It's not necessary to write an sproc for this purpose, unless you have special requirements. You can ask Db2 to do the work via:

CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('table', current schema, NULL)

You can read about that functionality here.

Otherwise , if you do have special requirements per the reorg, it may be simpler to use a cursor to find fully-qualified-table-names, i.e use the cursor to populate an array and iterate over that array to do the reorg.

Be extremely careful using offline reorgs on production environments, so you might want to build-in some defensive code to prevent causing service outages.

Otherwise you can process a result-set returned from a stored procedure using the techniques mentioned in the documentation at "Receiving procedure result sets in SQL routines"

Additionally if your Db2 database was created to support Oracle compatibilility, you can use Oracle PLSQL syntax and only add the ADMIN_CMD() stuff. You don't have to use the ANSI SQLPL style of Db2 in that case.

Here is a dumb bare bones example that uses a cursor and an array, there are many other ways to do this including more elegant ways:

--#SET TERMINATOR @

create or replace  type reorg_list  as VARCHAR(1024) array[]
@

create or replace procedure sp_reorgpending ()
language sql 
specific sp_reorgpending
begin 
    declare v_sqlcode integer default 0; 
    declare v_stmt varchar(1024); 
    declare v_tabname varchar(1024);
    declare tables_to_reorg reorg_list;
    declare v_counter integer;
    declare c1 cursor with hold for select rtrim(tabschema)||'.'||rtrim(tabname) from SYSIBMADM.ADMINTABINFO where REORG_PENDING = 'Y' AND TABSCHEMA=CURRENT SCHEMA;
    declare continue handler for not found set v_sqlcode=100;

    set v_counter=0;
    open c1;
    fetch c1 into v_tabname;
    while ( v_sqlcode = 0 ) do
        set v_counter = v_counter + 1 ;
        set tables_to_reorg[v_counter] = v_tabname;
        fetch c1 into v_tabname;
    end while;
    close c1;
    while ( v_counter > 0 ) do
        set v_stmt='reorg table '||tables_to_reorg[v_counter] ;
        call admin_cmd(v_stmt);
        set v_counter = v_counter -1 ;
    end while;
   return 0; 
end
@

edited Jan 28, 2018 at 16:23

answered Jan 18, 2018 at 19:37

mao

12.4k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DBPlayer Over a year ago

mao, this was a great help, thanks a lot. One more question, is there any way I can print value of each table names when procedure is executed, i.e. procedure execution should give output of each table name

mao Over a year ago

You can add logic to insert progress to another table (best if done via autonomous sproc), which would allow another process to query that table for progress. Or if you want to wait until the end of the job, use dbms_output.put_line().

DBPlayer Over a year ago

yes I have dealt with this by creating a temporary table to track this. Thanks.

DBPlayer Over a year ago

is there any documentation stating which activities will cause table to go into Reorg Pending status ? I know changing data type is one of them , however I am not sure what other things cause this, can't find proper documentation on this. Altering table 3 times consecutively can cause this?

Collectives™ on Stack Overflow

Process Each row in stored procedure or SQL in DB2

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related