From Hex Value to EBCDIC in DB2 for iSeries

0
votes

I have HEX data like this (F9F9F9F9) returned from a query. When I checked from IBM link : https://www.ibm.com/support/knowledgecenter/en/SS2MB5_14.1.0/com.ibm.xlf141.bg.doc/language_ref/asciit.html

F9 = 9 and F9 = 9

Here I should get result as 9999

I jhave around 1000 hex records like this in a table.

How can I convert this hex values to it corrsponding EBCDIC ?

I tried like :

  select cast(col char(2) as codebase(37)) from table

How ever, its not working. THis link is also not working: I'm not sure if its a cobol code or DB2 script. : http://www.ibmmainframeforum.com/db2/topic8785.html

Please help.Thanks.

1
db2db2-400
Your columns has a particular CCSID, see DB2 query results in Hex format — Need Character/String - Dam
I have written this like this : select cast(C as char(4) CCSID 37) from Table . However, its not showing EBCDIC. Its still returning me the HEX. Note: It looks like in Iseries Its not working. I think, the iSeries version I'm using is 7.2 . Any other solution please? - AskMe
What's the data type of the column you're selecting? - mustaccio
The original type is decimal(11,2) ; I'm converting this to HEX and from hex I'm trying to convert this (HEX) into Char - AskMe
Why don't you convert the value to CHAR directly? I presume the HEX() function does an implicit cast to CHAR anyway? - mustaccio

1 Answers

0
votes
select cast(col char(2) as codebase(37)) from table

Correct Syntax.

select cast(col as char(2) ccsid 37) as col from table

Generally you don't want to do this over and over so maybe just alter the table.

ALTER TABLE mylib/Z1 ALTER COLUMN JOJOB SET DATA TYPE CHARACTER ( 
10) CCSID 37 NOT NULL WITH DEFAULT

Or create a view over the table for read only data and read from the view.

create view tablev as 
select cast(col as char(2) ccsid 37) as col from table