IBM Software Group IBMs Information Integration Solutions for

® IBM Software Group IBM‘s Information Integration Solutions for 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, Germany Technical Sales Enablement for Information Integration on z/OS Platform Email: robkern@de. ibm. com, Phone +49 7031 16 4935 © 2003 IBM Corporation

IBM Software Group 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) Integration § Data. Stage § Product Demonstration 2

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

IBM Software Group The IBM Web. Sphere Information Integration Platform Delivering information you can trust Service Oriented Architecture Understand Cleanse Transform Federate 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 Connect & Deliver Content Access, publish, and replicate information 4

IBM Software Group 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) Integration § Data. Stage § Product Demonstration 5

IBM Software Group Web. Sphere 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 4 Data transparency 4 All mainframe data appears as one relational database 4 Power of SQL versus proprietary, database specific APIs § Metadata-driven: 4 No mainframe programming required 4 Fast installation & configuration 4 Ease of maintenance § Works with existing and new: 4 Mainframe infrastructure 4 Application infrastructure 4 Toolsets DB 2 UDB VSAM & for z/OS sequential IMS Software AG CA Adabas Datacom CA IDMS 6

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

IBM Software Group Classic Federation Component Overview AIX, HP-UX, Solaris, JVM 1. 4, Widows NT, 2000, XP, UNIX System Services z/OS BI Tool ODBC Client Servlet EJB JDBC Client class Web. Sphere Information Integrator Classic Federation Server Metadata Catalog USE Grammar Portal Data Data Connect Connect or or Copybooks, DBDs, … DB 2 UDB z/OSVSAM Sequential IMS Adabas CACA-IDMS Datacom Data. Mapper 8

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

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

IBM Software Group Large Bank in Eastern Europe IBM 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 Data. Stage ODBC Stage CICS/ VSAM Server Oracle DWH TCP/IP ODBC Client 20

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

IBM Software Group 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) Integration § Data. Stage § Product Demonstration 23

IBM Software Group Web. Sphere II Event Publishers Function Usage § Capture data events in real time § Application to application messaging § Publish these data events: § Event streaming 4 to a message queue for widespread delivery 4 in XML format for widespread use § Change-only data distribution ETL or other data propagation Tool Log-based capture DB 2 UDB IMS VSAM CA-IDMS Adabas Capture Web. Sphere MQ Web. Sphere Target DBs Business Integration Web. Sphere MQ User Application Integrator Broker JMS-aware Application 24

IBM Software Group Web. Sphere II Event Publishers for z/OS Real time DB 2, IMS, VSAM, CA-IDMS, and SW AG Adabas changeddata capture § Publish changes to Web. Sphere MQ for z/OS § Consistent “relational” XML format regardless of data source § Web. Sphere listener application or tool 4 Picks up message(s) Web. Sphere MQ changes 4 Takes action § Two Event Publisher infrastructures: 4 DB 2 Universal Database for z/OS (Web. Sphere II Replication) 4 IMS, VSAM and CA-IDMS (Web. Sphere II Classic Federation) CA-IDMS CICS-made IMS DB 2 UDB Adabas for z/OS changes VSAM changes 25

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

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

IBM Software Group Event Publisher for IMS Overview z/OS Changes correlatio n service IMS Active CCA Applications IMS Web. Sphere MQ Queue Manager Changes Distributi on service Metadata Catalog IMS Recovery CCA Change s I MS Log CHANGES Changes 36

IBM Software Group Event Publisher Example Message Column names of the source table are specified as property values 38

IBM Software Group Data Event Publishing at a Major Technology Reseller CICS transactions process Orders, Payments, Returns etc. Partner Application Systems Order Processing Return Payment Partner Connectivity Gateway changes Web. Sphere MQ XML Indian Operations Oracle HTML ODS Documents IMS Busines s Reports changes 39

IBM Software Group Large Bank in Turkey IBM Solution – Replication using Event Publisher for IMS and Data. Stage § Solution is to feed DB 2 DWH with IMS data and keep it up-to-date in real time § Classic Federation and Data. Stage are used to load DB 2 DWH with IMS data § Event. Publisher for IMS is used along with Data. Stage to push real-time updates into the DWHh Mission. Critical Applications z/OS TCP/IP ODBC Client Server Web. Sphere MQ IMS XML Stage Data Stage DWH CHANGES 41

IBM Software Group Example of Synchronization with CRM and ERP using Classic Event Publisher § Near real-time cross-silo data synchronization Loosely coupled integration 4 Minimizes development effort 4 Simplifies maintenance Call Center HR Portal Order Processing Employee Admin HR IMS VSAM Siebel DB Data. Stage TX. Web. Sphere MQ SAP DB CHANGES 42

IBM Software Group 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) Integration § Data. Stage § Product Demonstration 43

