By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

DB2 Connect returning data in HEX?

P: n/a
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

Apr 13 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Apr 13 '06 #2

P: n/a
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

Apr 13 '06 #3

P: n/a
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
Apr 13 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.