If the identity column is defined as GENERATED ALWAYS, an identity value
is generated for a table row whenever the corresponding row field in the input
file does not contain a value for the identity column. When a corresponding
row field in the input file already contains a value for the identity column,
the row cannot be inserted into the table and the import operation will fail.
To prevent such scenarios, the following examples show how to specify parameters
in the SYSCS_UTIL.SYSCS_IMPORT_DATA and SYSCS_UTIL.SYSCS_IMPORT_DATA_LOBS_FROM_EXTFILE
procedures to ignore data for the identity column from the file, or omit the
column name from the insert column list.
If the REPLACE parameter is used during import, resets its internal counter
of the last identity value for a column to the initial value defined for the
identity column.
The following table definition contains an identity column, c2 and
is used in the examples below:
CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY,
c3 REAL, c4 CHAR(1))
- Suppose that you want to import data into tab1 from a
file myfile.del that does not have identity column information.
The myfile.del file contains three fields with the following
data: Robert,45.2,J
Mike,76.9,K
Leo,23.4,I To import the data, you must explicitly list the
column names in the tab1 table except for the identity column c2 when
you call the procedure. For example: CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4',
null, 'myfile.del',null, null, null, 0)
- Suppose that you want import data into tab1 from a file empfile.del that
also has identity column information. The file contains three fields with
the following data: Robert,1,45.2,J
Mike,2,23.4,I
Leo,3,23.4,I To import the data, you must explicitly specify
an insert column list without the identity column c2 and
specify the column indexes without identity column data when you call the
procedure. For example: CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4',
'1,3,4', 'empfile.del',null, null, null, 0)