|
Writing Oracle SQL*Loader control files or Microsoft SQL Server format files based on COBOL copybooks
is an intensive, error prone, and time consuming task.
But today with +1Copybook, the process is automated, detailed analysis can automatically be performed,
HTML mapping documents generated, and overall turn around time in creating such files is much faster.
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:
To load data into the SQL Server database using BULK INSERT within the Transact-SQL utility, you can type:
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.
CUSTOMER Table Mapping
CUSTOMER's Status Report follows copybook to Oracle column mappings below.
Red - Oracle column not loaded from copybook.
Blue - Highlights DATE columns.
Green - Highlights potential DATE columns (i.e., PIC X(08)).
|   | Copybook Definition | Oracle Column Name | SQL*Loader Position | Type |
| ***********************************************************************
| | *** COBOL COPYBOOK - CUSTOMERS
| | *** DATA FOR CUSTOMER TABLE
| | ***********************************************************************
| |
1. | 05 CUSTOMER-LAST-NAME PIC X(20). | LAST_NAME | POSITION(001:020) | CHAR
th> |
|---|
|
2. | 05 CUSTOMER-FIRST-NAME PIC X(15). |
FIRST_NAME | POSITION(021:035) | CHAR |
|
3. | 05 CUSTOMER-AGE PIC 999. | AGE | POSITION(036:038) | CHAR |
|
4. | 05 CUSTOMER-PHONE PIC 9(10). |
---PHONE | POSITION(039:048) | CHAR |
|
5. | LOAD_DATE | SYSDATE |   |   | |
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.
CUSTOMER Table Mapping
CUSTOMER's Status Report follows copybook to Oracle column mappings below.
Red - Oracle column not loaded from copybook.
Blue - Highlights DATE columns.
Green - Highlights potential DATE columns (i.e., PIC X(08)).
|   | Copybook Definition | Oracle Column Name | SQL*Loader Position | Type |
| ***********************************************************************
| | *** COBOL COPYBOOK - CUSTOMERS
| | *** DATA FOR CUSTOMER TABLE
| | ***********************************************************************
| | 1. |
05 CUSTOMER-LAST-NAME PIC X(20).
| LAST_NAME | POSITION(001:020) | CHAR
th> |
|---|
| 2. |
05 CUSTOMER-FIRST-NAME PIC X(15). |
FIRST_NAME | POSITION(021:035) | CHAR |
| 3. |
05 CUSTOMER-AGE PIC 999. | AGE | POSITION(036:038) | CHAR |
| 4. |
05 CUSTOMER-PHONE PIC 9(03). | PHONE_NUMBER | POSITION(039:048) | CHAR |
| 5. |
LOAD_DATE | SYSDATE |   |   | |
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!
+1Copybook is a trademark of +1 Software Engineering.
+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.
To Top
|