db2hexibm-midrangemember

DB2 SQL query on member file returns hex values

I am using Squirrel SQL client with the ODBC driver to connect to the DB2 database, which has no issues in extracting data from the db tables.

I want to extract data from a member file, and have created the alias, and executed the query:

CREATE ALIAS MFXFER.I316_E157324133 FOR MFXFER.GIK316BKP ("E157324133");

SELECT * FROM MFXFER.I316_E157324133;

DROP ALIAS IF EXISTS MFXFER.I316_E157324133;

When I execute the query the returned data is made up of only hex pairs (snippet line 1):

2  f1  f0  40  40  40  40  40  40  40  40  40  40  40  f8  f6  f2  f0  f2  f3  f0  f6  f0  f6  f0  f1  f0  f0  f0  f0  f0  f1  f0  f1  f1  f2  f6  f4  f5  f3  f6  40  40  40  40  40  40  40  40  e5  d6  60  c2  f3  60  f0  f2  f4  40  40  40  40  40  40  40  f2  f0  f2  f3  f0  f6  f0  f6  40  40  40  40  e2  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40  40

This does not reflect the ASCII values I can see in the member, when I view the same using RDi (Rational Developer for i).

Contents of same member file

When I copy and paste the hex to a translation tool, I receive only special characters.

Solution

  • It seems the file has been created with CCSID 65535, data from this file are considered binary data by the driver.

    Don't forget IBMi is a EBCDIC platform, so converters have to know about EBCDIC like this one

    The RDi driver is probably configured to translate all binary/ccsid 65535 data.

    So you have to either set driver property translate binary to true in SQuirreL SQL (if you use JT400 JDBC driver) or cast the column using

    SELECT cast(GIK316BKP as char(nnn) ccsid ccc) as GIK316BKP FROM MFXFER.I316_E157324133