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 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. |
Specify whether the index is an encoded vector index (DB2 for i5/OS only).
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 and expressions to include in the index. You can add, remove, or change the sort order for include 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 an include member and click the arrow toolbar buttons to change the sort order. To delete include members, select a member and press Delete. |
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. |
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. |
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. |