IBM‘s Information Integration Solutions for z/OS Platform

1. IBM‘s Information Integration Solutionsfor z/OS Platform EMEA Executive Database Tools Briefing Event, London June 19-20, The Hyatt Regency Hotel, London Speaker:Robert Kern, IBM Boeblingen Development Lab, GermanyTechnical Sales Enablement for Information Integration on z/OS PlatformEmail: robkern@de.ibm.com, Phone +49 7031 16 4935

2. Agenda • Problems addressed by Information Integration on z/OS platform • Portfolio overview • Classic Federation for z/OS • Classic Event Publishers • Replication for z/OS (Q-Replication) • DataStage • Product Demonstration Integration

3. Problems addressed by Information Integration on z/OS • Data Warehousing • Populate Data Warehouses with mainframe data • Keep Data Warehouses up to date in real time • Profile, cleanse, and transform data • eBusiness • Real time access to operational mainframe data from Web applications, portals, packaged applications and tools, and business processes • SOA • Leverage mainframe data for SOA applications • Continuous availability / load distribution • Planned outage • Unplanned outage – Failover or Hot Standby • Disaster recovery • Global distribution and redundancy of data

4. The IBM WebSphere Information Integration PlatformDelivering information you can trust Service Oriented Architecture Transform Federate Understand Cleanse Discover, define, model, and govern information quality and structure Standardize, merge, and correct information Transform and enrich information Virtualize access to disparate information Integrated Metadata Management Parallel Processing Data Content Connect & Deliver Access, publish, and replicate information

5. Agenda • Problems addressed by Information Integration on z/OS platform • Portfolio overview • Classic Federation for z/OS • Classic Event Publishers • Replication for z/OS (Q-Replication) • DataStage • Product Demonstration Integration

6. WebSphere II Classic Federation for z/OS Read-from and write-to mainframe data sources using SQL from Unix, Windows and JVM platforms • Standardized SQL interfaces • Data transparency • All mainframe data appears as one relational database • Power of SQL versus proprietary, database specific APIs • Metadata-driven: • No mainframe programming required • Fast installation & configuration • Ease of maintenance • Works with existing and new: • Mainframe infrastructure • Application infrastructure • Toolsets DB2 UDB for z/OS VSAM &sequential IMS Software AG Adabas CA Datacom CA IDMS

7. COBOL Copybooks DBDs Schemas 1.Import Physical Definitions metadata catalog Adabas Predict 3.Export Definitions “Use” Grammar DB2 Catalog Data Mapper metadata utilities 4. Update metadata catalog Metadata Management Workflow 2. Customize Logical Tables And Views #2 – Customize views for Classic Federation – Set the change-capture flag for Event Publisher

8. Metadata Catalog DataMapper Classic Federation Component Overview Portal BI Tool Servlet Servlet Client class AIX, HP-UX, Solaris, JVM 1.4, Widows NT, 2000, XP, UNIX System Services ODBC Client JDBC Client EJB z/OS WebSphere Information IntegratorClassic Federation Server Data Connector Data Connector Data Connector Data Connector Data Connector Data Connector Data Connector USE Grammar Copybooks, DBDs, … DB2 UDB z/OS VSAM Sequential IMS Adabas CA-Datacom CA-IDMS

9. Solution Profile: Standard SQL 92 Support • SELECT/INSERT/UPDATE/DELETE all supported • Single and two phase commit • Commit - Rollback - Autocommit supported for all data sources • Rolling delivery of two phase commit:DB2, IMS and CA-Datacom available now • Standard SQL error handling • SQL error and response codes returned as part of result • Stored Procedure “Call” leverages existing programs • Reuse mainframe algorithms • Invoke IMS transactions

10. Value propositions for WebSphere II Classic Federation • Extend the value of existing mainframe investments • Instant integration of mainframe assets into current business initiatives • Non-disruptive to existing applications and data environment • Reduces or eliminates redundant data and its costs • Fits seamlessly into existing IT infrastructure out-of-the-box • Leverages SQL capabilities of modern tools • Works with mainframe infrastructure: security, accounting, monitoring, workload mgmt. • Reduces dependence on scarce mainframe skills • Accelerate time-to-value of enterprise integration projects • No mainframe programming required • Transactional speed and enterprise scale • Easy to configure & maintain using its metadata-driven approach

