To paraphrase David Byrne, you may find yourself behind the wheel of a large database in another part of the world asking, “how do I work this?”  Such was the case today with a customer database suffering a performance hiccup.  It had the symptoms of improper tablescans, but there was no time to enable snapshots, run explains, or do detailed analysis.  It needed a quick sanity check of statistics, indexes, and organization.  A few basic commands flushed out the problem: a required index was missing.

I’ve recorded various common DB2 Snippets here before, but had not yet included today’s common mantras.  So, I’ll extend my DB2 pastebin with these useful snippets…

Check for missing indexes

This will give you a quick index inventory to compare with your expectations.  Substitute for MYSCHEMA and MYNAMES.

select tabname, indname, colnames from syscat.indexes where tabschema = ‘MYSCHEMA‘ and tabname like ‘MYNAMES%’ order by tabname

Check for recent runstats

See when statistics were updated for your tables, if at all.  Substitute for MYSCHEMA.

select ‘   ‘ || substr(rtrim(tabschema) || ‘.’ || tabname, 1, 40), stats_time from syscat.tables where type = ‘T’ and tabschema = ‘MYSCHEMA‘ order by stats_time

If statistics are out of date or missing, then runstats is the obvious remedy.  If you only have CLI/ODBC access (no CLP), you can call runstats via the admin_cmd proc; substitute for MY.TABLE and tweak the statistics options if needed:

call admin_cmd(‘runstats on table MY.TABLE with distribution and indexes all’)

Generate a runstats script

Don’t let outdated statistics fool you more than once – automate and change-proof the process.  One option is to enable DB2’s auto runstats, but that has its limits.  Another common solution is a cron job or scheduled task kicking off a fixed set of runstats commands.  Good idea, but you must update the script each time you add a table.  You can go one step further with a script to generate the runstats commands for all tables matching a certain criteria, and then call the generated script, all from a cron job.  Something like this:

db2 -x “select ‘runstats on table ‘ || rtrim(tabschema) || ‘.’ || tabname || ‘ with distribution and indexes all allow write access;’ from syscat.tables where type = ‘T’ and tabschema in (‘MYSCHEMA‘ ) order by tabname with ur” >> %GeneratedScriptName%

db2 -tvf %GeneratedScriptName% >> %GeneratedScriptName%-out.txt

You may want to reduce runstats frequency for the more static tables.  If so, add name, time, and other criteria like the following:

db2 -x “select ‘runstats on table ‘ || rtrim(tabschema) || ‘.’ || tabname || ‘ with distribution and indexes all allow write access;’ from syscat.tables where type = ‘T’ and tabschema in (‘MYSCHEMA‘ ) and tabname like ‘MYNAME%’ and ((stats_time is null) or (stats_time < current timestamp – %SinceMinutes% minutes)) order by tabname with ur” >> %GeneratedScriptName%

Perhaps you’ll find these incantations (or variations of them) useful as the days go by.