Tag Archives: Sysproc.admin_cmd

I have covered a basic introduction to the SYPROC.ADMIN_CMD and looked at using it for running a RUNSTAT operation on a table and an index, now I am going to look at it for running a REORG command.

The code below will show you an example of a ONLINE table REGORG and an OFFLINE table REORG. There are several GOTCHA’s here at a high level, others exist and can be found here:

GOTCHA’s

1. OFFLINE CLASSIC REORG is quicker, does both Table and Index reorganization, but you cant access your table
2. Once on OFFLINE CLASSIC REORG is started it can’t be stopped. An ONLINE REORG can be stopped and paused, but the job is not done.
3. ONLINE REORG’s do not reorganize your index’s as well that has to be done separately!
4. ONLINE REORG’s can’t take place on a partitioned table, but there are ways and means around that.

SYSPROC.ADMIN_CMD – REORG Code

Much in the same way that the runstats worked you can get the ONLINE REORG to work in a stored procedure like the example below:

CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG_TABLE_ONLINE(IN IN_TABLE_SCHEMA VARCHAR(255),
IN IN_TABLE_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE REORG_STRING VARCHAR(1000);

SET REORG_STRING = 'REORG TABLE ' || TRIM(IN_TABLE_SCHEMA) || '.' || IN_TABLE_NAME || ' INPLACE ALLOW WRITE ACCESS START';

CALL SYSPROC.ADMIN_CMD(REORG_STRING);
END

The above will start an online reorganisation of the table and this can be monitored through a command like db2pd:

db2pd -d {Database Name} -reorg

So in one of the GOTCHA’s above I hinted that you can’t run a REORG on a partitioned table online and it has to be done offline which is true but you can get away with only doing one partition at a time with something like:

CREATE PROCEDURE DB_MAIN.AUTOMATED_REORG_TABLE_PARTITION_OFFLINE(IN IN_TABLE_SCHEMA VARCHAR(255),
IN IN_TABLE_NAME VARCHAR(255),
IN IN_PARTITION_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE REORG_STRING VARCHAR(1000);

SET REORG_STRING = 'REORG TABLE ' || TRIM(IN_TABLE_SCHEMA) || '.' || IN_TABLE_NAME || ' ALLOW NO ACCESS ON DATA PARTITION ' || IN_PARTITION_NAME;

CALL SYSPROC.ADMIN_CMD(REORG_STRING);
END

The data residing inside the partition will still be unavailable but, at least most of the table will still be there and it should be quicker to do just one partition offline than it is to do the whole table. The issue that you have with an offline reorg is that once it is started then you can’t pause it or stop it so make sure your maintenance window is large enough.

Future Post

The examples above should give you some idea on how to do this for your self in you database, but how do you know it needs doing?