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.
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 iSeries®: Dimension | Select to specify the index as a dimension. The columns for this index will be available on the Dimensions page in the Table properties. Each column or group of columns can be used to cluster the table data. |
Property | Description |
---|---|
Unique | Specifies that the index is a unique index. |
Clustered | Specifies that the index is a clustered index. |
DB2 for z/OS® only: Padded | 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. |
DB2 for Linux, UNIX, and Windows, or DB2 for iSeries only: PCTFree | Specify a PCTFREE value. This option specifies what percentage of each index page to leave as free space when the index is built. |
Key columns | Specify columns for the key of 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. |
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. |
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. |
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. |
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 executing 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 executing a DB2 utility or when creating an index for a table with existing rows. |
Compress | Specifies data compression for the partition. |
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. |