Unload and reload IDENTITY COLUMN defined as GENERATED ALWAYS but preserving the existing values stored

Products

• Fast Unload for DB2 for z/OS
• Fast Load for DB2 for z/OS
• Database Management for DB2 for z/OS - Utilities Suite

Issue/Introduction

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.

       

Environment

Release : 20.0
Component : Fast Unload for DB2 for z/OS, Fast Load for DB2 for z/OS

Resolution

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

Additional Information

Fast Unload : LOAD-CONTROL—Generate Load Control Statements

Fast Load: IGNOREGA—Override the GENERATED ALWAYS Setting