Transfer between two databases : How to solve Erro

Hi, I want to run this simple query between 2 databases with dbaccess : insert into dbs1:table1 select * from dbs2:table2; But, the database dbs1 has the codeset "en_us.819" and the database dbs2 has the codeset "fr_FR.57391" I have the following error : 23197 Database locale information mismatch. I tried to change CLIENT_LOCALE and DB_LOCALE environment, by example : export CLIENT_LOCALE=en_us.819 export DB_LOCALE=fr_FR.57391 It does not work ! How to do ? Regards, -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18

Try using my dbcopy utility. Since it keeps separate connections to the source and target database it should not run afoul of this problem and it is at least as fast as INSERT INTO ... SELECT and since you can break up the dataset by key ranges and run multiple copies of dbcopy for larger tables, it can be MUCH faster. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 21, 2013 at 6:52 AM, Franck THOMAS <franck.thomas@consult-ix.fr>wrote: > Hi, > > I want to run this simple query between 2 databases with dbaccess : > insert into dbs1:table1 > select * > from dbs2:table2;> > But, the database dbs1 has the codeset "en_us.819" > and the database dbs2 has the codeset "fr_FR.57391" > > I have the following error : 23197 Database locale information mismatch. > > I tried to change CLIENT_LOCALE and DB_LOCALE environment, by example : > export CLIENT_LOCALE=en_us.819 > export DB_LOCALE=fr_FR.57391> > It does not work ! > How to do ? > > Regards, > > -- > Franck Thomas > ConsultiX > franck.thomas@consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54b48d03b875c04d63c54a0

Hi Art, If I understand well, there is no direct solution to do the classic "INSERT INTO ... SELECT" when 2 databases have not the same codeset. I must use your utility dbcopy or write my own utility because I have to separate connections to the source and target database. I suspected it but I had hoped that a direct solution existed ! Thank you for your answer. Regards, Le 21/02/2013 14:48, Art Kagel a écrit : > Try using my dbcopy utility. Since it keeps separate connections to the > source and target database it should not run afoul of this problem and it > is at least as fast as INSERT INTO ... SELECT and since you can break up > the dataset by key ranges and run multiple copies of dbcopy for larger > tables, it can be MUCH faster. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, Feb 21, 2013 at 6:52 AM, Franck THOMAS > <franck.thomas@consult-ix.fr>wrote: > >> Hi, >> >> I want to run this simple query between 2 databases with dbaccess : >> insert into dbs1:table1 >> select * >> from dbs2:table2;>> >> But, the database dbs1 has the codeset "en_us.819" >> and the database dbs2 has the codeset "fr_FR.57391" >> >> I have the following error : 23197 Database locale information mismatch. >> >> I tried to change CLIENT_LOCALE and DB_LOCALE environment, by example : >> export CLIENT_LOCALE=en_us.819 >> export DB_LOCALE=fr_FR.57391>> >> It does not work ! >> How to do ? >> >> Regards, >> >> -- >> Franck Thomas >> ConsultiX >> franck.thomas@consult-ix.fr >> Téléphone : 33 (0) 1 39 12 18 00 >> Mobile : 33 (0) 6 78 81 09 33 >> Fax : 33 (0) 1 39 12 18 18 >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --bcaec54b48d03b875c04d63c54a0 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Franck Thomas ConsultiX - http://www.consult-ix.fr franck.thomas@consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18

Franck, There is a direct alternative I employ here. Get a shell script to do the work then you can set and unset the locales as necessary. Regards Andy G > To: ids@iiug.org > From: franck.thomas@consult-ix.fr > Subject: Re: Transfer between two databases : How to so.... [29556] > Date: Thu, 21 Feb 2013 09:47:43 -0500 > > Hi Art, > > If I understand well, there is no direct solution to do the classic > "INSERT INTO ... SELECT" when 2 databases have not the same codeset. > I must use your utility dbcopy or write my own utility because I have to > separate connections to the source and target database. > > I suspected it but I had hoped that a direct solution existed ! > > Thank you for your answer. > > Regards, > > Le 21/02/2013 14:48, Art Kagel a écrit : > > Try using my dbcopy utility. Since it keeps separate connections to the > > source and target database it should not run afoul of this problem and it > > is at least as fast as INSERT INTO ... SELECT and since you can break up > > the dataset by key ranges and run multiple copies of dbcopy for larger > > tables, it can be MUCH faster. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated nor > > those of the entities themselves. > > > > On Thu, Feb 21, 2013 at 6:52 AM, Franck THOMAS > > <franck.thomas@consult-ix.fr>wrote: > > > >> Hi, > >> > >> I want to run this simple query between 2 databases with dbaccess : > >> insert into dbs1:table1 > >> select * > >> from dbs2:table2;> >> > >> But, the database dbs1 has the codeset "en_us.819" > >> and the database dbs2 has the codeset "fr_FR.57391" > >> > >> I have the following error : 23197 Database locale information mismatch. > >> > >> I tried to change CLIENT_LOCALE and DB_LOCALE environment, by example : > >> export CLIENT_LOCALE=en_us.819 > >> export DB_LOCALE=fr_FR.57391> >> > >> It does not work ! > >> How to do ? > >> > >> Regards, > >> > >> -- > >> Franck Thomas > >> ConsultiX > >> franck.thomas@consult-ix.fr > >> Téléphone : 33 (0) 1 39 12 18 00 > >> Mobile : 33 (0) 6 78 81 09 33 > >> Fax : 33 (0) 1 39 12 18 18 > >> > >> > >> > >> > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > --bcaec54b48d03b875c04d63c54a0 > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Franck Thomas > ConsultiX - http://www.consult-ix.fr > franck.thomas@consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >