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


+1CopybookTM Users Guide
For SQL ServerTM

April 2016

+1 Software Engineering LLC®
2390-G 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 copybook.txt 3.3.1 Description 3.3.2 Format 3.3.3 Example
3.4 create_table_suffix.txt 3.4.1 Description 3.4.2 Format 3.4.3 Example
3.5 database_id.txt 3.5.1 Description 3.5.2 Format 3.5.3 Example
3.6 default_db.txt 3.6.1 Description 3.6.2 Format 3.6.3 Example
3.7 delimiters.txt 3.7.1 Description 3.7.2 Format 3.7.3 Example
3.8 deli_delimiters.txt 3.8.1 Description 3.8.2 Format 3.8.3 Example
3.9 dos_paths.txt 3.9.1 Description 3.9.2 Format 3.9.3 Example
3.10 override.txt 3.10.1 Description 3.10.2 Format 3.10.3 Example
3.11 prefixes.txt 3.11.1 Description 3.11.2 Format 3.11.3 Example

4. +1Copybook Outputs

5. +1 Software Engineering LLC Support

 


+1CopybookTM Users Guide
For SQL ServerTM

1. Introduction

Writing SQL Server format files and CREATE TABLE scripts based on COBOL copybooks is an intensive, error prone, and time consuming task. But today with +1Copybook, the process is significantly faster, highly automated, and HTML mapping documents generated. If changes are made to the original copybooks, turn around time in recreating scripts is much faster. For example, +1Copybook was used to load EBCDIC data defined by 70 COBOL copybooks. +1Copybook was used and completed the task in less than three weeks. The alternative was to hire remote programmers for a three month time period for 24 hour shifts using SSIS.

+1Copybook currently supports Microsoft's SQL Server, Oracle, and Teradata databases. This user's guide has been specifically written for SQL Server. If using Oracle or Teradata, please read +1CopybookTM Users Guide For Oracle and TeradataTM.

1.1 How It's Done Today

To load flat EBCDIC or plain text data into an SQL Server database, the Bulk-Copy Program (BCP) utility or BULK INSERT Transact-SQL statement are used. Typically, a mainframe generates a data set (i.e., a file) to be loaded into a 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 SQL Server DBA needs to write a CREATE TABLE script to match the COBOL copybook. Once the table is created, BCP and BULK INSERT use a default format file to load data. The main limitation of using a default format file is the column order and datatype in the flat file must match the column order and datatype in the SQL Server table. You can see the default format file used by running:

    C:\ bcp "Northwind.dbo.Orders" format orders.txt -f orders.fmt -c
    C:\ type orders.fmt

The above commands create the format file orders.fmt for the Northwind database which you can view using the "type" command.

After the data set has been created on the mainframe, the file is FTP'ed to the system running SQL Server. To load data into the SQL Server database using BCP, you can type:

    C:\ bcp Northwind.dbo.orders IN orders.dat -t "," -c -E
    

where "-t" specifies the field terminator to be a comma, "-c" specifies the data import use character data types instead of SQL Server internal data types, and "-E" specifies that BCP load identity columns with values from the data file.

You may also need to specify the server name, username, and password too if Windows authentication isn't being used.

To load data into the SQL Server database using BULK INSERT within the Transact-SQL utility, you can type:

    use Northwind
    go
    
    bulk insert Northwind.dbo.[Orders]
    from 'D:\data\Orders.dat'
    go
    

Typically, you would write and run a script when loading in data.

The initial COBOL copybook provided to an SQL Server DBA will not be normalized. The DBA needs to modify the COBOL copybook, normalize its contents into one or more SQL Server tables, and provide these changes to those who create the flat files on the mainframe. As such, the hardest part is to write the CREATE TABLE scripts based on the COBOL copybooks.

For the CUSTOMER table above, the CREATE TABLE script is:

    CREATE TABLE CUSTOMER (
    LAST_NAME                          	varchar(20),
    FIRST_NAME                         	varchar(15),
    AGE                                	smallint,
    PHONE                              	int
    )
    

1.2. How Do You Know It's Right?

When a flat file only has four columns defined, generating an SQL Server CREATE TABLE script is easy and no automation is required. The problem is when COBOL copybooks define hundreds of columns for one or more tables. Now generating a format file or CREATE TABLE script becomes an error prone and time consuming task.

If mapping 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.

Another problem in manually writing CREATE TABLE scripts is there usually is very little or no analysis performed nor mapping documents generated showing which COBOL copybook columns map to which SQL Server table columns.

Since such simple mistakes can negatively impact a company, the real question becomes: "How do you know it's right?"

1.3. +1Copybook Solution

An alternative to the manual way is to use +1Copybook. +1Copybook can automate this process by:

  • quickly generating CREATE TABLE scripts,

  • quickly generating the corresponding FORMAT FILEs,

  • ensure each column is sized correctly for the data it will hold, 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.

CUSTOMER Copybook Mapping
Table - Sorted Table - Copybook - Create Table - Row Size - Back To Table Index
Red- OCCURS seen.
Blue- Highlights DATE columns.
Brown- Highlights potential DATETIME or MONEY columns.
Orange- Redefine columns.
Purple- Override columns and data types.
Green- Comments.
 Copybook DefinitionColumn NameColumn Data Type
1.
00572 ********************************************************************************
  
2.
00572 *  COBOL COPYBOOK - CUSTOMERS
  
3.
00572 *  DATA FOR CUSTOMER TABLE
  
4.
00572 ********************************************************************************
  
5.
00572  01 CUSTOMER-RECORD.
  
6.
00573     05 CUSTOMER-LAST-NAME		PIC X(20).
LAST_NAME varchar(20)
7.
00574     05 CUSTOMER-FIRST-NAME	PIC X(15).
FIRST_NAME varchar(15)
8.
00575     05 CUSTOMER-AGE		PIC 999.
AGE smallint
9.
00576     05 CUSTOMER-PHONE	        PIC 9(10).
PHONE int




The above example is a very simple example. +1Copybook has been used to generate very large CREATE TABLE scripts based on COBOL copybooks for SQL Server databases. For tables exceeding SQL Server's 1024 maximum number of columns, +1Copybook supports a split table option to divide the one large table into two smaller tables.

2. Project Directory Structure
Any directory can be a project directory. +1Copybook configuration files, like database_id.txt, and touch files, like NODELI, are found here.

Subdirectories in project directory:

COPYBOOKS Directory where each file contains one (or more) copybooks. Each copybook has TABLE table_name as first line.
CREATE_TABLE Contains generated CREATE TABLE scripts.
DATA Directory that contains data. Subdirectories in DATA, like data_03162016, can contain data files for each table, like table_name_03162016.txt.
html Open index.html to display generated HTML output for conversions. HTML includes tables, sorted tables, copybook mapping, format files, sizing, and date/time table last loaded.
htmlPROJECT Saved HTML directory for project PROJECT.
FORMAT_FILES Contains generated FORMAT files.


2. How To Run +1Copybook

The syntax to run +1Copybook is:

    cb [-#0bdDfFhHlLmnoRstuvVwx]

where:

    /* */
    -# 99  Number of columns to generate in format file.
    -] 99  Split table > 1024 columns into two tables. First table is 99 columns.
    -0  Set starting field byte to 0. Default is 1.
    -b  Generate output in both upper and lower case.
    -d  If copybook column contains DATE, DTE, or DT anywhere in its
    name, define column as a DATE datatype.
    -D  Do not create delimiter (.deli) files.
    -f  Specify COBOL copybook format is in free format.
    -F  Do not print the first line of data in copybook HTML mapping file.
    -h  Display this help message on usage.
    -H  Display help message on +1Copybook configuration files.
    -l  Generate output in lower case.
    -L  Display copybook.txt lines as they are processed.
    -m  Add CREATE TABLE data type column to copybook mapping.
    -n  Add NOT NULL constraint to each column in the CREATE TABLE script.
    -o  Generate Oracle output.
    -R 99  Load row number 99. Only one line of data is loaded.
    -s  Generate SQL Server output.
    -S  Stream input datafile used. No CRs or LFs in INFILE datafile.
    -t  For SQL Server, use SMALLDATETIME instead of DATETIME for dates.
    -u  Generate output in upper case (default).
    -v  Display +1Copybook's version.
    -V  Display +1Copybook's 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.

When loading in data, sometimes SQL Server will generate what we call a "global error," which reports an error but doesn't tell you which column being loaded generated it. Trying to locate which column is the source of the error can take a long time.

To help, +1Copybook supports the "-#" option. With this option, you can load in 'n' number of columns. For example, say there are 100 columns in the table and SQL Server tells you a loading error has occurred but doesn't tell you which column is the source of the problem. Using +1Copybook, you can specify "-# 50" to load in the first 50 columns of the table. If the error is still generated, you can try "-# 25". If the error is not generated, you can try "-#75". You can increase or decrease the number of columns to load until you locate the column which is causing the error. By default, +1Copybook modifies the first line of test input data to support this feature. If SQL Server generates an error for data found on a different line, use the "-R" option to debug the data. For example, "cb -R 268" will load only row 268 in the data file.

