+1 Software Engineering®
  Home    Free Coffee    Tech Stuff    Contact Us


+1CopybookTM Users Guide
For OracleTM And TeradataTM

April 2016

+1 Software Engineering LLC®
2390-C Las Posas Road, Suite 438
Camarillo, California 93010
805-322-1497

Preface

+1Copybook is by far the fastest way to convert COBOL copybooks to load its associated data into Oracle, SQL Server, and Teradata databases. +1Copybook generates the CREATE TABLE scripts and control or format files using the minimum amount of bytes to define column data types. Data can be in ASCII or EBCDIC, contain COMP-3 and implied decimal data, and is converted into delimited data files.

Table of Contents

1. Introduction

2. How To Run +1Copybook

3. +1Copybook Inputs

3.1 acryomns.txt 3.1.1 Description 3.1.2 Format 3.1.3 Example
3.2 append_columns.txt 3.2.1 Description 3.2.2 Format 3.2.3 Example
3.3 control_file_datatypes.txt 3.3.1 Description 3.3.2 Format 3.3.3 Example
3.4 copybook.txt 3.4.1 Description 3.4.2 Format 3.4.3 Example
3.5 create_table_datatypes.txt 3.5.1 Description 3.5.2 Format 3.5.3 Example
3.6 create_table_suffix.txt 3.6.1 Description 3.6.2 Format 3.6.3 Example
3.7 database_id.txt 3.7.1 Description 3.7.2 Format 3.7.3 Example
3.8 datatype_override.txt 3.8.1 Description 3.8.2 Format 3.8.3 Example
3.9 default_database.txt 3.9.1 Description 3.9.2 Format 3.9.3 Example
3.10 external.txt 3.10.1 Description 3.10.2 Format 3.10.3 Example
3.11 header.txt 3.11.1 Description 3.11.2 Format 3.11.3 Example
3.12 infile.txt 3.12.1 Description 3.12.2 Format 3.12.3 Example
3.13 override.txt 3.13.1 Description 3.13.2 Format 3.13.3 Example
3.14 prefixes.txt 3.14.1 Description 3.14.2 Format 3.14.3 Example

4. Support for OCCURS and REDEFINES

5. +1DataElements Inputs

5.1 comments/*.txt
5.2 database_id.txt
5.3 database_sizes.txt
5.4 default_database.txt
5.5 index.txt
5.6 indexes.txt
5.7 keys.txt
5.8 row_counts.txt
5.9 tables.txt
5.10 views.txt

6. +1Copybook Outputs

7. Loading Data Scripts

8. +1 Software Engineering LLC Support

Appendix A. COBOL Copybook Data Types Supported

 


+1CopybookTM Users Guide
For OracleTM and Teradata
TM

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:

    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 looks 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 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 (
LAST_NAME                 VARCHAR2(20),
FIRST_NAME                VARCHAR2(15),
AGE                       NUMBER(4),
PHONE                     NUMBER(10)
)
/

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.

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

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.

Below is the output from running +1Copybook for our simple example above.

 Copybook DefinitionOracle Column NameSQL*Loader PositionType
***********************************************************************
*** COBOL COPYBOOK - CUSTOMERS
*** DATA FOR CUSTOMER TABLE
***********************************************************************
1.
05 CUSTOMER-LAST-NAME           PIC X(20).
LAST_NAME
POSITION(001:020)
CHAR
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. The exception is the copybook column CUSTOMER-PHONE 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.

 Copybook DefinitionOracle Column NameSQL*Loader PositionType
***********************************************************************
*** COBOL COPYBOOK - CUSTOMERS
*** DATA FOR CUSTOMER TABLE
***********************************************************************
1.
05 CUSTOMER-LAST-NAME           PIC X(20).
LAST_NAME
POSITION(001:020)
CHAR
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 is:

---
---   Load Script For CUSTOMER Table
---   Oracle SID: CHP
---   Created on: 4/31/2005 3:42 PM
---

OPTIONS(DIRECT=TRUE)

LOAD DATA

INFILE '/u5/u5data/cust.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                              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 as well as CREATE TABLE and external table scripts for Oracle databases.

2. How To Run +1Copybook

The syntax to run +1Copybook for Oracle and Teradata is:

    cb [-bcdfhHlLmnoprsSuvVwx] [-R 99]

where:

    -b  Generate output in both upper and lower case.
    -c  Compare COBOL copybook columns with existing database columns.
    -d  If copybook column contains DATE, DTE, or DT anywhere in its
    name, define column as a DATE datatype.
    -f fn  Use file name (fn) specified instead of copybook.txt.
    -F  Include FILLERS in CREATE TABLE script with suffix counter.
    -h  Display this help message.
    -H  Display help message on +1Copybook configuration files.
    -l  Generate output in lower case.
    -L  Display copybook.txt lines as they are processed.
    -m  Do not display CREATE TABLE data type column in copybook mapping.
    -M  Display MAC addresses for system.
    -n  Add NOT NULL constraint to each column in the CREATE TABLE script.
    -o  Generate Oracle output.
    -O #  Line offset. Specify column number (#) to start reading copybook.
    -p  COMP-3 Packed Decimals in copybook are not compressed.
    -r  Use NUMBER only for COMP-3 data types to avoid rounding.
    -R 99  Load row #. Only one line of data is loaded.
    -s  Generate SQL Server output.
    -S  Stream input datafile. No CRs or LFs in INFILE datafile.
    -t  Generate Teradata output.
    -u  Generate output in upper case (default).
    -v  Display +1Copybook's version and expiration date.
    -V  Display +1Copybook letter version and release dates.
    -w  Do not print warning messages.
    -x  Generate Excel output.

+1Copybook default is to generate CREATE TABLE SQL scripts based on COBOL copybooks found in the copybook.txt file. The "-s" and "-t" options are specific to SQL Server database.

3. +1Copybook Inputs

The following files can be created to configure +1Copybook. The only mandatory file is copybook.txt.

If a '#' sign is found in the first column for any of the above files, the rest of the line is a comment.

To comment out multiple lines, +1Copybook supports /* and */ so long as they both start in column 1.

To generate Oracle SQL*Loader control files and CREATE TABLE scripts or Teradata CREATE TABLE scripts, the following +1Copybook file is mandatory:

  • copybook.txt

All other configuration files are optional and can be used to customize generated control files or CREATE TABLE scripts. These files make it easier to use +1Copybook.