11. Server ODBC Client Large Bank in Eastern EuropeIBM Solution – Feed VSAM data into Oracle DWH • Building DWH based on Oracle and running on Sun Solaris. ETL solution is to extract data from VSAM and CICS/VSAM files on the mainframe an populate warehouse on Oracle • VSAM files are internal files owned by Kirchman banking software z/OS Solaris Kirchman Banking SW DataStage ODBC Stage Oracle DWH CICS/VSAM VSAM TCP/IP

12. Server Client European-based catalog retailer e-Commerce siteIBM Solution - Single-source mission critical data Seamlessly share order processing data and business logic • No impact on call-centers • New WebSphere e-commerce applications share critical data • Leverage “common” procedures such as ship-to-date calc or pricing • WebSphere Studio development independent of mainframe skills z/OS Customer Order Processing Call-Center Rep Solaris Ship-to Date Calc Customer e-Commerce Site Call-Center Rep Customer TCP/IP IDMS

13. Agenda • Problems addressed by Information Integration on z/OS platform • Portfolio overview • Classic Federation for z/OS • Classic Event Publishers • Replication for z/OS (Q-Replication) • DataStage • Product Demonstration Integration

14. Capture WebSphere II Event Publishers Function • Capture data events in real time • Publish these data events: • to a message queue for widespread delivery • in XML format for widespread use Usage • Application to application messaging • Event streaming • Change-only data distribution ETL or other datapropagation Tool Log-based capture DB2 UDB WebSphere Business Integration Target DBs IMS WebSphereMQ WebSphere MQ Integrator Broker User Application VSAM CA-IDMS JMS-aware Application Adabas

15. WebSphere MQ Adabaschanges CICS-madeVSAMchanges WebSphere II Event Publishers for z/OS Real time DB2, IMS, VSAM, CA-IDMS, and SW AG Adabas changed-data capture • Publish changes to WebSphere MQ for z/OS • Consistent “relational” XML format regardless of data source • WebSphere listener application or tool • Picks up message(s) • Takes action • Two Event Publisher infrastructures: • DB2 Universal Database for z/OS(WebSphere II Replication) • IMS, VSAM and CA-IDMS(WebSphere II Classic Federation) changes changes CA-IDMSchanges DB2 UDB for z/OS changes IMSchanges

16. Why Publish Data? • Application to Application Messaging • Use transactional database events to drive downstream applications or APIs • Event Notification • Stream changed data information to Web interfaces • Stream only particular events of interest (filter data) • MQ provides guaranteed delivery • Avoids the need for 2-phase commit • Works even when the target is not available • Integration is independent of the source applications • Relatively straight forward to find data items- rather than every business rule • Applications grow and evolve with - minimal impact on the integration Data Source Application

17. Information IntegratorEvent Publisher CA-IMS VSAM Event Publishing for Business Intelligence • Integrate captured changed data with WebSphere DataStage • Use a specific transaction format to update target • Deliver just the changes • “Trickle Feed” data on an on-going basis • Optimize resource utilization • Minimize bandwidth requirements • Maximize data currency • Complements WS II Classic Federation • Trickle feed using Event Publishers • Real-time access using Classic Federation • Bulk transfer using Classic Federation XML Stage DataStage IMS Adabas

18. Distribution service Metadata Catalog IMS Event Publisher for IMS Overview z/OS WebSphere MQ Queue Manager correlation service Changes Changes IMS Recovery CCA IMS Active CCA Applications IMS Changes CHANGES Changes IMSLog

19. Event Publisher Example Message Column names of the source table are specified as property values

20. WebSphere MQ Data Event Publishing at a Major Technology Reseller Partner Application Systems CICS transactions process Orders, Payments,Returns etc. OrderProcessing Partner Connectivity Gateway Payment Return changes XML IndianOperations OracleODS HTML Documents BusinessReports IMS changes

21. Server ODBC Client Large Bank in TurkeyIBM Solution – Replication using Event Publisher for IMS and DataStage • Solution is to feed DB2 DWH with IMS data and keep it up-to-date in real time • Classic Federation and DataStage are used to load DB2 DWH with IMS data • EventPublisher for IMS is used along with DataStage to push real-time updates into the DWHh Mission Critical Applications z/OS TCP/IP Data Stage XML Stage WebSphere MQ IMS DWH CHANGES

