Effect of utilities on objects that have the DEFINE NO attribute

When you create a table space or index space with the DEFINE NO attribute, the object is defined; however, Db2 does not allocate the associated data sets until a row is inserted or loaded into a table in that table space.

You can populate table spaces whose data sets are not yet defined by using the LOAD utility with either the RESUME option or the REPLACE option. In this case, LOAD performs the following actions:

  • Allocates the data sets for the table space and index space and any auxiliary objects.
  • Updates the SPACE column in the SYSTABLEPART and SYSINDEXPART catalog tables to indicate that data sets exist.
  • Loads the specified table space.

For a partitioned table space, all partitions are allocated even if the utility loads only one partition. Do not attempt to populate a partitioned table space with concurrent LOAD PART jobs until after one job creates all of the data sets.

Start of changeFor auxiliary objects with the DEFINE NO attribute, you can request that LOAD allocate the associated data sets even if the objects are not populated by LOAD. To do so, specify the DEFINEAUX YES option on the LOAD statement.End of change

After any objects are defined and committed by the current unit of work in the LOAD processing, this action cannot be rolled back or undone.

If one of the following online utilities encounters a target object with the DEFINE NO attribute, the utility issues informational message DSNU185I, skips that object, and continues processing:

  • CHECK DATA
  • CHECK INDEX
  • COPY
  • MERGECOPY
  • MODIFY RECOVERY
  • QUIESCE
  • REBUILD INDEX
  • RECOVER
  • REORG INDEX
  • REORG TABLESPACE (One exception is if ROWFORMAT RRF is specified in the REORG statement and the specified target is an undefined table space in basic row format. In this case, REORG updates the row format definition in the catalog and directory. No data sets are defined for the table space.)
  • REPAIR, but not REPAIR DBD
  • RUNSTATS TABLESPACE INDEX(ALL)
  • RUNSTATS INDEX ()
  • UNLOAD

RUNSTATS recognizes DEFINE NO objects and updates the access path statistics in the Db2 catalog to reflect the empty objects.

The SYSIBM.SYSSTATFEEDBACK table might contain rows that recommend collection of statistics for DEFINE NO objects or empty table space partitions. However, statistics cannot be collected for such objects until after data is inserted and the underlying data sets are created.

You cannot use stand-alone utilities on objects whose data sets are not yet defined.