Setting default statistics for created temporary tables

If you can estimate the normal cardinality and number of pages that a particular created temporary table uses, you can set the default values that Db2 uses for that table.

About this task

When Db2 prepares an SQL statement that refers to a created temporary table, the statistics that it uses depend on whether the table was already instantiated. If the table was already instantiated, Db2 uses the cardinality and number of pages that are maintained for that table in storage. If the table was never yet instantiated, Db2 uses the CARDF and NPAGES column values of the SYSTABLES row for the created temporary table. These columns normally contain default (-1) values because Db2 utilities cannot collect statistics for created temporary tables.

Procedure

To set statistics for created temporary tables:

Modify the values in the CARDF and NPAGES columns in the row for the created temporary table in the SYSIBM.SYSTABLES catalog table.

These values become the default values that are used if more accurate values are not available or cannot be used. The more accurate values are available only for dynamic SQL statements that are prepared after the instantiation of the created temporary table, but within the same unit of work. If the result of the dynamic bind is destined for the dynamic statement cache, these more accurate values are not used.