+1 Software Engineering Faq Copybook

+1Copybook FAQ

Questions:
1. What platforms does +1Copybook run on?
2. Does +1Copybook handle OCCURS clauses?
3. Does +1Copybook create external tables?
4. Why is +1Copybook cost effective?
5. Why did +1 Software Engineering write +1Copybook?

Answers:

1. What platforms does +1Copybook run on?

Microsoft Windows XP, 2003, and 2000 running Services For Unix (SFU) 3.5.

Red Hat

Solaris

SUSE

SFU 3.5 is a free download from Microsoft. Call us in regards to HP/UX support or other platforms not listed above.

To Top

2. Does +1Copybook handle OCCURS clauses?

Yes! 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 does not automatically generate the corresponding columns in the SQL*Loader control file. 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 do one of two things: (1) comment out the OCCURS statement and generate the corresponding columns in the copybook.txt file or (2) define a new table in the copybook.txt file instead.

Here's an example. Say, the copybook contains:

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

In our first approach, the DBA wishes to keep these columns in the original table. To do this, the DBA would need to comment out the OCCURS columns and convert the OCCURS columns in the copybook.txt file to:

/*
05 CUST-CODE-DATA.
   OCCURS 10 TIMES
   INDEXED BY AB-CD.
   10 CUST-CODE             PIC X(03).
   10 CUST-CODE-VALUE       PIC 99999.99.
*/
05 CUST-CODE-DATA.
   10 CUST-eODE-01		PIC X(03).
   10 CUST-CODE-VALUE-01        PIC 99999.99.
   10 CUST-CODE-02		PIC X(03).
   10 CUST-CODE-VALUE-02        PIC 99999.99.
   10 CUST-CODE-03		PIC X(03).
   10 CUST-CODE-VALUE-03        PIC 99999.99.
   10 CUST-CODE-04		PIC X(03).
   10 CUST-CODE-VALUE-04        PIC 99999.99.
   10 CUST-CODE-05		PIC X(03).
   10 CUST-CODE-VALUE-05        PIC 99999.99.
   10 CUST-CODE-06		PIC X(03).
   10 CUST-CODE-VALUE-06        PIC 99999.99.
   10 CUST-CODE-07		PIC X(03).
   10 CUST-CODE-VALUE-07        PIC 99999.99.
   10 CUST-CODE-08		PIC X(03).
   10 CUST-CODE-VALUE-08        PIC 99999.99.
   10 CUST-CODE-09		PIC X(03).
   10 CUST-CODE-VALUE-09        PIC 99999.99.
   10 CUST-CODE-10		PIC X(03).
   10 CUST-CODE-VALUE-10        PIC 99999.99.

Once all of the OCCURS statements have been converted in the copybook.txt file, you can run +1Copybook to generate the control file. The advantage of expanding the OCCURS columns in the copybook.txt file is there will only be one table with all of the data.

However, the disadvantage (and it's a big one!) is the table is not designed well for future maintenance.

Say for example a change is made and instead of 10 customer code values there are now 20. This is a problem. The DBA can append CUST-CODEs 11 through 20 to the end of the existing table. These columns will possibly no longer be next to the previous set of customer code values. To make use of the new columns, the application programs must be updated.

A better solution is to extract the columns within the OCCURS statement and create a new table. To do this using +1Copybook, you modify the copybook.txt file. For example, the copybook.txt file can contain:

CUSTOMER_CODES TABLE

   10 CUST-ID            PIC X(9).
   10 CUST-CODE          PIC X(03).
   10 CUST-CODE-VALUE    PIC 99999.99.

The resulting CREATE TABLE script is:

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

Now if a change is made and 20 or 50 or 100 customer codes are added to the database, there are no changes to the database or tables, only to the data contained in them. And it's more than likely there will be no changes needed to the application software to support additional customer codes.

Optionally, you can also add a counter column to the base table CUSTOMER named, say, CUSTOMER_CODES_COUNTER which contains the number of codes defined for a specific CUST_ID. The tables are still normalized and applications can run faster by knowing in advance whether CUSTOMER_CODES exist for a CUST_ID. It's one less query to run if there is no data in the CUSTOMER_CODES table.

To Top

3. Does +1Copybook create external tables?

Yes! 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.

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

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