Extracting Double Byte table from iSeries/DB2 using ODBC connection

[Anonymous]

I am consistently getting this message on the Load/SQL Select statement when extracting Double Byte files from our location in China.

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 22005, ErrorCode: 30019, ErrorMsg: [IBM][System i Access ODBC Driver]Error in assignment

I have also gotten an invalid file type message.  Originally I felt there was garbage in one of the fields; however, I had the location review the data and remove some special characters in the data.  I still can't load the file.  This is only happening with files defined as double byte.  I need to be able to cleanse the data before it is loaded.  Can an ODBC connector be modified to do a cleanse before the data reaches the Load?  I am open to suggestions.

[petter]

Hi Lorraine, when I have encountered problems very similar to what you describe it has always come down to wrong configuration of the ODBC drivers. It is very important that the codepage that is specified is correct.

Do you know which IBM codepage they are using in the location in China when they update the database? What does the IBM iSeries/DB2 database say itself about the database and tables where the data is located? When it comes to codepage?

[Anonymous]

Thank you Peter for your response.  How do I determine the codepage on the iSeries?  I've been an iSeries developer and I can't even say I know what the codepage is. 

[petter]

Codepage is particular character set being used. They are numbered and are made for catering for different countries, languages and scripts. In IBM lingo they are identified by a Coded Character Set Identifier (CCSID).

It is not so important what the iSeries computer use but rather what the DB2-database you are accessing is using. Here is IBM's recipe to find it: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/char/src/tpc/db2z_determineccsiddb2data...

If you want a more in-depth explanation then you could read this: https://www.ibm.com/developerworks/data/library/techarticle/dm-0506chong/index.html ‌

and the Wikipedia pages: EBCDIC - Wikipedia - EBCDIC is the system for character set encodings created by IBM which contains lots of codepages. EBCDIC code pages - Wikipedia

The trick is to determine the DB2 EBCDIC codepage and set the same codepage in the ODBC-driver (or iSeries/DB-driver that you have on the Qlik Sense server or desktop).