3.1 acryomns.txt

3.1.1 Description

File acryomns.txt allows you define acryomns when generating output in both upper and lower case output ("-b") or in lower case only output ("-l"). For example if the acryomn EFT is seen in the copybook column named TOTAL_EFT_TRANSFER_AMOUNT and the "-b" option is used, the resulting database column name would be Total_Eft_Transfer_Amount. By editing the acryomns.txt file and adding an "_EFT" line, the resulting column name would be Total_EFT_Transfer_Amount instead. The '_' character in front (or behind) the EFT string is used to prevent a column named, say, NUMBER_OF_THEFTS to be changed to Number_Of_ThEFTs.

3.1.2 Format

Acryomn Name

3.1.3 Example

DBA
_EFT
_PA_
_SCCI_
SQL

3.2 append_columns.txt

3.2.1 Description

File append_columns.txt allows you to append additional columns to the control file not found in the copybook when loading data in. For example, this file can be used to specify a LOAD_DATE column initialized using the SYSDATE built in function.

3.2.2 Format

Oracle Table Name Oracle Column Name Create Table Datatype Built-In Function

3.2.3 Example

EMPLOYEE        LOAD_DATE       DATE          SYSDATE
BILLING         LOAD_DATE       DATE          SYSDATE
BILLING         NAME            VARCHAR2(10)  USER

In the above example, the LOAD_DATE column is appended to the EMPLOYEE table and is initialized by the SYSDATE built-in function. In the BILLING table, LOAD_DATE is also appended to the table and is initialized by the SYSDATE built-in function. The datatype in the CREATE TABLE script is specified as DATE. Also appended to the BILLING table is the NAME column, initialized by the USER built-in function, and specified to be a VARCHAR2(10) in the CREATE TABLE script.

3.3 control_file_datatypes.txt

3.3.1 Description

File control_file_datatypes.txt allows you to override the +1Copybook default datatype in the SQL*Loader control file for one or more columns. +1Copybook's default datatypes are CHAR unless the COBOL copybook column name ends with the "DATE" string.

3.3.2 Format

Oracle Table Name Oracle Column Name Datatype

3.3.3 Example

EMPLOYEE        SALARY          FLOAT EXTERNAL
EMPLOYEE        LAST_UPDATE     DATE
EMPLOYEE        GROUP           CONSTANT "UNITED ARROWS"

In the example above, +1Copybook changes SALARY's datatype to FLOAT EXTERNAL datatype, LAST_UPDATE to DATE datatype, and GROUP to a constant value of "UNITED ARROWS" in the control file.

3.4 copybook.txt

3.4.1 Description

File copybook.txt contains the COBOL copybook description for one or more tables. Before each copybook description, the Oracle table name needs to be listed. Blank lines between table descriptions are allowed. A COBOL copybook is usually provided by the mainframe staff who generated the data set to load.

3.4.2 Format

There are two formats supported by +1Copybook: standard format and free format.

Standard Format COBOL Copybook
Before Each Table Description:
table_name TABLE [or TABLE table_name]
 
For Each Line In Table Description:
columns 1-6comment
column 7If column 7 contains a '*' character, entire line is a comment.
columns 8-72column name and datatype description
columns 73-80comment

Free Format COBOL Copybook
Before Each Table Description:
table_name TABLE
 
For Each Line In Table Description:
column name
column name and datatype description

3.4.3 Example

Standard Format COBOL Copybook Example

      CUSTOMER TABLE

00572 ********************************************************************************
00572 *  CUSTOMER COBOL COPYBOOK
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).

      PRODUCT TABLE

00700 ********************************************************************************
00701 *  PRODUCT COBOL COPYBOOK
00703 ********************************************************************************
00704  01 PRODUCT-RECORD.
00705     05 PROD-ID		PIC X(8).
00706     05 PROD-NAME		PIC X(35).
00707     05 PROD-PRICE		PIC S9(4)V9(5) COMP-3.
00708     05 PROD-DESCRIPTION	PIC X(60).
00709     05 PROD-COLOR-IND	PIC XX.
00710     05 PROD-INTRO-DATE	PIC 9(8).
00711     05 PROD-TYPE		PIC XXX.
00712     05 PROD-VOLUME	PIC 9(10).

Free Format COBOL Copybook Example

CUSTOMER TABLE

********************************************************************************
*  CUSTOMER COBOL COPYBOOK
********************************************************************************
01 CUSTOMER-RECORD.
   05 CUSTOMER-LAST-NAME		PIC X(20).
   05 CUSTOMER-FILE-FIRST-NAME		PIC X(15).
   05 CUSTOMER-FILE-AGE			PIC 999.
   05 CUSTOMER-FILE-PHONE		PIC 9(10).

PRODUCT TABLE

********************************************************************************
*  PRODUCT COBOL COPYBOOK
********************************************************************************
01 PRODUCT-RECORD.
   05 PROD-ID		PIC X(8).
   05 PROD-NAME		PIC X(35).
   05 PROD-PRICE	PIC S9(4)V9(5) COMP-3.
   05 PROD-DESCRIPTION	PIC X(60).
   05 PROD-COLOR-IND	PIC XX.
   05 PROD-INTRO-DATE	PIC 9(8).
   05 PROD-TYPE		PIC XXX.
   05 PROD-VOLUME	PIC 9(10).

3.5 create_table_datatypes.txt

3.5.1 Description

File create_table_datatypes.txt allows you to override the +1Copybook default datatype in the CREATE TABLE scripts for one or more columns. +1Copybook's default datatypes are VARCHAR2, NUMBER, or DATE.

3.5.2 Format

Oracle Table Name Oracle Column Name Datatype

3.5.3 Example

EMPLOYEE        MIDDLE_INITIAL  CHAR
EMPLOYEE        EMPLOYEE_ID     NUMBER(10)    NOT NULL
EMPLOYEE        SALARY          NUMBER(10,4)  CHECK(salary < 500000)

In the example above, +1Copybook changes MIDDLE_INITIAL from VARCHAR2(1) to CHAR, EMPLOYEE_ID now has a NOT NULL constraint, and SALARY has a CHECK constraint.

3.6 create_table_suffix.txt

3.6.1 Description

File create_table_suffix.txt allows you to append a suffix to the copybook table names.

3.6.2 Format

Suffix

3.6.3 Example

_HOLD

