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