DB2 Help

This field displays the name currently specified for the column that you're altering. You cannot alter this field for an existing column. To specify a different name for a column that you're adding, type the new name for the column in this field. This name:

Can contain up to 30 characters
Can be an ordinary or delimited identifier

Attention: You cannot use the same name for more than one column of the table.

Data type

This box contains the data type currently specified for the column. You cannot alter this box for an existing column. To specify a different data type for a column that you're adding, click on the down arrow to display a list of data types and select one.

The data type can be one of:

INTEGER: For a large integer with a precision of 11 digits.
SMALLINT: For a small integer with a precision of 5 digits.
BIGINT: For a large integer with a precision of 19 digits.
DOUBLE: For a floating-point number. Double Precision and Float are synonyms for Double.
DECIMAL: For a decimal number.
CHARACTER: For a fixed-length character string.
VARCHAR: For a varying-length character string with a maximum length of 4000.
LONG VARCHAR: For a varying-length character string with a maximum length of 32700.
BLOB: For a binary large object string.
CLOB: For a character large object string.
DBCLOB: For a double-byte character large object string.
GRAPHIC: For a fixed-length graphic string.
VARGRAPHIC: For a varying-length graphic string with a maximum length of 2000.
LONG VARGRAPHIC: For a varying-length graphic string with a maximum length of 16350.
DATE: For a date.
TIME: For a time.
TIMESTAMP: For a timestamp.
Distinct type: For a distinct type. Distinct types are listed in the form of schema.type.
REAL: For a single-precision floating-point number.

Length

This field displays the length (byte count) currently specified for the column. To specify a different length for a column that you are adding, (or an existing VARCHAR column that you are changing), type a new value in this field based on the specified data type:

CHARACTER

Length of the fixed-length character string, which can range from 1 to 254.

VARCHAR

Maximum length of the varying-length character string, which can range from 1 to 4000.

BLOB

Maximum length of the BLOB string, which can range from 1 to 2147483647. If a LOB unit of Kbytes, Mbytes, or Gbytes is specified in the LOB unit box, the maximum value of Length is limited as follows:

Kbytes: The maximum value for this field is 2097152. Each KB is equivalent to 1024 bytes, so the maximum length in bytes is 1024 times the integer value you specify in this field.
Mbytes: The maximum value for this field is 2048. Each MB is equivalent to 1048576 bytes, so the maximum length in bytes is 1048576 times the integer value you specify in this field.
Gbytes: The maximum value for this field is 2. Each GB is equivalent to 1073741824 bytes, so the maximum length in bytes is 1073741824 times the integer value you specify in this field.

CLOB

Maximum length of the CLOB string, which can range from 1 to 2147483647. If a LOB unit of Kbytes, Mbytes, or Gbytes is specified in the LOB unit box, the maximum value of Length is limited as follows:

Kbytes: The maximum value for this field is 2097152. Each KB is equivalent to 1024 bytes, so the maximum length in bytes is 1024 times the integer value you specify in this field.
Mbytes: The maximum value for this field is 2048. Each MB is equivalent to 1048576 bytes, so the maximum length in bytes is 1048576 times the integer value you specify in this field.
Gbytes: The maximum value for this field is 2. Each GB is equivalent to 1073741824 bytes, so the maximum length in bytes is 1073741824 times the integer value you specify in this field.

DBCLOB

Maximum length of the DBCLOB string, which can range from 1 to 1073741823 double-byte characters. If a LOB unit of Kbytes, Mbytes, or Gbytes is specified in the LOB unit box, the maximum value of Length is limited as follows:

Kbytes: The maximum value for this field is 1048576. Each KB is equivalent to 1024 bytes, so the maximum length in double bytes is 1024 times the integer value you specify in this field.
Mbytes: The maximum value for this field is 1024. Each MB is equivalent to 1048576 bytes, so the maximum length in double bytes is 1048576 times the integer value you specify in this field.
Gbytes: The maximum value for this field is 1. Each GB is equivalent to 1073741824 bytes, so the maximum length in double bytes is 1073741824 times the integer value you specify in this field.

GRAPHIC

Length of the fixed-length graphic string, which can range from 1 to 127. If the length is not specified, a length of 1 is assumed.

VARGRAPHIC

Maximum length of the varying-length graphic string, which can range from 1 to 2000.

