Transcript ADABAS to RDBMS - Mainframe | Data | Replication
03/2007
DPSync Overview
1
Agenda
03/2007
The Problem and the Options Concepts of ADABAS-to-RDBMS Replication A Brief History of ADABAS-to-RDBMS Replication DPSync: How It Works Points of Distinction Customer Benefits Summary: Ask the Difficult Questions
2
The Problem
This Is What I Have… ADABAS
• Legacy COBOL Programs • NATURAL Programs • Batch Jobs
This is What I Need… RDBMS
• Web Services • SQL Access • Remote Access • Data Warehouse • Sophisticated Analysis • Graphics • SOAP • Other Software Packages • Etc.
03/2007 3
03/2007
The Options
•
Rewrite All the Legacy & NATURAL Programs
•
Replication via PLOG-Based Periodic Updates
•
Replication via Extract-Based Periodic Updates
•
Real-Time ADABAS-to-RDBMS Replication
What I Have
ADABAS
• • •
Legacy COBOL Programs NATURAL Programs Batch Jobs
What I Need
RDBMS
• • • • • • • • •
Web Services SQL Access Remote Access Data Warehouse Sophisticated Analysis Graphics SOAP Other Software Packages Etc.
4
03/2007
The Replication Requirements Continuum
Timeliness of Replication Complexity of Replication Management 5
03/2007
Concepts Of Real-Time ADABAS-to-RDBMS Replication
6
03/2007
Concepts
What’s Involved In Implementing A Real-Time ADABAS-to-RDBMS Replication Application
• • • • • • • • • Analyze the ADABAS data content Design the RDBMS Produce all the metadata for the RDBMS and the transformation
Design, develop and test application programs*
Extract the ADABAS Data, Load the Initial RDBMS Activate the Real-Time Process For Intercepting the ADABAS Updates Transforming to Relational Transmitting of the Updates to the RDBMS Server Applying the updates to the RDBMS On-going operations, including recovery from exceptions, resynchronization of the databases and recovery from RDBMS errors
On-going maintenance of customer-written programs*
On-going maintenance of metadata
* Not applicable for DPSync
7
03/2007
Concepts
The Components of an ADABAS Replication Software Product
1.
2.
3.
Analysis and RDBMS Design Tools Extraction, Transformation, and Load (ETL) Facilities Real-Time Interception and Delivery of Changes 4.
Sophisticated and Complex Transformations (from ADABAS to RDBMS structures and data), Including Transforming PEs and MUs to Child and Grandchild Tables 5.
6.
7.
Application of the Updates, RDBMS Error Notification, and Recovery Special Functions (e.g., Synchronized Backups and Restores) Console Facilities for Statistics and Operations
8
03/2007
Concepts
ADABAS Replication Software Products: Attributes To Look For
1.
2.
3.
4.
5.
6.
7.
8.
9.
Robustness of features Ease of implementation, including no requirement for custom developed software Ease of operation (i.e., procedures for running synchronized backups/restores, handling errors, etc.) Ease of maintenance (software and metadata) Performance, including any impact on the production ADABAS system and use of ADASAV and high-speed RDBMS loaders Reliability -- software maturity Data integrity, including replicated, in their original sequence, without duplicates, and without any lost data guarantees that all the changes will be Scalability and Deployment Options An End-to-End Solution
9
03/2007
A Brief History of ADABAS-to-RDBMS Replication Software
10
03/2007
History of ADABAS-to-RDBMS Replication Software
It’s Not a New Idea
Treehouse Software
A innovator in ADABAS replication software for over 12 years From 1995 to 2007 our products have been used in the analysis, design, implementation and maintenance of ADABAS-to-RDBMS replication processes We have listened to the needs of ADABAS customers, and for the last decade we have supplied software for ADABAS replication for data warehouses and other applications
11
03/2007
History of ADABAS-to-RDBMS Replication Software
DPSync: 1995 to 2007 Treehouse: What Does 12 Years Give You?
An understanding of the needs of real customers An awareness of the technical pitfalls Robust features, as a result of 6 incremental releases of the product Broad Array of Target Platforms: Oracle, DB2, Sybase, SQL Server, UNIX, z/OS, Windows A mature, stable and reliable software product
12
03/2007
History of ADABAS -to-RDBMS Replication Software
DPSync (1995-2007) What Does 12 Years Give You?
• A Complete Solution • No Programs To Develop • Guarantee No Lost Data • ETL via ADASAV & High-Speed RDBMS Loaders • Robust Features • Sophisticated Transformations • Reliability • Data Integrity • Ease of Use • Ease of Maintenance • RDBMS Vendor Independence • Stability • Maturity
13
03/2007
DPSync
How It Works
•
System Overview
•
Analysis and Design
•
Materialization (ETL)
•
Real-Time Replication 14
03/2007
DPSync – How It Works
System Overview 15
03/2007
DPSync – How It Works
Analysis and Design
Analyze actual ADABAS data content Generate, view and modify system-recommended RDBMS schemata Select an RDBMS schema Modify the schema Define conversion processes Map ADABAS files/fields to RDBMS tables/columns Result generates the DPSync Metadata and RDBMS DDL statements Intuitive interactive user interfaces, including GUI
16
03/2007
ADABAS File Analysis
Statistics of MUs and PEs for sizing of child tables and potential de-normalization of tables to individual column(s).
17
ADABAS File Analysis
03/2007
Statistics of alphanumeric fields for candidate variable character text columns.
18
ADABAS File Analysis
03/2007
This screen shows descriptor/superdescriptor usage statistics to determine candidate Primary Keys and indexed columns.
19
03/2007
RDBMS Modeling and Mapping
Auto Generation Example: Four tables with Primary Key and Foreign Key constraints, and the added DPS PE Sequencer (PE occurrence).
20
03/2007
DPSync – How It Works
Materialization (ETL)
• • • • • • Standard utilities – no programs to write or maintain Automatic DDL generation High speed extraction via ADASAV Utility High speed RDBMS loaders Complex data transformations and conversions No Problems with concurrent ADABAS updates
21
DPSync – How It Works
Real-Time Replication - Overview 03/2007 22
03/2007
DPSync – How It Works
Real-Time Replication: Interception of the Changes
• Standard ADABAS Replication Exit • Volume Spikes Handled • Gaps Are Managed • No Impact on ADABAS Performance
23
DPSync – How It Works
Real-Time Replication: Transformation & Delivery
• Automated Analysis and Design • Sophisticated Transformations, including Child and Grandchild Tables from PEs and MUs • “Paced” SQL TCP/IP Transmissions • Only ET’ed Transactions Shipped
03/2007 24
03/2007
DPSync – How It Works
Real-Time Replication: Applying The Updates
• No Programs To Write • Sophisticated RDBMS Structures, Including Child & Grandchild Tables from MUs and PEs • Adheres To RDBMS Foreign-Key/Referential Integrity Constraints • “Paced” SQL TCP/IP Transmissions and Queue • Journal and Error Log • Automatic RDBMS Error Notification via Email • Built-In Error Recovery via Scripts • Multi-Threaded
25
03/2007
DPSync – How It Works
Real-Time Replication: DPSync Console
• Statistics • System States • Error Recovery Scripts • Run-Time Parameters • Journal and Error Log Display • Error Retry • Security
26
03/2007
DPSync – How It Works
Real-Time Replication – DPSync Console : Statistics and System States
• Latency Times • Queue Times • RDBMS Times • Number ADABAS Transactions • Number SQL Statements • Number of Errors • System Paused • System Halted • DPS/R-RSU Connection States • Overruns (Volume Spikes) • Etc.
27
DPSync – How It Works
Real-Time Replication – DPSync Console: Maintaining Error Recovery Scripts
• Conditions and Groups • Email Definitions • Email Groups • Actions by Error Type: Pause Retry Halt the System • Etc.
Write to Error Log Notify via Email • Options By Error Type Ignore Email Text to Send
03/2007 28
03/2007
DPSync – How It Works
Real-Time Replication – DPSync Console: Run-Time Parameters 29
03/2007
DPSync – How It Works
Real-Time Replication – DPSync Console: Journal and Error Log Display 30
03/2007
DPSync – How It Works
Real-Time Replication – DPSync Console: Security 31
03/2007
DPSync – How It Works
Real-Time Replication – DPSync Console: Other Console Functions 32
03/2007
Points of Distinction
DPSync vs. Other Software Products
• Loading the Initial RDBMS (ETL) • Lost (Replication) Data • Detection and Notification of RDBMS Errors • Ease of Implementation • Ease of Maintenance • Transformation Facilities • The Total Solution
33
03/2007
Points of Distinction
Two Ways to Load the Initial RDBMS
1. Full Live Extracts 2. Use High-Speed ADASAV and High-Speed RDBMS Loaders
34
03/2007 ADABAS
Points of Distinction
Flawed Method For Loading the Initial RDBMS Use “Live Data Extracts” ADABAS Replicator Memory Queues Msg Software Before/After Images Flaws:
• •
No Use of High-Speed Loaders Concurrent ADABAS Updates Can
• •
Cause RDBMS Errors Time Constraints Requires Special Programming Extracts For RDBMS Load Replicated Changes “Live Extract” Utility Simplex Transformations Example Transfor mation JDBC App.
Example JDBC App.
Complex Transformations Customer-Written Application Program RDBMS 35
03/2007
Points of Distinction
Loading the Initial RDBMS Flaws In “Live Data Extracts”
Scenario Flaw
1. Concurrent ADABAS DELETE occurs before the loader has extracted the base record.
2. Concurrent ADABAS INSERT occurs before the loader has extracted the base record.
3. Concurrent ADABAS UPDATE occurs before the loader has extracted the base record.
Etc.
RDBMS replication of the DELETE fails due to “row not found”.
A duplicate row is both replicated and then loaded to the RDBMS.
RDBMS replication of the UPDATE fails due to “row not found”.
Alternatives for Circumvention
•
Lock-out ADABAS Updates during the entire RDBMS Load
•
Custom Programming (Customer-Written Loader Program) 36
03/2007
Points of Distinction
DPSync: Loading the Initial RDBMS
• • • •
Standard Utilities – No Programs to Write Replications Are Accepted and Queued Until the RDBMS Load completes Built-In Sophisticated Transformations High-Speed 37
03/2007
Points of Distinction
Two Ways to Handle Lost Data
1. Lose Data, But Recover It Using Live Data Extracts 2. Replication Software Guarantees No Lost Data
38
03/2007 ADABAS
Points of Distinction
Flawed Process for Handling Lost Data Use “Live Data Extracts” to Recover It ADABAS Replication Data is Lost Replicator Memory Queues Msg Software After Images Only Lost Data Recovery Replicated Changes
• •
Flaws: What Should Be “Refreshed”?
Missing “Before” Image: Was the
• •
Record Inserted?
DELETEs Are Not Handled
•
Replicates Unmodified Records
•
Referential Integrity RDBMS Constraints May Be Violated PE/MU Child/Grandchild Tables
•
Handled?
Requires Special Programming “Live Extract” Utility Simplex Transformations Example Transfor mation JDBC App.
Example JDBC App.
Complex Transformations Customer-Written Application Program RDBMS 39
03/2007
Points of Distinction
DPSync: Guarantee No Lost Data 40
03/2007
Points of Distinction
DPSync: Notification of and Recovery From RDBMS Errors DPSync
• No Programs To Write • Automatic RDBMS Error Notification via Email • Built-In Error Recovery via Scripts • Console Display of RDBMS Errors • Console May Retry Erroneous Transactions
41
03/2007
Points of Distinction
DPSync: Ease of Implementation
• Built-In Analysis and Design Tools – Produces All the Metadata and RDBMS DDL • 100% Off the Shelf – No Programs to Write • Requires No Third-Part Messaging Software • Includes ETL Facility • No Modifications to ADABAS Applications • Uses Existing TCP/IP Network
42
03/2007
Points of Distinction
DPSync: Ease of Maintenance & Operation
• No Programs to Maintain • Off-the-Shelf Software Ensures Future Compatibility • No “Data Recovery” Operational Procedures Needed Due to Lost Data • Facilities for Synchronized Backups and Restores • Tools For Maintaining the Metadata • Built-In Statistics and Console Functions • Treehouse 24x7 Technical Support is Renowned Worldwide
43
03/2007
Points of Distinction
DPSync: Sophisticated Transformation Facilities
Map ADABAS data to multiple, independent RDBMS schemata Value-based mapping and handling of ADABAS record types Transform PEs and MUs to child and grandchild tables Adheres to RDBMS foreign-key/referential integrity constraints for child and grandchild tables Natural dates and binary fields Pseudo data, such as ISN, PE/MU occurrence numbers, DBID, FNR, etc.
Ability to “normalize” PEs and MUs into single rows Concatenate ADABAS fields into a single RDBMS column Extract ADABAS substrings (offset, length) Insert Constants (literals) Transformations are Built-In ( Not a Customer-Written Program ) Etc., on and on…
44
Points of Distinction
DPSync: The Total Solution ADABAS Analysis Design Extraction, Trans formation & Load RDBMS
• • •
Legacy COBOL Programs NATURAL Programs Batch Jobs Real-Time Replication Maintenance Operations Built-In Sophisticated Transformation Facilities
• No Programs to Write • Guarantees No Lost Data • High-Speed ADASAV and RDBMS Loaders • PEs and MUs Transformed To Child Tables • All the Tools Needed • All scenarios served: ETL, “Choose Your Time” Replication, Real-Time Replication
Windows Console 03/2007 45
03/2007
Customer Benefits
A product, not a consulting engagement No impact on the production ADABAS system Cost-effective implementation Time-proven vendor commitment to ADABAS to-RDBMS Replication Software
Over of 11 years of product evolution, from a company with over 30 years of ADABAS experience 46
03/2007
Summary
Real-Time ADABAS-to-RDBMS Replication
Ask The Difficult Questions 47
• • • • • • • • • • • • • • •
Summary: Ask The Difficult Questions
End-to-End Total Solution: Analysis, Design, ETL, Real-Time Replication, Operations Maintenance Guaranteed No Loss of Data Analysis and Design Tools Built-In Sophisticated & Complex Transformations PEs and MUs Transformed to Child and Grandchild Tables , Adherence to RDBMS Foreign-Key/Referential Integrity Constraints Automatic Generation of RDBMS DDL Uses ADASAV and High-Speed RDBMS Loaders No Need for “Full Live Extract” Programming for ETL Special Functions, Such as Synchronized Backups Exception Processing Notification (Email) and Recovery Facilities for RDBMS Errors Windows-Based Console for Statistics and Operations Easy to Implement, Use and Maintain Reliable, Mature Software – 11 Years Evolution No Programs to Write Are These Issues Important To Me?
DPSync – A Mature Product That Meets the Challenge 03/2007 48
03/2007 49