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.
