A common problem encountered when people start transferring data or copying data from their tables on IBM i to a PC is that the data does not look right. It is common when the user environment uses more than one language. Frequently the reason for this problem is a mismatch between the coded character set identifier (CCSID) on column and the CCSID, or code page, of the emulation session.

In essence, the problem really is that the file has the wrong data in it; however, it looks right when displayed in a 'green screen' application. One way to fix this problem is to correct the data and change the code page on the emulation session to match the CCSID of the column that the data is in. This solution, as you might guess, is not a popular choice. It is too much work. So, what else can be done?

The solution I prefer to use is what I call a double cast.  It makes use of the SQL cast function. You can use the SQL cast function with data transfer and other data middleware to experience the same 'wrong' behavior that lets the data look right. And that is the point to have the data look right. The column data first has to be cast to a binary CCSID and then to the CCSID that the emulation session is using.  This double casting is essentially what happens when you are displaying the data with something like DSPPFM.

To make the concept clearer, a practical example is necessary. The following SQL statements can be used to create a file with character columns in a Danish CCSID. Then, populate it with data that looks right when displayed by using the DSPPFM command on an emulation session that is using CCSID 37 (US English EBCDIC). The following commands can be run through the Run SQL Scripts feature of IBM Navigator for i. If you run them through other methods, you will probably have to remove the semicolons after each statement.

Create collection ccsidtest;

Create table ccsidtest.whatamess(f1 char(10) ccsid 277);

Insert into ccsidtest.whatamess values(x'D0C05B6A7C6740404040');

Select f1, hex(f1) from ccsidtest.whatamess;

Create view ccsidtest.lookinggood as (select cast( cast (f1 as char(10) ccsid 65535) as char(10) ccsid 37) as F1 from ccsidtest.whatamess);

Select * from ccsidtest.lookinggood;

When your PC applications select data from the WhatAMess table, they get data that does not look the same as what they see through the green screen. However, by using the view LookingGood, the data appears correct.