Index properties

Properties for index objects

For more information about specific syntax, see the reference information that is included with each database product. Syntax definitions for some database vendors or versions might differ from the following descriptions.

General page
Property Description
Name Specifies a name for the object.
Label Specifies a label that is displayed in visual diagrams.
Schema Shows the relational schema under which the object was created.
System generated Specifies that no code is generated for this index during DDL generation. This option is available if the database automatically generates an index.
DB2® for Linux, UNIX, and Windows, or DB2 for i5/OS™: Dimension Select to specify the index as a dimension. The columns for this index are available on the Columns page in the Table properties. Each column or group of columns can be used to cluster the table data.
DB2 for i5/OS only: System name Specifies that this index should be renamed to a system name during DDL generation.
Columns page
Specify whether the index is unique. Define the columns (and expressions, if the database vendor supports index expressions) that you want to include in the index.
Header Header
Key members Specify columns and expressions for the key of the index. You can add, remove, or change the sort order for members in the list. Click the ellipsis toolbar button above the table to open a window where you can select columns and expressions and add them to the list. Select a key member and click the arrow toolbar buttons to change the sort order. To delete key members, select a member and press Delete.
Include columns Specify non-key columns to include in the index. You can add, remove, or change the sort order for columns in the list. Click the ellipsis toolbar button above the table to open a window where you can select columns and add them to the list. Select a column and click the arrow toolbar buttons to change the sort order. To delete columns, select a column and press Delete.
XML Column page (DB2 for Linux, UNIX, and Windows)
Create indexes for the nodes of an XML document.
Privileges page (DB2 for Linux, UNIX, and Windows)
Specify which users, groups, and roles can access the index.
Performance page (DB2 for Linux, UNIX, and Windows)
Specify whether the index is a clustered index, whether null keys should be excluded, if forward and reverse scans are supported, the PCTFree value, whether index compression in enabled, and if statistics are collected during index creation. The PCTFree value specifies what percentage of each index page to leave as free space when the index is built.
Details page (DB2 for z/OS®)
Specify the details of the index, including clustering, the bufferpool, and the piece size. The Padded option specifies how varying-length string columns are stored in the index. If the index contains no varying-length columns, this option is ignored, and a warning message is returned. Indexes that do not have varying-length string columns are always created as physically padded indexes.
Partitioning page (DB2 for Linux, UNIX, and Windows)
Specify whether a single index should be created that spans all of the data partitions defined for the table. To use partitions, the table name must identify a table defined with data partitions.
Partitions page (DB2 for z/OS)
Property Description
Use no partitions Specifies that the index is not partitioned. The options on this page change depending on whether you select this option or the Use partitions option.
Use partitions Specifies that the index is partitioned. This option is only available if the table is partitioned. The options on this page change depending on whether you select this option or the Use no partitions option. If you select this option, you can add or delete partitions by clicking the Add Partition or Delete Partition toolbar buttons.
Storage group/VCAT Specifies whether to use the USING STOGROUP or the USING VCAT option. Also specifies which storage group or integrated catalog facility catalog (VCAT) is being used. The meaning of the USING STOGROUP or USING VCAT clause is different depending on whether the index is partitioned or not partitioned.
Primary quantity Specifies a PRIQTY value. Available only if a storage group is specified. Specifies the minimum primary space allocation for a DB2-managed data set.
Secondary quantity Specifies a SECQTY value. Available only if a storage group is specified. Specifies the minimum secondary space allocation for a DB2-managed data set.
Erase Specifies whether DB2-managed data sets are erased when they are deleted when a utility or an SQL statement that drops the index runs.
Free page Specifies a FREEPAGE value. Specifies how often to leave a page of free space when index entries are created as the result of running a DB2 utility or when creating an index for a table with existing rows. One free page is left for every integer pages. The value of integer can range from 0 to 255. The default is 0, leaving no free pages.
PCT free Specifies a PCTFREE value. Determines the percentage of free space to leave in each nonleaf page and leaf page when entries are added to the index or index partition as the result of running a DB2 utility or when creating an index for a table with existing rows.
GBPCache Specifies how changes to the group bufferpool are cached.
Table space page (DB2 for Linux, UNIX, and Windows)
Specifies the table space in which the index is to be created. This clause is only supported for indexes on partitioned tables. You can specify this clause even if the INDEX IN clause was specified when the table was created. This will override that clause.
Partition key (Oracle only)
In edit mode, use the grid and the toolbar buttons on this page to add, delete, change the order, and modify properties for partition key columns.
Prerequisites for setting partition key properties:
  • The index must contain at least one column for partition with GLOBAL locality.
  • The table that owns the index must have an existing partition key for the index partition with LOCAL locality.
  • For GLOBAL locality: To add a new column to the partition key, click the ellipsis [...] toolbar button, select columns in the window that opens, and click OK. For LOCAL locality, the columns in the index data partition are the same as the owning table.
  • For GLOBAL locality: To change the order of columns, select a column in the grid and click Move Column Up or Move Column Down. For LOCAL locality, the columns order in the index data partition is the same as in the owning table.
  • For GLOBAL locality: Use the grid to modify the properties of a partition key column. Double-click a cell or click a check box in a cell to edit the cell contents. For LOCAL locality, the properties of the partition key columns are the same as in the owning table.
    Property Description
    Oracle only: Locality Select LOCAL or GLOBAL from the list. If you select LOCAL, the index data partition is the same as that of the table that owns the index. Therefore, the Maintained By list only contains the same types as the table. The Maintained By list is empty if the owning table does not have a partition key defined. If you select GLOBAL, you can specify HASH or RANGE in the Maintained By list.
    Maintained By Select an option for the PARTITIONING KEY clause. Options in this field are different depending on what you selected in the Locality field.
    Name or Column Specifies the name of the column.
    Data type Specifies the data type of the column.
    DB2 for z/OS only: Ordering type Specifies an option for ordering the entries by column. You can select ASC (ASCENDING) or DESC (DESCENDING).
    DB2 for z/OS only: Nulls last Specifies using the NULLS LAST clause. Specifies that null values are treated as positive infinity for purposes of comparison.
    DB2 for z/OS only: Ending limit Specifies an ending limit for each partition.
Data partition page (Oracle)
Use the toolbar buttons and the grid to add, remove, or modify data partitions.
Prerequisites for creating data partitions:
  • You must create at least one partition key.
  • To add a new data partition, click the ellipsis toolbar button, specify partition details in the window that opens, and click OK.

    For HASH partitions: You must specify a partition name.

    For LIST or RANGE partitions (Oracle only): You must specify a partition name and column values.

Property Description
Partition Name Specifies the name of the partition.
Values Specifies the column values for the partition.
Table Space Specifies the table space under which this partition was created.
Volumetrics page (DB2 for z/OS only)
Enter the average number of data records per distinct key value of a nonunique index. For more information, see the topic: Estimating storage from the number of index pages in the DB2 for z/OS Administration Guide in the DB2 for z/OS information center. The average number of data records per distinct key value of a UNIQUE index is 1.
Documentation page
Specifies user-supplied information about the object.
Annotation page (in edit mode only)
Add, modify, or delete properties.

Feedback