Previous Topic: Define DB2 for i Table Column PropertiesNext Topic: Define DB2 z/OS Table Column Properties


Define DB2 LUW Table Column Properties

In a model based on the DB2 for LUW target server, you use the DB2 for LUW tab in the Table Column Editor to manage the data type, null option, and other properties for the selected column.

Note: The options described here are the basic options available for most physical data types. There can be more options available depending on the physical data type you assign to the table column. We recommend that you refer to your DB2 for LUW documentation for more information about using these properties.

To define DB2 for LUW table column properties

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

    The DB2 for LUW Table Column Editor opens.

  2. Select the table from the Table drop-down that contains the column that you want to define.
  3. Select the column in the Navigation Grid that you want to define.

    Note: Click New New icon in property editors to create a new object on the toolbar to create a column. Use the Enter filter text text box to filter a large list of columns to locate the one that you want to define.

  4. Click the DB2 for LUW tab and work with the following options:
    Physical Data type

    Lets you select a data type from the drop-down list to apply to the selected column. If the selected data type requires a value for precision, scale, or both, enter the value inside the data type parentheses. For example, you can select the DECIMAL() data type and specify a precision of 2 and a scale of 10 inside the parentheses to assign the data type DECIMAL(2,10) to the selected column.

    Character Type

    Lets you specify the data subtype for a character column. Select a value from the drop-down list.

    Note: This option is applicable for CHAR, CHAR(), CHARACTER, CHARACTER(), VARCHAR(), and LONG VARCHAR() data types.

    Maximum Length In Row

    Lets you enter the maximum length allowed in a row for the data type you assigned (this is same as the Inline Length property).

    Null Option

    Lets you specify a null option for the selected column.

    Average Width

    Lets you enter an estimated average width for the column. You can later use the Volumetrics dialog to calculate table and database size estimates based on these and other values.

    Percent Null

    Lets you enter the estimated percentage of nulls used for that column if the option is available. For example, you can estimate a variable width ADDRESS 2 column assigned a width of 50 characters, to be NULL about 30 percent of the time. Use whole numbers. You can later use the Volumetrics dialog to calculate table and database size estimates based on these and other values.

    Security Label

    Specifies the security label for the column. Create or select from the drop-down list.

    Column Compression

    Specifies whether to use column compression. Select the check box to enable this option.

    Is LOB Data Compacted

    Specifies whether or not the LOB data in a LOB column is compacted.

    Is LOB Data Logged

    Specifies whether or not updates to a LOB column are recorded in the system log.

    Generated

    Specifies how a table column generates values when using an expression. Select a value from the drop-down list; valid values are Always and Default.

    Generation Expression

    Specifies the expression used to compute the value for the column.

    Note: The following options are valid only for identity columns.

    Generated Identity

    Specifies whether or not the column is the identity column for the selected table.

    Starting Value

    Specifies the first value for the identity column.

    Increment By

    Specifies the interval between consecutive values of the identity column.

    No Minimum Value

    Specifies whether or not there is a minimum value for the identity column sequence.

    Minimum Value

    Specifies the minimum value for the identity column sequence.

    No Maximum Value

    Specifies whether or not there is a maximum value for the identity column sequence.

    Maximum Value

    Specifies the maximum value for the identity column sequence.

    Cycle Values

    Specifies whether or not the identity column should continue to generate values after generating either the maximum or minimum value.

    Cache Values

    Specifies whether or not to keep some pre-allocated values in memory for faster access.

    Cache Capacity

    Specifies the number of values of the identity column sequence that DB2 LUW can preallocate and keep in memory.

    Order Values

    Specifies whether or not the values of the identity column sequence are generated in order of request.

  5. Click the Options tab and work with the following options:
    Name

    Specifies any additional options on the columns of the nickname.

    Value

    Specifies the value for the selected additional option.

    Note: You can enter these values only if you are creating or editing a Nickname.

  6. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
  7. (Optional) Click the Where Used tab to view where the object is used within the model.
  8. (Optional) Click the UDP tab to work with user-defined properties for the object.
  9. (Optional) Click the History tab to view the history information for the object.
  10. (Optional) Click the Notes tab to view and edit user notes.
  11. Click Close.

    DB2 for LUW table column properties are defined and the DB2 for LUW Table Column Editor closes.