Transcript TM +1Copybook Converting COBOL Copybooks into SQL*Loader Control Files Presented To The Los Angeles Oracle Users Group (LAOUG) John Dempsey +1 Software Engineering® http://plus-one.com 805-389-1778

TM
+1Copybook
Converting COBOL Copybooks into
SQL*Loader Control Files
Presented To The
Los Angeles Oracle Users Group (LAOUG)
John Dempsey
+1 Software Engineering®
http://plus-one.com
805-389-1778
Overview
Technical Presentation
–
–
–
–
–
Moving Mainframe Data
Extract, Transform, Load, and Document (ETLD)
COBOL Copybooks
Oracle SQL*Loader Control Files
Oracle External Tables
+1 Product Presentation
– +1CopybookTM
Questions
+1 Software Engineering®
2
Moving 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
+1 Software Engineering®
3
Our 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.
Mainframe
Client/Server
(e.g., Unix or
Windows
System)
+1 Software Engineering®
4
Extract, 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
– SQL*Loader loads data into a database.
– External Tables can be queried to load data into a database
table.
Document
– Generate Data Element Dictionary/HTML documentation.
+1 Software Engineering®
5
Start With Two Files
Data File
– Contains the actual data.
COBOL Copybook File
– Defines the layout of the data file.
+1 Software Engineering®
6
COBOL 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.
+1 Software Engineering®
7
COBOL Copybook Data Types
Character Data
Numeric Data
Signed Data
Decimal Point Data
Implied Decimal Point
Packed Decimal Data
Computational Data
PIC X(10).
PIC 9999.
PIC S999.
PIC 999.99.
PIC 9(03)V99.
COMP-3
COMP,-1,-2,-4,-5
+1 Software Engineering®
8
OCCURS 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.
+1 Software Engineering®
9
REDEFINES 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.
+1 Software Engineering®
10
CUSTOMER 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).
+1 Software Engineering®
11
SQL*Loader Control File
OPTIONS (DIRECT=TRUE)
 The fastest way to load data!
LOAD DATA
INFILE ‘/u1/project/data/customer.dat’ “FIX 55”
APPEND
INTO TABLE CUSTOMER
(
ID
POSITION(001:006)
CHAR,
LAST_NAME
POSITION(007:026)
CHAR,
FIRST_NAME
POSITION(027:041)
CHAR,
AGE
POSITION(042:044)
CHAR,
PHONE
POSITION(045:054)
CHAR,
DATE_LOADED
SYSDATE
)
+1 Software Engineering®
12
INSERT vs. DIRECT PATH
DIRECT PATH (DIRECT=TRUE)
–
–
–
–
–
–
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.
SQL*Loader 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.
+1 Software Engineering®
13
SQL*Loader
To load data from a flat file, you can
use Oracle’s SQL*Loader utility
“sqlldr”.
% sqlldr \
username/password \
control=/u1/project/ctl/customer.ctl \
log=/u1/project/log/customer.log \
bad=/u1/project/bad/customer.bad
+1 Software Engineering®
14
External 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
database’s tablespace.
No updates are allowed to an external
table.
External tables provide DBAs another way
to load data.
+1 Software Engineering®
15
External Table Example
CREATE TABLE CUSTOMER (
CUST_ID
LAST_NAME
FIRST_NAME
…
CREDIT_SCORE
VARCHAR2(8),
VARCHAR2(20),
VARCHAR2(12),
VARCHAR2(3)
)
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,
…
CREDIT_SCORE
POSITION(876:878)
CHAR
))
LOCATION (‘/u01/external_tables/customer.txt’))
REJECT LIMIT 1
;
+1 Software Engineering®
16
+1CopybookTM
Converts COBOL copybooks into:
– SQL*Loader control files
– CREATE TABLE scripts
– EXTERNAL TABLE scripts
Generates HTML output:
–
–
–
–
–
–
Table Columns
Sorted Table Columns
COBOL Copybook Mappings
SQL*Loader Control Files
CREATE TABLE/External Table Scripts
Row, table and database sizing estimates
+1 Software Engineering®
17
+1Copybook
TM
+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.
+1 Software Engineering®
18
index.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.
+1 Software Engineering®
19
Display
All
Tables
You can view:
•
Table Columns
•
Sorted Table
Columns
•
Copybook
Mapping
•
Control File
•
CREATE
TABLE Script
•
Comments
•
Row Size
+1 Software Engineering®
20
Table
Columns
Displays
table
columns as
defined in the
database.
+1 Software Engineering®
21
Sorted
Table
Columns
Displays
table
columns in
sorted order
for faster
lookup.
+1 Software Engineering®
22
Column
Attributes
By selecting a
column name,
column
information is
displayed.
+1DataElementsTM
is used to
generate data
element dictionary
entries.
+1 Software Engineering®
23
COBOL
Copybook
Mapping
Displays:
•
COBOL
Copybook
•
Oracle Column
Names
•
First Line (or
Nth Line) of
Data
•
SQL*Loader
POSITIONs
•
Control File
Data Types
•
Create Table
Data Types
+1 Software Engineering®
24
Control
File
Displays
generated
control
file.
+1 Software Engineering®
25
CREATE
TABLE
Script
Displays
generated
CREATE
TABLE
script.
+1 Software Engineering®
26
Table
Comments
Displays
customized data
element dictionary
entries for table
and columns.
+1 Software Engineering®
27
Table
Row
Size
Display table
row size.
With table
counts,
shows table
and database
estimated
sizes.
+1 Software Engineering®
28
Map 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.
+1 Software Engineering®
29
+1Copybook’s
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.”
+1 Software Engineering®
30
®
+1 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
+1 Software Engineering®
31
TM
+1Copybook 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.
-s Generate SQL Server output.
-S Stream input datafile. No CRs or LFs in INFILE datafile.
-u Generate output in upper case (default).
-v Display +1Copybook's version and expiration date.
-V Display +1Copybook letter version and release dates.
-w Do not print warning messages.
-x Generate Excel output.
+1 Software Engineering®
32
+1Copybook Configuration Files
TM
acryomns.txt
append_columns.txt
control_file_datatypes.txt
copybook.txt
create_table_datatypes.txt
create_table_suffix.txt
data_path.txt
database_id.txt
datatype_override.txt
default_database.txt
external.txt
header.txt
infile.txt
override.txt
prefixes.txt
Identify acryomns in column names.
Append columns to end of table.
Override default control file data types.
COBOL copybook.
Override default create table data types.
Append suffix to table names, e.g., _HOLD.
Directory path name to data.
Contains ORACLE_SID value.
Override default data types.
Oracle or SQL Server
Define external table options.
Customize top part of control files.
Customize control files (short form).
Override column name and/or data type.
Delete/Replace COBOL copybook prefixes.
+1 Software Engineering®
33
CONVENTIONAL PATH
vs. DIRECT PATH
Conventional Path (INSERT statements)
1.
2.
3.
4.
5.
6.
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 (DIRECT=TRUE option)
1. Format input data into Oracle data blocks.
2. Get new extents for the table to be loaded.
3. Write the formatted data blocks to disk above high-water mark.
From: Oracle SQL*Loader: The Definitive Guide, O’Reilly, Ch. 10. Direct Path Loads, pg. 182.
+1 Software Engineering®
34