DB2 Help

Use this box to specify the data type of the column that you're adding. To specify the data type, 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

Use this field to specify the length (byte count) of the columns 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.

LOB unit

Use this box to specify a multiplier for the length of columns of data type BLOB, CLOB, or DBCLOB. If you specify a LOB unit of:

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.

Precision

Use this field to specify the precision of a DECIMAL number. The precision is the total number of digits, 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.

Scale

Use this field to specify the scale of a DECIMAL number. 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.

LOB option

Use the following check boxes to define your LOB options:

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.

Bit data

Select this check box if you want the contents of the column to 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.

Nullable

Select this check box to allow the column to 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

Optional: Use this box to specify a default value for the column. 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.
Note: You cannot select Default if you want the column to be a generated column.

Table 3. 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: Type a comment to document the column that you're adding. You can type up to 254 characters, including embedded blanks.

Generate column contents

Select this check box if you want DB2 to automatically generate the contents of the column.

Notes:

Before adding a column for which the contents are generated, you must turn integrity checking off for the table.
If you want the contents of the column to be automatically generated, you cannot select Default.
You cannot create a generated column for a table in a partitioned database.

Identity

Select this radio button if you want the generated column to be an identity column. DB2 generates a unique numeric value for each row in the table. An identity column must conform 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

Select the initial value that you want DB2 to use when it generates a numeric value for the first row of the table.

Increment

Select the incremental number that you want DB2 to use when it generates a number for each row.

Cache value

Select this check box if you want DB2 to cache identity column values to obtain better performance for applications. Use the scroll box to specify the number of identity column values that you want to be cached.

By default

Select this check box if you want DB2 to always generate 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. (Selecting this check box is equivalent to specifying GENERATED ALWAYS for the identity column.)

Formula

Select this radio button if you want to supply a formula for DB2 to use 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.

Cancel

Click on this push button to close the Add Column window and return to the Columns page of the Alter Table notebook. Any changes that you made that you didn't add are not saved. You can add multiple columns before clicking on Cancel.

Apply

Click on this push button to add the new column that you just defined. You can add multiple columns before closing the Add Column window.

You can add up to 500 columns to your table. The sum of the byte counts of the columns must not be greater than 4005.