The suffix found in the create_table_suffix.txt file is "_HOLD". If the copybook table is named ADDRESS, the Oracle table name defined in the CREATE TABLE and control file will be ADDRESS_HOLD. +1Copybook applies the same suffix to all copybook tables.

3.7 database_id.txt

3.7.1 Description

For Oracle, the database_id.txt file contains the ORACLE_SID for the SQL*Loader control files being generated. The value found in the database_id.txt file is used to create or update the html/database_id directory and is used to organize +1Copybook files when more than one SID is defined.

3.7.2 Format

ORACLE_SID

3.7.3 Example

COMPANY

The Oracle SID found in the database_id.txt file is COMPANY. +1Copybook will create or update the html/COMPANY directory to store it's results.

3.8 datatype_override.txt

3.8.1 Description

The datatype_override.txt file allows you to override the +1Copybook default datatypes used in the control file and CREATE TABLE script. For each COBOL definition listed, The default datatypes used for the control file and CREATE TABLE scripts may be made available upon request. The datatype_override.txt file overrides entire datatypes so you don't have to define overrides on a column by column basis.

+1Copybook allows you to override entire datatypes. This is especially useful for Teradata databases where clients may want to define all numeric data types using DECIMAL instead of +1Copybook's defaults of BYTEINT, SMALLINT, INTEGER, and BIGINT.

In addition, +1Copybook can match the PIC definition exactly when it determines whether to override a individual default datatype. As such, a PIC 9(6), a PIC 9(06), and a PIC 9(006) would need three entries defined in the datatype_override.txt file to override each of these occurances if they are used in the copybook.txt file.

To specify the current column name in the control file override string, $COLNAME can be used. When seen in the override string, $COLNAME is replaced by the actual column name. For example, if the override control file datatype for column MY_COLUMN_NAME is defined as:

    RAW(4) NULLIF "$COLNAME"=BLANKS "to_number(:$COLNAME)"

The resulting datatype in the control file will be:

    RAW(4) NULLIF "MY_COLUMN_NAME"=BLANKS "to_number(:MY_COLUMN_NAME)"

The current column name replaces the $COLNAME string when there is a datatype override. The $COLNAME keyword can be in upper or lower case, e.g., $colname.

3.8.2 Format

Copybook Datatype
COMP-#
(optional)
CREATE TABLE DDL Datatype Control File Datatype

3.8.3 Example

In the first example, the default datatypes for a Teradata database are overridden using:

X(1-5)	CHAR(*)
X(6-2000)	VARCHAR(*)
9(1-2)	DECIMAL(2)		DECIMAL(2)
9(3-4)	DECIMAL(4)		DECIMAL(4)
9(5-9)	DECIMAL(9)		DECIMAL(9)
9(10-18)	DECIMAL(18)		DECIMAL(18)
9(19-38)	DECIMAL(38)		DECIMAL(38)

The above default_override.txt file will define a PIC X(4) as CHAR(4) and a PIC X(20) as a VARCHAR(20). For numeric values, a PIC 9(1) will be defined as a DECIMAL(2), a PIC 9(4) as a DECIMAL(4), and a PIC 9(14) as a DECIMAL(18). +1Copybook's default Teradata datatypes use BYTEINT, SMALLINT, INTEGER, and BIGINT depending on the size of the value.

In the second example, six COBOL copybook PIC datatypes are overridden.

PIC 9(6)                        DECIMAL(6)      DECIMAL(6)
PIC 9(6)          COMP-3        DECIMAL(6)      DECIMAL(6)
PIC S9(11)V9(4)                 NUMBER(15,4)    NUMBER(15,4)
PIC S9(11)V9(4)   COMP-3        NUMBER(15,4)    NUMBER(15,4)
PIC 9(05)V02                    NUMBER(7,2)     ZONED(7,2) NULLIF "$COLNAME"
PIC 9(08)         COMP-6        NUMBER(8)       RAW(4) NULLIF "$COLNAME" "to_number(:$COLNAME)"

In the first line, PIC 9(6) usually defaults as a CHAR datatype in the control file and NUMBER(6) in the CREATE TABLE script. But the datatype override defines all PIC 9(6) definitions as DECIMAL(6) in the control file and DECIMAL(6) in the CREATE TABLE script instead.

The second line, PIC 9(6) COMP-3, defaults to DECIMAL(6,0) in the control file and NUMBER in the CREATE TABLE script. The datatype override changes these defaults to DECIMAL(6) in the control file and DECIMAL(6) in the CREATE TABLE script.

The third line, PIC S9(11)V9(4), overrides +1Copybook's defaults of DECIMAL(15,4) in the control file and NUMBER in the CREATE TABLE script with NUMBER(15,4) and NUMBER(15,4), respectively.

The fourth line, PIC S9(11)V9(4) COMP-3, overrides +1Copybook's defaults of CHAR and NUMBER(15,4) with NUMBER(15,4) and NUMBER(15,4).

The fifth line, PIC 9(05)V02, overrides the CREATE TABLE DDL with NUMBER(7,2) and the control file data type override with "ZONED(7,2) NULLIF "$COLNAME", where $COLNAME is replaced with the current column name.

The sixth line, PIC 9(08) COMP-6, overrides the CREATE TABLE DDL with NUMBER(8) and the control file data type override with "RAW(4) NULLIF "$COLNAME" "to_number(:$COLNAME)", where $COLNAME is replaced with the current column name.

It is possible to combine two approaches in a single datatype_override.txt file.

3.9 default_database.txt

3.9.1 Description

The default_database.txt file specifies whether Oracle, SQL Server, or Teradata is the default database. Valid values found in this file are ORACLE, SQLSERVER, and TERADATA, and can be in upper or lower case letters. If the "-o" option for Oracle, "-s" option for SQL Server, or "-t" option for Teradata are not used and the default_database.txt file does not exist, the +1Copybook default is determined by your license. If the "-o", "-s", or "-t" options are used and the default_database.txt file exists, the default_database.txt contents will not be used. To display the current default database, type: "cb -h".

3.9.2 Format

ORACLE or SQLSERVER or TERADATA

3.9.3 Example

ORACLE

3.10 external.txt

3.10.1 Description

The external.txt file allows you to define external tables for one or more tables. An external table allows Oracle to query the data physically found in a file even though the data is not stored in or was loaded into an Oracle tablespace. This feature is especially useful for data warehousing applications.

