High Performance Enterprise Data Propagation Russell Donovan

High Performance Enterprise Data Propagation Russell Donovan

BMC Company Profile u Established u Leader in Application Management u Estimated u Over in 1980 FY 2000 Revenues of $1. 8 B 6, 000 Employees u Development Labs in Austin (TX), Conyers (GA), Houston, San Jose, Sunnyvale (CA), Waltham (MA) Germany, Israel, Singapore u Market Coverage in Over 50 Countries u Member of the S&P 500

BMC Software e-Business Availability u Provides application management solutions that ensure the availability, performance, and recovery of businesscritical applications. u We call this application service assurance and it means that the applications companies and their customers rely on will be there when they need them. e-vailability - We Guarantee Our Solutions!

Enterprise Data Propagation (EDP) Requirement For All Enterprises Need to synchronize data between legacy systems and distributed relational databases for: u Data warehousing, operational data stores, data mining u e-Business applications access to legacy data u Enterprise application integration u Distributed IMS enterprises, ERP solutions, Acquisitions DB 2 70% of corporate data in IMS, VSAM, DB 2 Need high performance solutions Need near real time solutions VSAM Other

Data Propagation - Strategies For Synchronizing Multiple Copies of Data Copy Unload/Load SQL Query Distributed Database 2 Phase Commit Source Change Capture --- With Asynchronous Propagation Target

Key Challenges Implementing a Data Warehouse Data Management Review Survey u Business rule analysis u Managing End User Expectation u Business data modeling u Reliability and integrity of data u Data acquisition u Meta Data management u Managing Management Expectation u Database performance

Data Warehouse Implementations For Customers With u u u Large operational databases High transaction rates 24 x 7 operations requirements Critical Management Issues u u u Availability of operational systems Performance of operational transactions Maintaining service levels Increasing volumes of data Time required to load and refresh data warehouse Quality, currency & accuracy of decision making data

Building Data Warehouses: A Perspective VSAM Files Operational Database Images Subject Time Variant Oriented Subject Oriented Operational Data Warehouses Images Data Marts

Building Data Warehouses: A Perspective Mainframe Tools Prism, ETI Carleton, SAS Platinum Data Warehouse Data Ware. House DB 2 IMS VSAM Oper. Data Store Data Mart End-Users Query Tools Brio, Bus. Objects COGNOS Microstrategy

Building Data Warehouses: A Perspective Mainframe Tools Prism, ETI Carleton, SAS Platinum Data Warehouse Integration DB 2 IMS Area Data Ware. House Oper. Data Store VSAM Dist. Systems. Tools Informatica, Constellar D 2 K, Sagent, Ardent Data Mart End-Users Query Tools Brio, Bus. Objects COGNOS Microstrategy

Building Data Warehouses: A Perspective Mainframe Tools Prism, ETI Carleton, SAS Platinum BMC Solution Data Warehouse Integration Area DB 2 Bulk Data. Move IMS VSAM Change. Data. Move Data Ware. House Oper. Data Store Dist. Systems. Tools Informatica, Constellar D 2 K, Sagent, Ardent Data Mart End-Users Query Tools Brio, Bus. Objects COGNOS Microstrategy

Change Data Propagation: A Perspective Change Data Propagation Is Preferred When: u Databases are large and bulk move would take too long l Batch window limitations l Database availability limitations u u u Support for 24 x 7 is a requirement of operational application Minimum latency “Near-real-time” is required in target database! Currency of information in target database is important Small percentage of a large database has changed Need to reduce network traffic by transmitting only data changes Source Target

Transaction Based Change Data Propagation Synchronous Data Propagation u. Original l update waits until all targets are updated Single, global transaction with multi-site, coordinated commit processing Asynchronous Data Propagation u. Propagation transaction l l Source Target of updates occurs asynchronous to originating Minimizes resource consumption at source Minimizes impact on source transaction response times Source Target

Synchronous vs Asynchronous Change Data Propagation Synchronous 2 Phased Commit Source transaction completes when all databases updated Advantages: t Real time propagation t. All sites always synchronized u Disadvantages: t. Transaction response time t. Data availability impact t. System resiliency u t. Usually not practical Asynchronous Data Propagation Source transaction does not wait for target databases to be updated u Advantages: t. Minimum performance impact t. Availability t. Autonomy t. Recoverability u. Disadvantages: ttarget locations updates may be delayed t. All sites not always synchronized

Asynchronous Change Capture: Implementation Considerations Trigger Based u u Triggers used to capture changes to database records Incremental updates collected in staging tables Significant resource consumption for triggers and logging Typically low volume applications (< 20 transactions/second) Log Exit Based u u Increased logging in operational environment Increased response times for source transactions Increased resource consumption Log management issues Log Post Process Based u u u Increased logging in operational environment Log management issues Long latency interval can not support near real time

Enterprise Data Propagation (EDP) The BMC Solution A Data Propagation Management System A single point of access for managing Legacy data propagation across the enterprise DB 2 IMS Fast Path VSAM Bulk Data Change Data Operational Data Store u. Efficient change capture u. Basic data transformation u. High performance data movement u. High performance utilities Common look and feel u Integrated transformations and mappings u Integrated recovery/restart u

Change. Data. Move: Product Positioning Change. Data. Move is a high performance, efficient, change data propagation solution, which captures changes made to IMS, Fast Path, VSAM, and DB 2 databases, and propagates those changes to the most prevalent relational databases. What It Does Transaction-based data propagation Supports high volume production applications with hundreds of transactions per second Supports ‘near real-time’ as well as scheduled data propagation Advantages A data propagation system (complete solution vs a point product) Highly efficient change capture does not impact applications Only solution for IMS, Fast. Path and VSAM that does not require logging Optionally integrated with Data. Move for bulk data movement

Change Data Propagation for IMS and VSAM Synchronous Change Capture Asynchronous Data Propagation u Transparent high performance u Data Propagated Within Context of change capture u Minimum impact on source system logging, CPU & user response time u Data is available immediately for asynchronous propagation Original Transaction u Updates applied in proper sequence u Inter and intra-table consistency u Source and target(s) consistent within transaction boundaries 1 1 2 3 EDP Log EDP Apply 2 3 u Not affected by network delays or slow remote processors u Supports “Near Real Time” and/or Scheduled Propagation

IMS Change Capture u Resides within the IMS environment u Captures DL/I calls as they occur l l Supports IMS/TM (MPP, BMP), Fast Path, CICS DBCTL, Batch DL/I Commits updates at transaction or job (batch) end User Application IMS Subsystem Based on BMC Software’s CHANGE RECORDING FACILITY EDP Logger IMS Database ECCR EDP Log LRP BMC Apply DB 2 Oracle SQL Server TNR OEM Apply Sybase UDB

CICS/VSAM Change Captures changes at each Get, Put & Erase request u Utilizes CICS TRUE, File, and Re-sync exits u Resides as functional part of CICS address space u l l Participates in two phase commit with CICS transaction Updates are committed when transaction commits User Application BMC Apply CICS Subsystem EDP Logger VSAM Database ECCR EDP Log LRP DB 2 Oracle SQL Server TNR OEM Apply Sybase UDB

VSAM Batch Change Capture Journad exit dynamically activated u ECCR resides within the batch address space u UOW is complete when application closes VSAM file u DB 2 User Application Batch Address Space BMC Based on BMC Software’s RECOVERY PLUS Apply for CICS/VSAM product. EDP Logger VSAM Database ECCR EDP Log LRP Oracle SQL Server TNR OEM Apply Sybase UDB

DB 2 MVS Change Capture u Requires DB 2 change data capture be activated u Reads log records via DB 2 IFI, external decompression u Maintains multiple versions of schema DB 2 MVS DB 2 Uses DB 2 IFI Facility User Application DB 2 ECCR EDP Logger EDP Log BMC Apply LRP Oracle SQL Server TNR Sybase OEM Apply UDB

The Transformation Process u Transforms IMS, Fast Path and VSAM data to relational formats Hierarchical structures to relational structures u Converts non-relational data types to relational l Uses relational DBMS catalog information l Uses copy libraries and IMS database descriptors l Automatically handles Dates, Times, Data Types l Repeating groups, Redefined records u Customizable through user exits l VSAM Files Transformation

