Ferhat's Blog

There will be only one database

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

Posted by fsengonul on December 20, 2010

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 ?  :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: