Systems Engineering and RDBMS

Ignoring/Preserving Identity Column value during Import in DB2 LUW

Posted by decipherinfosys on June 15, 2007

In one of our previous blog post, we had covered how to generate the export and import commands in DB2 LUW via SQL.  In this post, we will cover how one can preserve or ignore the identity value in the text file during the import process when the data is being loaded into a table that has identity property assigned to a column.  We can either take the column value in the text file as is or we can ignore the value and generate a new corresponding identity value. We will also show, what will be the behavior of the import command when identity column is defined as ‘GENERATED BY DEFAULT’ versus ‘GENERATED ALWAYS’.

Let us create a table first to import the data from the text file. Connect to the database either from the command editor or command window and execute the following statement:

CREATE TABLE TEST
(
TEST_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
CACHE 20
NO ORDER ) ,
TEST_NAME VARCHAR(40) NOT NULL
);

Text file test_load.txt contains following data and it is in comma delimited file in DEL format. Save it on your local drive.

11,”Oracle”
12,”SQLServer”
13,”DB2″
14,”Informix”
15,”Sybase”
16,”Access”

Use the following command to import data into the TEST table as is. Make sure that a database connection already exists. We ran this command from command window and from the same folder where we have created test_load.txt file.

db2 import from test_load.txt  of del insert into test

Upon completion, it will display summary information of how many records read, skipped, inserted or rejected.

To ignore the column values in the text file and re-generate new identity value for all the records, we need to use IDENTITYIGNORE clause. Following is the syntax.

db2 import from test_load.txt of del MODIFIED BY IDENTITYIGNORE replace into test

Selecting records from the table will show that import command has ignored the column value in the text file and generated new identity values for the column.

Now,  assume that we have the text file with the data as shown below (i.e. without the values for the column that has the identity property tied to it):

“Oracle”
“SQLServer”
“DB2”
“Informix”
“Sybase”
“Access”

To load data into the TEST table, we can use following command:

db2 import from test_load.txt of del MODIFIED BY IDENTITYMISSING replace into test

Here we are using the IDENTITYMISSING clause, so that the import command knows that it has to generate identity values. If we issue this command without the IDENTITYMISSING clause, then we will encounter an error.

One more scenario to test is when we have value for some records and we don’t have value for some other set of records (though this is less likely to occur in real life). Consider the following text file. Here for “DB2” and “Informix” record, we don’t have corresponding identity value. We need to load data from this text file.

11,”Oracle”
12,”SQLServer”
,”DB2″
,”Informix”
13,”Sybase”
14,”Access”

Use following command to load the data.

db2 import from test_load.txt  of del replace into test

This command will load first two rows and last two rows as is since it already has identity value defined for the records. For middle two rows, it will generate new identity values. All records will be loaded successfully. This may not happen frequently in actual production scenarios, but we are showing it as an example to demonstrate that that is also supported in DB2 LUW and there are ways to make things work regardless of the source data in the text files.

Now, let us drop and re-create the TEST table. This time TEST_ID column will be created as ‘GENERATED ALWAYS’ identity column instead of ‘GENERATED BY DEFAULT’ identity column.

CREATE TABLE TEST
(
TEST_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +2147483647
NO CYCLE
CACHE 20
NO ORDER ) ,
TEST_NAME VARCHAR(40) NOT NULL
);

Let us re-test the last scenario now. Data in the text file remains same.

db2 import from test_load.txt  of del replace into test

The above command will only import records where identity value is missing (DB2, Informix). All other records are rejected because explicit values are defined for them and identity column on the table is defined as ‘GENERATED ALWAYS’. In order to import all records successfully, we need to use IDENTITYIGNORE clause.

db2 import from test_load.txt  of del MODIFIED BY IDENTITYIGNORE replace into test

Export/Import command are being invoked from db2 command window. So whenever there is a need to invoking export/import from an application we can put it in the batch file and can invoke the batch file using ‘db2cmd’ command.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: