+1 Software Engineering LLC Faq Copybook

/*
   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
;

To Top

4. Why is +1Copybook cost effective?

Here's an actual at work conversation that says it all!

A manager asked, "How long will it take to integrate the copybook changes and generate new control files?"

The DBA answered, "With +1Copybook, it will take about 15 minutes per table."

The manager then asked, "How long will it take without +1Copybook?"

The DBA answered, "Without +1Copybook? Well, it would take about 1 or 2 days per table."

Why?

First, most tables were 200+ columns. If one or more changes are made to the copybook, the entire Oracle SQL*Loader control file may need to be rewritten. If there is only a small change made towards the top of the copybook, the entire Oracle control file needs to be recalculated from that point on. Determining the new column sizes takes a long time, each time.

Second, when you are done, you now have to go back and manually verify one or more times you did it correctly. Little mistakes can quickly make database data inaccurate.

Third, when writing control files based on a COBOL copybooks, the DBA needs to convert every COBOL data type to determine how many bytes it takes in the input file and what its POSTITION values should be, what data type to use in the control file, and what data type should be used in the CREATE TABLE script. For example, a COBOL column defined as a "PIC S9(10)V9(2) COMP-3" takes 7 bytes for an example POSITION (001:007), should be defined as a DECIMAL(12,2) in the control file, and a NUMBER(12,2) in the CREATE TABLE script. Due to loading problems, +1Copybook supports options to prevent rounding issues for certain data types.

Typically, there's no analysis done beyond checking column lengths and testing is usually done by loading data into a test database. There is usually no written documentation to describe the mappings between the copybook and control files. There is usually no review performed by fellow DBAs. If the data loads, the data is assumed to be loaded correctly.