473,326 Members | 2,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
Nov 12 '05 #1
2 17228
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

Nov 12 '05 #2
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 ... "

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: davvid | last post by:
hello, please I would like to know if it's possible to load data in hierarchical table structure with identity column used as pk (for all the tables) : I mean table root (Id_root(pk,identity...
2
by: erkki | last post by:
Hi, I am trying to move DB2 8.1 database from AIX to Windows. Following errors appears when I try to run db2move db import. <IMPORT.out> ***** DB2MOVE ***** Action: IMPORT
1
by: Terry | last post by:
Hi All, Im attempting to use db2move from 7.1.0 to 8.1.4. It fails to import tables defined using the columns with GENERATED ALWAYS. The error message generated follows: "SQL3550W The field...
1
by: smauldin | last post by:
Creating a table with an identity column works fine create table test(a integer, id integer generated always as identity ) When I attempt to add an identity column it fails. create table...
5
by: Veeru71 | last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS IDENTITY), is there any way to get the last generated value by DB2 for the identity column? I can't use identity_val_local() as...
1
by: Pydimarri Srinivas | last post by:
Hi All, I am trying to purge the data from a DB2 table having identity column defined usind DB2 utilitties. I am getting the following error "DSNU269I .............Field is invalid" "The...
0
by: PeeWee | last post by:
Hello everyone, sorry for this stupid question (i'm a bit of a novice as fas as QMF is concerned): How can I insert values into a table (using the QMF Table Editor!), where one Column (DECIMAL...
5
by: E.Bartosiewicz | last post by:
I have several files with data I would like to import into DB2, but I have timestamps set in a format, which DB2 can't catch - DD-MM-YYYY HH:MM:SS DB2 wants to get the year first. Is there a way...
5
by: dos360 | last post by:
Hello, I have two tables, one is a list of activities, the other a list of participants. I want to insert one record in the activities table and then using its identity column as foreign key, I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.