How to preserve existing values for IDENTITY columns defined as GENERATED ALWAYS executing
Fast Unload for Db2 for z/OS (PFU) and Fast Load for Db2 for z/OS (PFL).
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 reload to the table
after the table is unloaded.
Release : 20.0
Component : Fast Unload for DB2 for z/OS, Fast Load for DB2 for z/OS
Fast Load has the following syntax option:
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 Fast Unload 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.xxxxxxxx;
Fast Load 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