Migration of binary data from Win2000 to Unix

Hi people, I have some problem with moving data from IDS on Windows 2000 to IDS on Unix (HP-UX). We have some application which can run on both Windows and UX. The application stores data in IDS (we use version 9.21 on Win and UX). I use dbexport to get data from Windows. When I use dbimport to create whole database with all tables, the application doesn't work - it complains about error while connection to database and while tcpdumping communication there was some error about "ODBC/Native Error number <-1/-11007> : [Informix][Informix ODBC Driver]Option value changed.". This problem can be eliminated using LOAD SQL commands manualy instead of running dbimport. I can create the empty database on UX using the application on UX. I use also dbexport to get structure of all tables in UX database and compare it to the structure of tables from Windows-they are same. Then I delete data from all tables on UX and manualy load Windows data into each table on UX. Now the application can login to database (in contrast of the case when I use dbimport to create whole database). But just after login to database the application fails, because there is some problem with data. Note: Only conversion I did was CRLF to LF conversion of data files I got from dbexport. I haven't converted the data in files in any way. I did many tests with different versions of the application - each version of application creates database with different number of tables. One version also has different structure of two tables so I had to change order of data in data files. I also tested from UX to UX. While installation process of application, it generates different data each time and some of data writes in files. I have installed first time and made dbexport of data and copied files. Then I did second installation, deleted data from all tables, imported data from first installation and updated files where data changed. And the application worked. But if I do it with Windows data, it doesn't work. And now, the questions: Is there any known problem with structure of any data type when moving data from Intel+Windows to HP-UX Informix database server? Is any safe solution to move data from Win to HP-UX or is needed some conversion of data in fields of BYTE or other type? The dbexport generates text form of each data - it is probably OK for text and numbers, but what about BYTE etc. fields? Do you have anyone some experience with moving data from DB on Windows to DB on Unix/Linux and what application this data belongs to? Thank you very much for any answer. Bye. Robert Wolf. sending to informix-list

rowos@atlas.cz wrote: > Hi people, > > I have some problem with moving data from IDS on Windows 2000 to IDS on > Unix (HP-UX). > > We have some application which can run on both Windows and UX. The > application stores data in IDS (we use version 9.21 on Win and UX). > > I use dbexport to get data from Windows. dbexport/dbimport is the right tool to use. It's purpose is platform independent migration using ascii representation of the data. Manual: "IBM Informix Migration Guide". > > When I use dbimport to create whole database with all tables, the > application doesn't work - it complains about error while connection to > database and while tcpdumping communication there was some error about > "ODBC/Native Error number <-1/-11007> : [Informix][Informix ODBC > Driver]Option value changed.". This problem can be eliminated using LOAD > SQL commands manualy instead of running dbimport. > If I understand this correct, what you're saying is that dbimport runs the import job without any errors, but when you start your application it fails. Could it be that your application is using transactions (begin work/commit/rollback) ? If you just import the database by issuing the command "dbimport database" then you will get a database without transaction logging! If this is the case try: dbimport database -l [buffered] If you omit 'buffered' you will get unbuffered transaction logging. Btw, use also '-d dbspace' to avoid the database being created in rootdbs. > I can create the empty database on UX using the application on UX. I use > also dbexport to get structure of all tables in UX database and compare it > to the structure of tables from Windows-they are same. To get the database description in DDL use the dbschema command. > > Then I delete data from all tables on UX and manualy load Windows data into > each table on UX. Now the application can login to database (in contrast of > the case when I use dbimport to create whole database). But just after > login to database the application fails, because there is some problem with > data. > > Note: Only conversion I did was CRLF to LF conversion of data files I got > from dbexport. I haven't converted the data in files in any way. If you use ftp to move the files, set ftp to ascii mode before put/get, then ftp will automatically change end-of-line to the correct value. > > I did many tests with different versions of the application - each version > of application creates database with different number of tables. One > version also has different structure of two tables so I had to change order > of data in data files. The AGS Server Studio has a tool to compare the schemas of two databases (but it will only work for 30 days, unless you pay) > > I also tested from UX to UX. While installation process of application, it > generates different data each time and some of data writes in files. I have > installed first time and made dbexport of data and copied files. Then I did > second installation, deleted data from all tables, imported data from first > installation and updated files where data changed. And the application > worked. But if I do it with Windows data, it doesn't work. > > And now, the questions: > > Is there any known problem with structure of any data type when moving data > from Intel+Windows to HP-UX Informix database server? No, dbexport/dbimport works normally ok. The problems typically seen are date formats, decimal point and field delimiters. You can control these by setting DBDATE, DBMONEY and DBDELIMITER on both machines before running dbexport/dbimport. > > Is any safe solution to move data from Win to HP-UX or is needed some > conversion of data in fields of BYTE or other type? BYTE data is exported as hexadecimal ascii representation. If you have binary data in char columns (like BAAN do), then you should use the '-X' option for both dbexport and dbimport. > > The dbexport generates text form of each data - it is probably OK for text > and numbers, but what about BYTE etc. fields? > > Do you have anyone some experience with moving data from DB on Windows to > DB on Unix/Linux and what application this data belongs to? > > Thank you very much for any answer. > > Bye. > > Robert Wolf. > sending to informix-list