1CopybookTM Converting COBOL Copybooks into SQL*Loader Control Files Presented To The Los Angeles Oracle Users Group (LAOUG) - PowerPoint PPT Presentation

About This Presentation
Title:

1CopybookTM Converting COBOL Copybooks into SQL*Loader Control Files Presented To The Los Angeles Oracle Users Group (LAOUG)

Description:

1CopybookTM Converting COBOL Copybooks into SQLLoader Control Files Presented To The Los Angeles Or – PowerPoint PPT presentation

Number of Views:901
Avg rating:3.0/5.0
Slides: 35
Provided by: johnde2
Category:

less

Transcript and Presenter's Notes

Title: 1CopybookTM Converting COBOL Copybooks into SQL*Loader Control Files Presented To The Los Angeles Oracle Users Group (LAOUG)


1
1CopybookTMConverting 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

2
Overview
  • 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

3
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

4
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.

Client/Server (e.g., Unix or Windows System)
Mainframe
5
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
  • 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.

6
Start With Two Files
  • Data File
  • Contains the actual data.
  • COBOL Copybook File
  • Defines the layout of the data file.

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

8
COBOL 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

9
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.

10
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.

11
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).

12
SQLLoader 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
  • )

13
INSERT 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.

14
SQLLoader
  • 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

15
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 databases
    tablespace.
  • No updates are allowed to an external table.
  • External tables provide DBAs another way to load
    data.

16
External 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
  • ))

17
1CopybookTM
  • 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

18
1CopybookTM
  • 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.

19
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.
20
Display All Tables
  • You can view
  • Table Columns
  • Sorted Table Columns
  • Copybook Mapping
  • Control File
  • CREATE TABLE Script
  • Comments
  • Row Size

21
TableColumns
Displays table columns as defined in the database.
22
SortedTableColumns
Displays table columns in sorted order for faster
lookup.
23
ColumnAttributes
By selecting a column name, column information is
displayed. 1DataElementsTMis used to generate
data element dictionary entries.
24
COBOLCopybookMapping
  • Displays
  • COBOL Copybook
  • Oracle Column Names
  • First Line (or Nth Line) of Data
  • SQLLoader POSITIONs
  • Control File Data Types
  • Create Table Data Types

25
ControlFile
Displays generated control file.
26
CREATETABLEScript
Displays generated CREATE TABLE script.
27
TableComments
Displays customized data element dictionary
entries for table and columns.
28
TableRowSize
Display table row size. With table counts, shows
table and database estimated sizes.
29
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.

30
1Copybooks 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.

31
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

32
1CopybookTM 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.

33
1CopybookTM 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.

34
CONVENTIONAL 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.
Write a Comment
User Comments (0)
About PowerShow.com