Table node

A table node represents a table that is referenced in the SQL statement. The referenced table can be a base table, a global temporary table, or a view.

An SQL statement can reference the same table multiple times; each reference is represented by a table node.

A table node is labeled with the name of the referenced table and is, by default, displayed as a rectangle. The correlation name, creator name, or cardinality of the table can also be displayed on the label. If the RUNSTATS utility has not collected statistics for the table, the table node is outlined in red. If the optimizer uses the default value for the table cardinality, the cardinality is marked as the default. The default cardinality value is stored in the catalog table SYSIBM.SYSTABLES as -1; however, the optimizer uses 10 000 as the default value.

If RUNSTATS has not collected statistics, the optimizer does not necessarily use the default values. If you manually update the values in SYSIBM.SYSTABLES, the optimizer uses these values and not the default values.

How DB2 accesses a table

DB2® accesses the table by using a table space scan (TBSCAN), a single- or multiple- index access with fetch (FETCH), or a direct fetch (DFETCH). The DB2 optimizer determines the access method based on the table statistics that are listed in the table below. These statistics are listed as attributes on the Descriptor window for a table node. To provide these statistics, either use the RUNSTATS utility to collect them or manually update the catalog table SYSIBM.SYSTABLES. If you do not provide these statistics, DB2 uses the default values that are listed in the following table.
Table 1. Table statistics that the optimizer uses
Attribute Explanation Default value
Rows Total number of rows 10 000
Pages Total number of pages 501
Compressed Row Percentage Percentage of compressed rows 0

Feedback