To define the EMPLOYEE table in the external.txt file, several lines are used. The first line identifies the table by using "EMPLOYEE TABLE" or "TABLE EMPLOYEE". The last line for the EMPLOYEE table needs to be a semicolon, ';', starting on a new line. All lines between the "EMPLOYEE TABLE" and ';' can specify any and all external table keywords and their values.

There are two options supported by +1Copybook. The first option is that +1Copybook searches for "9999" and replaces this RECORDS FIXED value with the actual value. The second option is FIELDS(). +1Copybook fills in the columns defined using their SQL*Loader control file definitions for each Oracle column between the open and closed parentheses. You can also define configuration text between the "FIELDS" keyword and opening parentheses too.

3.10.2 Format

table_name TABLE [or TABLE table_name]
External Table Options and Values
;

3.10.3 Example

Here are the CUSTOMER and EMPLOYEE entries in the external.txt file.

CUSTOMER TABLE
ORGANIZATIONAL EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS (
RECORDS FIXED 9999
FIELDS (
))
LOCATION ('/u01/external_tbls/customer.txt')
)
REJECT LIMIT 1
;

TABLE EMPLOYEE
ORGANIZATIONAL EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS (
RECORDS FIXED 9999
FIELDS (
))
LOCATION ('/u01/external_tbls/employee.txt')
)
;

Here is the CREATE EXTERNAL TABLE script based on the above external.txt file for the CUSTOMER table entry.

/*
   CREATE EXTERNAL TABLE Script For CUSTOMER Table
   Created on: 01-DEC-2006 12:35 PM
   Created by: +1Copybook
*/

CREATE TABLE CUSTOMER
(
CUST_ID                            VARCHAR2(8),
LAST_NAME                          VARCHAR2(20),
FIRST_NAME                         VARCHAR2(12),
MIDDLE_INITIAL                     VARCHAR2(1),
HOME_PHONE                         VARCHAR2(10),
... continued ...
MARITIAL_STATUS                    VARCHAR2(1)
)
ORGANIZATIONAL EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXT_DIR
ACCESS PARAMETERS (
RECORDS FIXED 879
FIELDS (
CUST_ID                            POSITION(001:008)    CHAR,
LAST_NAME                          POSITION(009:028)    CHAR,
FIRST_NAME                         POSITION(029:040)    CHAR,
MIDDLE_INITAL                      POSITION(016:016)    CHAR,
HOME_PHONE                         POSITION(017:027)    CHAR,
... continued ...
MARITIAL_STATUS                    POSITION(878:878)    CHAR
))
LOCATION ('/u01/external_tbls/customer.txt')
)
REJECT LIMIT 1
;

Note the 9999 value in the external.txt file is replaced with the actual fix width length as calculated by +1Copybook in the generated external table.

3.11 header.txt

3.11.1 Description

The header.txt file allows you to customize the top portion of the control file to define control file keyword options for one or more tables. If the table name is found in the header.txt file, the infile.txt and the default configuration are not used.

To customize the EMPLOYEE table in the header.txt file, several lines are used. The first line identifies the table by using "EMPLOYEE TABLE" or "TABLE EMPLOYEE". The last line for the EMPLOYEE table needs to be "INTO TABLE EMPLOYEE". +1Copybook checks the first table name defined matches the INTO TABLE name to avoid any problems from users copy/pasting table definitions in the header.txt file, but forgetting to update the INTO TABLE name part. All lines between the "EMPLOYEE TABLE" and "INTO TABLE EMPLOYEE" can specify any and all control file keywords and their values.

The only option supported is found in the "FIX 9999" part. +1Copybook replaces the 9999 with the actual value when it generates the control file.

3.11.2 Format

table_name TABLE [or TABLE table_name]
Control File Options and Values
INTO TABLE table_name

3.11.3 Example

Here are the CUSTOMER and EMPLOYEE entries in the header.txt file.

CUSTOMER TABLE
-- OPTIONS (DIRECT=TRUE, SILENT=(FEEDBACK))
LOAD DATA CHARACTERSET WE8EBCDIC37
INFILE 'CUSTOMER.DATA' "FIX 9999"
BADFILE 'CUSTOMER.BAD'
DISCARD 'CUSTOMER.DIS'
TRUNCATE
INTO TABLE CUSTOMER

EMPLOYEE TABLE
LOAD DATA
INFILE '/u2/data/EMPLOYEE.DAT' "FIX 9999"
BADFILE '/u2/bad/EMPLOYEE.BAD'
DISCARD '/u2/discard/EMPLOYEE.DIS'
TRUNCATE
INTO TABLE EMPLOYEE

Here is the control file based on the above header.txt file for the CUSTOMER table entry.

---
---   SQL*Loader Control File For CUSTOMER Table
---   Created on: 30-DEC-2006 5:12 PM
---   Created by: +1Copybook
---

-- OPTIONS (DIRECT=TRUE, SILENT=(FEEDBACK))
LOAD DATA CHARACTERSET WE8EBCDIC37
INFILE 'CUSTOMER.DATA' "FIX 879" 
BADFILE 'CUSTOMER.BAD'
DISCARDFILE 'CUSTOMER.DIS'
TRUNCATE
INTO TABLE CUSTOMER
(
CUST_ID                            POSITION(001:008)    CHAR,
LAST_NAME                          POSITION(009:028)    CHAR,
FIRST_NAME                         POSITION(029:040)    CHAR,
MIDDLE_INITAL                      POSITION(016:019)    CHAR,
HOME_PHONE                         POSITION(020:029)    CHAR,
... continued ...
MARITIAL_STATUS                    POSITION(878:878)    CHAR
)

Note the 9999 value in the header.txt file is replaced with the actual fix width length as calculated by +1Copybook in the generated control file.

3.12 infile.txt

3.12.1 Description

File infile.txt is used to:

  • specify the default action as what to do with the existing data found in a table to be loaded,

  • specify the default directory where the load data is found on the system, and

  • specify non-default directory locations and actions for one or more tables to be loaded.

If there is no infile.txt file defined, the default action is "APPEND" and the default directory is the current directory, i.e., "." directory. If there is a header.txt file table entry, the infile.txt file is not used for the tables defined in the header.txt file. Unlike the header.txt file, the infile.txt file can be used to configure several tables at once.

The first line in infile.txt is the default action on what to do with the existing data found in the tables. The four valid values are:

    APPENDAppends data into existing table.
    INSERTInserts data into empty tables. If table is not empty, an error is generated.
    REPLACEReplaces existing table data with new data using SQL DELETE statement.
    TRUNCATEReplaces existing table data with new data using SQL TRUNCATE statement.

