Problem in changing locale via dbexport/dbimport

Dear All, I want to import an existing database having default locale settings as (DB_LOCALE=en_US.819 ;export DB_LOCALE), and my destination/target locale setting would be in Chinese language support locale as (DB_LOCALE=zh_cn.GB2312-80 ;export DB_LOCALE). But while dbimport, it is giving me the following error: ------------------------------------------------------------------- *** Import data is corrupted! 0 - Unknown error message 0. ------------------------------------------------------------------- I am trying to follow this link of Informix 11.50 documentation (http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ib m.mig.doc%2Fids_mig_130.htm) which gives me following steps. But it is not working: Changing the database locale with dbimport =========================================== You can use the dbimport utility to change the locale of a database. To change the locale of a database: 1. Set the DB_LOCALE environment variable to the name of the current database locale. 2. Run dbexport on the database. 3. Use the DROP DATABASE statement to drop the database that has the current locale name. 4. Set the DB_LOCALE environment variable to the desired database locale for the database. 5. Run dbimport to create a new database with the desired locale and import the data into this database. I need urgent support as I need to import the data of lots of tables. My Database Environment is following: -------------------------------------- IDS Version: IDS 11.50 FC8W3 OS: Solaris 10 H/W: SunFire T2000 Thanks. Regards, Omer Saeed Khan

Hi, I have done something like that and got some problems, too (other locales involved, but problem is the same). The conversion of LOCALE is done using the env vars DB_LOCALE and CLIENT_LOCALE. E.g. when you export with settings DB_LOCALE=en_US.819, it will export data from the DB_LOCALE and convert it in the files to CLIENT_LOCALE. Then, while importing data, you should modify DB_LOCALE to the value you want to have. For us it was: while exporting DB_LOCALE=de_DE.8859-15 CLIENT_LOCALE=de_DE.utf-8 while importing DB_LOCALE=de_DE.utf-8 CLIENT_LOCALE=de_DE.utf-8 Or vice versa (DB_LOCALE/CLIENT_LOCALE identical while exporting and DB_LOCALE modified during import). The way we did the conversion, we could control the charset in the export files. Hope this helps, Marcus ----- Ursprüngliche Mail ----- Von: "OMER KHAN" <oskhan@i2cinc.com> An: ids@iiug.org Gesendet: Donnerstag, 14. Juni 2012 13:56:31 Betreff: Problem in changing locale via dbexport/dbimport [27365] Dear All, I want to import an existing database having default locale settings as (DB_LOCALE=en_US.819 ;export DB_LOCALE), and my destination/target locale setting would be in Chinese language support locale as (DB_LOCALE=zh_cn.GB2312-80 ;export DB_LOCALE). But while dbimport, it is giving me the following error: ------------------------------------------------------------------- *** Import data is corrupted! 0 - Unknown error message 0. ------------------------------------------------------------------- I am trying to follow this link of Informix 11.50 documentation (http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ib m.mig.doc%2Fids_mig_130.htm) which gives me following steps. But it is not working: Changing the database locale with dbimport =========================================== You can use the dbimport utility to change the locale of a database. To change the locale of a database: 1. Set the DB_LOCALE environment variable to the name of the current database locale. 2. Run dbexport on the database. 3. Use the DROP DATABASE statement to drop the database that has the current locale name. 4. Set the DB_LOCALE environment variable to the desired database locale for the database. 5. Run dbimport to create a new database with the desired locale and import the data into this database. I need urgent support as I need to import the data of lots of tables. My Database Environment is following: -------------------------------------- IDS Version: IDS 11.50 FC8W3 OS: Solaris 10 H/W: SunFire T2000 Thanks. Regards, Omer Saeed Khan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On Thu, Jun 14, 2012 at 4:56 AM, OMER KHAN <oskhan@i2cinc.com> wrote: > I want to import an existing database having default locale settings as > (DB_LOCALE=en_US.819 ;export DB_LOCALE), and my destination/target locale > setting would be in Chinese language support locale as > (DB_LOCALE=zh_cn.GB2312-80 ;export DB_LOCALE). > Does the original database contain only characters from the ASCII subset of ISO 8859-1 (IBM CCSID 819)? If not, you have problems from the start. The 8859-1 code set ascribes a meaning to each code point 0..255. This means you can get away with all sorts of tricks which a less pliant code set would not allow. When it comes to the import, the data must follow the rules of the GB2312-80 code set, which will be much more stringent than 8859-1. Only certain sequences of bytes are valid. What I think you are seeing is that the original data is not valid as GB2312-80 data. That's the bad news. Working out how to fix this is going to require a detailed understanding of what data is in the database and how it was put there, and how it should be recoded in GB2312-80. It is likely to be a fiddly process. It is likely to involve editing the data files from the DB-Export output so that it is acceptable as GB2312-80 input. But how you get it to that state depends on what was in the 8859-1 data (as well as the intricacies of GB2312-80, with which I'm not familiar - yet). > But while dbimport, it is giving me the following error: > ------------------------------------------------------------------- > *** Import data is corrupted! > 0 - Unknown error message 0. > ------------------------------------------------------------------- > > I am trying to follow this link of Informix 11.50 documentation > ( > http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm .mig.doc%2Fids_mig_130.htm > ) > which gives me following steps. But it is not working: > > Changing the database locale with dbimport > =========================================== > You can use the dbimport utility to change the locale of a database. > > To change the locale of a database: > > 1. Set the DB_LOCALE environment variable to the name of the current > database > locale. > 2. Run dbexport on the database. > 3. Use the DROP DATABASE statement to drop the database that has the > current > locale name. > 4. Set the DB_LOCALE environment variable to the desired database locale > for > the database. > 5. Run dbimport to create a new database with the desired locale and import > the data into this database. > This is straight-forward enough when the two locales share the same code set. It is not always sensible when the two locales use different code sets. For example, exporting from 8859-1 (Latin 1 for West Europe) to 8859-2 (Latin 2 for East Europe) may work 'physically', but many accented characters will have a different meaning in 8859-2 from 8859-1. If you migrated to 8859-5 (Cyrillic), 8859-6 (Arabic), 8859-7 (Greek), or 8859-8 (Hebrew), the problems would be worse. Switching from 8859-1 to GB2312-80 is more complex still. It all comes down to: * How did you get the data into the 8859-1 database? How did you get it out? Did it contain any Chinese characters? If so, how did you achieve that, and which code set are you really using? > I need urgent support as I need to import the data of lots of tables. > > My Database Environment is following: > -------------------------------------- > IDS Version: IDS 11.50 FC8W3 > OS: Solaris 10 > H/W: SunFire T2000 > Thanks for including this information! -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04071785ddb09f04c2717069

