Issues related to CCSID on DB2 while trying to export a table

Question

I have been trying to export a table from db2 from one external source to another, so far, I noticed that there is an error while I tried to import it on the source table.

I was using this on a shell script:

myDate=$(date +"%Y%m%d%I%M")
myPath=/tmp/test
myDataSource="external"
myTableName=tableSchema.sourceTable

db2 +w -vm "export to $myPath/$myDataSource.$myTableName.$myDate.ixf of ixf
    messages $myPath/$myDataSource.$myTableName.$myDate.ixf.xmsg
    SELECT * from $myTableName"

After further investigation, it seemed like there was a weird character "▒" being inserted. I checked the source and there wasn't any weird character. SO the after checking on detail I issued this command:

select *
from SYSIBM.SYSCOLUMNS
where
    tbcreator = 'SOURCESCHEMA'
    and tbname = 'SOURCETABLE'
for fetch only with ur;

Which showed that the CCSID on the source columns is 37. I did the same on the target schema and the CCSID for the columns is 1208.

When I tried to export the table again forcing it to convert to CCSID 1208 adding modified by codepage=1208:

db2 +w -vm "export to $myPath/$myDataSource.$myTableName.$myDate.ixf of ixf
    modified by codepage=1208
    messages $myPath/$myDataSource.$myTableName.$myDate.ixf.xmsg
    SELECT * from $myTableName"

This causes the script to work, but I get this warning:

SQL3132W  The character data in column "COLUMN" will be truncated to size "4".

Said column is the same size on the source and the target but it seems that due to the CCSID I will need to change the size on the target (I can't change anything on the source and changing the CCSID on the target will break things on the target) So, my questions are:

How do I calculate the size needed for each varchar/char column depending on the encoding, for example if a varchar(4) on CCSID 37 will need a varchar(5) to hold a value with CCSID 1208?
Will it be possible to do something like:

SELECT CAST(COLUMN as VARCHAR(12) CCSID 1208) --and for all columns from tableSchema.sourceTable;

So I don't lose any part of those strings.

What about the numbers?

Thanks!

What platform and version of DB2 are involved? The reference to CCSID 37 (US EBCDIC) leads to to suspect at least one is a mainframe (z/OS) or midrange (IBM i). CCSID 1208 is unicode (UTF-8). So it's single byte, not sure why truncation would occur. How is the column defined in both source and target? What tool are you using to see the "werid character"? Finally, what's the actual hex value of the data with the werid character? — Charles
Hey @Charles, so in the source it seems to be db2 version 10, and 9.7 on the target. Both servers have the same column definition (varchar size) and the target is (I believe) a Power7. The weird caracter is shown as "a0" on the hex editor which I can see in hex until I exported the IXF file. To see the character I was using a strings <ixf file> | grep "value near the character" does it help? — StrayChild01
On the source machine, use the HEX(problem_column) function in SQL and show the hex value for the entire column. — Charles

StrayChild01 StrayChild01 · Accepted Answer · 2015-12-15T21:38:20

At the end, I ended up using this

SELECT
    CAST(COLUMN AS VARCHAR(12) CCSID 1208) as COLUMN,
    CAST(COLUMN2 AS VARCHAR(12) CCSID 1208) as COLUMN2,
    (...),
    CAST(COLUMNM AS VARCHAR(12) CCSID 1208) as COLUMNM,
FROM tableSchema.sourceTable;

It converted across codepages and no truncation was raised.

Issues related to CCSID on DB2 while trying to export a table

1 Answers