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

Leave a Reply