How can the data type be changed from BigInt to Integer

[Rahul Babbar]

Hi,

What are the possible ways to change the data type of a column from
BigInt to Integer?

the normal command
Alter table <tabnamealter column <colnameset data type Integer
gives the error
SQL0190N ALTER TABLE <tablespecified attributes for column "VALUE"
that are not compatible with the existing column. SQLSTATE=42837

Why is it failing, when i have only 2 records in the table which are
less than the value that Integer will take.

What could be other possible ways of changing the data type?
Dropping and recreating the column could be an option but the data
might be lost in such a case..

Also, what considerations need to be taken (while altering the data
type or while dropping it and recreating it) if there are indexes
existing on that column.

Thanks a lot.

Rahul Babbar

[Serge Rielau]

Rahul Babbar wrote:

What are the possible ways to change the data type of a column from
BigInt to Integer?

Check the alter table wizard in DeveloperWorkbench. It supports downcasting.
The DB2 for LUW server side ALTER TABLE statement today supports only
"safe" casts. That is casts from a smaller to a bigger type.
Extending this is a known requirement which is being looked at for a
future release.

Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab