When import is ran against table using NLI, the import completes with SQL0803N error message

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

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
"/db2/location".

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
SQLSTATE=40506

SQL3110N  The utility has completed processing.  "1" rows were read from the
input file.


commit
DB20000I  The SQL command completed successfully. <-------Completes

****************************************************************

After this the table is inaccessible and in drop pending state.

Cause

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.


Environment

All Environments

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.

Related information

Alter Table Statement

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.1, 9.5, 9.7

Operating system(s):

AIX, HP-UX, Linux, Linux iSeries, Linux pSeries, Linux zSeries, Solaris, Windows

Software edition:

Advanced Enterprise Server, Enterprise Server, Express, Express-C, Personal, Workgroup Server

Reference #:

1509271

Modified date:

2013-07-01

Translate my page

Machine Translation

Content navigation