Indexes Wizard (DB2 Z/OS)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for z/OS Object Wizards

Like the index in a book, a database index makes it easier for you to access information quickly. An index lists the location of rows, sorted by the contents of one or more columns.

To create a new index using a wizard:

  1. Open a creation wizard for an index. For details, see Opening an Object Wizard.
  2. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  3. Finally, use the Execute button to create the object.

Indexes (DB2 z/OS) - Properties

When creating or editing an index, this tab/panel lets you work with the following settings:

Setting Description

Table Schema and Table Name

Choose the owner and name of the table in which the index is being created.

Schema and Name

Choose the owner and name of the index being created.

Index Type

Unique: Prevents the selected table from having two or more rows with the same value of the index key. The uniqueness is enforced at the end of the SQL statement update. Also, null values are treated like any other values, so a column cannot contain more than one null. If you later elect to partition the index, the columns specified for the unique key must include all columns for the partitioning key.

Non-Unique (default)


Enable or disable clustering. Unless you specifically select the CLUSTER option when you create an index, the first index you create on a table will be bestowed with that distinction. Each table can only have one clustering index at a time. The clustering index tells DB2 to insert rows in the table in the order of the clustering key values. Inserted rows will be stored contiguously in sequence when possible. Columns frequently searched with a range of values BETWEEN, less than, greater than, and LIKE, or GROUP BY, etc., are good candidates for a clustering index.

Number of Partitions

If you enabled Clustering, specify the number of partitions.

Buffer Pool

Provide the buffer pool in which this index should reside

Defer, Close, Copy, and Define

Enable or disable these DB2 options as required.

Piece Size

The maximum addressability of the dataset for a non-partitioned index.


Enabling this setting specifies a COMPRESS=YES parameter when creating the table, resulting in a compressed index. This feature is available if the size of the specified Buffer Pool is 8k, 16k, or 32k.

Index on expression

Setting the check box enables the Index on Expression feature. The key expression is then provided on the Columns tab/panel. For details, see Indexes (DB2 z/OS) - Columns.


Setting this check box specifies that the table is to be created with the PADDED keyword. This feature must be enabled if you are using index keyrandomization. The RANDOM setting is applied on a column by column basis on the Columns tab/panel.

Indexes (DB2 z/OS) - Columns

The actions you take on this tab/panel depend on whether you set the Index on Expression check box on the Properties panel. For more information, see Indexes (DB2 z/OS) - Properties.

  • If Index on Expression is disabled:
From the Column dropdown, select a column for the index and specify a Sort option.
Note: If you want to use the RANDOM sort option, you must first set the Padded check box on the Properties tab/panel.
  • If Index on Expression is enabled:
Enter a valid key-expression in the Expression field and choose a Sort order.

To add more columns/key-expressions, click the New button and then follow the steps in the last instruction. Use the Delete button to drop columns.

Indexes (DB2 z/OS) - Storage

This tab/panel lets you perform the following tasks:

  • Select a dataset management scheme
  • Provide associated attribute values

To select a data set management scheme:

  1. Click the Edit button. The Data Set Management dialog opens.
  2. Set one of the following data set management options:
    • DB2 will define and manage the data sets on a volume of the default storage group of the database
    • DB2 will define and manage the data sets on a volume of the specified storage group - Select a storage group (a storage group is made up of disk device volumes): Each data set will be defined on a volume listed in the storage group you select.
    Minimum primary space allocation: 12 kilobytes is the default.
    Minimum secondary space allocation: 12 kilobytes is the default.
    Note: If the primary and (118 x) secondary space allocations combine to be greater than 2 gigabytes, more than one data set may eventually be used.
    Erase data sets when index dropped? If choose this option, DB2 will overwrite all data with zeros before they are deleted as a security measure.
    • User will manage the data sets on a specified VCAT catalog-name - Enter or select the VCAT. Do not select this option for an index on a declared temporary table.
  3. Click OK.

In addition to the attributes specific to your data set management choice, this tab/panel also offers the following settings:

Free Page

One free page exists for every x pages. The x specifies how often to leave a page of free space when index entries are created from executing a DB2 utility or creating an index for a table with pre-existing rows. (0-255)

Percent Free

The percentage of free space you want to leave in every page when entries are added to an existing index. The default is 10%.

GBP Cache

This option is available only in a data-sharing environment. ALL: As pages are read, all of them will be cached in the group buffer pool. CHANGED: Updated pages are cached to the group buffer pool. NONE: No pages will be cached.

Indexes (DB2 z/OS) - Partitions

Displays the default settings for the number of partitions you specified on the Properties pane. Select a partition and click the Edit button to modify details for that partition.