Tables Wizard (DB2 Z/OS)

From DBArtisan
Jump to: navigation, search

Go Up to IBM DB2 for z/OS Object Wizards

All data in a database is stored in a tabular format, that is a collection of rows and columns. Tables, therefore are fundamental to whatever database you are administering.

To create a new table using a wizard:

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

The table that follows describes the fields you may encounter as you complete the Table Wizard.

Tables (DB2 z/OS) - Properties

Select the Schema, provide a Name, and provide or select other table properties.

Tables (DB2 z/OS) - Columns

For each column in the table, click the Add Column button to create a column, provide a Name for the column and provide or select the remaining column attributes.

Use the Delete button to drop a selected column.

Use the arrow buttons to reposition the columns.

Row Properties (V10) settings are available when you select a Type of TIMESTAMP. These settings are used in conjunction with settings on the Temporal Properties tab in setting up system and application temporal tables. For more information, see Tables (DB2 z/OS v10) - Temporal Properties.

Specific settings are:

  • As Row Begin - adds a GENERATED ALWAYS AS ROW BEGIN option to the column specification, used to track when a row is current.
  • As Row End - adds a GENERATED ALWAYS AS ROW END option to the column specification.
  • Transaction Start ID - adds a GENERATED ALWAYS AS TRANSACTION START ID option to the column specification indicating that this column captures the start times for transactions that affect rows.

A column can have only one of the three settings selected.

When you select a Row Properties setting, the Scale value and if appropriate, the Allow Nulls, Default Value, and Expression settings are automatically adjusted for compatibility with row generation. Similarly, if one of the three Row Properties settings is selected for a column, and any of the Scale, Allow Nulls, Default Value, or Expression settings are modified, then the selected Row Properties setting is cleared.

Tables (DB2 z/OS) - Indexes

Click Add to open the Indexes Wizard (DB2 Z/OS).

Tables (DB2 z/OS v10) - Temporal Properties

This tab/panel lets you specify temporal properties to a table, designating it as a table that records the period of time when a row is valid.

Note: Before using this object action, consult DB2 LUW documentation for details on setup and use of the temporal tables feature. For more information, see Accessing Third Party Documentation.

This tab/panel has the following controls:

  • System-time table - selecting this option creates a table with the PERIOD SYSTEM_TIME clause, designating it as a system-period temporal table. This option is available if the table has three columns of type TIMESTAMP, and the following prerequisites have been met:
  • At least one TIMESTAMP-typed column has the As Row Begin property selected
  • At least one TIMESTAMP-typed column has the As Row End property selected
  • At least one TIMESTAMP-typed column has the Transaction Start ID property selected
For details on column definitions, see Tables (DB2 z/OS) - Columns.
The Row-begin column, Row-end column, and Transaction Start ID column controls are automatically populated with the columns having the corresponding property.
When you select this option, in the generated DDL, the Row-begin column and Row-end column values are used as the PERIOD SYSTEM_TIME column parameters, defining the system period.
  • Business-time table - selecting this option creates a table with the PERIOD BUSINESS_TIME clause, designating it as an application-period temporal table. This option is available if the table has two columns of type DATE or TIMESTAMP, for which no Row Properties have been assigned.
The Begin column and End Column controls are automatically populated with the valid candidate columns from the table's column list and the first distinct column pair is automatically chosen for convenience.
  • Use history table - this set of controls is only active when Business-time table is selected. It lets you associate a history table. When you select Use history table, the Owner and Name controls are activated, letting you identify the history table to associate with this table.

Note that a constraint created on a business-time table can enforce the uniqueness of the data for any point in business time via the Time period policy property that has been added in the constraint wizards. For details, see the following topics:

Tables (DB2 z/OS) - Constraints

Selecting a constraint type and clicking Add opens the object wizard for that object type. For details see: