3.3.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).
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 VARCHAR(3);
CAT_OF_SERVICE_2 VARCHAR(3);
CAT_OF_SERVICE_3 VARCHAR(3);
CAT_OF_SERVICE_4 VARCHAR(3);
CAT_OF_SERVICE_5 VARCHAR(3);
For multi-column OCCURS statements,
+1Copybook automatically generates the corresponding columns in the
CREATE TABLE script and format 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:
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 format 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 VARCHAR(9),
OCCURS_ID INT,
CUST_CODE VARCHAR(3),
CUST_CODE_VALUE DECIMAL(7,2)
)
Now if a change is made and 20, 50, or 100 customer codes are added to the database,
the database tables do not need to change.
And it's more than likely there will be no changes needed to the application software to
support additional customer codes too.
By default, the first column defined in the COBOL copybook is used as the key column in the
child table.
For example, CUST_ID is the first column defined in the COBOL copybook.
To define a different key or more than one keys for a CHILD table,
you can define entries in the keys.txt file.
For example:
CUSTOMER_CODES COLUMN_KEY1_NAME
CUSTOMER_CODES COLUMN_KEY2_NAME
3.4 create_table_suffix.txt
3.4.1 Description
File create_table_suffix.txt allows you to append a suffix to the copybook table
names when the CREATE TABLE (-c) option is used.
3.4.2 Format