Note: REPLACE and TRUNCATE actions should be used with great care as any existing data in the table will be deleted.

The second line specifies the default directory name where the input data files are found. If this line is left blank, +1Copybook will use filename.dat, where filename matches the Oracle table name to be loaded.

To override the above defaults defined on lines 1 and 2 used for all tables, the rest of the infile.txt file starting on line 3 can specify an individual table name, directory name, and action. The action column is optional. If the action is not specified, the action defined on line 1 will be used. If only a directory name is specified, the directory name is APPENDed by the Oracle table name with a ".dat" prefix. A directory name must end in a '/', e.g., "/my/dir/".

Here is an infile.txt file.

    APPEND
    /ora01/data
    CUSTOMER	/u5/data/cust.dat	TRUNCATE
    EMPLOYEE	/u6/data/
    

For all Oracle tables except CUSTOMER and EMPLOYEE, the default action is to APPEND the data into empty tables using table_name.dat files found in the /ora01/data directory. If there was a STORE table in the copybook.txt file, the data would be APPENDed to the existing STORE table and it's data would be found in the /ora01/data/STORE.dat file.

The input data file for the CUSTOMER table is found at /u5/data/cust.dat and the existing contents of the CUSTOMER table will be truncated before loading in the new data.

For the EMPLOYEE table, the data is found in /u6/data/CUSTOMER.dat and the data will be APPENDed to the existing EMPLOYEE table. The file name matches the table name with the .dat suffix.

3.12.2 Format

First Line:
Default Action
Second Line:
Default Directory
Lines 3 On (one per table):
Oracle Table NameDirectory For Load DataDefault Action For Existing Data (optional)

The first line specifies the default action to apply to the existing data. Possible values are INSERT, APPEND, REPLACE, or TRUNCATE. The second line specifies the default directory for load data. Lines 3 on are optional.

3.12.3 Example

TRUNCATE
/u2/load_data
CLAIMS	/u3/claim_data/		APPEND
EMPLOYEE	/u2/load_data/emp.txt

In the above example, the default action on how to handle existing data in the Oracle table is TRUNCATE. The directory where the load data is found is in /u2/load_data. Since the CUSTOMER table is not defined later in this file, the control file for CUSTOMER table would TRUNCATE the existing CUSTOMER table contents, then load data from /u2/load_data/CUSTOMER.dat. The file name matches the Oracle table name with the ".dat" prefixed.

For the CLAIMS table, the data to be loaded is found at /u3/claim_data/CLAIMS.dat and will be APPENDed to the existing data found in the CLAIMS table.

For the EMPLOYEE table, the data to be loaded is found at /u2/load_data/emp.txt and the data in the existing EMPLOYEE table will be truncated first.

3.13 override.txt

3.13.1 Description

File override.txt allows you to override the column name, column datatype, or both the column name and datatype. The default database column name and database datatype are derived from the COBOL copybook.

The COBOL Copybook Column Name field needs to match the string found in the COBOL Copybook. Be careful to distinguish between the '-' character commonly used in COBOL copybooks and the '_' used for Oracle column names. If the copybook uses the '-' character, this is the character to use in the Copybook Column Name field. You can cut and paste the copybook column name from the COBOL copybook and paste it's contents into the override.txt file.

Also note if the create_table_suffix.txt file is defined, the suffix found in this file needs to be added to the Table Name field in the override.txt file. For example, if the create_table_suffix.txt file contains "_HOLD" and the table name in the copybook.txt file is "CUSTOMER", the Table Name field, which is the first field in the override.txt file, should be "CUSTOMER_HOLD".

3.13.2 Format

Oracle Table NameCopybook Column NameOracle Column NameOracle Column Datatype (optional)

3.13.3 Example

INVENTORY_TABLE   COST-OF-PRODUCE   Produce_Cost 
INVENTORY_TABLE   DATE-OF-DELIVERY   Date_Of_Delivery   Number(6)
EMPLOYEEFNAMEFirst_Name 
EMPLOYEELNAMELast_Name 
EMPLOYEEMNAME Middle_Initial 
SALESNUMB-ITEMS number_of_itemsNUMBER(15) 
SALESSALES-DATEsales_datedate