Possible Target Keys u To allow resulting target rows to be unique l Replication Key (REPKEY) u l Ancestor Keys u l If REPKEY is a composite key (I. e. IMS concatenated key) each level is available to be used as the key of the target row Sequential number u l This key will make the target row unique n For IMS it is the full concatenated key or segments RBA If a single input segment or record creates multiple output rows, a sequential numeric column can be generated. Any field in the input segment or record

Transforming Cobol Structures u Repeating Groups l all repeated fields to a single target column l As individual rows in the same or a different table u Update results in set of deletes and inserts for target rows u Redefined Records l assigned unique names and schema definitions l Record identification exit identifies record types l Schema applied to segment or record based on redefined record type u Redefined records can be propagated to same or different targets

High Performance Transport & Apply u u Data is blocked, compressed and encrypted Multi-threaded apply tasks for increased performance EDP Apply Send T R A N S P O R T Receive TCP/IP T R A N S P O R T DB 2 Dynamic Memory Staging Queue Oracle Dynamic Memory Queue EDP Apply EDP Apply

Automated Schema Replication u Reduce administration costs by automating the creation of target tables from IMS, VSAM, and DB 2 source schema DB 2 DBD Copybook VSAM Files Schema. Move Oracle DB 2 Catalog MS SQL Server

Bulk Data Propagation Bulk move is usually simpler and easier to implement Needed to initially create or to refresh a target database Bulk move is the preferred solution when: u Data volumes are not large and the move can be performed within time constraints u Database availability is not a concern (source/target) u Network volumes and network overhead are not issues u Currency of information in target database is not a concern u Change data propagation cannot handle the volumes Source Target

Bulk Data Movement DB 2 to Oracle The Traditional Approach MVS Host File DB 2 Extract DB 2 Time DB 2 35% DB 2 Unload 20 min. 13% File Transfer 7 min. Gateway TCP/IP UNIX Server 52% Gateway File Oracle Loader Oracle SQL Load 28 min. Oracle Total Time 55 min.

Bulk Data Movement DB 2 to Oracle Parallel Unload Parallel load MVS Host File DB 2 Extract DB 2 Time DB 2 Parallel Unload 7 min Gateway TCP/IP File Transfer 7 min. UNIX Server Gateway File Oracle Loader Oracle SQL Load 16 M Oracle Total Time 30 min.

Bulk Data Movement DB 2 to Oracle Parallel Unload/load & Piping MVS Host File DB 2 Extract DB 2 Parallel Unload Gateway PIPING TCP/IP UNIX Server Parallel load Oracle load starts as first record is read from DB 2 Gateway File Oracle Loader Oracle Total Time 17 min.

Data. Reach: Product positioning Positioning Data. Reach is a high performance, high availability data movement solution for extracting MVS/ESA DB 2 data and loading it into Informix, Oracle or Sybase database on Unix. A joint development effort of EMC & BMC - Not A Product We Sell Today What It Does Uses EMC Storage to move data at channel speeds vs network speeds u Moves the work of extracting DB 2 MVS data from MVS to Unix u Advantages u u u Moves data 10 to 100 times faster than network solutions Completely eliminates mainframe processing Completely eliminates network traffic and network overhead Allows nearly 100% availability of the source DB 2 database Enables customers to more frequently refresh data warehouses

Bulk Data Movement DB 2 to Oracle The Data. Reach Approach Data. Reach Directly Extracts DB 2 Data u. Eliminates network traffic & network overhead u. Familiar SQL-based SELECT syntax u. Subset of data via WHERE predicate u. Optional parallel extraction capability u. Optional access via DB 2 Index structures u. Data conversion l EBCDIC to ASCII l DB 2 to generic format u. Direct load of Oracle, Sybase, Informix u. Optional parallel load capability u. Distributed capabilities Intermediate File MVS Host DB 2 UNIX Host DB 2 Extract Oracle Loader Oracle

Data. Reach: How It Works MVS System DB 2 Escon Channels CKD Volumes DB 2 Source FBA Volumes SYMMETRIX ESP Target DBMS SCSI Channels Extractor UNIX Translation Module Target DBMS Native load utility Target RDBMS Flat File

Data. Reach: Performance Benchmark DB 2 to Oracle on HP/UX 1, 000. 00 900. 00 800. 00 Minutes 700. 00 600. 00 Traditional 500. 00 Data. Reach 400. 00 300. 00 200. 00 100. 00 10 Mbytes 100 Mbytes 1 Gbyte Size 5 Gbytes 10 Gbytes

