DB2 Maintenance Script (RUNSTATS, REORG)

DB2 Maintenance Script (RUNSTATS, REORG)

I had a local copy of a WebSphere Commerce database that was performing horribly, and I decided to create a script that would retrieve a list of all available tables for all non-system schemas, and perform a RUNSTATS and REORG for those tables.

What I came up with was the following DB2 command line scripts. I’ve put these into a batch file, and run them periodically after making serious changes to the database through product loads, or other significant data activities to keep the database performing as desired. Keep in mind that before running these scripts, you’ll need to ensure that you’ve entered a DB2 command line session and established a DB2 connection with proper user authorities capable of running the maintenance activities.

db2 -x "SELECT 'RUNSTATS ON TABLE ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ' AND INDEXES ALL;' FROM SYSCAT.TABLES WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%' ORDER BY TABSCHEMA, TABNAME" > db2_runstats.sql.out
db2 -tvf db2_runstats.sql.out

db2 -x "SELECT 'REORG TABLE ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ';' FROM SYSCAT.TABLES WHERE TYPE = 'T' AND TABSCHEMA NOT LIKE 'SYS%' ORDER BY TABSCHEMA, TABNAME" > db2_reorg.sql.out
db2 -tvf  db2_reorg.sql.out

db2 -tvf db2_runstats.sql.out

You can download the db2_maintenance command script I’ve put together. You’ll need to rename the downloaded txt file to use a .bat or .cmd extension, if running on Windows in order to get it to execute properly.

Author: daharveyjr

I’m a solution architect responsible for the design, development, implementation, testing, and maintenance of e-commerce operations and applications using the Hybris and WebSphere Commerce product suites and other web technologies such as Java, J2EE/JEE, Spring, PHP, WordPress and more. Twitter | Facebook | LinkedIn

Leave a Reply

Your email address will not be published. Required fields are marked *