This field is available only if you specified CHARACTER, VARCHAR, BLOB, CLOB, DBCLOB, GRAPHIC, or VARGRAPHIC in the Data type box and only for a new column.

LOB unit

This box contains the LOB unit currently specified for the column. You cannot alter this box for an existing column. To specify a different multiplier for the length of a column that you're adding of data type BLOB, CLOB, or DBCLOB, select one of the following LOB units:

Bytes: The maximum length is the value specified in the Length box. The maximum value of Length is 2147483647 for BLOB or CLOB data. For DBCLOB data, the maximum value is 1073741823 because a DBCLOB string is in double-byte characters.
Kbytes: The maximum length is 1024 times the value specified in the Length box. The maximum value of Length is 2097152 for BLOB or CLOB data. For DBCLOB data, the maximum value is 1048576 because a DBCLOB string is in double-byte characters.
Mbytes: The maximum length is 1048576 times the value specified in the Length box. The maximum value of Length is 2048 for BLOB or CLOB data. For DBCLOB data, the maximum value is 1024 because a DBCLOB string is in double-byte characters.
Gbytes: The maximum length is 1073741824 times the value specified in the Length box. The maximum value of Length is 2 for BLOB or CLOB data. For DBCLOB data, the maximum value is 1 because a DBCLOB string is in double-byte characters.

This box is available only if you specified BLOB, CLOB, or DBCLOB in the Data type box and only for a new column.

Precision

This field displays the precision currently specified for the column of data type DECIMAL. You cannot alter this field for an existing column. To change the precision for a column that you're adding, type a new value in this field. The precision is the total number of digits in a DECIMAL number, which can range from 1 to 31. If you do not specify the precision, a default value of 5 is used.

This field is available only if you specified DECIMAL in the Data type box and only for a new column.

Scale

This field displays the scale currently specified for the column of data type DECIMAL. You cannot alter this field for an existing column. To change the scale for a column that you're adding, type a new value in this field. The scale is the number of digits to the right of the decimal point, which can range from 0 to the precision of the number. If you do not specify the scale, a default value of 0 is used.

This field is available only if you specified DECIMAL in the Data type box and only for a new column.

LOB option

These check boxes display the LOB options currently selected for the column of LOB data types. You cannot alter these check boxes for an existing column. To change the LOB options for a column that you're adding, select or clear the following check boxes:

Logged

Specifies that changes made to the column be written to the log. The data in such columns is then recoverable with database utilities, such as RESTORE DATABASE. The implication of not logging is that during a roll-forward operation, after a backup or load operation, the LOB values that would have had log records replayed during the roll-forward are replaced by zeroes. During crash recovery, all committed changes and changes rolled back will reflect the expected results.

Note:

LOBs greater than 1 GB cannot be logged, and LOBs greater than 10 MB should not be logged.

Compact

Specifies that the values in the LOB column should take up minimal disk space (free any extra disk pages in the last group used by the LOB value), rather than leave any left-over space at the end of the LOB storage area that might facilitate subsequent append operations.

Note:

Storing data in this way can cause a performance penalty in any append (length-increasing) operations on the column.

These check boxes are available only if you specified a data type of BLOB, CLOB, DBCLOB, or a distinct type with any of these as source types in the Data type box and only for a new column.

Bit data

This check box is displayed as currently specified for the column. You cannot alter this check box for an existing column. For a column that you're adding, you can select or clear this check box to change the setting. If you select this check box, the contents of the column will be treated as bit (binary) data. During data exchange with other systems, code page conversions are not performed. Comparisons are done in binary, irrespective of the database collating sequence.

This check box is available only if you specified a data type of CHARACTER, VARCHAR, or LONG VARCHAR in the Data type box and only for a new column.

Nullable

This check box is displayed as currently specified for the column. You cannot alter this check box for an existing column. For a column that you're adding, you can select or clear this check box to change the setting. If you select this check box, the column can contain null values. The default value for the column is either the null value or the value specified in the Default field.

Attention: If the column will be a primary key column, it cannot be nullable.

Default

This field displays the default value currently specified for the column. You cannot alter this field for an existing column. To change the default value for a column that you're adding, select the check box, then type the constant value, CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP, USER, or NULL in this field. The default value you type will be used exactly as entered. For example, if you put double quotes around the value you type in, the double quotes will be part of the default value. If you do not specify a specific default value, the default value depends on the data type of the column as shown in the following table.

Table 4. Default Values (when no value specified)

