+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-586-3445

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.