CA Fast Unload for DB2 for z/OS Unload and Reload IDENTITY COLUMN defined as GENERATED ALWAYS but preserving the existing values stored.

book

Article ID: 143847

calendar_today

Updated On:

Products

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

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

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-mainframe-software/database-management/ca-fast-unload-for-db2-for-z-os/20-0/ignorega-override-the-generated-always-setting.html

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 .

http://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-mainframe-software/database-management/ca-fast-load-for-db2-for-z-os/20-0/reference/product-syntax/keyword-descriptions/ignorega-override-the-generated-always-setting.html

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.

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-mainframe-software/database-management/ca-fast-unload-for-db2-for-z-os/20-0/reference/product-syntax/keyword-descriptions/load-control-generate-load-control-statements.html

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

IGNOREGA YES

 

LOAD-CONTROL—Generate Load Control Statements