Data Type Default Value
Numeric 0
Fixed-length character string Blanks
Varying-length character string A string of length 0
Fixed-length graphic string Double-byte blanks
Varying-length graphic string A string of length 0
Date For existing rows, a date corresponding to January 1, 0001. For added rows, the current date.
Time For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time.
Timestamp For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds. For added rows, the current timestamp.
Binary string (BLOB) A string of length 0

Data Type	Default Value
Numeric	0
Fixed-length character string	Blanks
Varying-length character string	A string of length 0
Fixed-length graphic string	Double-byte blanks
Varying-length graphic string	A string of length 0
Date	For existing rows, a date corresponding to January 1, 0001. For added rows, the current date.
Time	For existing rows, a time corresponding to 0 hours, 0 minutes, and 0 seconds. For added rows, the current time.
Timestamp	For existing rows, a date corresponding to January 1, 0001, and a time corresponding to 0 hours, 0 minutes, 0 seconds and 0 microseconds. For added rows, the current timestamp.
Binary string (BLOB)	A string of length 0

You can specify the following types of default values:

Constant

Specifies the constant you specify as the default value for the column. The specified constant must:

Represent a value that can be assigned to the column in accordance with the rules of assignment
Not be a floating-point constant unless the column is defined with a floating-point data type
Not have nonzero digits beyond the scale of the column data type if the constant is a DECIMAL constant
Be expressed with no more than 254 characters including the quote characters, any introducer character such as the X for a hexadecimal constant, characters from the fully qualified function name, and parentheses when the constant is the argument of a cast-function
Be expressed in the following way:
- 'character string' for a character constant
- X'hexadecimal string' for a hexadecimal constant
- G'double-byte character string' or N'double-byte character string' for a graphic constant

Datetime special register

Specifies the value of the datetime special register (CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP) at the time of INSERT or UPDATE as the default for the column. The data type of the column must be the data type that corresponds to the special register specified.

USER

Specifies the value of the USER special register at the time of INSERT or UPDATE as the default for the column. If USER is specified, the data type of the column must be a character string with a length not less than the length attribute of USER.

NULL

Specifies NULL as the default for the column. If the Nullable check box is not selected, NULL can be specified as the default but any attempt to set the column to the default value will result in an error.

Cast function

This form of a default value can be used only with columns defined as a distinct type, BLOB, or DATETIME data type.

Comment

Optional: This field contains the comment currently specified for the column. To change the comment, type the new comment in this field. You can type up to 254 characters, including embedded blanks.

Generate column contents

This check box is selected if DB2 automatically generates the contents of the column.

Identity

This radio button is selected if the generated column is an identity column. DB2 generates a unique numeric value for each row in the table. The identity column conforms to the following rules:

The data type of the column must be SMALLINT, INTEGER, BIGINT, or DECIMAL(n,0).
The column cannot be NULL.

Initial value

This field displays the initial value that DB2 uses when it generates a numeric value for the first row of the table.

Increment

This field displays the incremental number that DB2 uses when it generates a number for each row.

Cache value

This check box is selected if DB2 caches identity column values to obtain better performance for applications. The scroll box displays the number of identity column values that are cached.

By default

This check box is selected if DB2 always generates a value for the identity column. When you load or import data into the table, a unique value will be generated for each row, regardless of whether the input data includes values for the identity column. (This check box is equivalent to the GENERATED ALWAYS parameter of the CREATE TABLE statement.)

Formula

This radio button is selected if DB2 uses a formula when it when it generates the values for the generated column. The formula cannot contain:

Subqueries
Dereference operations or DEREF functions where the reference value is other than the object identifier column for the current row
User-defined or built-in functions that are non-deterministic
User-defined functions using the EXTERNAL ACTION option
User-defined functions using the SCRATCHPAD option
User-defined functions using the READS SQL DATA option
Host variables or parameter markers
Special registers
Reference to a column defined later in the column list
Reference to another generated column.

The data type for the column is based on the result data type of the generation-expression. A CAST specification can be used to force a particular data type and to provide a scope (for a reference type only). If data-type is specified, values are assigned to the column under the assignment rules described in the SQL Reference. A generated column is implicitly considered nullable. The data type of a generated column must be one for which equality is defined. This excludes LONG VARCHAR columns, LONG VARGRAPHIC columns, LOB data types, DATALINKs, structured types, and distinct types based on any of these types.