Previous Topic: Define a DB2 z/OS FunctionNext Topic: Define DB2 z/OS Table or Index Partitions


Define a DB2 z/OS Index

A primary key (PK) and a foreign key (IF) index is automatically created for each table in your model, based on the primary key and foreign key columns. If you designate columns as an alternate key group (AK) or inversion entry group (IE), the equivalent unique (AK) and non-unique (IE) indexes are also generated.

Use the Index editor to create alternate key (unique) and inversion entry (non-unique) indexes for tables in your model. After you create an index, you can use the Index editor to modify its properties, such as the index name, column members, physical properties, user-defined properties, and comments.

To define a DB2 z/OS Index

  1. Right-click a table and select Index Properties from the shortcut menu.

    The Table Index Editor opens.

  2. Select an index and 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.

    Show FK Indexes

    Displays foreign key indexes in the Index list.

    Physical Name

    Specifies the name of the index.

    Schema

    Specifies the default schema for the index.

    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.

    Generate As Consraint

    Specifies whether or not to enforce the Is Unique option as a constraint while generating the index.

  3. Click the Members tab and work with the following options:
    Column

    Specifies the columns that you want to include in the index.

    Index Member Sort Order

    Specifies the order in which you want to sort the index columns. Select from the drop-down list; valid values are Ascending, Descending, and Random.

    Column Expression

    Specifies a key-expression that returns a scalar value. You cannot include the same expression more than once in an index. This field is enabled only when you create a non-unique index. For more information about including key expressions in indexes, see the IBM DB2 for z/OS documentation.

    Column Editor

    Displays the Column Editor for DB2 for z/OS so that you can edit the selected column.

    Reset Order

    Displays the Reset Order dialog where you can select the reset option you want to use.

  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 that varying-length string columns within the index are always padded with the default pad character to their maximum length. Clear the check box to indicate NOT PADDED.

    Define Data Sets

    Specifies that the data sets are created when the index is created. Clear the check box to set the DEFINE parameter to NO.

    Use Bufferpool

    Identifies the buffer pool to use for the index.

    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

    Indicates that the COPY utility is allowed for the index.

    Is Compressed

    Specifies that the index will use index compression. Clear the check box to set the parameter to COMPRESS NO.

  5. Click the Using Clause tab and work with the following options:
    Volume Catalog

    Specifies that the first data set of the index is managed by the user. Catalog Name identifies the linear VSAM data sets that are cataloged in an integrated catalog facility catalog.

    Storage Group

    Specifies that DB2 manages the data set.

    Primary Space Allocation

    Specifies the minimum primary space allocated to a DB2-managed data set. If you do not specify any value, DB2 uses a default value.

    Secondary Space Allocation

    Specifies the minimum secondary space allocated to a DB2-managed data set. If you do not specify any value, DB2 uses a formula to calculate the value. For more information, refer to IBM DB2 for z/OS documentation.

    Erase When Deleted

    Specifies whether or not to delete the DB2-managed data sets when the index is dropped while executing a utility or an SQL statement.

    Free Page Interval

    Specifies the frequency at which you want to leave a page of free space when you create index entries while executing a DB2 utility or when you create an index for a table with existing rows.

    Percent Free

    Specifies the percentage of free space you want to leave in each nonleaf page and leaf page when you add entries to an index or index partition while executing a DB2 utility or when you create an index for a table with existing rows.

    Group Bufferpool

    Specifies the index pages that are written to the group buffer pool and applies to the data sharing environment. Select from the drop-down list; valid values are: Changed, All, and None.

  6. Click the Partition tab and work with the following options:
    Name

    Specifies a name for the partition.

    Partition Number

    Specifies the highest value of the sorting sequences of index columns in a single partition of a partitioning index.

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

    The DB2 for z/OS Table Index Editor closes.

More information:

Specify DB2 z/OS Using Clause Parameters

Define DB2 z/OS Table Partition Elements

Define DB2 z/OS Table or Index Partitions

Add a Comment in a Property Editor

Add a UDP in a Physical Property Editor