+1Copybook also support the NO_DELI and NO_COMMENTS files. If the NO_DELI file is seen in the same directory where the copybook.txt file exists, the delimiter files are not created. If the NO_COMMENTS file is seen, no comments are created. If previous delimiter files and/or comments have been generated, they are still accessible. To create these files, use the "touch NO_DELI" and/or "touch NO_COMMENTS" commands. To remove these files, use "rm NO_DELI" and/or "rm NO_COMMENTS" commands.

3. +1Copybook Inputs

The following files can be edited to configure +1Copybook. Most are optional files.

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 CREATE TABLE scripts based on COBOL copybooks, the following +1Copybook file is mandatory:

  • copybook.txt

All other configuration files are optional and can be used to customize the CREATE TABLE scripts. These files make it easier to support your needs using +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 CREATE TABLE script which are not found in the copybook. For example, this file can be used to specify a LOAD_DATE column.

3.2.2 Format

Table Name Column Name Create Table Datatype Built-In Function

3.2.3 Example

EMPLOYEE        LOAD_DATE       SMALLDATETIME
BILLING         LOAD_DATE       SMALLDATETIME
BILLING         NAME            VARCHAR2(10)

In the above example, the LOAD_DATE column is appended to the EMPLOYEE table. In the BILLING table, LOAD_DATE is also appended to the table. The datatype in the CREATE TABLE script is specified as SMALLDATETIME. Also appended to the BILLING table is the NAME column and is specified to be a VARCHAR2(10) in the CREATE TABLE script.

3.3 copybook.txt

3.3.1 Description

File copybook.txt contains the COBOL copybook description for one or more tables. Before each copybook description, the SQL Server 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.3.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.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

Suffix

3.4.3 Example

_HOLD

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

3.5 database_id.txt

3.5.1 Description

The database_id.txt file contains the SQL Server instance identifier for the CREATE TABLE scripts 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 multiple SQL Server instances are defined. You can view multiple SQL Server databases from the html/index.html file.

3.5.2 Format

SQL Server Instance Identifier

3.5.3 Example

COMPANY

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

3.6 default_db.txt

3.6.1 Description

File database.txt specifies which database is the default: Oracle or SQL Server. Valid values in upper or lower case are ORACLE or SQLSERVER. If the "-o" option for Oracle or "-s" option for SQL Server are not used and the default_db.txt file does not exist, the +1Copybook default is is determined by the license assigned to you by +1 Software Engineering. By setting the contents of the default_db.txt file to SQLSERVER, the +1Copybook default is SQL Server. If either the "-o" or "-s" options are used and the default_db.txt file exists, the default_db.txt contents will not be used.

3.6.2 Format

SQLSERVER

3.6.3 Example

SQLSERVER

3.7 delimiters.txt

3.7.1 Description

The file delimiters.txt specifies the field delimiter and row delimiter used to create format files. Format files specify the layout of the data in a file used for imports. A field delimiter delimits a single value in the data, while a row delimiter delimites a single line of data. The default values are tabs for field delimiters and newline characters (i.e., "\n") for row delimiters.

3.7.2 Format

Field Delimiter
Row Delimiter

3.7.3 Example

\t
\n

3.8 deli_delimiters.txt

3.8.1 Description

The deli_delimiters.txt file specifies the field delimiter and row delimiter used to create format files. Format files specify the layout of the data in a file used for imports. A field delimiter delimits a single value in the data, while a row delimiter delimites a single line of data. The default values are comma (i.e., ",") for field delimiters and carrige return and newline characters (i.e., "\r\n") for row delimiter.

Delimiter files are only created if needed. If +1Copybook sees implied decimal points or COMP-3 data types defined in the COBOL copybook, +1Copybook will create a delimiter file.

By default, +1Copybook tries to locate a table_name.txt, table_name.dat, or table_name.asc file in the "DATA/SQL-Server-Instance-Identifier" directory. If a data file directory is defined in the dos_path.txt file (explained next), the directory named is searched instead.

If the "-D" option or the "NO_DELI" file exists, +1Copybook does not create ".deli" files. This is a useful option if the delimiter files have already been created and do not need to be created again.

3.8.2 Format

Field Delimiter
Row Delimiter

3.8.3 Example

,
\r\n

3.9 dos_paths.txt

3.9.1 Description

The dos_paths.txt file contains two lines. First line is the DOS path to the location where data files are located. Data files can end in .txt, .dat, or .asc. The second line is the DOS path to the location where format files are located. Data and format files can be centralized and located anywhere on system. Both test and production dos_files.txt files can be created.

3.9.2 Format

Full DOS Path Name To Location Of Data Files
Full DOS Path Name To Location Of Format Files

3.9.3 Example