The first entry for INVENTORY_TABLE changes the column named COST-OF-PRODUCE in the copybook to Produce_Cost in the Oracle table. The second entry for INVENTORY_TABLE changes the case of DATE-OF-DELIVERY from upper case to mixed case of Date_Of_Delivery and sets the datatype to Number(6). The case of the letters in the override.txt file override whatever the default or specified case is for the rest of the table. (Just something to remember when you think the "-b" or "-l" options aren't quite working the way you might think.)

The EMPLOYEE table entries rename FNAME to First_Name, LNAME to Last_Name, and MNAME to Middle_Initial. The datatypes are not changed and are determined by the copybook's datatypes.

The first SALES table entry renames NUMB-ITEMS to number_of_items and changes the datatype to be NUMBER(15). The second SALES table entry changes SALES-DATE to sales_date (which would have occurred anyways if the "-l" option was selected) and changes the datatype to date. This last example shows the need to repeat the column name when only the datatype needs to be changed.

3.14 prefixes.txt

3.14.1 Description

File prefixes.txt informs +1Copybook to truncate one or more characters from the COBOL copybook column names. This useful feature allows you to strip a prefix off of all COBOL copybook columns to generate the CREATE TABLE script or is used to match groups of COBOL copybook columns with their Oracle table column name counterparts. A COBOL copybook typically will prefix all column definitions to help group and identify columns for a copybook table. To assist in mapping copybook columns to Oracle table columns, the prefixes found on COBOL copybooks can be truncated allowing the rest of the column name to match an Oracle table column name.

Sometimes COBOL copybooks extract information from different sources and each source is identified by its own prefix. For example, columns in the CUSTOMER copybook might start with CUST-, CUST-WEST-, and CUST-EAST-. To truncate all three of these prefixes off of the copybook column name, you can define the following entries in the prefixes.txt file:

CUSTOMER	CUST-WEST-
CUSTOMER	CUST-EAST-
CUSTOMER	CUST-
The first prefix which matches the start of a COBOL copybook column will be used. As such, the order of these entries is important. If CUST- was listed first, it would be used for COBOL copybook column names starting with CUST-, CUST-WEST-, and CUST-EAST-. For example if the COBOL copybook name was CUST-WEST-CODE, the resulting Oracle column name would be WEST-CODE. But if the order matches the list above, the resulting Oracle column name would be CODE.

If three columns in the COBOL copybook are called CUST-WEST-CODE, CUST-EAST-CODE, and CUST-CODE, the resulting table would have three Oracle columns named CODE. This can be seen when you list the column names alphabetically or try to run the generated CREATE TABLE script which will generate an error. If two or more columns have the same column name, you can use the override.txt file to rename duplicate column names.

Although used less often, you can also replace the truncated prefix with a new prefix. This is useful when Oracle table column names are prefixed. For example, each column in the EMPLOYEE Oracle table may be prefixed by EMP-, e.g., EMP_LAST_NAME and EMP_AGE. If the copybook prefix for each COBOL copybook column name is EMPLOYEE- for the EMPLOYEE copybook and most columns in the EMPLOYEE Oracle table start with EMP-, the EMPLOYEE entry in the prefixes.txt file is:

EMPLOYEE        EMPLOYEE-         EMP-

3.14.2 Format

Table NamePrefix to delete from all
copybook column names
Prefix to replace deleted
prefix with (optional)

3.14.3 Example

CUSTOMER_TABLE CUST-
EMPLOYEE EMPLOYEE- EMP-

The CUSTOMER table entry will truncate the CUST- prefix from all COBOL copybook columns defined for the CUSTOMER table before matching the copybook column to the Oracle table column. (Note the hypen ('-') is being used to match the copybook column name and not the underbar character ('_').)

The EMPLOYEE table entry will truncate the EMPLOYEE- prefix from all COBOL copybook columns defined for the EMPLOYEE table and replace EMPLOYEE- with EMP- before matching COBOL copybook columns with Oracle columns found in the EMPLOYEE table.

4. Support for OCCURS and REDEFINES

+1Copybook supports both OCCURS and REDEFINES which are commonly found in COBOL copybooks.

4.1 Support for OCCURS and Child Tables

To represent an array of data, COBOL copybooks use the OCCURS statement.

For simple OCCURS statements, +1Copybook automatically generates the corresponding columns. For example, if the COBOL copybook contains:

    05 CAT-OF-SERVICE PIC XXX OCCURS 5.

+1Copybook will generate:

CAT_OF_SERVICE_1		VARCHAR2(3);
CAT_OF_SERVICE_2		VARCHAR2(3);
CAT_OF_SERVICE_3		VARCHAR2(3);
CAT_OF_SERVICE_4		VARCHAR2(3);
CAT_OF_SERVICE_5		VARCHAR2(3);

For multi-column OCCURS statements, +1Copybook default is to automatically generate the corresponding columns in the SQL*Loader control file.

However, columns found within an OCCURS statement usually should be defined as a new table in the copybook.txt file. To handle the multi-column OCCURS statements, DBAs can define CHILD tables. Here's an example. Say, the copybook contains the following segment in a large COBOL copybook:

05 CUST-CODE-DATA
   OCCURS 10 TIMES
   INDEXED BY AB-CD.
   10 CUST-CODE         PIC X(03).
   10 CUST-CODE-VALUE   PIC 99999.99.

You can add "CHILD CUSTOMER_CODES" and "CHILD END" to create a child table named CUSTOMER_CODES.

CHILD CUSTOMER_CODES
05 CUST-CODE-DATA.
   OCCURS 10 TIMES
   INDEXED BY AB-CD.
   10 CUST-CODE             PIC X(03).
   10 CUST-CODE-VALUE       PIC 99999.99.
CHILD END

Now the CREATE TABLE script and control file for both the CUSTOMER and CUSTOMER_CODES tables will be created.

The resulting CREATE TABLE script for CUSTOMER_CODES is:

CREATE TABLE CUSTOMER_CODES (
CUST_ID                  VARCHAR2(9),
OCCURS_ID		 NUMBER(4),
CUST_CODE                VARCHAR2(3),
CUST_CODE_VALUE          DECIMAL(8,2)
)

By default, the first column defined in the COBOL copybook is used as the key column in the child table and is followed by the OCCURS_ID column to handle the number of occurances. For example, CUST_ID is the first column defined in the COBOL copybook, and as such, is the key column defined for the CUSTOMER_CODES table above.

To define a different key or more than one key for a CHILD table, you can define entries in the keys.txt file. For example:

CUSTOMER_CODES	KEY1_COLUMN_NAME
CUSTOMER_CODES	KEY2_COLUMN_NAME

By defining CHILD tables to create new tables, if new customer codes are added to the database, neither the database tables nor the application needs to be significantly changed. The only likely changes would be to handle the new customer codes, but no change in how the customer codes are read in.

4.2 Support for REDEFINES

COBOL copybooks use REDEFINES to redefine how data in specific columns can be defined differently.

In the data file, certain data columns can be used to store different data types or to divide a previously defined variable into two or more variables.

    10 NAME		PIC X(36).
    10 FIRST_LAST REDEFINES NAME.
       15  LAST_NAME	PIC X(20).
       15  FIRST_NAME	PIC X(15).
       15  MIDDLE_INITIAL   PIC X.
    

In the above example, NAME is defined as 36 characters. But the same space found in the data file is also futher refined into 20 characters representing LAST_NAME, 15 characters representing FIRST_NAME, and one character representing the MIDDLE_INITIAL. There is only 36 characters in the data file for both of these COBOL copybook definitions. As such in this example, the easiest way to support REDEFINES is simply to comment out the less detailed description of the data. For the above example, we could comment out the NAME column and REDEFINES lines.

    /*
    10 NAME		PIC X(36).
    10 FIRST_LAST REDEFINES NAME.
    */
       15  LAST_NAME	PIC X(20).
       15  FIRST_NAME	PIC X(15).
       15  MIDDLE_INITIAL   PIC X.
    

Now +1Copybook will only generate column names for LAST_NAME, FIRST_NAME, and MIDDLE_INITIAL.

REDEFINES are also used to define different data types for the same columns found in a data file. In this case, you may need to divide a single data file into two or more smaller data files where each line of data is defined by the same data types. You can then edit the copybook.txt file to match the contents of the one data file by commenting out any other REDEFINES statements and running +1Copybook to generate the control file. Then you can repeat this process for the other data files to ensure the data file contents matches the copybook.txt file and generate the next control file using +1Copybook.

How can you divide one data file into two or more data files based on REDEFINES?

Below is one example.

% cat divide_data_file.sh

awk '{

if ((substr($0,136,4) == "1963") && (substr($0,38,1)=='2'))
	print > "1963.txt"
else
  if (substr($0,$136,4) == "1974")
	print > "1974.txt"
else
  if (substr($0,$136,4) == "1988")
	print > "1988.txt"
else
	print > "other.txt"
}' Building.txt

