Primary Keys Wizard (DB2 Z/OS)

From DBArtisan

Go Up to IBM DB2 for z/OS Object Wizards

A primary key is a unique key that is part of a table's definition. There can be only one primary key for each table, and the columns contained in a primary key cannot have null values. A primary key constraint forbids duplicate values in one or more columns. A table with a primary key will be considered the parent table to a table with a foreign key, which becomes the dependent table.

Note: A nullable column cannot be a part of a primary key.

To create a new primary key using a wizard

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

Primary 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 index 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 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.

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.

Primary Keys (DB2 z/OS) - Columns

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

Primary Keys - 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.

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

When creating or editing a primary 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.

Primary Keys (DB2 z/OS) - Partitions

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

Retrieved from "https://docwiki.embarcadero.com/DBArtisan/191/e/index.php?title=Primary_Keys_Wizard_(DB2_Z/OS)&oldid=7668"