ids 10 datetime migrate error

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 ?

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 roger@star2000.com.tw wrote: > 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 ?

> > roger@star2000.com.tw wrote: >> 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 ? scottishpoet wrote: > 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. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

well spotted jonathan! roger what is the output of SELECT count(*) FROM x WHERE log_date IS NULL when run on the source system? Jonathan Leffler wrote: > > > > roger@star2000.com.tw wrote: > >> 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 ? > > scottishpoet wrote: > > 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. > > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/