Previous Topic: Define a DB2 z/OS Auxiliary TableNext Topic: Define a DB2 z/OS Storage Group


Define a DB2 z/OS Auxiliary Table Index

You create an index on an auxiliary table to quickly locate a LOB value. An auxiliary table can have only one index.

To define a DB2 z/OS Auxiliary Table Index

  1. Expand a table in the Model Explorer, locate a column that has a LOB data type and then expand it.
  2. Expand Auxiliary Tables and then Indexes. Right-click and index and select Properties.

    The DB2 for z/OS Auxiliary Table Index Editor opens.

  3. Work with the following options:

    Note: Click New New icon in property editors to create a new object on the toolbar to create an index. Use the Enter filter text text box to filter a very large list of indexes to locate the one you want to work with.

    Physical Name

    Specifies the name of the index.

    Schema

    Specifies the default schema for the index. Select from the drop-down list.

    Type

    Shows the key group type. This is a read-only field.

    Is Unique

    Specifies that the fields defined in the index must be unique.

    Where Not Null

    Specifies that a table cannot have two or more rows with the same value of the index key; this applies when you select the Is Unique check box.

    Physical Only

    Lets you select whether the index should be suppressed from a logical model and appear in a physical model only.

    Generate

    Specifies whether or not to generate DDL for this object during Forward Engineering.

  4. Work with the following fields on the General tab:
    Constraint Name

    Specifies the constraint associated with the index.

    Clustered

    Specifies whether the index is clustered or nonclustered.

    Is Partitioned

    Specified whether or not the index is data partitioned.

    DB2 z/OS Type

    Specifies the index type. Valid values are Type2, Data Partitioned, and Partitioning.

    Pad Variable Length Columns

    Specifies whether or not varying-length string columns within the index are always padded with the default pad character to their maximum length.

    Define Data Sets

    Specifies whether or not data sets are created when the index is created.

    Use Bufferpool

    Identifies the buffer pool to use for the index. Select from the drop-down list.

    Close

    Specifies that the index is eligible for closing. The CLOSE parameter specifies whether or not the data set is eligible to be closed when the index is not being used and the limit on the number of open data sets is reached. Select from the drop-down list.

    Defer

    Specifies whether or not to build the index during the execution of the CREATE INDEX statement. Default is No, which means the index is built.

    Piece Size

    Specifies the maximum addressability of each data set for a secondary index. Enter a valid integer (see your DB2 z/OS documentation for specific information on valid integers).

    Copy

    Specifies whether or not the COPY utility is allowed for the index.

    Is Compressed

    Specifies whether or not the index will use index compression.

  5. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
  6. (Optional) Click the Where Used tab to view where the object is used within the model.
  7. (Optional) Click the UDP tab to work with user-defined properties for the object.
  8. (Optional) Click the Notes tab to view and edit user notes.
  9. (Optional) Click the Extended Notes tab to view or edit user notes.
  10. Click Close.

    The DB2 for z/OS Auxiliary Table Index Editor closes.