I will need your advice on a SSIS package which moves IBM DB2 (for z/OS V7) data to Oracle (AIX 10g). Thank you in advance.
Basically, this is a very simple SSIS package (created by SSIS import / export wizard) and just maps column by column. I used IBM OLEDB provider for DB2 to connect the source and Oracle Provider for OLEDB to connect to the destination.
The problem I ran into was that if the DB2 source value contains special (or invisible) characters then this value will be converted to a SPACE (' ') value on Oracle. To be more detailed, take the following example:
On DB2, value 000002D0000002D7 (EBCDIC HEX codes). This is actually a value of a column defined on DB2 as CHAR(8). Please note HEX '00' in EBCDIC stands for NULL and HEX 'D0' stands for character 'K';
On Oracle, this value will be stored as a SPACE (or a string of SPACEs) after data move.
On both DB2 and Oracle this column was defined as CHAR(8).
I guessed this was related to a code page issue, so I tried a few different code page to set for the source property (by setting the DefaultCodePage of the DataFlow source property), I used code page 37, 500, etc... All resulted in the same Oracle value.
The character set on DB2 is EBCDIC and the one on Oracle is ALT32UTF8. I guess it was the source provider (DB2 OLEDB provider) that converted this value to a SPACE.
So my questions are:
1. Which code page should I use for the DB2 source? And how can I know that the DB2 source should use this codepage? I remember there was a page on MSDN indicating all possible code pages, I tried but cannot find it.
2. How do I preserve the original value on DB2? that means I will have some invisible characters on Oracle while having the visible 'K' (or D2, if represented by EBCDIC HEX code) in the value. This is my essential goal. However, for doing this,
I do NOT want to change data type to CHAR(8) type on Oracle. Actually, I changed the oracle column type to BLOB and it was working (meaing I have the binary code on Oracle side after the data move)
Any input will be greatly appreciated.