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.

Procedure

To alter the data type of a column:

  1. Issue an ALTER TABLE statement.
  2. 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.

When you retrieve table rows, the columns are retrieved in the format that is indicated by the catalog, but the data is not saved in that format. When you change or insert a row, the entire row is saved in the format that is indicated by the catalog. When you reorganize the table space (or perform a load replace), DB2 reloads the data in the table space according to the format of the current definitions in the catalog.

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.

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.
End general-use programming interface information.