db2move, problem importing data into table with GENERATED ALWAYS identity column

[valerio]

Hi all,

I've some problem to import data to DB2 using the db2move and db2look
tools.
Follow the problem :

I have exported data from db2 v. 7 database on windows server, using
the db2look and db2move tools.
Then I moved the *.SQL and *.IXF files to the linux server, where is
installed DB2 v.8 fp 6.
There I created the database using the schema file "db2look.sql".
After that I ran the "db2move <DBNAME> load" to insert data to the new
database, but for two of the many tables loaded I got the following
error :
SQL3550W The field value in row "F0-1" and column "1" is not NULL,
but the
target column has been defined as GENERATED ALWAYS.

SQL3185W The previous error occurred while processing data from row
"F0-1" of
the input file.
One of the two tables that give the error above, was created using the
SQL instruction below :

------------------------------------------------
-- DDL Statements for table "ISSVADM "."SMTIVCNT"
------------------------------------------------

CREATE TABLE "ISSVADM "."SMTIVCNT" (
"ID_MODULO" INTEGER NOT NULL GENERATED ALWAYS AS
IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) )
IN "ISSVDATA" ;
I looked for help on internet but I haven't found a solution.
I tried also the following instruction :

db2 "load from tab57.ixf of del modified by identityoverride insert
into ISSVADM.SMTIVCNT"

but I got the error :

SQL3120W The field value in row "F0-1" and column "1" cannot be
converted to
an INTEGER value, but the target column is not nullable. The row was
not
loaded.

SQL3185W The previous error occurred while processing data from row
"F0-1" of
the input file.
I'd like to reload the exported tables into the new database keeping
the same data, without regenerate the Identity column.

Can somebody help ?

Thanks
Valerio

[Dmitry Tolpeko]

Hi Valerio,

First, you have to use LOAD command with "identityoverride" option to save
identity values from the source database.
Have a look at the data file (ASCII file) and check what actual values
exported. It seems that something is wrong with data. Are there any
successfully exported rows for this table?

--
Best regards, Dmitry Tolpeko
SQLWays - Migrates Data, Schema, Procedures from all major databases to IBM
DB2
http://www.ispirer.com
----

"valerio" <va**************@e4b.it> wrote in message
news:2f*************************@posting.google.co m...

Hi all,

I've some problem to import data to DB2 using the db2move and db2look
tools.
Follow the problem :

I have exported data from db2 v. 7 database on windows server, using
the db2look and db2move tools.
Then I moved the *.SQL and *.IXF files to the linux server, where is
installed DB2 v.8 fp 6.
There I created the database using the schema file "db2look.sql".
After that I ran the "db2move <DBNAME> load" to insert data to the new
database, but for two of the many tables loaded I got the following
error :
SQL3550W The field value in row "F0-1" and column "1" is not NULL,
but the
target column has been defined as GENERATED ALWAYS.

SQL3185W The previous error occurred while processing data from row
"F0-1" of
the input file.
One of the two tables that give the error above, was created using the
SQL instruction below :

------------------------------------------------
-- DDL Statements for table "ISSVADM "."SMTIVCNT"
------------------------------------------------

CREATE TABLE "ISSVADM "."SMTIVCNT" (
"ID_MODULO" INTEGER NOT NULL GENERATED ALWAYS AS
IDENTITY ( START WITH +1 , INCREMENT BY +1 , NO CACHE ) )
IN "ISSVDATA" ;
I looked for help on internet but I haven't found a solution.
I tried also the following instruction :

db2 "load from tab57.ixf of del modified by identityoverride insert
into ISSVADM.SMTIVCNT"

but I got the error :

SQL3120W The field value in row "F0-1" and column "1" cannot be
converted to
an INTEGER value, but the target column is not nullable. The row was
not
loaded.

SQL3185W The previous error occurred while processing data from row
"F0-1" of
the input file.
I'd like to reload the exported tables into the new database keeping
the same data, without regenerate the Identity column.

Can somebody help ?

Thanks
Valerio

[Ian]

valerio wrote:

SQL3550W The field value in row "F0-1" and column "1" is not NULL,
but the
target column has been defined as GENERATED ALWAYS.

SQL3185W The previous error occurred while processing data from row
"F0-1" of the input file.
db2move is limited in its ability to load some data, and generated
always identity columns are one issue.
I tried also the following instruction :

db2 "load from tab57.ixf of del modified by identityoverride insert
into ISSVADM.SMTIVCNT"

This is your problem. The IXF file is IXF format, not DEL.

"load from tab57.ixf OF IXF modified by identityoverride ... "