When import is ran against table using NLI, the import completes with SQL0803N error message
An import that runs against a table that has the "not logged initially" enabled may complete with SQL0803N error logged. This happens when using the insert_update option with the import command.
The import completes with a SQL0803N error message as below. The import was run from a script as the import had to be done in the same unit of work as the activation of "not logged initially".
Example: Please note that the numbers have been removed and replaced them with #.
$ db2 +c -tvf import.sql
alter table test.nli activate not logged initially
DB20000I The SQL command completed successfully.
import from "/db2/location" of asc METHOD L (# #,# #) NULL INDICATORS (#,#) ALLOW NO ACCESS COMMITCOUNT # insert_update into test.nli
SQL3109N The utility is beginning to load data from file
SQL3306N An SQL error "-1476" occurred while inserting a row into the table.
SQL1476N The current transaction was rolled back because of error "-803". <-------SQL0803
SQL3110N The utility has completed processing. "1" rows were read from the
DB20000I The SQL command completed successfully. <-------Completes
After this the table is inaccessible and in drop pending state.
This is expected behavior due to the nature of the insert_update and not logged initially. When the import encounters an insert that violates the unique constraint on the table, the import attempts to roll back that transaction. Normally it would then do an update on that row. Which is the behavior expected by the insert_update clause in the import command.
In the case of a table with not logged initially, it can't do this because there are no logs to rollback through. Thus the import is unable to import any data, and causes the table to be put in drop pending state.
The table is in drop pending due to the failure to rollback, and is expected in tables with not logged initially activated.
Resolving the problem
The import with the insert_update clause will work as long as not logged initially is not enabled on the table. It is not possible to utilize the insert_update clause on an import while a table has "Not logged initially" activated.
More support for:
DB2 for Linux, UNIX and Windows
OTHER - Uncategorised
Software version: 9.1, 9.5, 9.7
Operating system(s): AIX, HP-UX, Linux, Solaris, Windows
Software edition: Advanced Enterprise Server, Enterprise Server, Express, Express-C, Personal, Workgroup Server
Reference #: 1509271
Modified date: 01 July 2013