1. How It's Done Today

To load flat file data into a system running an Oracle database, the Oracle SQL*Loader utility is used. For example, a mainframe can generate a data set (i.e., a file) to be loaded into a Unix database. Using COBOL declaration statements, the attributes stored in the extract file can be defined in a COBOL copybook. Here's one example. Each line in a flat file might consist of the last name (columns 1-20), first name (columns 21-35), age (columns 36-38), and phone number (columns 39-47). The COBOL copybook could look like:

00572 ********************************************************************************
00572 *  COBOL COPYBOOK - CUSTOMERS
00572 *  DATA FOR CUSTOMER TABLE
00572 ********************************************************************************
00572  01 CUSTOMER-RECORD.
00573     05 CUSTOMER-LAST-NAME		PIC X(20).
00574     05 CUSTOMER-FILE-FIRST-NAME	PIC X(15).
00575     05 CUSTOMER-FILE-AGE		PIC 999.
00576     05 CUSTOMER-FILE-PHONE	PIC 9(10).

Based on the above description, an Oracle DBA needs to generate an Oracle SQL*Loader control file. A control file for the above description can look like:

OPTIONS(DIRECT=TRUE)

LOAD DATA

INFILE '/u1/project_abc/data/CUSTOMER.dat' "FIX 49"

TRUNCATE
INTO TABLE CUSTOMER
(
LAST_NAME               POSITION(001:020)    CHAR,
FIRST_NAME              POSITION(021:035)    CHAR,
AGE                     POSITION(036:038)    CHAR,
PHONE_NUMBER            POSITION(039:048)    CHAR,
DATE_LOADED             SYSDATE
)

In this example, each row is a fixed length containing 42 characters. On a Unix system, the new line character delimits each line.

After the data set has been created on the mainframe, the file is FTP'ed to the Unix system which has Oracle. To load in the data into Oracle, Oracle's sqlldr command is used. Here's an example:

sqlldr  abc/password \
        control=/u1/project_abc/control_files/customer.ctl \
        log=/u1/project_abc/log/customer.log \
        bad=/u1/project_abc/bad/customer.bad

For SQL Server, +1Copybook generates the CREATE TABLE script converting COBOL definitions into SQL Server data types. The generated CREATE TABLE script for the CUSTOMER copybook is:

CREATE TABLE CUSTOMER (
LAST_NAME                          	varchar(20),
FIRST_NAME                         	varchar(15),
AGE                                	smallint,
PHONE                              	int
)

To load data into the SQL Server database using BULK INSERT within the Transact-SQL utility, you can type:

use Northwind
go

bulk insert Northwind.dbo.[Orders]
from 'D:\data\orders.dat'
go

The bulk insert assumes the column order found in the orders.dat file matches the order found in the CREATE TABLE script. If the order is different, a format file needs to be created.

2. How Do You Know It's Right?

When a flat file only has four columns defined, generating an Oracle control file or SQL Server format file is easy. The problem is when COBOL copybooks define hundreds of columns for one or more tables. Now generating a control file becomes a risk, one if done wrong and goes unnoticed, can impact a company severly.

Not only does it takes a longer to generate a control file, but if you accidentally make a simple mistake early in defining the control file, you have to start all over again. But it's not just how long it takes to generate either.

In the above control file, if FIRST_NAME was mistakenly defined to be POSITION(021:036) and AGE was defined as POSITION(037:038), the data would load fine! There would be no error messages. SQL*Loader worked as defined. But everyone in the database would be less than 99 years old and some 100+ year olds would be young again!

Also, when manually generating control files, there's no analysis being performed nor mapping documents generated showing which COBOL copybook columns load which Oracle table columns. In the above control file, the COBOL copybook CUSTOMER-FILE-PHONE column loads PHONE_NUMBER column. In this example, this a close match (which is desired!). But what happens when the COBOL copybook has a column named CREDIT_AMT and in your database, you don't have a column named CREDIT_AMT but do have CREDIT_AMT and CREDIT_TO_AMT columns. You could intuitively assign CREDIT_AMT to CREDIT_AMT, but what if the correct mapping actually is CREDIT_AMT to CREDIT_TO_AMT and the copybook simply doesn't initialize CREDIT_AMT in the table?

The real question becomes: "How do you know it's right?" How can you prevent errors and ensure that every column sent to you is being used and that every column defined in the Oracle table is being initialized?

3. +1Copybook

An alternative to the above manual way is to use +1Copybook. +1Copybook can automate this process by quickly generating SQL*Loader control files or SQL Server format files based on COBOL copybooks and other formats, generating a mapping HTML documents which can be reviewed for correctness, and performing analysis. Below is the output from running +1Copybook for our above Oracle example.

Table CUSTOMER
*************************************************
 1. CUSTOMER-PHONE                      copybook column is not used.

 1. PHONE_NUMBER                        oracle column is not initialized.

In the above example, all but one of the copybook columns matched the Oracle CUSTOMER table names when the CUSTOMER- prefix was first dropped from the copybook column name by +1Copybook. The exception is CUSTOMER-PHONE in the copybook did not map to PHONE_NUMBER in the Oracle table.

After mapping CUSTOMER-PHONE to PHONE_NUMBER and rerunning +1Copybook, +1Copybook displays no errors. +1Copybook now shows all copybook columns are used and all Oracle columns are initialized.

Table CUSTOMER
*************************************************

The corresponding control file, which gets generated and can also be viewed on the web too, follows:

---
---   Load Script For CUSTOMER Table
---

OPTIONS(DIRECT=TRUE)

LOAD DATA

INFILE '/u1/project_abc/data/CUSTOMER.dat' "FIX 49"

TRUNCATE
INTO TABLE CUSTOMER
(
--- *********************************************************************
--- *  COBOL COPYBOOK - CUSTOMERS
--- *  DATA FOR CUSTOMER TABLE
--- *********************************************************************
LAST_NAME                          POSITION(001:020)    CHAR,
FIRST_NAME                         POSITION(021:035)    CHAR,
AGE                                POSITION(036:038)    CHAR,
PHONE_NUMBER                       POSITION(039:048)    CHAR,
LOAD_DATE                          SYSDATE
)

The above example is a very simple example. +1Copybook has been used to generate very large control files and SQL Server CREATE TABLE scripts. This is a great tool and saves a lot of time!

+1 Software Engineering is a registered trademark of +1 Software Engineering.

Oracle, SQL*Loader, and all Oracle-based trademarks are trademarks or registered trademarks of of Oracle Corporation in the United States or other countries.

Microsoft is a registered trademark of Microsoft Corporation.

+1 Software Engineering is independent of Oracle Corporation and Microsoft Corporation.

All other product names are trademarks of their respective holders.