This discussion is archived
1 Reply Latest reply: Mar 21, 2011 1:46 AM by wkobargs RSS

Migration DB2 Files of EBCDIC format to Oracle - Integer Column Issue..

848693 Newbie
Currently Being Moderated
Hello,

I am facing little bit of a challenge and need URGENT help with this issue that I am facing. THANKS FOR ANY URGENT HELP POINTING ME TO RIGHT DIRECTION ON THIS ISSUE!

So I have a DB2 file given to me in the EBCDIC format. I have the DB2 .SYSPRINT file of the same as well. Based on the same, I wrote the Oracle Loader .CTL script to load the data. I am able to successfully load the data but when I look at the data in the INTEGER columns, I notice that they are getting converted to some very different values than expected and half of the data set of these integers is becoming negative value data too. I know for sure that is NOT the right data in the INTEGER columns. NOTE: The conversion of these integer column values are pretty consistent which makes me think some kind unicode/byte conversion is going on.. So I need help with the right code that I should be using to avoid all these conversion that might be happening and I am able to load the CORRECT integer value that DB2 is trying to pass me in the file.

NOTE: Below is just 1 file as example, I have several such file and all the data is getting LOADED SUCCESSFULLY for Character Columns, Decimal Columns, Date Columns and even Small Integer columns.. It's just every table that has INTEGER column like the below is blowing up the INTEGER column to some different values consistently instead of loading the real values.

Here is the .CTL file:
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE 'f:\data\db2\bulk\CATALOG.DATA'"FIX 8"
BADFILE 'f:data\badfiles\title.bad'
DISCARDFILE 'f:\data\badfiles\title.dsc'
REPLACE INTO TABLE PRODUCT_CATALOG
(CATALOG_ID POSITION(1:4) INTEGER
,ITEM_CLS_CD POSITION(5:6) CHAR (2)
,SBJCT_CTGRY_MJR_CD POSITION(7:7) CHAR (1)
,SBJCT_CTGRY_MNR_CD POSITION(8:8) CHAR (1)
)

Here is my table structure:
CREATE TABLE PRODUCT_CATALOG AS
(CATALOG_ID INTEGER
,ITEM_CLS_CD VARCHAR (2)
,SBJCT_CTGRY_MJR_CD VARCHAR (1)
,SBJCT_CTGRY_MNR_CD VARCHAR (1)
)



Thanks!
Shiv

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points