Unique Keys Wizard (DB2 Z/OS)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for z/OS Object Wizards

A unique key constraint is a key for which no two of its values can be equal and no values can be null. A table can have a number of unique constraints, but it cannot have more than one unique constraint on the same set of columns. If you are creating a unique key constraint on a table that already exists (as opposed to creating a unique key at the time the table is first generated), a unique index must already exist on the columns of the unique key you want to constrain. If no unique index exists, the Index Wizard will open as you complete the Create Unique Key Constraint dialog.

To create a new unique key using a wizard:

  1. Open a creation wizard for a unique key. 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.

Unique Keys (DB2 z/OS) - Properties

When creating or editing a primary key, 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 unique key is being created.

Schema and Name

Choose the owner and name of the index being created.

Clustered

Enable or disable clustering.

Number of Partitions

If you enabled Clustering, specify the number of partitions.

Buffer Pool

Provide the buffer pool in which this unique key 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.

Time period policy

This setting is for use with tables set up as business-time tables. For more information, see Tables (DB2 z/OS v10) - Temporal Properties. When selected, a BUSINESS_TIME WITHOUT OVERLAPS option is specified against the column or columns defining the constraint, in the generated DDL. This ensures that values for the constraint columns are unique with respect to the time for the BUSINESS_TIME period. When you select this option, columns specified as business-time begin period and end period are not available as candidates on the Columns tab/panel.

Unique Keys (DB2 z/OS) - Columns

From the Column dropdown, select a column for the unique key and specify a Sort option. To add more columns, click the New button and then follow the steps in the last instruction. Use the Delete button to drop columns.

Unique Keys (DB2 z/OS) - Storage - Edit button (manage datasets)

Note: Availability differs according to the dataset management options you chose

Choose a data set management option:

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 you 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.

Unique Keys (DB2 z/OS) - Storage - Attributes

Note: Availability differs according to the dataset management options you chose

When creating or editing a unique key, this tab/panel lets you work with the following settings:

Setting Description

Storage Group, Primary Space Allocation, Secondary Space Allocation, Erase, and VCAT catalog

The ability to set these options depends on the dataset management options you chose.

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.

Unique Keys (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.