Thanks jonathan for such a detailed response. Here are response from our side: [JL]Does the original database contain only characters from the ASCII subset of ISO 8859-1 (IBM CCSID 819)? [OSK] As I told earlier that we are using "en_US.819", so, is there any possible way to know that if the database contains any additional character other than ISO 8859-1 (IBM CCSID 819)? Also can you please tell me a bit more about code set thing? [JL] How did you get the data into the 8859-1 database? How did you get it out? [OSK] At the moment, We uses JDBC to insert & pull the data using default US english locale (en_US.819) [JL]Did it contain any Chinese characters? If so, how did you achieve that, and which code set are you really using? [OSK] No, at the moment, it does not contain any chinese character, but it is our future requirement now, that's why we are setting up a new database using new locale and trying to add the existing data in few key tables into a new database having locale(zh_cn.GB2312-80). [OSK] Also I think that details that you have shared in this email, should be part of this link in Informix documentation, so that one should know that these step will only work between those two locales which will share the same code set. Dont you think its bit unclear/unfair at IBM part. anyhow, thanks for all your guidance.

On Thu, Jun 14, 2012 at 10:22 AM, OMER KHAN <oskhan@i2cinc.com> wrote: > Thanks Jonathan for such a detailed response. > > Here are response from our side: > > [JL]Does the original database contain only characters from the ASCII > subset > of ISO 8859-1 (IBM CCSID 819)? > [OSK] As I told earlier that we are using "en_US.819", so, is there any > possible way to know that if the database contains any additional character > other than ISO 8859-1 (IBM CCSID 819)? Also can you please tell me a bit > more > about code set thing? > Code sets is a complex subject. I don't have time to write a book on the subject. A starting point for your searches is: http://en.wikipedia.org/wiki/Character_encoding. You can also look at Unicode (http://www.unicode.org/) and so on. As a starting point, you can also look at http://en.wikipedia.org/wiki/GB2312-80 for GB2312-80 (or, if I end up needing to know more, that's one place I'll start). Are you sure you wouldn't be better off with one of the other, newer, Chinese code sets, such as GB18030? As to 'additional characters', that looks like it is not directly a problem. You don't currently have Chinese characters in the database. You did run into problems, so the data you have is not acceptable as GB2312-80. You are now going to have to work out how to map the characters in 8859-1 to GB2312-80. You will have to understand how GB2313-80 is organized. I don't know whether the character codes 0..127 are the same as 8859-1 or not; my suspicion would be not. So, you will need to find a conversion program that converts the 8859-1 to GB2312-80. There are likely to be such programs; look up GNU iconv to see whether that will help (Google search with 'gnu iconv manual' gets you lots of good information). > [JL] How did you get the data into the 8859-1 database? How did you get it > out? > [OSK] At the moment, We uses JDBC to insert & pull the data using default > US > english locale (en_US.819) > OK; that's routine. > [JL]Did it contain any Chinese characters? If so, how did you achieve > that, and which code set are you really using? > [OSK] No, at the moment, it does not contain any Chinese characters, but > it is > our future requirement now, that's why we are setting up a new database > using > new locale and trying to add the existing data in few key tables into a new > database having locale(zh_cn.GB2312-80). > And again, that's fine. > [OSK] Also I think that details that you have shared in this email, should > be > part of this link in Informix documentation, so that one should know that > these step will only work between those two locales which will share the > same > code set. Don't you think its bit unclear/unfair at IBM part. > > anyhow, thanks for all your guidance. > One difficulty is writing it so it is comprehensible to people who aren't sure what a code set is (and there's no slight intended; you're part of the vast majority, and there are times when I wonder whether I know what a code set is, too). You can always write to the Tech Pubs (Information Development) team for Informix: the email address is in the manuals (docinf@us.ibm.com). They are usually pretty receptive to cogent, well explained issues, especially if the complaint suggests a way to fix the problem. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --bcaec552428aca969704c273d45a