CA Fast Unload for DB2 for z/OS : Unload and Reload IDENTITY COLUMN defined as GENERATED ALWAYS but preserving the existing values stored.
Article ID: 143847
Updated On:
Products
Issue/Introduction
Normally these GENERATED ALWAYS values would be regenerated upon load to the table. It may be required that existing GENERATED ALWAYS
values stored in a table column be preserved upon re-load to the table after the table is unloaded.
Environment
Release : 20.0
Component : CA Fast Unload for DB2 for z/OS, CA Fast Load for DB2 for Z/OS
Resolution
The FASTUNLOAD Manual has this keyword: IGNOREGA
IGNOREGA—Override the GENERATED ALWAYS Setting
The IGNOREGA keyword specifies whether columns defined as GENERATED ALWAYS are treated as GENERATED BY DEFAULT. This parameter applies to row ID and identity columns.
This keyword has the following format:
IGNOREGA [NO|YES]
NO
Enforces the restraint if a column is defined as GENERATED ALWAYS. This setting is the default.
YES
Overrides the restraint temporarily when a column is defined as GENERATED ALWAYS. The data is loaded from the input data set rather than being generated. Default values are still generated when necessary.
In addition, you would need to use IGNOREGA YES in the CA FAST LOAD parms when loading the data previously unloaded using the same parm .
Take note of this note:
IGNOREGA and NO-IGNOREGA are intended for use with FASTLOAD. If you specify IGNOREGA with DB2LOAD and you want IBM LOAD to preserve the column values, modify the ROWID and IDENTITY column definitions. For the proper procedure, see your IBM documentation.
Example:
Fast Unload Parms:
FASTUNLOAD
DISCARDS 100
DISPLAY-STATUS 10000
EXCP YES
INPUT-FORMAT TABLE
IO-BUFFERS 50
LOAD-CONTROL FASTLOAD,EST-ROWS,ENFORCE-NO,DISCARDS=1,IGNOREFIELDS,SYSTE
MPERIOD,PERIODOVERRIDE,TRANSIDOVERRIDE,BUSINESSPERIOD,IGNOREGA
OUTPUT-FORMAT LOAD
PART-INDEPENDENCE NO
SHRLEVEL CHANGE
SORTFLAG ALL
SORTSIZE 4M
SORTNUM 8
ESTIMATED-ROWS 2
SQL-ACCESS ONLY
SELECT * FROM authid.TXAUDTTB;
FastLoad parms:
FASTLOAD INDDN SYSULD
INPUT-FORMAT UNLOAD
OUTPUT-CONTROL BUILD
RESUME NO REPLACE ENFORCE NO
ESTIMATED-INPUT 2
EBCDIC CCSID(1027,5035,4396)
DISCARDS 1
IGNOREGA YES
INTO TABLE authid.TXAUDTTB
PERIODOVERRIDE TRANSIDOVERRIDE
OBID 0
This unloads the existing GENERATED ALWAYS AS IDENTITY column values and on the load it loads them back as they were. Also if you manually alter the identity value in the input file and use a RESUME YES, the manually created identity values are loaded as they were on the dataset.
Additional Information
Feedback
