Tables Wizard (DB2 LUW)

From RapidSQL
Jump to: navigation, search

Go Up to IBM DB2 for Linux, Unix, and Windows Object Wizards

The Table Wizard lets you create a table without knowing the underlying commands.

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:
  3. Finally, use the Execute button to create the object.

Tables (DB2 LUW) - Properties

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

Setting Description

Schema

Select the schema that is to own the table.

Name

Provide a name for the table.

Percent Free

Lock Size

The table-level lock prevents concurrently operating applications from using or changing a table. When scanning a table for a query, a row-level lock locks the row when it is being assessed.

Append Data

Enable or Disable appending data to the end of the table.

Volatile

Enable this feature if a table contents may fluctuate from empty to very large. The access plan will not depend on the existing statistics for that table.

Compress

Enable or disable value compression

Row Compression

Enable or disable row compression.

Security Policy

Lets you add a security policy to a table.

RestrictDrop

Corresponds to the DB2 Restrict on Drop attribute.

Log Index Build

Enables this level of logging when creating, recreating, or reorganizing an index.

CCSID

Specify ASCII or UNICODE or leave unspecified. If specified, this is the encoding scheme for string data. If unspecified, CCSID default encoding is used.

Tablespace, Index Tablespace, and Long Data Tablespace

Select a tablespace, an index tablespace, and a tablespace for Long or LOB table columns.

Do not initially log

If enabled, all changes to the table will be flushed out at commit time. This also means that if a statement fails, the unit of work will rollback. If you are concerned about recoverability, disable this feature.

Data Capture

Specify additional information logged by selecting DATACAPTURE NONE, DATA CAPTURE CHANGES, or DATA CAPTURE CHANGES INCLUDE LONGVAR.

Tables (DB2 LUW) - Columns

For each column in the table, click the New button to create a column and provide a name for the column. Then, in the Column Attributes area, provide details for the column.

Use the Delete button to drop a selected column.

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 LUW 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 LUW) - Partition

Under Partition Columns, for each partition column, click the New button and then choose a column name from the dropdown. To add a Data Partition, click the New button to open a dialog that lets you add a partition.

Tables (DB2 LUW) - Tablespaces

For each Data Tablespace or Long Tablespace, click the New button and then choose a tablespace from the dropdown. To specify an Index Tablespace, select a tablespace from the dropdown.

Tables (DB2 LUW) - Dimensions

For each column that is to make up a dimension, click the New button to open a dialog that lets you add a column.

Tables (DB2 LUW) - Distribution Key Columns

For each column that is to make up the distribution key, click the New button and then select a column from the dropdown

Tables (DB2 LUW) - Indexes

Click Add to open the Index wizard. For more information, see Indexes Wizard (DB2 LUW).

Tables (DB2 LUW 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 LUW) - 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 LUW) - Constraints

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