22. WebSphere MQ Example of Synchronization with CRM and ERP using Classic Event Publisher • Near real-time cross-silo data synchronizationLoosely coupled integration • Minimizes development effort • Simplifies maintenance HRPortal CallCenter Order Processing SiebelDB Siebel EmployeeAdmin HR DataStage TX . IMS VSAM SAP DB SAP CHANGES

23. Agenda • Problems addressed by Information Integration on z/OS platform • Portfolio overview • Classic Federation for z/OS • Classic Event Publishers • Replication for z/OS (Q-Replication) • DataStage • Product Demonstration Integration

24. IBM’s Information Replication Architectures SQL Replication (a.k.a. IBM DataPropagator) • Log or trigger-based asynchronous changed-data capture • Apply to DB2 staging tables • Additional transformations and fan-out from the staging tables • Versatile replication architecture for both homogeneous (DB2 Family)and heterogeneous replication Q-based Replication • Log-based asynchronous changed-data capture • Data distribution via WebSphere MQ message queues • Highly parallel apply mechanism at target platform • High-volume, low-latency architecture • Uni-directional, bi-directional and peer-to-peer • Proven successes with about a year of general availability

25. CD CD CD CD1 CD1 CD1 IMS DB2 DB2 Sybase Sybase Oracle Oracle SQL Server SQL Server Informix Informix Ｃｏｎｔｒｏｌ Ｃｏｎｔｒｏｌ Teradata Capture Admin Apply SQL Replication • Broad set of sources and targets • Well suited to “fan out” requirements • Flexible scheduling, transformation, distribution Trigger Staging Table Trigger based Federation Engine Log Log based Nicknames Nicknames

26. Source Target Control Tables Control Tables Q Capture Q Apply Admin Q Replication • New replication architecture • High throughput and low latency • Multi-directional replication • Highly parallel apply process • Differentiated conflict detection and resolution StoredProcedure ** agent Log agent agent WebSphere MQ Informix Oracle Nicknames ** uni-directional only

27. Production 1 Production 2 Many Models of Replication High Availability(bi-directional) Rollup(many to 1) • Provide continuous availability for critical applications – zero downtime for planned and unplanned outages, automatic conflict detection and resolution • Spread application load across multiple servers or data centers with low-latency data synchronization • Provide consistent, timely information to users and applications for better decision-making while offloading critical application servers • Automatically distribute data to many locations, reducing development costs and effort Central Database Live Backup Production Replication Replication Reporting Tools Regional Database 1 Regional Database 2 Peer To Peer(multi-directional) Distribution(1 to many) DB2 Replication Replication Balanced Workload Oracle MS SQL Informix Sybase

28. Q Capture I/U/D Transaction Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Admin Queue Agent Q Capture constructs transaction messages, at most one tran per msg Log Agent Agent Restart Queue Browser MQ Channel MQ Msg Send Queue Receive Queue

29. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Admin Queue Agent Only committed Transactions are written to MQ message Log Agent Agent Restart Queue Browser MQ Channel MQ PUT Send Queue Receive Queue

30. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Admin Queue Q Capture issues MQCMIT to commit the transaction to the send queue and to update restart information per interval Agent Log Agent Agent Restart Queue Browser MQ Channel MQCMIT Send Queue Receive Queue

31. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Admin Queue Agent Log Agent Agent Restart Queue Browser MQ Channel Send Queue Receive Queue

32. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Admin Queue Agent The role of the browser thread is to browse the queue and analyze transaction dependencies Log Agent Agent Restart Queue Browser MQ Channel Send Queue Receive Queue

33. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Apply agents simply check the work queue for work to do. Admin Queue Agent Log Agent Agent Restart Queue Browser MQ Channel Send Queue Receive Queue

34. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel rebuild SQL to replay the changes and execute them on the target, then, inserts DoneMSG table Admin Queue Agent Log Agent Agent Restart Queue Browser MQ Channel Send Queue Receive Queue

35. Q Capture Q Apply How does Q Replication work ? MQ QMGR MQ QMGR MQ Channel Admin Queue Agent The house keeping thread deletes MQ msg periodically by looking at the DONEMSG table. Log Agent Agent Restart Queue Browser MQ Channel Send Queue Receive Queue

