1

When I try to import an informix database with command dbexport -c <mydbname> <myDbspace> that was already exported. I get this Error: 23103 - Code-set conversion function failed due to illegal sequence or invalid value.

I wonder if this is related to whether the text encoding I try to import is not compatible or something like that..

This is the full log:

{ TABLE "informix".XXX row size = 134 number of columns = 19 index size = 8 }

{ unload file name = XXX__00103.unl number of rows = 7 }

create table "informix".XXX
  (
    XXX_id char(3) not null ,
    XXX_name char(50) not null ,
    XXX_dec_nb decimal(1,0) not null ,
    XXX_rounded decimal(4,3) not null ,
    XXX_symbol char(3) not null ,
    XXX_location decimal(1,0)
        default 0 not null ,
    XXX_negative decimal(2,0) not null ,
    XXX_dec_char char(1) not null ,
    XXX_group_char char(1) not null ,
    XXX_euro decimal(16,8) not null ,
    XXX_active decimal(1,0)
        default 1 not null ,
    XXX_coef decimal(7,3),
    XXX_rnd_0 decimal(7,2) not null ,
    XXX_rnd_1 decimal(7,2),
    XXX_rnd_2 decimal(7,2),
    XXX_rnd_3 decimal(7,2),
    XXX_rnd_4 decimal(7,2),
    XXX_rnd_5 decimal(7,2),
    XXX_cts_desc char(20),

    check (((XXX_active >= 0. ) AND (XXX_active <= 1. ) ) AND (XXX_active IN (0.
              ,1. )) ) constraint "informix".ckc_XXX_acti_XXX,

    check (((XXX_location >= 0. ) AND (XXX_location <= 3. ) ) AND (XXX_location IN (0.
              ,1. ,2. ,3. )) ) constraint "informix".ckc_XXX_loca_XXX,
    primary key (XXX_id)  constraint "informix".pk_XXX
  );

revoke all on "informix".XXX from "public" as "informix";
*** put loadXXX
23103 - Code-set conversion function failed due to illegal sequence or invalid value.

EDIT:

FIXED:

Changed my 'servername'.cmd file in C:\Program Files\IBM Informix Software Bundle by setting:

  • set CLIENT_LOCALE=EN_US.8859-1
  • set DB_LOCALE=EN_US.8859-1
Amrouane
  • 143
  • 3
  • 16
  • 1
    What is the code set of the original database ? What is the codeset of the destination database? What are the codeset / locale of the servers where the Informix instances are running? Are the environment variables SERVER_LOCALE, DB_LOCALE and CLIENT_LOCALE defined when you do the dbexport or the dbimport ? – Luís Marques Jan 23 '20 at 10:55
  • @LuísMarques fixed by setting DB_LOCALES to: set CLIENT_LOCALE=EN_US.8859-1 set DB_LOCALE=EN_US.8859-1 – Amrouane Jan 23 '20 at 11:06
  • Note that `en_US.8859-1` (however it is spelt) accepts any byte sequence as valid. If the data was exported with a different code set, it may have complicated rules (for example, UTF8 has rules that mean bytes 0xC1, 0xC11, and 0xF5..0xFF are never valid, and there are constraints on the byte sequences too). If you don't know the code set used when the data was exported, and therefore can't match that when you import it, you can run into code set issues — invalid characters, etc. _[…continued…]_ – Jonathan Leffler Jan 24 '20 at 03:04
  • _[…continuation…]_ If you know the original code set and know which code set you want to import it as, you can use tools such as `iconv` to convert the data from one code set to the other. For the most part, that's straight-forward; you might run into problems with TEXT or CLOB large objects. But the first step is understanding where the data comes from (which code set was used in the database that was exported). The second step is deciding which code set should be used when the data is imported. It's easiest if you don't change the code set. If you do, you have to convert the data too. – Jonathan Leffler Jan 24 '20 at 03:05

1 Answers1

0

FIXED:

Changed my 'servername'.cmd file in C:\Program Files\IBM Informix Software Bundle by setting:

  • set CLIENT_LOCALE=EN_US.8859-1
  • set DB_LOCALE=EN_US.8859-1
Amrouane
  • 143
  • 3
  • 16
  • That's not a "real" fix. If both locale variables are set to the same locale, there is no codeset conversion, so everything that is in the unload files will go to the tables. You must have some characters outside the allowed range of your database codeset (Like a '€' in a 8859-1 database). If you don't fix that, you may encounter problems later on. – jsagrera Jan 23 '20 at 11:13
  • @jsagrera I don't know the difference between the two variables, but what I need is exactly to import everything from the unload files to the tables. And like you said, what was causing the error earlier is a '$' character. But can you explain more the difference between these variables and why they should not be settled to same locale ? – Amrouane Jan 23 '20 at 11:19
  • 1
    have a read at this technote: `https://www.ibm.com/support/pages/error-21005-when-using-odbc-select-data-database`. I wrote that years ago and explains why you may get errors like that 23103 or 21005 if you have 'bad' characters in your database. – jsagrera Jan 23 '20 at 11:35