Populating a generated column that has been added

awking00
awking00 used Ask the Experts™
on
I had a DB2 table, mytable, with a field named flr that was defined as char(9). A column was added with a alter table mytable add column flr_num generated always as cast(flr as decimal(5,0). After the column was added, I would have expected to see values in the flr_num column but all the values are null. How does that column get populated or is the problem that the column is decimal(5,0) while the source is char(9)?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Developer / Database Administrator
Commented:
I think you nailed it on the head.

You can't populate a CHAR field with a DECIMAL(5,0)

HTH,
DaveSlash
As long as the CHAR(9) values convert properly to numeric values, CHAR(9) can CAST as DEC(5,0). It's not clear if the same can be said about a GENERATED ALWAYS case.

Some sample rows from the table should be shown. Also, what version of DB2 is this?
awking00Information Technology Specialist

Author

Commented:
Okay, I think the flr_num column showed null because the conversion to decimal(5,0) would not be performed. So the issue now is to modify the column to the correct datatype. Incidentally, I am primarily an Oracle person trying to help someone out with this DB2 issue. We tried issuing the command ALTER TABLE mytable ALTER COLUMN flr_num GENERATED ALWAYS AS CAST(flr AS INTEGER) but I also see in going through the DB2 syntax that a SET keyword may be required. If so, I'm not sure where, either ALTER COLUMN SET flr_num GENERATED ALWAYS AS CAST(flr AS INTEGER) or ALTER COLUMN flr_num SET GENERATED ALWAYS AS CAST(flr AS INTEGER). Can any one of these work? Also, it seems that we have run into issues where DB2 tells us we have an illegal use of CAST. Are there any other means of converting a datatype?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Hi!

Before you alter the table and add the generated column make sure you issue
SET INTEGRITY FOR  OFF
then do the alter and afterwards and if the alter was successful issue
SET INTEGRITY FOR IMMEDIATE CHECKED FORCE GENERATED
to generate   the values for the new or altered column.

And issue a reorg on the table to make the table as optimal as possible.

Regards,
     Tomas Helgi
awking00Information Technology Specialist

Author

Commented:
This problem was actually posed back to the party I was trying to help and they seem to have found a solution. Thanks for you input.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial