Defining your own user-managed data sets

You can use Db2 storage groups to let Db2 manage the VSAM data sets. However, you can also define your own user-managed data sets. With user-managed data sets, Db2 checks whether you have defined your data sets correctly.

About this task

You can let Db2 manage the data sets for your database objects, and Db2 always manages the data sets for partition-by-growth table spaces. However, you might choose to define your own user-managed VSAM data sets for several reasons, including:

  • You have a large nonpartitioned table space on several data sets. If you manage your own data sets, you can better control the placement of individual data sets on the volumes (although you can keep a similar type of control by using single-volume Db2 storage groups).
  • You want to prevent deleting a data set within a specified time period, by using the TO and FOR options of the Access Method Services DEFINE and ALTER commands. You can create and manage the data set yourself, or you can create the data set with Db2 and use the ALTER command of Access Method Services to change the TO and FOR options.
  • You are concerned about recovering dropped table spaces. Your own data set is not automatically deleted when a table space is dropped, making it easier to reclaim the data.
Tip: As table spaces and index spaces expand, you might need to provide additional data sets. To take advantage of parallel I/O streams when doing certain read-only queries, consider spreading large table spaces over different disk volumes that are attached on separate channel paths.

If you define your own user-managed data sets, you must define a data set for each of the following items:

  • Table space partition
  • LOB table space
  • Segmented (non-UTS) or simple table space (deprecated)
  • Index partition
  • Non-partitioned index (NPI)

You must define the data sets before you can issue the CREATE TABLESPACE, CREATE INDEX, or ALTER TABLE ADD PARTITION statements.

When you drop indexes or table spaces that you defined user-managed data sets for, you must also delete the data sets unless you want to reuse them. To reuse a data set, first commit, and then create a new table space or index with the same name. When Db2 uses the new object, it overwrites the old information with new information, which destroys the old data.

Procedure

