Loading mainframe data into linux oracle via sqlldr (comp-comp3)

I’m just suffering a “little” jetlag and it is a good time for opening old mainfame issues. One of my friends has asked about loading comp and comp3 data from mainframe into linux oracle. Here are the method I had used on my old “banking” days.

I’ve ftp the data in binary mode from mf into linux.  Good side is no  valuable mf cpu is used for the conversion. Bad side is if there is an error in the data you have to ftp the rejected records into mf in order to understand what was wrong.

Then add characterset TR8EBCDIC1026  byteorder big to  force sqlldr to do the conversion.

I used smallint and integer  for comp  ; decimal(18,2) for comp-3.

In 9i documentation there was also a section about the comparison between db2 mf loader and sqlldr , which is not kept after 9i. I wonder why?

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/apb.htm#620714

You may find an example control file I’ve used  below:

UNRECOVERABLE
LOAD DATA characterset TR8EBCDIC1026  byteorder big
INFILE ‘/export/HRK0607/G060630/H0001.ASC’ “FIX 407″
INFILE ‘/export/HRK0607/G060703/H0001.ASC’ “FIX 407″
BADFILE ‘/export/HRK0607.bad’
DISCARDFILE ‘/export/HRK0607.dsc’
TRUNCATE
INTO TABLE SMUHAS.TMUH_DEFKEB_HRK0607C
(
SBKD            POSITION(001:002) smallint,
TLTUT_BORC      POSITION(042:051) decimal(18,2),
KAR_MCARIHN     POSITION(070:073) integer,
DVKUR           POSITION(276:280) decimal(9,7),
DO_KAR          POSITION(281:290) decimal(18,2),
ARDVKD          POSITION(302:303) smallint,
ARDVTUT         POSITION(304:313) decimal(18,2)
)

 

An “architect” may ask why not use an ETL tool for this. Although I would say the same if I were an architect,  using simplest tools is the fastest way to finish a migration. What is the job of a DBA anyway if not migration ?  :)

Just because I have been a good DBA in 2010, Santa Claus is bringing me 2 new x2-2 for christmas :)

It seems that our new 2 x2-2 boxes will arrive before the end of this year. We have already started to plan our new migration process from the existing v2 full rack sas machine into the newly comming x2-2 high performance systems. This time we will deal will 16 nodes. Migration will be quite simple as taking a backup and restoring it. And taking an incremental backup and restoring it , until we had a read only window to direct our etls. But we do not like easy projects So to get things complicated we are planning to change the place of our datacenter. It’s good news for me because I will have more time to play with this new toy before it becomes production.
If you have anything in mind to test or try on x2-2 machine, just write your comments and we will plan the stress tests together.