Add Column -- Fields and controls

Built-in Type page

Distinct Type page

Built-in Type page

Data type

Length

LOB unit

Constant value

Always

By default

Data type

Use this box to specify the data type of the column. The data type can be one of:

INTEGER

For a large integer.

SMALLINT

For a small integer.

DECIMAL

For a decimal number.

REAL

For a single-precision floating-point number.

DOUBLE

For a double-precision floating-point number.

CHAR

For a fixed-length character string.

VARCHAR

For a varying-length character string.

GRAPHIC

For a fixed-length graphic string.

VARGRAPHIC

For a varying-length graphic string.

CLOB

For a character large object string. A CLOB column has a varying length and is a long string column regardless of its length.

BLOB

For a binary large object. A BLOB column has a varying length and is a long string column regardless of its length.

DBCLOB

For a double-byte character large object string of the specified maximum length in double-byte characters. A DBCLOB column has a varying length and is a long string column regardless of length.

DATE

For a date.

TIME

For a time.

TIMESTAMP

For a timestamp.

ROWID

For a row ID type. A table can have only one ROWID column. The values in a ROWID column are unique for every row in the table and cannot be updated. DB2 implicitly defines a ROWID column as NOT NULL.

Length

Use this field to specify the length (byte count) of the columns based on the specified data type:

CHAR

Length of the fixed-length character string, which can range from 1 to 255. The default is 1.

VARCHAR

Maximum length of the varying-length character string, which can range from 1 to the maximum record size minus 8 bytes.

The maximum record size depends on the page size of the table space (the size of its buffer) and whether an edit procedure is specified for the table. The maximum record size when there's no edit procedure on the table is:

• 4056, for a 4 KB page size.
• 8138, for an 8 KB page size.
• 16330, for a 16 KB page size.
• 32714, for a 32 KB page size.

and when there is an edit procedure on the table:

• 4046, for a 4 KB page size.
• 8128, for an 8 KB page size.
• 16320, for a 16 KB page size.
• 32704, for a 32 KB page size.

Therefore, the maximum VARCHAR length that you can specify when there is not an edit procedure is:

• 4048, for a 4 KB page size.
• 8130, for a 8 KB page size.
• 16322, for a 16 KB page size.
• 32706, for a 32 KB page size.

and when there is an edit procedure, the maximum VARCHAR length is:

• 4038, for a 4 KB page size.
• 8120, for a 8 KB page size.
• 16312, for a 16 KB page size.
• 32696, for a 32 KB page size.

GRAPHIC

Length of the fixed-length graphic string, which can range from 1 to 127. The default is 1.

VARGRAPHIC

Maximum length of the varying-length graphic string, which can range from 1 to N/2, where N is the maximum row size minus 2 bytes.

CLOB

Length of the character large object string. The value that you can type in this field for a CLOB can range from:

• 1 to 2 147 483 647 if the LOB unit is in bytes.
• 1 to 2 097 152 if the LOB unit is in K bytes.
• 1 to 2048 if the LOB unit is in M bytes.
• 1 to 2 if the LOB unit is in G bytes.

If you specify a value that evaluates to 2 GB (2 147 483 648 bytes), DB2 uses a value that is one byte less (2 147 483 647 bytes).

BLOB

Length of the binary large object string. The range of the value that you can type in this field for a BLOB is the same as for a CLOB.

DBCLOB

Length of the double-byte character large object string. The value that you can type in this field for a DBCLOB can range from:

• 1 to 1 073 741 824 if the LOB unit is in bytes.
• 1 to 1 048 576 if the LOB unit is in K bytes.
• 1 to 1024 if the LOB unit is in M bytes.
• If the LOB unit is in G bytes, you can type only a 1.

LOB unit

Use this field to specify the units in which the length of a large object (LOB) column is to be measured. This field is available only if the column's data type is CLOB, BLOB, or DBCLOB. The LOB units multiplied by the value you specify in the Length field sets the maximum length for the column.

Constant value

Use this field to specify the constant value to be assigned to the column in the absence of a value specified on an insert or load operation. The value that you specify must be a valid value for the column's data type. If you do not specify a value in this field, a default value is assigned depending on the data type of the column.

• For numeric data types (for example, INTEGER), the default value is 0.
• For fixed-length string data types, the default value is a string made of blanks.
• For varying-length strings, the default value is a string of length 0.
• For the DATE data type, the default value is the current date.

  Exception: If you are altering a table, the default value for existing rows is 1 January 0001.

• For the TIME data type, the default value is the current time.

  Exception: If you are altering a table, the default value is 0 hours, 0 minutes, and 0 seconds.

• For the TIMESTAMP data type, the default value is the current timestamp.

  Exception: If you are altering a table, the default value is a timestamp corresponding to a date of 1 January 0001 and a time corresponding to 0 hours, 0 minutes, 0 seconds, and 0 microseconds.

• For LOB data types, the default value is a string of length 0.

Always

Select this radio button to specify that DB2 will always generate values for the column when a row is inserted into the table. This radio button is only available for the ROWID column data type. Selecting Always is recommended unless you are using this table for replication and have selected the Data capture check box on the Table page of the Create Table notebook.

By default

Select this radio button to specify that DB2 will generate a value for the column when a row is inserted into the table unless a value is specified in the insertion operation. This radio button is only available for the ROWID column data type.

If the insertion operation specifies a value, DB2 uses that value only if it is a valid ROWID value that was previously generated by DB2. If you select this radio button, you must also define a unique single-column index on this ROWID column. Until this index is created, the SQL INSERT statement and the LOAD utility cannot be used to add rows to the table.

Distinct Type page

Constant value

Always

By default

Constant value

Use this field to specify the constant value to be assigned to the column in the absence of a value specified on an insert or load operation. If you do not specify a value, the default value that is assigned depends on the data type on which the distinct type is sourced. See Constant value for the list of default values according to data type.

If you selected the Use cast function check box, the constant value that you specify is cast (converted) into the column's distinct type.

Always

If the column's distinct type is sourced on the DB2 ROWID data type, you can select the Always radio button to specify that DB2 will always generate values for the column when a row is inserted into the table. Selecting Always is recommended unless you are using this table for replication and have selected the Data capture check box on the Table page of the Create Table notebook.

By default

If the column's distinct type is sourced on the DB2 ROWID data type, you can select the By default radio button to specify that DB2 will generate a value for the column when a row is inserted into the table unless a value is specified in the insertion operation.

If the insertion operation specifies a value, DB2 uses that value only if it is a valid ROWID value that was previously generated by DB2. If you select this radio button, you must also define a unique single-column index on this ROWID column. Until this index is created, the SQL INSERT statement and the LOAD utility cannot be used to add rows to the table.