All,

Having an issue with RUNSTATS on DB2 OS/390 v7.1 ... We have a 24 partition tablespace ... It had at the end of Feb. 0 rows in 1st 2 partitions, 1-3 million rows each in partitions 3 - 23, and 0 in partition 24 ... Each is partitioned by date range ... RUNSTATS last ran on 2/26/05 by partition ... We started loading data into partition 24 on 3/1/2005 ... As of this morning all queries selecting data prior to 3/1/2005 were using indexes, any queries selecting data from 3/1/2005 forward were doing tablespace scans and running for hours ... This almost locked up the whole subsystem ... Does anyone have any suggestions to resolve this issue ...

Also, our intent is to convert all partition tablespaces to have partition number as the clustering key and have the app decide on which partition to insert into based on a partition control table to automate rolling the partitions ... We want to purge the oldest data a month in advance which means we will almost always have 1 empty partition ... Is there a way to force something in RUNSTATS so that inconsistent stats will not cause us similar problems ...

Presumably you haven't run RUNSTATS on partition 24, so the statistics indicate an empty partition - so DB2 thinks a partition scan is the way to go. Do a RUNSTATS on just partition 24 and rebind any plans that have hard-coded data ranges.

You can update SYSTABLEPART and SYSINDEXPART to provide dummy statistics so that DB2 will prefer an index. Or just not run RUNSTATS after purging a partition.

James Campbell