Transcript GDT ETL - the process

Converting COBOL Data to SQL
Data: GDT-ETL Part 1
GDT ETL

What is ETL?
 Extract Transform Load
 Extract data from your legacy system.
 Transform (Convert or cleanse).
 Load the data into the relational database, data warehouse,
data mart or Operational Data Store.
 Why GDT-ETL?
 Built for Legacy Systems.
 Native access to ISAM data.
 Transformation from COBOL data types to more general
types.
 Allows you to focus on where the data is going not how you
get access to it.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL
 Data flow for a system running GDT
 MF file system to a relational database
MF Files
GDT-ETL
GDT-ETL
RDMS
RDMS
GDT
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL
 Data flow for Legacy Systems
UFA
S
Fileconv2
Fileconv2
MF
Files
RDMS
RDMS
GDT-ETL
GDT
GCOS
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL
 ETL Features
 Command line tool and now with a Graphical User Interface
(to be shown)
 Object Oriented Design.
 Will work on Windows or UNIX platforms.
 Comprehensive error logging and process monitoring.
 Will work with existing enterprise scheduling facilities.
 ETL Requirements
 Windows



GDT V4.1
.NET Framework 2.0
Solaris

GDT V4.1
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
What can I do with GDT-ETL?
 There are two main features of this tool.
 Help build database schemas from your ISAM data.




Creating Data marts
Data warehouse
Data Stores
Loading data into a database system.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL
A Working Example! ICOM system
DATA
TPR-ICOM2
CUSTOMER
File Definition
FD-CUSTOMER
MULTI-01
PAR?
SELECTCUSTOMER
FD-EMPLOYEE
TPR-ICOM4
EMPLOYEE
SELECTEMPLOYEE
MULTI-01
PAR?
FD-ORDERS
TPR-ICOM1
ORDERS
SELECTORDERS
MULTI-01
PAR?
GDT ODS
1pm Class Today!
Don't miss it
FD-PRODUCT
TPR-ICOM3
PRODUCT
SELECTPRODUCT
SQL1GEN
SQLCONV.PAR
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
MULTI-01
PAR?
GDTFAC
DLL
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
SQL1GEN process
 GDT process that generates a File Access
Component (GDTFAC.DLL)


To be used by GDT ETL to generically access all
Micro Focus ISAM files
Configuration






SQLCONV.PAR
FILE DEFINITION(S) fd’s
FILE SELECT STATEMENTS
MULTIPLE 01 RECORD DEFINITION PARAMETER FILES
Requires GDT V4.1 
GDTFAC.DLL contains all necessary file info.

Key information, data types, record types, fields etc..
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
A Typical Production Environment
ISAM
Order
Master
File
Customer Data
Batch process
Report Data
Report
Generation
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
Using the ORDERS file
FILE DEFINITION
S
Q
L
1
G
E
N
PLUS
FILE COBOL Select
statement
PLUS
GDTSQL
Parameter File
------------------------------------------------------------------------------------------Creates GDTFAC.DLL
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
The ETL process
G
D
T
E
T
L
GDTFAC
DLL
Order
Info.
File Access Component
PLUS
ETL PARAMETER FILE
-------------------------------------------------------------------------------------------
GDT_ETL dt
ct
ld
Delete
ORDER
Master
Database
Table
Create
ORDER
Master
Database
Table
Load
ORDER
Master
Database
Table
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
Results of the ETL process
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
ETL in a nutshell
 Step 1: File Analysis
 Using the File Access Component built by GDT
GDTFAC.DLL, the ETL tool first analyzes the file
layout for each file.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
ETL in a nutshell
 Step 2 - Build the tables and the relationships
in SQL


Each record denotes a new table.
Each sub record denotes a new table with a 1 to
many relationship with the parent record.


Nested OCCURS within a file produces more relationships.
Each field produces a field in a SQL table and ETL
maps COBOL data types to the SQL Servers data
types.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
ETL in a nutshell
 Step 3:
 Optional

Modify the table schema and stored procedures to
suit your needs.
 Build transformations as needed.
 Step 4:
 Load the Data

Read each file, extract the data, transform from COBOL
data types to database server data types and load the data
into the database.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL IN ACTION
 How does ETL build database schemas?
 When Generating a table schema GDT-ETL makes
the following assumptions.




Each file consists of 1 or more record types.
Each record in a file consists of 1 or more fields and it may
contain hierarchy of sub records as denoted by the
OCCURS clause.
Each field has a location in a record as well as an
associated COBOL data type.
Group name and redefines are ignored.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL IN ACTION
 Create the database schema and stored procedures.


Build the tables and relationships
Three basic data type transformations:




PIC X(NN) to char(NN)
PIC 9(NN) to INT
PIC S9(NN)V9(NN) to double
Setup default stored procedures for each table:

For each table there are 3 separate stored procedures
 Insert
 Update (ODS Only)
 Delete (ODS Only)


The tables can be modified but the stored procedures must reflect any
schema modifications.
The store procedures can be modified but the parameters must stay the
same.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT ETL IN ACTION
 Build the default database objects

Setup the ETL Configuration.



Set database properties.
Reference the location of the file access component.
Build the database objects.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
Building Transformations
 What value do transformations add?
 Allows for cleansing of data before it enters the
database.



Determine if it is accurate.
Avoid data corruption.
Create more interesting data transformation

Example: Date time
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
Building Transformations
 Use Data Integration tools
 BusinessObjects Data Integrator.
 Develop the transformations by hand.


Modify the table layout if desired.
Modify the default stored procedures to setup desired
transformations.
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
ETL in a nutshell
 ETL Configuration Utility - GUI



Set the database properties.
Modify which files you want to load.
AND MORE TO COME…..
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans
GDT-ETL

THE DEMONSTRATION!
GDT 2006 International User Conference: Evolving the Legacy – Revolutions
June 25 - 28  Palm Springs, California
Converting COBOL Data to SQL Data: GDT-ETL Part 1
Doug Evans