As I mentioned last post you can use SYSPROC.ADMIN_CMD to carry out runstats. I originally developed this Stored procedure using SYSPROC.ADMIN_CMD when I had a junior DBA that was new to the administration side of the database game, and therefore they could RUNSTATS without too many issues. Calling a RUNSTATS this way can be very useful in large stored procedure calls in a data-warehouse without having to break up a controlling Stored Procedure.
SYSPROC.ADMIN_CMD – RUNSTATS Code
So you can create a dynamic string to pass into the SYSPROC.ADMIN_CMD using parameters from the stored procedure call that are passed in something like:
RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS
Where IN_TABLESCHEMA and IN_TABLENAME are passed into the stored procedure by the user. You can create a stored procedure like:
CREATE PROCEDURE DB_MAIN.RUNSTATS (IN IN_TABLESCHEMA VARCHAR(100), IN IN_TABLENAME VARCHAR(100))
LANGUAGE SQL
BEGIN
DECLARE RSSTRING VARCHAR(255);
SET RSSTRING = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS';
CALL SYSPROC.ADMIN_CMD(RSSTRING);
END
As I said in my last post I have a load of stored procedures in a DB_MAIN schema that allows a database based “installable” schema that can carry out many maintenance tasks. The one above will do a general runstats against everything, I also created a more specific one for RUNSTAT’ing an index only:
CREATE PROCEDURE DB_MAIN.RUNSTATS_INDEX (IN IN_TABLESCHEMA VARCHAR(100),
IN IN_TABLENAME VARCHAR(100),
IN IN_INDEX_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE RSSTRING VARCHAR(1000);
SET RSSTRING = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ' || IN_TABLESCHEMA || '.' || IN_INDEX_NAME || ' ALLOW WRITE ACCESS';
CALL SYSPROC.ADMIN_CMD(RSSTRING);
END
This is obviously superseded by the first example but it will be quicker.
Future Posts
I will look at combining this with other SYSPROC.ADMIN_CMD run commands to perform the 4 R’s of DB2. The 4 R’s are:
- Reorganise Tables
- Reorganise Index’s
- Runstat’s Table / Index’s
- Rebind
Pingback: How to run administrative commands using Stored Procedures (using database connection to send CLP commands) | How to rule the DB2!