In the above example, the year is found in column 136 with a length of 4 characters. If the year is "1963" and if column 38 is a '2' (as an example), the line of data from Building.txt is placed into the 1963.txt file. If the year is "1974", the line of data from Building.txt is placed into the 1974.txt file. If the year is "1968", the line of data is placed into the 1968.txt file. And if there is no match to any of the above criteria, the line of data is copied into the other.txt file.

Once you have the data files containing the same data types and have generated the corresponding control files that match how to load each data file, you're ready to start loading in the data from each file.

Another way to handle REDEFINES is supported by Oracle's SQL*Loader utility. You will still need to edit the copybook.txt file to match the data types for the REDEFINES and run +1Copybook, but you can load the data from one file and place each control file in one file.

Below is an example.

LOAD DATA
INFILE 'BUILDING.txt'
BADFILE 'BUILDING.bad.txt'

REPLACE

INTO TABLE BUILDING_1963
WHEN (138)='6' AND (139)='3'
(
PROPERTY_ID	POSITION(1:10)          CHAR,
PROPERTY_TYPE	POSITION(11:14)         CHAR,
. . .
CODE		POSITION(158:162)       CHAR
)

INTO TABLE BUILDING_1974
WHEN (138)='7' AND (139)='4'
(
PROPERTY_ID	POSITION(1:10)          CHAR,
PROPERTY_TYPE	POSITION(11:14)         CHAR,
. . .
CODE_CHANGE	POSITION(158:162)       CHAR
)

INTO TABLE BUILDING_OTHER
(
PROPERTY_ID	POSITION(1:10)          CHAR,
PROPERTY_TYPE	POSITION(11:14)         CHAR,
. . .
CODE_CHANGE	POSITION(158:162)       CHAR
)

In this example, the WHEN clause is used. If column 138 equals '6' and column 139 equals '3', which uniquely represents 1963 in the data, the row of data stored in the BUILDING.txt file is loaded into the BUILDING_1963 table. Similarly when column 138 equals 7 and column 139 equals 4, the row of data is loaded into BUILDING_1974. Otherwise, the row of data is loaded into the BUILDING_OTHER table.

This example is actually an important example. For example, using this approach you can load data for two or more tables with radically different data types defined where the first few columns can specify which table to load. The only condition being that the length of each row must be the same. As such, mainframe extracts will pad the shorter rows of data with blanks to ensure each line of data is the same length.

Here's an example:

LOAD DATA
INFILE 'COMPANY_DATA.txt'
BADFILE 'COMPANY_DATA.bad.txt'

REPLACE

INTO TABLE STORE
WHEN (1)='1'
(
STORE_ID	POSITION(1:10)		CHAR,
STORE_NAME	POSITION(11:41)		CHAR,
STORE_CITY	POSITION(42:62)		CHAR,
. . .
STORE_CODE	POSITION(158:162)	CHAR
)

INTO TABLE EMPLOYEE
WHEN (1)='2'
(
SSN		POSITION(1:9)		CHAR,
CODE_VALUE	POSITION(11:14)		CHAR,
. . .
RATE		POSITION(100:102)	CHAR
)

INTO TABLE CUSTOMER
WHEN (1)='3'
(
CUSTOMER_CODE	POSITION(1:10)		CHAR,
CARD_ID		POSITION(11:20)		CHAR,
. . .
DISCOUNT	POSITION(99:104)	CHAR
)

In the example above, data found in COMPANY_DATA.txt file which is used to load the EMPLOYEE and CUSTOMER tables needs to be padded with blanks to ensure it matches the length used to load in the STORE table.

5. +1DataElements Inputs

+1Copybook calls +1DataElements. As such, all +1DataElements input files can also be used to configure +1Copybook output. A brief synopsis for each of the +1DataElements configuration files follows. See the +1DataElements Users Guide for more information.

