Allowable conversions of data types

 

When you change the data type of an existing column, the old and new attributes must be compatible.

Table 1. Allowable conversions
From data type To data type
Decimal Numeric
Decimal Bigint, Integer, Smallint
Decimal Float
Numeric Decimal
Numeric Bigint, Integer, Smallint
Numeric Float
Bigint, Integer, Smallint Decimal
Bigint, Integer, Smallint Numeric
Bigint, Integer, Smallint Float
Float Numeric
Float Bigint, Integer, Smallint
Character DBCS-open
Character UCS-2 or UTF-16 graphic
DBCS-open Character
DBCS-open UCS-2 or UTF-16 graphic
DBCS-either Character
DBCS-either DBCS-open
DBCS-either UCS-2 or UTF-16 graphic
DBCS-only DBCS-open
DBCS-only DBCS graphic
DBCS-only UCS-2 or UTF-16 graphic
DBCS graphic UCS-2 or UTF-16 graphic
UCS-2 or UTF-16 graphic Character
UCS-2 or UTF-16 graphic DBCS-open
UCS-2 or UTF-16 graphic DBCS graphic
distinct type source type
source type distinct type

When you change an existing column, only the attributes that you specify are changed. All other attributes remain unchanged. For example, you have a table with the following table definition:

     CREATE TABLE EX1 (COL1 CHAR(10) DEFAULT 'COL1',
                       COL2 VARCHAR(20) ALLOCATE(10) CCSID 937,
                       COL3 VARGRAPHIC(20) ALLOCATE(10)
                            NOT NULL WITH DEFAULT)

After you run the following ALTER TABLE statement, COL2 still has an allocated length of 10 and CCSID 937, and COL3 still has an allocated length of 10.

     ALTER TABLE EX1 ALTER COLUMN COL2 SET DATA TYPE VARCHAR(30)
                     ALTER COLUMN COL3 DROP NOT NULL

 

Parent topic:

Changing a table definition

 

Related reference


Changing a column