Title: 1CopybookTM Converting COBOL Copybooks into SQL*Loader Control Files Presented To The Los Angeles Oracle Users Group (LAOUG)
11CopybookTMConverting COBOL Copybooks into
SQLLoader Control FilesPresented To TheLos
Angeles Oracle Users Group (LAOUG)
- John Dempsey
- 1 Software Engineering
- http//plus-one.com
- 805-389-1778
2Overview
- Technical Presentation
- Moving Mainframe Data
- Extract, Transform, Load, and Document (ETLD)
- COBOL Copybooks
- Oracle SQLLoader Control Files
- Oracle External Tables
- 1 Product Presentation
- 1CopybookTM
- Questions
3Moving Mainframe Data
- Up to 75 of operational data is found on
mainframes. - However, mainframe data is needed on
client/servers for - Report generation
- Point of Sale updates
- Data warehousing
- Web servers to provide access to data
4Our Goals
- Move mainframe data defined by flat files to an
Oracle or SQL Server database defined by
normalized tables. - Start loading data on the same day you are handed
a COBOL copybook and data file.
Client/Server (e.g., Unix or Windows System)
Mainframe
5Extract, Transform, Load, Document (ETLD or E-LTD)
- Extract
- Extract program on mainframe writes out data to a
flat file as defined by a COBOL copybook. - Transform
- Modify extracted data to derive the data to be
loaded. - Load
- SQLLoader loads data into a database.
- External Tables can be queried to load data into
a database table. - Document
- Generate Data Element Dictionary/HTML
documentation.
6Start With Two Files
- Data File
- Contains the actual data.
- COBOL Copybook File
- Defines the layout of the data file.
7COBOL Copybooks
- COBOL copybooks define how the data is laid out
in a flat file using the COBOL programming
language syntax. - Copybooks are similar to an include file used in
C or C. - Copybooks are used by COBOL programmers to ensure
everyone uses the same file layout.
8COBOL Copybook Data Types
- Character Data PIC X(10).
- Numeric Data PIC 9999.
- Signed Data PIC S999.
- Decimal Point Data PIC 999.99.
- Implied Decimal Point PIC 9(03)V99.
- Packed Decimal Data COMP-3
- Computational Data COMP,-1,-2,-4,-5
9OCCURS Clause
- Three Examples
- 10 CUST-CREDIT-SCORE PIC XXX OCCURS 5 TIMES.
- 05 CUST-CREDIT-SCORE OCCURS 4 TIMES.
- 10 CUST-CREDIT-RATING PIC 9(3).
- 10 CUST-CREDIT-DATE PIC S9(07) COMP-3.
- 05 CUST-CREDIT-SCORE OCCURS 5 TIMES.
- 10 CUST-REVISED-SCORE OCCURS 3 TIMES.
- 15 CUST-CREDIT-RATING PIC
9(3). - 15 CUST-CREDIT-DATE PIC
S9(07) COMP-3. - 15 CUST-CREDIT-END-DATE PIC S9(07)
COMP-3.
10REDEFINES Clause
- 05 CUST-RISK PIC X(4).
- 05 CUST-RISK-LEVEL REDEFINES CUST-RISK.
- 10 CUST-RISK-LEVEL-A PIC X.
- 10 CUST-RISK-LEVEL-B PIC X.
- 10 CUST-RISK-LEVEL-C PIC X.
- 10 CUST-RISK-LEVEL-D PIC X.
- 05 CUST-TOP-LEVEL REDEFINES CUST-RISK.
- 10 CUST-TOP-RISKS PIC XXX.
- 10 FILLER PIC
X. -
11CUSTOMER Copybook Example
- 005720
- 005730 COBOL Copybook For Customer
- 005740
- 005750 01 CUSTOMER-RECORD.
- 005760 05 CUST-ID
PIC 9(6). - 005770 05 CUST-LAST-NAME PIC X(20).
- 005780 05 CUST-FIRST-NAME PIC X(15).
- 005790 05 CUST-AGE PIC 999.
- 005800 05 CUST-PHONE PIC
9(10).
12SQLLoader Control File
- OPTIONS (DIRECTTRUE) ? The fastest way to load
data! - LOAD DATA
- INFILE /u1/project/data/customer.dat FIX 55
- APPEND
- INTO TABLE CUSTOMER
- (
- ID POSITION(001006) CH
AR, - LAST_NAME POSITION(007026) CHAR,
- FIRST_NAME POSITION(027041) CHAR,
- AGE POSITION(042044) CHAR,
- PHONE POSITION(045054) CHAR,
- DATE_LOADED SYSDATE
- )
13INSERT vs. DIRECT PATH
- DIRECT PATH (DIRECTTRUE)
- SIGNIFICANTLY faster than using INSERT
statements. - No SQL statements need to be parsed and executed.
- Blocks in database buffer cache are not accessed.
- No need to search for free space in existing
blocks. - No rollback information written to rollback
segments. - Oracle data blocks written directly to files on
disk. - SQLLoader supports both conventional path (using
INSERT statements) and direct path loads. - XML COBOL Copybook mapping products/tools use
INSERT statements. While appropriate for some
loads, for most loads it is a much slower way to
load data.
14SQLLoader
- To load data from a flat file, you can use
Oracles SQLLoader utility sqlldr. - sqlldr \
- username/password \
- control/u1/project/ctl/customer.ctl \
- log/u1/project/log/customer.log \
- bad/u1/project/bad/customer.bad
15External Tables
- DBAs can select data from an external table and
insert data into a database table. - The data in an external table is actually stored
in a file and is not stored in the databases
tablespace. - No updates are allowed to an external table.
- External tables provide DBAs another way to load
data.
16External Table Example
- CREATE TABLE CUSTOMER (
- CUST_ID VARCHAR2(8),
- LAST_NAME VARCHAR2(20),
- FIRST_NAME VARCHAR2(12),
-
- CREDIT_SCORE VARCHAR2(3)
- )
- ORGANIZATIONAL EXTERNAL (
- TYPE ORACLE_LOADER
- DEFAULT DIRECTORY EXT_DIR
- ACCESS PARAMETERS (
- RECORDS FIXED 879
- FIELDS (
- CUST_ID POSITION(001008) CHAR,
- LAST_NAME POSITION(009028) CHAR,
- FIRST_NAME POSITION(029040) CHAR,
-
- CREDIT_SCORE POSITION(876878) CHAR
- ))
171CopybookTM
- Converts COBOL copybooks into
- SQLLoader control files
- CREATE TABLE scripts
- EXTERNAL TABLE scripts
- Generates HTML output
- Table Columns
- Sorted Table Columns
- COBOL Copybook Mappings
- SQLLoader Control Files
- CREATE TABLE/External Table Scripts
- Row, table and database sizing estimates
181CopybookTM
- 1Copybook supports options and configuration
files. In the following example - database_id.txt contains SEINFO,
- index.txt configures the index.html page,
- prefixes.txt contains CUSTOMER CUST-, and
- the -d option checks for DATE columns.
- To run 1Copybook, type
- cp /path/to/customer.cpy copybook.txt
- Edit copybook.txt file using vi or notepad.
- Add line TABLE CUSTOMER.
- cb d
- To view results, open file html/index.html in a
web browser.
19index.html
This is the index.html page, which we customized.
From here you can view tables, indexes, views,
and documentation for one or more
databases. Information on indexes and views
captured by running 1 SQL scripts i.sql and
v.sql.
20Display All Tables
- You can view
- Table Columns
- Sorted Table Columns
- Copybook Mapping
- Control File
- CREATE TABLE Script
- Comments
- Row Size
21TableColumns
Displays table columns as defined in the database.
22SortedTableColumns
Displays table columns in sorted order for faster
lookup.
23ColumnAttributes
By selecting a column name, column information is
displayed. 1DataElementsTMis used to generate
data element dictionary entries.
24COBOLCopybookMapping
- Displays
- COBOL Copybook
- Oracle Column Names
- First Line (or Nth Line) of Data
- SQLLoader POSITIONs
- Control File Data Types
- Create Table Data Types
25ControlFile
Displays generated control file.
26CREATETABLEScript
Displays generated CREATE TABLE script.
27TableComments
Displays customized data element dictionary
entries for table and columns.
28TableRowSize
Display table row size. With table counts, shows
table and database estimated sizes.
29Map Copybook Entries To Existing Database Columns
- 1Copybook can map copybook entries to existing
database columns. - Informs you which COBOL copybook entries are not
used. - Informs you which Oracle table columns are not
initialized.
301Copybooks Return on Investment
- Our customers state it best
- 1Copybook will enable us to compress our
development schedule immensely. - This report is extremely useful.
- I was very impressed with your product.
- You certainly know how to support your clients.
- More predictable and consistent results.
- Significant cost savings.
311 Software Engineering
- Products
- 1CopybookTM
- 1DataElementsTM
- Products supported on Microsoft Windows,
Solaris, Linux, Red Hat, Mac OS X, and other
platforms. - Services
- We work with clients to load their data into
Oracle and/or SQL Server databases. - For more information
- Please contact us or visit our web site at
- http//plus-one.com
321CopybookTM Options
- The syntax to run 1Copybook for Oracle is
- cb -bcdfhHlLmnoprsSuvVwx -R 99
- where
- -b Generate output in both upper and lower case.
- -c Compare COBOL copybook columns with existing
database columns. - -d If copybook column contains DATE, DTE, or DT
anywhere in its name, define column as a
DATE datatype. - -f Specify file copybook.txt is in free format.
- -h Display this help message.
- -H Display help message on 1Copybook
configuration files. - -l Generate output in lower case.
- -L Display copybook.txt lines as they are
processed. - -m Do not display CREATE TABLE data type column
in copybook mapping. - -n Add NOT NULL constraint to each column in the
CREATE TABLE script. - -o Generate Oracle output.
- -p COMP-3 Packed Decimals in copybook are not
compressed. - -r Use NUMBER only for COMP-3 data types to
avoid rounding. - -R 99 Load row . Only one line of data is
loaded. Use for debugging loads.
331CopybookTM Configuration Files
- acryomns.txt Identify acryomns in column names.
- append_columns.txt Append columns to end of
table. - control_file_datatypes.txt Override default
control file data types. - copybook.txt COBOL copybook.
- create_table_datatypes.txt Override default
create table data types. - create_table_suffix.txt Append suffix to table
names, e.g., _HOLD. - data_path.txt Directory path name to data.
- database_id.txt Contains ORACLE_SID value.
- datatype_override.txt Override default data
types. - default_database.txt Oracle or SQL Server
- external.txt Define external table options.
- header.txt Customize top part of control
files. - infile.txt Customize control files (short
form). - override.txt Override column name and/or data
type. - prefixes.txt Delete/Replace COBOL copybook
prefixes.
34CONVENTIONAL PATH vs. DIRECT PATH
- Conventional Path (INSERT statements)
- Generate SQL commands for the data to be loaded.
- Process SQL commands (parse, fetch, execute)
- Find partial data blocks or get new extents.
- If blocks not in buffer cache, read the data
blocks from disk. - Fill the appropriate data blocks in the buffer
cache. - Write data blocks to disk.
- DIRECT PATH (DIRECTTRUE option)
- Format input data into Oracle data blocks.
- Get new extents for the table to be loaded.
- Write the formatted data blocks to disk above
high-water mark. - From Oracle SQLLoader The Definitive Guide,
OReilly, Ch. 10. Direct Path Loads, pg. 182.