+1Copybook Users Guide For Oracle and Teradata 1. Introduction
+1Copybook converts COBOL copybooks into Oracle SQL*Loader control files and CREATE TABLE DDL scripts or Teradata CREATE TABLE DDL scripts. Typically a mainframe generates a data set (i.e., a file) to be loaded into a Unix database. Using COBOL declaration statements, the file layout is defined by a COBOL copybook. To load mainframe data, DBAs must convert the COBOL copybook definitions into CREATE TABLE scripts and for Oracle SQL*Loader control files. This is a very time consuming and error prone process, which if done incorrectly, may load erroneous data into a database without any error messages. +1Copybook automates, documents, and optimizes this process.
+1Copybook currently supports Oracle, Microsoft's SQL Server, and Teradata databases. This user's guide has been specifically written for Oracle and Teradata. If using SQL Server, please read +1CopybookTM Users Guide For SQL ServerTM.
1.1 How It's Done Today
To load flat file data into an Oracle database, the Oracle SQL*Loader utility and CREATE TABLE scripts are used. To load flat file data into a Teradata, the CREATE TABLE scripts are used. Typically, a mainframe generates a data set (i.e., a file) to be loaded into a Unix database. Using COBOL declaration statements, the file layout is defined by a COBOL copybook.
Below is one example. Each line in a file consists of the last name (columns 1-20), first name (columns 21-35), age (columns 36-38), and phone number (columns 39-48). The COBOL copybook for this data set looks like:
In this example, each line in the data file is fixed length containing 48 characters. On a Unix system, the new line character delimits the end of each line.
The Oracle CREATE TABLE script is:
CREATE TABLE CUSTOMER (
After the data set has been created on the mainframe, the file is FTP'ed to the Unix system. To load data into the Oracle database, Oracle's sqlldr command is used.
For Teradata databases, only the CREATE TABLE scripts are needed before data can be loaded.
1.2. How Do You Know It's Right?
When a flat file only has four columns defined, generating an Oracle control file or a Teradata CREATE TABLE script is easy and no automation is required. DBAs can manually create these files by hand.
The problem is when COBOL copybooks define hundreds of columns for one or more tables. Now generating a control file or CREATE TABLE script becomes an error prone and time consuming task. If you finish writing a control file and on review notice a mistake towards the beginning of your control file, you have to recalculate a majority of the column definitions all over again.
An Oracle DBA will test a completed control file by loading in data. Unfortunately control files can work even though they contain errors. 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!
If these types of errors are not detected, erroneous data can be loaded into a production or test database and this can impact a company severly. Depending on how long erroneous data has been loaded, the DBA must now backout or update days, weeks, even months worth of data. The application programmers must now determine the impact of the erroneous data to the application during this time period. In addition, customers need to be notified of the problem. This takes time, money, and goodwill away from the project.
+1Copybook optimizes the process. For example, say the COBOL declaration for a value is defined by "PIC S9999V99 COMP-3". In this example, the declaration tells us the data is compressed (using "COMP-3" compression), is a signed data type ('S'), and contains an implied decimal point ('V'). +1Copybook will know this is a compressed value taking up four characters in the data file (POSITION 1:4), the control file data type should be defined as a "DECIMAL (6,2)", and that the CREATE TABLE data type should be a "NUMBER (6,2)" every time it is used.
Another problem in manually generating control files is there usually is very little or no analysis performed nor mapping documents generated showing which COBOL copybook columns load which Oracle table columns. This is a time consuming task, especially after you have spent a long time writing one or more control files, which is usually not done at all.
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 if the COBOL copybook has a column named CREDIT_AMT and in your Oracle database there is 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, or even worse, should be mapped to some esotericly named column, such as TOTAL_CR?
Since such simple mistakes can negatively impact a company, the real question becomes: "How do you know it's right?" How can you prevent errors and ensure every column sent to you is being used and every column defined in the Oracle table is being initialized?
1.3. +1Copybook Solution
An alternative to the manual way is to use +1Copybook. +1Copybook can automate this process by:
quickly generating SQL*Loader control files based on COBOL copybooks and other formats,
generate CREATE TABLE scripts for Oracle and Teradata,
generate external CREATE TABLE scripts using Oracle,
When using +1Copybook to load data into an existing database, +1Copybook can:
perform analysis ensuring each copybook column is used,
perform analysis ensuring each Oracle column is intialized, and
generate color coded mapping HTML and error report documents which can be reviewed.