Allowable conversions of data types
When you change the data type of an existing column, the old and new attributes must be compatible.
| From data type | To data type |
|---|---|
| Decimal | Numeric |
| Decimal | Bigint, Integer, Smallint |
| Decimal | Decfloat |
| Decimal | Float |
| Numeric | Decimal |
| Numeric | Bigint, Integer, Smallint |
| Numeric | Decfloat |
| Numeric | Float |
| Bigint, Integer, Smallint | Decimal |
| Bigint, Integer, Smallint | Numeric |
| Bigint, Integer, Smallint | Decfloat |
| Bigint, Integer, Smallint | Float |
| Float | Decimal |
| Float | Numeric |
| Float | Bigint, Integer, Smallint |
| Float | Decfloat |
| 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