You can use the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure to import data into
a table that contains an identity column. 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 arguments in the SYSCS_UTIL.SYSCS_IMPORT_DATA
procedure to ignore data for the identity column from the file, and/or omit
the column name from the insert column list.
If the REPLACE option 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.
Consider the following table that contains an identity column, c2:
CREATE TABLE tab1 (c1 CHAR(30), c2 INT GENERATED ALWAYS AS IDENTITY, c3 REAL,
c4 CHAR(1))
- Suppose you want to import data into tab1 from a file myfile.del that
does not have identity column information and myfile.del contains
three fields with the following data: Robert,45.2,J
Mike,76.9,K
Leo,23.4,I To import data from myfile.del into
the tab1 table, explicitly list the column names for tab1 without
the identity column c2 and execute the SYSCS_UTIL.SYSCS_IMPORT_DATA
procedure as follows: CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4' , null,
'myfile.del',null, null,null,0)
- Suppose you want import data into tab1 from a file empfile.del that
also has identity column information and 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 data from empfile.del into
the tab1 table, explicitly specify an insert column list without the
identity column c2 and specify the column indexes without identity
column data and execute the SYSCS_UTIL.SYSCS_IMPORT_DATA procedure as follows:
CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (NULL, 'TAB1', 'C1,C3,C4' , '1,3,4',
'empfile.del',null, null,null,0)