Is there any DB2 SQL Builtin Conversion from EBCDIC - ASCII

[pongal]

Hi,
I am extracting the data from DB2 Database table with the following query
like...

SELECT CMMPU,CMMKU,CMLDA FROM FIN017
WHERE
ASCII(CMMPU) = 'HT';
here the field CMMPU data type is in Binary.

it is giving an error ASCII Function is undefined name.

could anyone give me exact conversion function for converting EBCDIC to ASCII in DB2

Thanks in advance...

[ray.wurlod]

Surely you can find this in DB2 manuals/help? Or ask your DBA?

I suspect there is no such function in DB2's SQL but I don't know for sure.

One thing you can do is to extract in EBCDIC and convert to ASCII within DataStage.

However, you also baffle me with a claim that the column is "in binary"; how can you legitimately compare it with the string value 'HT' in this case? What is the actual data type of the CMMPU column when you inspect the table definition (or import it into DataStage)?

[clshore]

I used to do a 'tricky' conversion with Informix, reading an EBCDIC flat file into the DB as an external table, then writing it back out to a different ASCII flat file.
The key to success of course was the metadata, to correctly describe the two external files.
Given DB2's roots and parents, I'd be surprised if there wasn't some way to do this.

So take Ray's advice, and ask a DBA!

Carter

[bcarlson]

I agree with Ray, do it in DataStage - if it isn't a standard database function, let something else do the work. In this case, converting EBCDIC to ASCII is not a normal database function, but it is normal for DataStage. Use the tool (or tools) most appropriate for the work to be done.

Also, the ASCII function in DB2 simply returns the ASCII code of the FIRST character of the string you pass it.