DSN_IDENTITY is generated for an IDENTITY column in the IBM LOAD statement instead of the column name
Article ID: 371942
Updated On:
Products
Issue/Introduction
When you have a Db2 Table with an IDENTITY column defined as follows:
CREATE TABLE PREFIX.TABLE_NAME
(COL_1 DECIMAL(10, 0)
NOT NULL
,COL_2 SMALLINT
NOT NULL
GENERATED ALWAYS AS IDENTITY ( START WITH 1
, INCREMENT BY 1
, MINVALUE 1
, MAXVALUE 32767
, CYCLE
, CACHE 20
, NOORDER )
...
The Fast Unload utility generates a DSN_IDENTITY column name for the IDENTITY column in the IBM LOAD statement instead of the actual column name COL_2.
FASTUNLOAD
...
LOAD-CONTROL DB2LOAD
And the following is generated in the IBM LOAD statement:
LOAD DATA INDDN SYSREC01
...
INTO TABLE PREFIX.TABLE_NAME
IGNOREFIELDS YES
(
COL_1 POSITION( 1: 6 )
DECIMAL PACKED
,
DSN_IDENTITY POSITION( 7: 8 )
SMALLINT
...
Is there any problem for this mismatch to cause data lost when the IBM LOAD utility is executed?.
The same data that was unloaded by Fast Unload utility will not be exactly reloaded by the IBM LOAD utility.
Resolution
The FASTUNLOAD parameter LOAD-CONTROL DB2LOAD generates a name DSN_IDENTITY for the field that do not match the column together with this syntax:
IGNOREFIELDS YES
See the IBM LOAD utility documentation:
IGNOREFIELDS
Indicates whether LOAD is to skip fields in the input data set that do not correspond to
columns in the target table. Examples of fields that do not correspond to table columns are
the DSN_NULL_IND_nnnnn, DSN_ROWID, DSN_IDENTITY, and DSN_RCTIMESTAMP fields
that are generated by the REORG utility.
NO
Specifies that the LOAD process is not to skip any fields.
YES
Specifies that LOAD is to skip fields in the input data set that do not correspond to columns in the
target table.
Specifying YES can be useful if each input record contains a variable-length field, followed by
some variable-length data that you do not want to load and then some data that you want to
load. Because of the variable-length field, you cannot use the POSITION keyword to skip over the
variable-length data that you do not want to load. By specifying IGNOREFIELDS, you can give a
field specification for the variable-length data that you do not want to load; and by giving it a name
that is not one of the table column names, LOAD skips the field without loading it.
Use this option with care, because it also causes fields to be skipped if you intend to load a
column but have misspelled the name.
The column is an IDENTITY column so the data will be generated as per this clause:
GENERATED ALWAYS AS IDENTITY ( START WITH 1
, INCREMENT BY 1
, MINVALUE 1
, MAXVALUE 32767
, CYCLE
, CACHE 20
, NOORDER )
There should be no problem at all doing this, so the FASTUNLOAD statement and the IBM LOAD utility worked correctly.
The other option you have if you want to reload the exact data unloaded by the Fast Unload utility is to work with a FASTUNLOAD statement using the IGNOREGA parameter, on this way a FASTLOAD statement will be generated with the IGNOREGA YES parameter as well.
FASTUNLOAD
...
LOAD-CONTROL FASTLOAD,IGNOREGA
Fast Load utility can load data from the unloaded data set with the IGNOREGA YES parameter and the field name will match the column name. The load syntax would be fine using this FASTLOAD statement (Note the IBM LOAD utility does not support this feature).
FASTLOAD INDDN SYSULD
...
IGNOREGA YES
INTO TABLE TABLE_NAME
(
COL_1 POSITION( 1: 6 )
DECIMAL PACKED
,
COL_2 POSITION( 7: 8 )
SMALLINT
...
Feedback