36. Replication Administration • Replication Center GUI • Launchpads, Wizards, Online Help • Definitions, Operations, Monitoring • Command Line Interface • Scripts or interactive mode • Example: • Java API’s • Typically used when replication is embedded • C:\asnclp • REPL > CREATE QSUB USING REPLQMAP ... • REPL > CREATE SUBSCRIPTION SET SETNAME ... • REPL > CREATE MEMBER IN SETNAME ...

37. Q Replication SQL Replication CD2 CD1 CD1 Some Rules – When to choose What • Low-latency, high-volume replication • Source and target tables of similar structure • Bi-directional replication(e.g. for hot-standby purposes) • Peer-2-Peer – splits workload • Huge number of tables (e.g. Siebel) • Replication across DB2-family • Replication from DB2 to federated targets • Fan-out to huge number of targets • Multi-tier staging via CCD • Source and target tables not of the same structure (which requires source views, joins or SQL expressions) • Replication from federated sources

38. Continuous Availability – Basic Requirements • Planned Outage • scheduled outages are still a necessity for most organizations • includes application changes, software and hardware upgrades, and migrations in addition to utility operations • typically handled with a local copy • Unplanned Outage - Failover or Hot Standby • localized failure • temporary or permanent outage of primary source • typically handled with a local copy • Disaster Recovery • widespread failure • temporary or permanent outage of primary source • typically handled with a remote copy • Global Distribution and Redundancy of Data • geographical distribution of data for improved local access • redundancy of data for continuity of business • typically handled with multiple remote copies

39. Q Capture Q Apply Primary Database DSNA Secondary Database DSNB Batch Application Customer Access Heavy Batch Processing Workload Relief • At the beginning of batch processing, the secondary copy is frozen (replication is suspended). Customer access is pointed at the frozen data with excellent performance. Batch processing can proceed at top speed. • At the termination of batch processing, customer access is returned to the primary. Replication of batch data occurs from primary to secondary. After replication is caught up with the batch data, replication of online data continues throughout the day until the start of batch processing.

40. Read Only Applications Q Capture Q Apply Q Apply Q Capture Primary Database DSNA Secondary Database DSNB Primary Connection Connection Available for Failover Continuous Availability using Q Replication Read/Write Applications • Q Replication provides a solution for continuous availability where the active secondary system is also available for other applications

41. Distributed Application for Performance and Availability • Each peer server contains a full global copy of the data, so each application gets a complete data view • Each peer server owns a set of rows within the database and these rows are only updated within the owning server • All row operations are replicated to all other servers • Any individual peer server could provide continuous availability for another server

42. Agenda • Problems addressed by Information Integration on z/OS platform • Portfolio overview • Classic Federation for z/OS • Classic Event Publishers • Replication for z/OS (Q-Replication) • DataStage • Product Demonstration Integration

43. WebSphere DataStage Strengths • Graphical design metaphor • Extensible, component based architecture • Extensive re-use • Built-In scripting language • Graphical sequencing (“job flow”) • Rich support for application deployment • Ubiquitous Connectivity • Unlimited scalability: parallelism

44. Graphical Design Metaphor

45. Agenda • Problems addressed by Information Integration on z/OS platform • Portfolio overview • Classic Federation for z/OS • Classic Event Publishers • Replication for z/OS (Q-Replication) • DataStage • Product Demonstration Integration

46. THANK YOU!

47. Backup

48. WebSphere II Packaging und PIDs Distributed (Linux, UNIX, Windows) Mainframe (IBM eServer zSeries) WS II Advanced Ed. Unlimited PID 5724-C74 WS II Classic Federation for z/OS PID5697-I82 WS II Advanced Ed. WS II Classic Event Publisher for IMS PID5655-M38 WS IIStandardEd. WS II Classic Event Publisher for VSAM WS II Replication Ed. PID5655-M35 WS II Classic Event Publisher for CA-IDMS WS IIEventPublisherEd. PID5655-N56 PID5655-L88 WS II Replication for z/OS DB2 DataPropagator for z/OS PID5655-I60 WS II Developer Edition WS II Event Publisher for DB2 UDB for z/OS PID5655-M36 WS II OmniFind Edition PID 5724-C74 WS II Content Edition PID 5724-J31 • Processor-based pricing except for Developer Edition which is priced by user • Priced Connectors to access non-IBM sources • Value Unit pricing Model