D:\Projects\State of Ohio\Load Data\
D:\Projects\State of Ohio\FORMAT_FILES\

3.10 override.txt

3.10.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 SQL Server 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.10.2 Format

Table NameCopybook Column NameColumn NameColumn Datatype (optional)

3.10.3 Example

INVENTORY_TABLE   COST-OF-PRODUCE   Produce_Cost 
INVENTORY_TABLE   DATE-OF-DELIVERY   Date_Of_Delivery   int
EMPLOYEEFNAMEFirst_Name 
EMPLOYEELNAMELast_Name 
EMPLOYEEMNAME Middle_Initial 
SALESNUMB-ITEMS number_of_itemsbigint 
SALESSALES-DATEsales_datesmalldatetime

The first entry for INVENTORY_TABLE changes the column named COST-OF-PRODUCE in the copybook to Produce_Cost in the 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 int.

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.11 prefixes.txt

3.11.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 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 table columns, the prefixes found on COBOL copybooks can be truncated allowing the rest of the column name to match a 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 SQL Server column name would be WEST-CODE. But if the order matches the list above, the resulting SQL Server column name would be CODE.

One disadvantage is if three columns in the COBOL copybook were called CUST-WEST-CODE, CUST-EAST-CODE, and CUST-CODE, the resulting table would have three SQL Server 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 table column names are prefixed. For example, each column in the EMPLOYEE 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 table start with EMP-, the EMPLOYEE entry in the prefixes.txt file is:

EMPLOYEE        EMPLOYEE-         EMP-

3.11.2 Format

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

3.11.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 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 columns found in the EMPLOYEE table.

4. +1Copybook Outputs

After running +1Copybook, generated output can be found in the following directories:

  • CREATE_TABLES,
  • DATA,
  • EXCEL,
  • FORMAT_FILES, and
  • html.
The create_tables.txt file is also created. The EXCEL directory is only created when the "-x" option is specified.

+1Copybook checks and reports the following errors:

  1. Check for blanks inbetween numeric data, e.g., 12 34.
  2. Check for invalid signed data character.
  3. Check for characters in numeric columns, e.g., 12A34.
  4. Check that the copybook defined length equals the first line of data length.

In the Copybook Mapping web page if any errors in the data are seen, the data bytes in the First Line of Data column are highlighted in red. If the copybook defined length does not equal the first line of data length, an error is displayed in the copybook mapping HTML output.

4.1 create_tables.txt

The create_tables.txt file is created by +1Copybook and is read as input by +1DataElements. You don't need to make any changes to this file.

4.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 defined by the database.txt file. The database_id directory contains the CREATE TABLE scripts which match the column order as found in the COBOL copybook. The sorted.database_id 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.

use Northwind
go

drop table EMPLOYEE_HOLD
go

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

4.3 DATA Directory

+1Copybook will transform any signed, implied decimal point, or COMP-3 data read from fixed width data into a field delimited file. For example, if the COBOL copybook is defined as:

01 CUSTOMER-RECORD.
   05 CUST-ID		PIC 9(05).
   05 AMOUNT-DUE	PIC 9(05)V9(02).
   05 A-SIGNED-NUMBER	PIC S9(03).
   05 LAST_NAME		PIC X(25).

+1Copybook will generate a delimiter file since AMOUNT-DUE uses an implied decimal point and because A-SIGNED-NUMBER is a signed number.

If the first line of data found in the DATA/datbase_id/CUSTOMER.txt file is:

30123 4567812CDAY

normally SQL Server will read the value 45678 instead of the actual value of 456.78 and will generate an error when reading A-SIGNED-NUMBER because the 'C' character is seen in the number column.

To correct these errors, +1Copybook will create the CUSTOMER.deli file, whose content is:

30123, 456.78, 123, DAY

The CREATE TABLE scripts generated by +1Copybook provide the BULK INSERT commands which you can copy and paste into TSQL to run and save. In the above example, the CUSTOMER.deli file would be used in the CREATE TABLE script as the data to read in.

By default, delimiter files will be created in the DATA/database_id directory. Files found in the database_id directories must match the table name found in the copybook.txt file. For example, if "CUSTOMER TABLE" is found prior to the CUSTOMER COBOL copybook layout, DATA/database_id/CUSTOMER.txt file will be read to generate the DATA/database_id/CUSTOMER.deli file.

4.3 EXCEL Directory

4.3.1 Description

When the "-x" option is used, the EXCEL directory contains the COBOL copybook mapping ready to be loaded into Microsoft Excel. 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.

4.4 html Directory

To view the +1Copybook output, visit:

    html/index.html

Users can copy/paste the output seen on the web screen into TSQL and save it as an SQL script or use the scripts found in the various output directories.