CODEWITHSUNDEEP
db2
gpezzini
gpezzini

Reputation: 93

db2 alter column statement from char to varchar

Does it possible using some db2 trick to alter a column from type char to type varchar trimming the white space on the right?

I know that is possible to alter the column type from char to varchar (to extends its size) but db2 leaves the left white space on the right, so I need to issue an update statement after the alter table statement to trim the white space on the right.

But we have table also with 400 million of records and the update statement has an important cost in terms of time.

I ask this question also after I read the db2 documentation of alter table statement: seems that does exists nothing that allows me to change the type and trim right the values at the same time.

Upvotes: 0

Views: 2182

Answers (3)

The_Data_Doctor
The_Data_Doctor

Reputation: 11

Doing an export+load would have a significant downtime for the table.

If you use the alter to varchar and update approach you could minimize the log and lock amount by performing the update within a compound sql or stored procedure which commits after X rows updated.

This would still run quite long but locks won't be held that long.

Upvotes: 0

WarrenT
WarrenT

Reputation: 4532

As you said, you can do the UPDATE after the ALTER. You have the spaces now. In a sense, you are simply making a small improvement to the data. It may take a while, but how much does it matter if that process chugs away for a while in the background?

Upvotes: 0

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

As far as I know it is not possible to do what you ask for. Have you considered export and load replace?

Upvotes: 0

Related Questions