Force values into DB2 GENERATED ALWAYS fields

If doing a conversion of data where you need to force a value into a field that is GENERATED ALWAYS after the conversion, you can still load values into the generating and generated fields.

In your conversion load process, use the “generatedoverride” modifier (ex: LOAD FROM C012 OF CURSOR MODIFIED BY generatedoverride MESSAGES $l_sLog). This will allow you to force values into GENERATED ALWAYS fields.

Once your load completes, issue one of the following statements to take your table out of the “set integrity pending” state:

SET INTEGRITY FOR table-name GENERATED COLUMN IMMEDIATE UNCHECKED; 
-- will not verify the values you supplied in the load

SET INTEGRITY FOR table-name IMMEDIATE CHECKED; 
-- will verify the values you supplied in the load

Ref: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0004592.htm

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply