Creating Models › Target Servers for Physical Models › Database Property Editors › DB2 LUW Property Editors › Define a DB2 LUW Table

Define a DB2 LUW Table

Use the DB2 for LUW Table Editor to create a table, or work with table properties for an existing DB2 LUW table.

To define a DB2 LUW table

1. Right-click a Table in the Model Explorer and select Properties.

   The DB2 for LUW Table Editor opens.

2. Select a table and work with the following options:

   Note: Click New on the toolbar to create a table. Use the Enter filter text text box to filter a large list of names to locate the one you want to work with.

   Physical Name

   Specifies the physical name for the table.

   Schema

   Specifies the schema associated with the selected table. Select a schema from the drop-down list.

   DB2/LUW Type

   Specifies the table type. Select a value from the drop-down list: Permanent, Global Temporary, Nickname. The tabs on this dialog are enabled or disabled based on this value.

   Use Replace Syntax

   Specifies whether or not to use the CREATE or REPLACE command instead of the CREATE command during Forward Engineering. You can select this check box only if you selected the DB2 LUW Type as Nickname.

   Note: This field applies to DB2 for LUW version 9.7 only.

   Physical Only

   In a Logical/Physical model, specifies that the table does not appear on the logical side as an entity.

   Generate

   Specifies whether or not to generate DDL for this object during Forward Engineering.

3. Click the Dimensional tab and work with the following options:

   Note: This tab is displayed when you select the Is Dimensional check box on the General tab of the Model Editor.

   Dimensional Notation Rule Type

   Specifies the role assigned to the table for dimensional modeling. Select from the drop-down list; valid values are Fact, Dimension, and Outrigger.

   Dimensional Notation Update Type

   Specifies the type of update you want to use to update dimensional notations. Select from the drop-down list; valid values are Fixed, Type 1, Type 2, and Type 3.

4. Click the Data Movement Rule tab and work with the following options:

   Note: This tab is displayed when you select the Data Movement check box on the General tab of the Model Editor.

   Refresh Rule

   Append Rule

   Backup Rule

   Recovery Rule

   Archive Rule

   Purge Rule

5. Click the General tab and work with the following options:

   Value Compression (check box)

   Specifies whether or not the row format is used. For more information about value compression, see the IBM DB2 documentation.

   Is Table Row Compressed

   Specifies whether or not data compression applies to the rows of the table. Select from the drop-down list; valid values are True and False.

   Data Capture

   Indicates whether extra information for data replication is written to the log. Valid Values: None, Changes, Changes (including long vars).

   Has Restriction On Drop

   Indicates that the selected table has the restriction on drop. Clear the check box to remove the restriction on dropping the table and the tablespace that contain the table.

   Is Logging Index Builds

   Specifies whether or not to log any index build operations on this table. Select from the drop-down list; valid values are True and False.

   Not Logged Initially (check box)

   Specifies whether or not to log changes made by an Insert, Delete, Update, Create Index, Drop Index, or Alter Table operation in the same unit of work in which the table is created.

   Lock Type

   Specifies the granularity of locks used when the table is accessed. Valid Values are row, table, and block insert.

   Coded Character Set Identifier (CCSID)

   Specifies the encoding scheme for string data stored in the table. Valid Values are ASCII and UNICODE.

   Security Policy

   Specifies the security policy to associate with the selected table. Select a security policy from the drop-down list.

   Has Volatile Cardinality

   Specifies that index access should be used on this table wherever possible for SQL operations. Clear the check box to specify NOT VOLATILE.

   Is Appendable

   Specifies that append processing is used for the selected table. Clear the check box to indicate APPEND NO.

   Audit Policy

   Specifies the audit policy for the selected table. Select from the drop-down list.

   Percent Free

   Specifies the percentage of each page that you want to leave as free space.

6. On the Organize By tab, select an organization type (Key Sequence or Dimension), and then work with the following options:

   For the Key Sequence Organization Type:

   Is Key Sequence Overflow Allowed

   Specifies whether or not the range-clustered table allows rows with key values that are outside of the defined range of values.

   Key Sequence Percent Free

   Specifies the percentage of each page that you want to leave as free space.

   Column

   Specifies a column of the table that is included in the unique key that determines the sequence of the range-clustered table.

   Sequence Starting Value

   Specifies the constant value at the lower end of the range for column-name.

   Sequence Ending Value

   Specifies the constant value at the higher end of the range for column-name.

   For the Dimension Organization Type:

   Dimension Groups

   Specifies a dimension for each column or group of columns that you have used to cluster the table data.

7. Click the Tablespace tab and work with the following options:

   Table Tablespaces

   Specifies the name of the tablespace where you want to store the table.

   Cycle Tablespaces

   When selected, specifies that if the number of data partitions with no explicit tablespace exceeds the number of specified table spaces, the table spaces are assigned to data partitions in a round-robin fashion.

   Long In Tablespaces

   Specifies the tablespace where you want to store the values of long column.

   Index In Tablespace

   Specifies the tablespace where you want any indexes on a nonpartitioned table or nonpartitioned indexes on a partitioned table to be created.

8. Click the Distribution tab and work with the following options:

   Distribute By

   Specifies the default distribution method across database partitions.

   Distribution Key Columns

   Specifies the columns on which you want to use hash as the default distribution method.

9. Click the Partition tab and work with the following options:

   Partition Columns

   Specifies the columns of the selected table that participate in the partition and whether nulls compare high or low.

   Partition Elements

   Specifies the data range for the partition and the tablespace where you want to store the rows of the table in the specified data range.

10. Click the Table Options tab.

    You use the Table Options tab to identify the remote base table. Work with the following options

    Name

    Specifies the name of the table option.

    Value

    Specifies the setting for the table option.

11. Click the Validation tab and work with the following options:

    Validation

    Specifies the validation check constraint that you want to enforce on the selected table.

    Is Enforced

    Specifies whether or not the validation is enforced on the selected table.

    Is Query Optimization Disabled

    Specifies whether using the constraint for query optimization when appropriate is disabled.

    Validation Rule

    Specifies the validation rule for the selected validation.

12. Work with the other tabs in the editor to access additional design features, for example:
    • Specify an alias for the current table on the Alias tab.
    • Associate permissions with the selected table on the Permission tab.
    • Specify volumetrics options on the Volumetrics tab.
    • Preview the forward engineering script for a table in the SQL tab.
    • Specify a theme for the table on the Style tab.
    • Select an icon for the current table on the Icon tab.
    • Change the object creation order on the Object Creation Order tab.
13. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
14. (Optional) Click the Where Used tab to view where the object is used within the model.
15. (Optional) Click the UDP tab to work with user-defined properties for the object.
16. (Optional) Click the History tab to view the history information for the object.
17. (Optional) Click the Notes tab to view and edit user notes.
18. Click Close.

    The DB2 for LUW Table Editor closes.

More Information:

Define a DB2 LUW Tablespace

Define DB2 LUW Table Partition Elements

Specify Volumetric Information for a Table

Define DB2 LUW Permissions at Database Level

Add a Comment in a Property Editor

Add a UDP in a Physical Property Editor