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?
You may find an example control file I’ve used below:
LOAD DATA characterset TR8EBCDIC1026 byteorder big
INFILE ‘/export/HRK0607/G060630/H0001.ASC’ “FIX 407”
INFILE ‘/export/HRK0607/G060703/H0001.ASC’ “FIX 407”
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 ? 🙂