To define your own user-managed VSAM data sets, complete the following steps:

  1. Issue a DEFINE CLUSTER statement to create the data set an specify the following attributes:
    1. Specify a name for each data set in a format that complies with the following naming convention. 
      catname.DSNDBx.dbname.psname.y0001.znnn
      catalog-name
      The catalog name or alias.

      The data sets are VSAM linear data sets cataloged in the integrated catalog facility catalog that catalog-name identifies. For more information about catalog-name values, see Naming conventions in SQL.

      Use the same name or alias here as in the USING VCAT clause of the CREATE TABLESPACE and CREATE INDEX statements.

      x
      C (for VSAM clusters) or D (for VSAM data components).
      dbname
      Db2 database name. If the data set is for a table space, dbname must be the name given in the CREATE TABLESPACE statement. If the data set is for an index, dbname must be the name of the database containing the base table. If you are using the default database, dbname must be DSNDB04.
      psname
      Table space name or index name. This name must be unique within the database.

      You use this name on the CREATE TABLESPACE or CREATE INDEX statement. (You can use a name longer than eight characters on the CREATE INDEX statement, but the first eight characters of that name must be the same as in the psname for that data set.)

      y0001
      Instance qualifier for the data set.

      If you plan to run any of the following utilities, define two data sets, one data set with a value of I for y, and one with a value of J for y:

      • LOAD REPLACE SHRLEVEL REFERENCE
      • REORG with SHRLEVEL CHANGE or SHRLEVEL REFERENCE
      • CHECK DATA with SHRLEVEL REFERENCE
      • CHECK INDEX with SHRLEVEL REFERENCE
      • CHECK LOB with SHRLEVEL REFERENCE

      Otherwise, define one data set for the table space or index with a value of I for y.

      Tip: Instance numbers for cloned tables before and after an exchange: The instance numbers in the underlying VSAM data set names for the objects (tables and indexes) in a clone relationship toggle in the range 1–2. For example, suppose that a base table exists with the data set name *I0001.*. When the table is cloned, the clone's data set is initially named *.I0002.*. After an exchange, the base objects are named *.I0002.* and the clones are named *I0001.*. Each time that an exchange happens, the instance numbers that represent the base and the clone objects change.
      znnn
      Data set number. The first digit z of the data set number is represented by the letter A, B, C, D, or E, which corresponds to the value 0, 1, 2, 3, or 4 as the first digit of the partition number.

      For partitioned table spaces, if the partition number is less than 1000, the data set number is Annn in the data set name (for example, A999 represents partition 999). For partitions 1000 to 1999, the data set number is Bnnn (for example, B000 represents partition 1000). For partitions 2000 to 2999, the data set number is Cnnn. For partitions 3000 to 3999, the data set number is Dnnn. For partitions 4000 up to a maximum of 4096, the data set number is Ennn.

      The naming convention for data sets that you define for a partitioned index is the same as the naming convention for other partitioned objects.

      For simple or segmented (non-UTS) table spaces, the number is 001 (preceded by A) for the first data set. When little space is available, Db2 issues a warning message. If the size of the data set for a simple or a segmented (non-UTS) table space approaches the maximum limit, define another data set with the same name as the first data set and the number 002. The next data set will be 003, and so on.

      You can reach the VSAM extent limit for a data set before you reach the size limit for a partitioned or a nonpartitioned table space. If this happens, Db2 does not extend the data set.

    2. In the DEFINE CLUSTER statement, specify the size of the primary and secondary extents of the VSAM cluster. If you specify zero for the secondary extent size, data set extension does not occur.
    3. Specify that the data sets be LINEAR. Do not use RECORDSIZE; this attribute is invalid. Use the CONTROLINTERVALSIZE attribute if you are using variable-sized control intervals.
    4. Specify the REUSE option. You must define the data set as REUSE before running the DSN1COPY utility.
    5. For the SHAREOPTIONS option of the DEFINE CLUSTER statement, specify SHAREOPTIONS(3 3).
    For example, the following DEFINE CLUSTER command defines a VSAM data set for the SYSUSER table space in database DSNDB06. Assume that an integrated catalog facility catalog named DSNCAT is already defined.
    DEFINE CLUSTER -
       (NAME(DSNCAT.DSNDBC.DSNDB06.SYSUSER.I0001.A001) -
        LINEAR                                         -
        REUSE                                          -
        VOLUMES(DSNV01)                                -
        KILOBYTES(40 40)                               -
        SHAREOPTIONS(3 3) )                            -
      DATA                                             -
       (NAME(DSNCAT.DSNDBD.DSNDB06.SYSUSER.I0001.A001))-
    CATALOG(DSNCAT)

    The DEFINE CLUSTER command has many optional parameters that do not apply when Db2 uses the data set. If you use the parameters SPANNED, EXCEPTIONEXIT, BUFFERSPACE, or WRITECHECK, VSAM applies them to your data set, but Db2 ignores them when it accesses the data set.

    The value of the OWNER parameter for clusters that are defined for storage groups is the first SYSADM authorization ID specified at installation.

  2. With user-managed data sets, you must pre-allocate shadow data sets before you can run the following Db2 utilities against the table space:

    For example, you can specify the MODEL option for the DEFINE CLUSTER command so that the shadow is created like the original data set, as shown in the following example code. 

    DEFINE CLUSTER -
       (NAME('DSNCAT.DSNDBC.DSNDB06.SYSUSER.x0001.A001') -
       MODEL('DSNCAT.DSNDBC.DSNDB06.SYSUSER.y0001.A001')) -
      DATA                                                -
       (NAME('DSNCAT.DSNDBD.DSNDB06.SYSUSER.x0001.A001') -
       MODEL('DSNCAT.DSNDBD.DSNDB06.SYSUSER.y0001.A001')) -

    In the example, the instance qualifiers x and y are distinct and are equal to either I or J. You can querying the Db2 catalog for the database and table space to determine the correct instance qualifier to use.

What to do next