Specifying a distinct-type column's data type and default options

These steps are part of the larger task of adding a column. When you complete the steps for specifying a distinct-type column, return to adding a column.

To specify a column based on a distinct (user-defined) type:

1. On the Distinct Type page, use the Distinct type schema box to specify the schema of the distinct type on which you want to base this column.
2. Use the Distinct type name box to specify the name of the distinct type.

   The length and scale of the column are respectively the length and scale of the source type of the distinct type.

   The encoding scheme of the distinct type must be the same as the encoding scheme of the table. The subtype, if any, for the distinct type, is the subtype with which the distinct type was created.

3. If you do not want the column to contain null values, clear the Nullable check box. The default is nullable.

   If you're adding this column when you are altering a table and you clear this check box to make this column non-nullable, you must also specify a non-nullable default for this column in the next step, unless the distinct type is sourced on ROWID.

4. Optional: To specify a default value be assigned to the column when no value is specified on an INSERT or LOAD, select the Default check box and use the Default value box to specify one of the following choices. The available choices shown in the box depend on the DB2 built-in data type on which the distinct type is based.

   constant value

   The column's default value is a constant. To specify the constant, type a value in the Constant value field. The value of the constant must be a valid value for the data type on which the distinct type is based.

   If you do not type a value in the Constant value field, the default value depends on the data type of the column.

   If the distinct type is based on the CLOB, BLOB, or DBCLOB data type, do not specify a value in the Constant value field. The default value will be a zero-length string.

   USER

   The column's default value is the execution-time value of the USER special register. This choice is available only for distinct type that is based on a character string column with a length attribute of at least 8 bytes.

   If you're adding this column when you are altering a table, the value for existing rows is that of the USER special register at the time that the table is altered.

   CURRENT SQLID

   The column's default value is the SQL authorization ID (SQLID) of the process. The SQLID is the DB2 subsystem user identifier for the current connection. This value can be specified only for a distinct type that is based on a character string column with a length attribute of at least 8 bytes.

   If you're adding this column when you are altering a table, the value for existing rows is that of the SQLID special register at the time that the table is altered.

   NULL

   The column's default value is the null value. NULL is available only if the Nullable check box is selected.

   Do not specify NULL for a distinct type based on the CLOB, BLOB, or DBCLOB data type.

5. Optional: To specify using a cast function to convert the Default value into the distinct data type, select the Use cast function check box. The name of the cast function will match the name of the distinct type for the column.

   A cast function is a function used to convert instances of a (source) data type into instances of a different (target) data type. In general, a cast function has the name of the target data type.

6. Optional: If the distinct type for this column is based on the ROWID data type, select either the Always or By default radio button to specify how values are generated for that column.
7. If you do not want to define a field procedure or comment for this column, click Apply to add the new column. Otherwise, return to the main task.