5.1 comments/*.txt The .txt files found in the comments directory can be used to comment the tables and column names. You can write a description, link to a URL, specify the column format, or define values and descriptions for a column.
5.2 database_id.txt For Oracle databases, contains the ORACLE_SID. For SQL Server databases, contains the SQL Server Database Identifier.
5.3 database_sizes.txt For Oracle, allows you to set the DB_BLOCK_SIZE, PCT_FREE, DATE, and NUMBER database attributes used to calculate column sizes, average row sizes, and average table size.
5.4 default_database.txt Defines whether Oracle or SQL Server is the default database.
5.5 index.txt Used to configure how the html/index.html file looks.
5.6 indexes.txt The i.sql script generates the indexes.txt file.
5.7 keys.txt Columns in this file are listed first in the CREATE TABLE scripts.
5.8 row_counts.txt Lists the number of rows for one or more tables. Used to estimate table and database size.
5.9 tables.txt The t.sql script generates the tables.txt file.
5.10 views.txt The v.sql script generates the views.txt file.

6. +1Copybook Outputs

After running +1Copybook, generated output can be found in four directories: CONTROL_FILES, CREATE_TABLES, EXCEL, and html.

6.1 CONTROL_FILES Directory

The Oracle control files generated by +1Copybook are found in the CONTROL_FILES/database_id directory, where database_id is the ORACLE_SID as defined by the database_id.txt file.

When using +1Copybook with existing tables, you first want all of the "copybook column is not used" and "oracle column is not initialized" warnings to be resolved. Once resolved and the resulting +1Copybook output reviewed, the generated SQL*Loader control file can be used to load in Oracle data. File names found in the CONTROL_FILES/database_id/* directory follow the following convention:

    table_name.ctl

For example, the CUSTOMER control file can be found in file CONTROL_FILES/COMPANY/CUSTOMER.ctl. The contents of this file follows:

---
---   Load Script For CUSTOMER Table
---   Oracle SID: CHP
---   Created on: 22-DEC-2006 3:42 PM
---

OPTIONS(DIRECT=TRUE)

LOAD DATA

INFILE '/u5/u5data/cust.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                              POSITION(039:048)    CHAR,
LOAD_DATE                          SYSDATE
)

Comment lines are prefixed with "---" characters. If a column name is commented out, this typically indicates a problem which needs to be addressed.

6.2 CREATE_TABLES Directory

The CREATE_TABLES directory contains the CREATE TABLE scripts for each copybook table. The CREATE_TABLES directory contains two subdirectories named database_id and database_id.sorted, where database_id is the ORACLE_SID defined in the database_id.txt file. The database_id directory contains the CREATE TABLE scripts which typically match the column order as found in the COBOL copybook. The database_id.sorted directory contains the CREATE TABLE scripts with the column names in sorted order.

Below is an example for the EMPLOYEE table with the "_HOLD" suffix appended in the "as defined" order. The "_HOLD" suffix is defined in the create_tables_suffix.txt file.

CREATE TABLE EMPLOYEE_HOLD (
EMP_ID                             VARCHAR2(10),
COMPANY_ID                         VARCHAR2(25),
LAST_NAME                          VARCHAR2(40),
FIRST_NAME                         VARCHAR2(40),
MR                                 VARCHAR2(4),
TITLE                              VARCHAR2(80),
ADDRESS                            VARCHAR2(60),
ADDRESS2                           VARCHAR2(60),
CITY                               VARCHAR2(40),
STATE                              VARCHAR2(40),
ZIP                                VARCHAR2(10),
COUNTRY                            VARCHAR2(40),
PHONE                              VARCHAR2(40),
FAX                                VARCHAR2(40),
EMAIL                              VARCHAR2(80),
HIRE_DATE                          DATE,
DATE_OF_BIRTH                      VARCHAR2(8),
LAST_REVIEW                        VARCHAR2(8),
LAST_UPDATE                        VARCHAR2(8)
)
/

6.3 EXCEL Directory

The EXCEL directory contains the COBOL copybook mapping ready to be loaded into Microsoft Excel. The EXCEL directory is created when the "-x" option is used. To load into Excel, simply open the file in Excel and accept all defaults. The columns in the ".txt" files are separated by the tab characters.

In addition, the contents of the override.txt file is in the EXCEL directory and is ready to be loaded into Excel. This file can be viewed in Excel to review which columns do not match the column name or datatype as specified by the COBOL copybook.

The contents of the ".txt" files for Excel can be viewed using a web browser. A directory which matches the name found in the database_id.txt file is created. In this directory, an index.html file lists the HTML files for each table which can be viewed. The contents found in an HTML file matches the contents found in the corresponding Excel file.

6.4 html Directory

For each Oracle SID, the html directory contains:

  1. an HTML copy of the generated control files,

  2. mapping documents showing which copybook column maps to which Oracle table column name,

  3. analysis showing which copybook columns are not being used and which Oracle columns are not being initialized by the copybook, and

  4. a Copybook To SQL*Loader Control File Mapping Report.

The SQL*Loader Control File Mapping Report shows for each Oracle table which copybook columns are not used and which Oracle columns are not initialized.

To view the +1Copybook Overall Status Report, visit:

    html/database_id/index.html

where database_id is the named ORACLE_SID found in the database_id.txt file.

From the index.html file, the copybook mapping file (e.g., CUSTOMER.map.html) and control file (e.g., CUSTOMER.ctl.html) can be displayed. The Copybook To SQL*Loader Control File Mapping Report, stored in report.html, can also be displayed. +1DataElements can integrate with +1Copybook output.

7. Loading Data Scripts

7.1 load Command

Using SQL*Loader to perform the actual load, the load script loads in data for one table. The input to the load script is the table name and Oracle SID. Each time the load script is run, an entry is added to the load.log to keep track of when, who, and whether the load was successful. Using this script to load in all data, you can check to see what tables have been loaded and when by using the showloads command.

For example to load in the ADDRESS copybook into the ADDRESS_HOLD table, you can type:

    load ADDRESS_HOLD TEST10

where ADDRESS_HOLD is the table name to load and TEST10 is the ORACLE_SID.

7.2 loadscript Command

To list all loads applied to the database, you can use the showloads command. There are no options to this command.

Here is sample output from the showloads command:

% showloads
LOAD: Thu Dec 10 13:16:16 PDT 2006      load ADDRESS_HOLD TEST10
LOAD: Thu Dec 12 09:26:01 PDT 2006      load ADDRESS_HOLD TEST10
LOAD: Thu Dec 12 20:27:35 PDT 2006      load ADDRESS_HOLD TEST10
LOAD: Fri Dec 15 16:56:09 PDT 2006      load EMPLOYEE_HOLD TEST10
LOAD: Fri Dec 16 17:26:20 PDT 2006      load CUSTOMER_HOLD TEST10
LOAD: Fri Dec 16 20:20:31 PDT 2006      load ADDRESS_HOLD TEST10

To show the history for one table, you can type:

% showloads | grep EMPLOYEE_HOLD
LOAD: Fri Dec 15 16:56:09 PDT 2006      load EMPLOYEE_HOLD TEST10

8. +1 Software Engineering LLC Support

For technical support, call:

    805-322-1497
    Monday-Friday
    8 AM-5 PM PST

or e-mail us at support4u@plus-one.com

Using technical support, you can:

  1. Discuss problems or ask questions to knowledgeable support personnel.
  2. Receive updates and releases for products originally purchased.
  3. Access customer-only documentation, such product presentations, white papers, and user manuals, on our web site.

All products include one year of free technical support. Extended support hours can be arranged.


Appendix A. +1Copybook's COBOL Copybook Data Types Supported

+1Copybook supports the vast majority of COBOL data types. To see a complete list, contact +1 Software Engineering LLC.

To override a default Oracle datatype for a single column using +1Copybook, you can edit the override.txt file and specify a new datatype for each column. To override an entire datatype used by +1Copybook in the control file and/or CREATE TABLE scripts, you can edit the default_datatypes.txt file. Data type defined in the default_datatypes.txt file will change all columns that match the new COBOL copybook definition found in the default_datatypes.txt file.

To Top


+1Copybook Users Guide For Oracle and Teradata, February 2016

+1Copybook and +1DataElements are trademarks of +1 Software Engineering LLC.

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

Microsoft, Excel, and SQL Server are trademarks or registered trademarks of Microsoft Corporation.

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

Teradata is a trademark of Teradata Corporation.

All other product names are trademarks of their respective holders.

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