In the previous english tip I wrote about how to make ASCII files readable in ISPF. Under normal circumstances DB2 will automatically convert ASCII or UNICODE data to readable characters. Think about the DB2 catalog in DB2 version 8 and later versions. They are saved as UNICODE, but when you execute a SELECT against a table in the DB2 catalog the data is presented to you in EBCDIC.

Unfortunately not all DB2 tables or columns are defined in such a manner that DB2 automatically converts data to readable characters. My first thought was that DB2 has a built-in function to make the conversion. Imagine you know that a column contains ASCII or UNICODE data, but when you select this column it is not converted and the data are presented to you as quite unreadable unless you are an expert in ASCII/UNICODE conversions. I am not. Unfortunately there is no obvious function available to make the conversion. Start by trying the CHAR function:

SELECT CHAR(myAsciiColumn)
FROM myAsciiTable
WHERE ...

Where MYASCIICOLUMN contains ASCII or UNICODE data. If this trick does not work you have to use the CAST function which is not a traditional function. CAST is a way of temporarily redefining a column in a SQL statement. Try for instance:

SELECT CAST (myAsciiColumn as char(254) CCSID ASCII)
FROM myAsciiTable
WHERE ...

This SQL statement will force DB2 to interpret the contents of MYASCIICOLUMN as being in ASCII characters and being 254 characters wide. Unfortunately the above CAST will not always work. It is also depending on how the column is actually defined. If the column is defined as FOR BIT DATA the above will not convert anything. Then you can try:

SELECT CAST (myAsciiColumn as varchar(200) FOR MIXED DATA)
FROM myAsciiTable
WHERE ...

Now you are forcing DB2 to consider the contents of the conlumn as ordinary characters instead of treating them as a long stream of bits. Please note that you can adjust the definiton of the resulting column almost as you like. Here I chose to use VARCHAR(200) instead. The length of the column does not play any important role although the type must be a kind of a CHAR column.

When you use CAST to make DB2 data readable you may run into a SQLCODE -331. This happens when DB2 cannot translate a character from the DB2 table correctly. This error can be avoided by encapsulating the CAST function in a CHAR function:

SELECT CHAR(CAST (myAsciiColumn as varchar(1000) FOR MIXED DATA))
FROM myAsciiTable
WHERE ...

The CHAR function has the disadvantage of cutting off the result to 255 characters no matter how long the source column is defined and which length you use in your CAST.

I cannot give you a unified solution on how to convert different formats to readable characters. If the above does not help you then you must follow the link to the CAST function and read about the various options. If you come up with a general solution I will most certainly like to hear about it.