Traditional Process vs Data. Reach

Data. Reach: Operational Considerations Data Consistency: Quiesce DB 2 u High Availability: Use A mirror copy in Symmetrix u Security: Data. Reach Authorization Table in DB 2 u l l l DB 2 Read access Unix Login Target RDBMS authorizations

Extract, Transform, Move & Load Options A Performance Perspective M Bytes per Hour 4000 Making The Right Choice 3500 3000 2500 2000 1500 1000 500 0 Change Data Propagation RYO Bulk Move Solutions Data. Reach Parallel Unload/Load Piping

High Performance Data Propagation Strategy for Supporting Data Warehouse Operational Applications IMS Fast Path DB 2 VSAM Other High Performance Data Propagation Integration Area Operational Data Store Change History Data Warehouse Refresh Data Mart Business Intelligence Systems Data Mart

High Performance Data Propagation Strategy for Supporting DW & e-Business Operational Applications Web Server App. Server In qu ire s Updates IMS Fast Path DB 2 Integration Area VSAM Operational Data Store Other Change History High Performance Data Propagation Data Warehouse Refresh Data Mart Business Intelligence Systems Data Mart

High Performance Data Propagation Strategy for Enterprise Application Integration Operational Applications Note: This is a BMC Services Offering IMS DB 2 People. Soft Baan VSAM Other Oracle Messaging Bulk Message Queue High Performance Data Propagation SAP ERP Tools Change Message Queue Data Warehouse Data Mart e-business Applications

Major U. S. Brokerage Firm Application Integration example Global corporation headquartered in New York City providing: u Securities u Asset Management u Credit and transaction services

The Problem Business challenge u Migration to new strategic DBMS could not impact business operations Technical challenge u Keep current ADABAS DBMS synchronized with new strategic DB 2 DBMS u The solution had to be sustainable for the longterm and also be scalable

The Solution u u u Client already had an ADABAS log capture mechanism and MQSeries. A “Custom Adapter for Source MQSeries” to Change Data Move l written in ASM l runs as a started task Primarily batch with over 700 files (as sources). ADABAS Batch Log Capture Address Space MQSeries Queue MQGET EDM Logger Custom Adapter EDM Log

Major U. S. Bank e-Business example Provides anytime, anywhere access to products and services through: u Walk up services u Automated Teller Machines (ATM) u 24 -Hour Phone Banking u Internet banking Offices in 17 Midwestern and Western states

The Problem Business Challenge u Multiple access methods drive a need to provide a common method to authenticate an account owner Technical Challenge u Account verification information is maintained in purchased IMS application u Move to leading edge Storage Area Network technology and required integration.

The Solution Process Action Controller LRP u u u TNR Target is not a “conventional DBMS” but a storage area network. High data volumes Target data written to MQSeries End UOW Data DB 2 Custom Adapter MQSeries Queue MQPUT

High Performance Data Propagation Facilitating DBMS Migrations u Change target DBMS without impacting operational applications l Move target DB from Sybase to Oracle to SQL Server to UDB to ? ? DB 2 User Application DB 2 IMS Fast Path VSAM BMC Apply EDP Logger ECCR EDP Log LRP Oracle SQL Server TNR OEM Apply Sybase UDB

BMC’s Data Propagation is Different? Transaction based data propagation supports applications executing hundred’s of transactions/second u For IMS, Fast Path, CICS VSAM and VSAM Batch l l l u. For Does not use IBM* capture exits, logs, or require any additional logging Automatically transforms non-relational data structures to relational Supports “Near-Real-Time” with minimum latency for target updates No requirement for DB 2 staging tables and associated logging Captures changes from VSAM batch applications even when no logs are used DB 2 l l l No requirement for DB 2 staging tables and associated logging Transaction consistent propagation Supports “Near-Real-Time” with minimum latency for target updates u. Component l l l of a Complete Enterprise Data Movement Solution Common management console - Easy to administer Integrated restart/recovery of the propagation process Shared data transformations

Extract, Transform, Move & Load Options A Performance Perspective M Bytes per Hour 4000 Making The Right Choice 3500 3000 2500 2000 1500 1000 500 0 Change Data Propagation RYO Bulk Move Solutions Data. Reach Parallel Unload/Load Piping