ids 10 datetime migrate error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

roger@star2000.com.tw - 22 Oct 2006 10:36 GMT

Hi ,
I tried to migrate database from ids73 to ids10,
I can not move data from ids73 to ids10 by INSERT ... SELECT statement.
the steps that I done list below :
(source host)
aix 4.3
ids : 7.31.FD2 server name: on14tcp
table name: Create table x(log_date datetime year to second not null)
insert into x values("2006-10-30 12:10:00");

(target host)
aix 5.3
ids : 10.00.FC3R1 server name: on17tcp
table name: Create table x(log_date datetime year to second not null)

(migrate data )
issue commands at aix 5.3 , ids10.FC3R1, server on17tcp

insert into x select * from test2@on14tcp:x;

(error message)
391: Cannot insert a null into column (x.log_date)

If I migrate by unload &load, or dbexport&dbimport , It will be OK!
But due to save disk space and move the data fast and other reasons,
I have to migrate by INSERT .. SELECT statement.
Somebody can help out of this ?

Reply to this Message

scottishpoet - 22 Oct 2006 23:16 GMT

remove the NOT NULL constraint from the table definition

or add a where claose in the select to say only select the rows where
the datetime column is not null

> Hi ,
> I tried to migrate database from ids73 to ids10,
[quoted text clipped - 23 lines]
> I have to migrate by INSERT .. SELECT statement.
> Somebody can help out of this ?

Reply to this Message

Jonathan Leffler - 23 Oct 2006 04:32 GMT

>> I tried to migrate database from ids73 to ids10,
>> I can not move data from ids73 to ids10 by INSERT ... SELECT statement.
[quoted text clipped - 22 lines]
>> I have to migrate by INSERT .. SELECT statement.
>> Somebody can help out of this ?

> remove the NOT NULL constraint from the table definition
>
> or add a where clause in the select to say only select the rows where
> the datetime column is not null

But the source table has 'NOT NULL' and the data in it is non-null.

First question: what do you seen when you run the SELECT without the
INSERT - on the on17tcp server, querying the on14tcp server? Do you get
the correct data then?

If you get the correct data on a plain SELECT and not on an INSERT INTO
... SELECT, then there's something seriously amiss on the newer system.

If you don't get the correct data on a plain SELECT either, then there
is a chance the problem is in the 7.31 system, or in the communication
between that and the 10.00 system - at any rate, the INSERT becomes
immaterial, and the problem is that much simpler.

IDS 7.31.FD2 is not the newest release, but that shouldn't be a major
problem. Ditto for 10.00.FC3. I'm worrying that there's a bug lurking
in the background here, in case you haven't guessed.

Signature

Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Reply to this Message

scottishpoet - 24 Oct 2006 10:08 GMT

well spotted jonathan!

roger what is the output of

SELECT count(*) FROM x WHERE log_date IS NULL

when run on the source system?

> >> I tried to migrate database from ids73 to ids10,
> >> I can not move data from ids73 to ids10 by INSERT ... SELECT statement.
[quoted text clipped - 50 lines]
> Email: jleffler@earthlink.net, jleffler@us.ibm.com
> Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Reply to this Message