Deferring allocation of Db2-managed data sets

When you execute a CREATE TABLESPACE statement with the USING STOGROUP clause, Db2 generally defines the necessary VSAM data sets for the table space. However, you might want to define a table space without immediately allocating the associated data sets.

About this task

For example, you might be installing a software program that requires that many table spaces be created, but your company might not need to use some of those table spaces. You might prefer not to allocate data sets for the table spaces that you will not be using.

The deferral of allocating data sets is recommended when:
  • Performance of the CREATE TABLESPACE statement is important
  • Disk resource is constrained

Procedure

Begin general-use programming interface information.To defer the physical allocation of Db2-managed data sets:

Issue a CREATE TABLESPACE statement with the DEFINE NO clause.

The DEFINE NO clause is allowed on some Db2 objects, such as explicitly created LOB table spaces, auxiliary indexes, and XML indexes. Additionally, the IMPDSDEF subsystem parameter specifies whether Db2 defines the underlying data set for implicitly created table spaces and index spaces. When you specify this subsystem parameter as NO, the data set is not defined when the table space or index space is implicitly created.

Restriction: The DEFINE NO clause is not allowed for table spaces in a work file database, or for user-defined data sets. (In the case of user-defined data sets, the table space is created with the USING VCAT clause of the CREATE TABLESPACE statement).

Do not use the DEFINE NO clause on a table space if you plan to use a tool outside of Db2 to propagate data into a data set in the table space. When you use DEFINE NO, the Db2 catalog indicates that the data sets have not yet been allocated for that table space. Then, if data is propagated from a tool outside of Db2 into a data set in the table space, the Db2 catalog information does not reflect the fact that the data set has been allocated. The resulting inconsistency causes Db2 to deny application programs access to the data until the inconsistency is resolved.

Results

The table space is created, but Db2 does not allocate (that is, define) the associated data sets until a row is inserted or loaded into a table in that table space. The Db2 catalog table SYSIBM.SYSTABLEPART contains a record of the created table space and an indication that the data sets are not yet allocated. End general-use programming interface information.