IBM Software Group IBM’s Information Replication Architectures SQL Replication (a. k. a. IBM Data. Propagator) § Log or trigger-based asynchronous changed-data capture § Apply to DB 2 staging tables § Additional transformations and fan-out from the staging tables § Versatile replication architecture for both homogeneous (DB 2 Family) and heterogeneous replication Q-based Replication § Log-based asynchronous changed-data capture § Data distribution via Web. Sphere 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 44

IBM Software Group SQL Replication Sybas e Oracl e Admin SQL Serv er Inform ix DB Log based 2 Staging Table CD 1 CD Trigger based IM S DB 2 Trigg er Ｃｏｎｔｒｏｌ CD 1 CD Capture CD 1 CD Apply Federation Engine Log § Broad set of sources and targets Sybas e Oracl e SQL Serv er Inform ix Teradat a Nicknames § Well suited to “fan out” requirements § Flexible scheduling, transformation, distribution 45

IBM Software Group Q Replication Control Tables Sourc Log e Admi n Q Capture Stored Procedure ** Control Tables Q Apply agent Target Web. Sphere MQ Inform ix § New replication architecture 4 High throughput and low latency 4 Multi-directional replication Oracl e Nicknames ** uni-directional only § Highly parallel apply process § Differentiated conflict detection and resolution 47

IBM Software Group Many Models of Replication High Availability Rollup (bi-directional) Live Backup Production (many to 1) § Provide continuous availability for critical applications – zero downtime for planned and unplanned outages, automatic conflict detection and resolution Central Database Replication Reporting Tools Regional Database 1 Peer To Peer Distribution (multi-directional) Production 1 Regional Database 2 (1 to many) DB 2 Production 2 Replication Balanced Workload Oracle MS SQL Informix Sybase § 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 49

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

Information Management How does Q Replication work ? MQ QMGR SOURCE MQ QMGR TARGET MQ SOURCE DONEMSG TARGET Channel Admin Queue Log Only committed Transactions are written to MQ message Agent Restart Queue Browser MQ Q Capture Q Apply Channel MQ PUT Send Queue Receive Queue 51

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

Information Management How does Q Replication work ? SOURCE MQ QMGR TARGET MQ SOURCE DONEMSG TARGET Channel Admin Queue Agent Log Agent Restart Queue Browser MQ Q Capture Q Apply Channel Send Queue Receive Queue 53

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

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

Information Management How does Q Replication work ? SOURCE MQ QMGR TARGET MQ SOURCE Admin Queue Log DONEMSG Channel rebuild SQL to replay the changes and execute them on the target, then, inserts Done. MSG table TARGET Agent Restart Queue Browser MQ Q Capture Q Apply Channel Send Queue Receive Queue 56

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

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

IBM Software Group 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 DB 2 -family ü Replication from DB 2 to federated targets Q Replication CD 2 CD 1 SQL Replication Ë 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 60

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

IBM Software Group Heavy Batch Processing Workload Relief Q Capture DSNA Database Primary Q Apply DSNB Secondary Database Batch Application Customer Access ▪ 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. © 2005 IBM Corporation 62

IBM Software Group Continuous Availability using Q Replication Read Only Applications Q Apply Q Capture Q Apply DSNA Database Primary DSNB Secondary Database Primary Connection Available for Failover Read/Write Applications ▪ Q Replication provides a solution for continuous availability where the active secondary system is also available for other applications © 2005 IBM Corporation 63

IBM Software Group 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 © 2005 IBM Corporation 64

IBM Software Group 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) Integration § Data. Stage § Product Demonstration 68

IBM Software Group Web. Sphere Data. Stage 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 69

IBM Software Group Graphical Design Metaphor 70

IBM Software Group 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) Integration § Data. Stage § Product Demonstration 72

IBM Software Group THANK YOU! 73

IBM Software Group Backup 74

IBM Software Group Web. Sphere II Packaging Distributed (Linux, UNIX, Windows)und PIDs Mainframe (IBM e. Server z. Series) WS II Advanced Ed. Unlimited PID 5724 -C 74 WS II Classic Federation for z/OS PID 5697 -I 82 WS II Standard Ed. WS II Classic Event Publisher for IMS PID 5655 -M 38 WS II Replication Ed. WS II Classic Event Publisher for VSAM PID 5655 -M 35 WS II Event Publisher Ed. WS II Classic Event Publisher for CA-IDMS PID 5655 -N 56 WS II Replication for z/OS PID 5655 -L 88 DB 2 Data. Propagator for z/OS PID 5655 -I 60 WS II Event Publisher for DB 2 UDB for z/OS PID 5655 -M 36 WS II Advanced Ed. WS II Developer Edition WS II Omni. Find Edition PID 5724 -C 74 WS II Content Edition PID 5724 -J 31 • Processor-based pricing except for Developer Edition which is priced by user • Priced Connectors to access non-IBM sources • Value Unit pricing Model 75