Altering the data type of a column

You can use the ALTER TABLE statement to change the data types of columns in existing tables in several ways.

About this task

In general, Db2 can alter a data type if the data can be converted from the old type to the new type without truncation or without losing arithmetic precision.

Restriction: The column that you alter cannot be a part of a referential constraint, have a field procedure, be defined as an identity column, or be defined as a column of a materialized query table.

When you alter the data type of a column in a table, Db2 creates a new version for the table space that contains the data rows of the table.

Start of change You can specify whether changes to columns are applied immediately or as pending definition changes by setting the value of the DDL_MATERIALIZATION subsystem parameter. For more information about pending changes for columns, see Pending column alterations. End of change

Procedure

To alter the data type of a column:

Issue an ALTER TABLE statement.
Specify the data type change that you would like to make. Potential changes include:
- Altering the length of fixed-length or varying-length character data types, and the length of fixed-length or varying-length graphic data types.
- Switching between fixed-length and varying-length types for character and graphic data.
- Switching between compatible numeric data types.

Results

When you change the data type of a column by using the ALTER TABLE statement, the new definition of the column is stored in the catalog. Start of change

When the changes are materialized depends on the value of the DDL_MATERIALIZATION subsystem parameter. End of change

Recommendation: If you change both the length and the type of a column from fixed-length to varying-length by using one or more ALTER statements, issue the ALTER statements within the same unit of work. Reorganize immediately so that the format is consistent for all of the data rows in the table.

Altering the data type, length, precision, or scale of a column

When you change the data type, length, precision, or scale of a column, consider the following information:

Altering character data

When columns are converted from CHAR to VARCHAR, normal assignment rules apply, which means that trailing blanks are kept instead of being stripped out. If you want varying length character strings without trailing blanks, use the STRIP function for data in the column after changing the data type to VARCHAR.

When a CHAR FOR BIT DATA column is converted to a BINARY data type, the following applies:

The existing space characters in the table will not be changed to hexadecimal zeros (X'00')
If the new length attribute is greater than current length attribute of the column, the values in the table are padded with hexadecimal zeros (X'00')

When a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA column is converted to a BINARY or VARBINARY data type, the existing default value will be cast as a binary string. The resulting binary string will be at least twice the original size. The alter will fail if the resulting binary string length exceeds 1536 UTF-8 bytes.

Altering fixed-length to varying-length or increasing varying-length column

When you change a column from a fixed to varying length or change the length of a varying-length column, process the ALTER TABLE statements in the same unit of work or do a reorganization between the ALTER TABLE statements to avoid anomalies with the lengths and padding of individual values

Altering DECIMAL(19,0) to BIGINT.

In releases of Db2 prior to DB2 9, use of the DECIMAL(19,0) data type for applications that work with BIGINT data was encouraged. For performance reasons, columns it is best to alter the DECIMAL(19,0) columns to BIGINT. Note that altering from DECIMAL(19,0) to BIGINT is provided only for DECIMAL(19,0) columns that are used for applications that work with BIGINT (thus, the data in those columns is within the range of the BIGINT).

When altering from DECIMAL(19,0) to BIGINT you should ensure that all values in the DECIMAL(19,0) column are within the range of BIGINT before the alter. The following query or a similar query can be run to determine which rows (if any) contain values that are outside of the range of BIGINT:

SELECT * FROM table_name 
    WHERE dec19_0_column > 9223372036854775807 
    OR dec19_0_column < -9223372036854775808;

Altering a column in a partitioning key

When a partitioning key column with a numeric data type is altered to a larger numeric data type, and the limit key value for the original numeric data type of the column is X'FF', the limit key value for the new numeric data type of the column is left-padded with X'FF'. For example, if a column is converted from SMALLINT to INTEGER, and a limit key value for the SMALLINT column is 32767 (which is 2 bytes of X'FF'), the limit key for the INTEGER column is 2147483647 (which is 4 bytes of X'FF').

When a partitioning key column with a character data type is altered to a longer character data type, and the limit key value for the original character data type of the column (excluding the first NULL byte if the column is nullable) is neither all X'FF' nor all X'00', the limit key value for the new character data type of the column is right-padded with blank(s) of the encoding scheme of the table. For example, if a column is converted from CHAR(1) to VARCHAR(2), and a limit key value for the CHAR(1) column is 'A' (which is X'C1'), the limit key for the VARCHAR(2) column is 'A ' (which is X'C140' when the encoding scheme of the table is EBCDIC, or is X'C120' when the encoding scheme of the table is UNICODE or ASCII).

Statistics for altered columns

New COLUMN statistics should be collected for all altered columns. Even though the COLCARDF value is valid, the HIGH2KEY and LOW2KEY values are invalid, and any SYSCOLSTATS catalog entries for the column are removed. Any frequencies or histogram statistics which include this column should also be collected again.

Example

Begin general-use programming interface information.

Assume that a table contains basic account information for a small bank. The initial account table was created many years ago in the following manner:

CREATE TABLE ACCOUNTS (
   ACCTID       DECIMAL(4,0)   NOT NULL,
   NAME         CHAR(20)       NOT NULL,
   ADDRESS      CHAR(30)       NOT NULL,
   BALANCE      DECIMAL(10,2)  NOT NULL)
IN dbname.tsname;

The columns, as currently defined, have the following problems:

The ACCTID column allows for only 9999 customers.
The NAME and ADDRESS columns were defined as fixed-length columns, which means that some of the longer values are truncated and some of the shorter values are padded with blanks.
The BALANCE column allows for amounts up to 99,999,999.99, but inflation rates demand that this column hold larger numbers.

By altering the column data types in the following ways, you can make the columns more appropriate for the data that they contain. The INSERT statement that follows shows the kinds of values that you can now store in the ACCOUNTS table.

ALTER TABLE ACCOUNTS ALTER COLUMN NAME    SET DATA TYPE VARCHAR(40);
ALTER TABLE ACCOUNTS ALTER COLUMN ADDRESS SET DATA TYPE VARCHAR(60);
ALTER TABLE ACCOUNTS ALTER COLUMN BALANCE SET DATA TYPE DECIMAL(15,2);
ALTER TABLE ACCOUNTS ALTER COLUMN ACCTID  SET DATA TYPE INTEGER;
COMMIT;

INSERT INTO ACCOUNTS (ACCTID, NAME, ADDRESS, BALANCE)
  VALUES (123456, 'LAGOMARSINO, MAGDALENA',
          '1275 WINTERGREEN ST, SAN FRANCISCO, CA, 95060', 0);
COMMIT;

The NAME and ADDRESS columns can now handle longer values without truncation, and the shorter values are no longer padded. The BALANCE column is extended to allow for larger dollar amounts. Db2 saves these new formats in the catalog and stores the inserted row in the new formats.

End general-use programming interface information.