Translate EBCDIC comparison sql to ASCII

[leathermana]

I am converting data load processes currently being done with COBOL and Unix scripts to DataStage. One of the existing queries does comparison selections (<, >, <=, >=) on a CHAR(3) DB2 field which holds mixed alpha and numeric characters. The incoming DB2 data is EBCDIC in which alpha < numeric. Once extracted using ODBC connector the data is ASCII where alpha > numeric. Is there a way to keep the data in EBCDIC so I can use the same query comparison logic in my Transformer derivations? I am quite ignorant of code pages and NLS etc. but am not finding clear guidance in documentation. Can someone help with my problem or point me to documentation that will be helpful?

[ArndW]

If you are doing your query in DB2 then it would be done in whatever character set is defined in the database, which would seem to be EBCDIC.
Normally once the data comes into DataStage it will be converted - unless you define per-column mapping. It might be simpler and less prone to error to change your queries to be ASCII-collation compatible.

[leathermana]

Thanks. I ended up using CASE WHEN to do the comparison logic updates on the fly in the SELECT statement in the ODBC Connector stage. Since the Select is executed on the DB2 database, the original logic using EBCDIC comparisons worked fine. The only iffy thing is that the "Select statement" property did not like my statement, giving me the "Invalid Property Value, Expecting :Other Input" warning. I ignore it and it compiles and runs fine. Should I be worried? If anyone would be willing to look at my Select statement to see why I'm getting this warning... Here it is:

SELECT (a bunch of fields),
CAST(CASE
WHEN CL_DEPARTMENT < '360' AND SUBSTR(CL_DEPARTMENT, 1, 1) <> 'Z' THEN '11'
WHEN CL_DEPARTMENT >= '430' AND CL_DEPARTMENT <= '439' THEN '22'
WHEN (CL_DEPARTMENT >= '360' AND CL_DEPARTMENT <= '429')
OR (CL_DEPARTMENT >= '440' AND CL_DEPARTMENT <= '519') THEN '33'
WHEN CL_DEPARTMENT >= '520' AND CL_DEPARTMENT <= '749' THEN '44'
WHEN CL_DEPARTMENT >= '750' OR SUBSTR(CL_DEPARTMENT, 1, 1) = 'Z' THEN '55'
ELSE '66'
END AS SMALLINT) AS MIDB_CD
FROM MAIN.TB_DEPARTMENT

[asorrell]

Apparently a known issue of some sort regarding functions or cast statements:

viewtopic.php?p=420922

and

viewtopic.php?t=145186