Populating a generated column that has been added

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)?
LVL 33
awking00Information Technology SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
I think you nailed it on the head.

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

HTH,
DaveSlash

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Member_2_276102Commented:
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 SpecialistAuthor 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 EngineerCommented:
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 SpecialistAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.