DB2 Connect returning data in HEX?

[Lee]

Hi All,
I am using DB2 Connect on AIX 5.3 to access DB2/400 data. I can see
and return data from some libraries/tables as you would expect but when
I access some JD Edwards data (e.g. library.F0902) the columns are
being returned as HEX values.
For example, using STRSQL form within my 5250 emulator I run the
following SQL:
select GBCO, count(*) from library.F0902 group by GBCO
which returns the following:

GBCO Count(*)
00100 3734656
00110 4258
....
....

DB2Connect returns this:
GBCO 00002
------------- -----------
x'F0F0F1F0F0' 3734656
x'F0F0F1F1F0' 4258

Any idea what I can do to resolve this? (It is not happening with
every table.)
Many thanks in advance for your suggestions.
Regards,
Lee

[Karl Hanson]

Lee wrote:

Hi All,
I am using DB2 Connect on AIX 5.3 to access DB2/400 data. I can see
and return data from some libraries/tables as you would expect but when
I access some JD Edwards data (e.g. library.F0902) the columns are
being returned as HEX values.
For example, using STRSQL form within my 5250 emulator I run the
following SQL:
select GBCO, count(*) from library.F0902 group by GBCO
which returns the following:

GBCO Count(*)
00100 3734656
00110 4258
...
...

DB2Connect returns this:
GBCO 00002
------------- -----------
x'F0F0F1F0F0' 3734656
x'F0F0F1F1F0' 4258

Any idea what I can do to resolve this? (It is not happening with
every table.)
Many thanks in advance for your suggestions.

You might check the data type and CCSID (if a string type) of GBCO on
the server. Comparing column attributes between tables that work (vs
not) may offer a clue. In a 5250 emulator session, either:

Command line: dspffd library/f0902
STRSQL: select substr(column_name,1,20), data_type, ccsid from syscolumns

where table_schema = 'LIBRARY' and table_name = 'F0902'

--
Karl Hanson

[Lee]

Karl, you are right - the CCSIDs are different. For the tables that
come back as text the CCSID is 37, for the others it is 65535. I do
not expect to be able to modify these on the source systems, so a
colleague told me about a setting in the Client Access ODBC setup for
my desktop to change - I am testing that next. However, does anyone
know if (and how) you can configure the DB2Connnect connection on the
AIX server to do the same thing (convert binary to text)?
Thanks Again,
Lee

[Karl Hanson]

Lee wrote:

Karl, you are right - the CCSIDs are different. For the tables that
come back as text the CCSID is 37, for the others it is 65535. I do
not expect to be able to modify these on the source systems, so a
colleague told me about a setting in the Client Access ODBC setup for
my desktop to change - I am testing that next. However, does anyone
know if (and how) you can configure the DB2Connnect connection on the
AIX server to do the same thing (convert binary to text)?

Don't know of a config option, but a suggested workaround is to use a
cast on the client side:

select cast(GBCO as char(5) ccsid 37), count(*)
from library.F0902 group by GBCO

--
Karl Hanson