® ibm software group © 2003 ibm corporation ibms information integration solutions for z/os...

®

IBM Software Group

© 2003 IBM Corporation

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: [email protected], Phone +49 7031 16 4935

IBM Software Group

2

Agenda

Integration

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

IBM Software Group

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

IBM Software Group

4

The IBM WebSphere Information Integration PlatformDelivering information you can trust

Understand

Cleanse Transform Federate

Service Oriented Architecture

Discover, define, model, and govern information quality

and structure

Standardize, merge,and correct information

Transform and enrich information

Virtualize access to disparate

information

Data ContentConnect & Deliver

Access, publish, and replicate information

Integrated Metadata Management

Parallel Processing

IBM Software Group

5

Agenda

Integration



Portfolio overview




DataStage


IBM Software Group

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/OSSoftware AG

AdabasVSAM &

sequentialCA

IDMSCA

DatacomIMS

IBM Software Group

7

Metadata Management Workflow

2.Customize

Logical TablesAnd Views

Data Mapper

1.Import Physical

Definitions

COBOL Copybooks DBDs Schemas

3.Export

Definitions

“Use”Grammar

metadatacatalog

metadatautilities 4.

Update metadata catalog

DB2Catalog

AdabasPredict

#2 – Customize views for Classic Federation – Set the change-capture flag for Event Publisher

IBM Software Group

8

MetadataCatalog

WebSphere Information IntegratorClassic Federation Server

z/OS

DB2 UDB z/OS VSAM IMS CA-Datacom CA-IDMSAdabas

Data Connector

Data Connector

Data Connector

Data Connector

Data Connector

Data Connector

Classic Federation Component OverviewPortal BI Tool Servlet Servlet Client

class

EJB

AIX, HP-UX, Solaris, JVM 1.4,Widows NT, 2000, XP, UNIX System Services ODBC Client JDBC Client

DataMapper

Copybooks, DBDs, …

USEGrammar

Sequential

Data Connector

IBM Software Group

17

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

IBM Software Group

19

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

Value propositions for WebSphere II Classic Federation

IBM Software Group

20

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

SolarisKirchman

Banking SWDataStage

TCP/IPVSAMCICS/VSAM Server

ODBC Client

z/OS

ODBCStage

Large Bank in Eastern EuropeIBM Solution – Feed VSAM data into Oracle DWH

OracleDWH

IBM Software Group

21

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

Customer

Customer

CustomerTCP/IP

OrderProcessing

e-CommerceSite

Solaris

Call-CenterRep

Call-CenterRep

Ship-toDate Calc

Server ClientIDMS

z/OS

European-based catalog retailer e-Commerce siteIBM Solution - Single-source mission critical data

IBM Software Group

23

Agenda

Integration



Portfolio overview




DataStage


IBM Software Group

24

WebSphere II Event Publishers

Usage

Application to application messaging

Event streaming

Change-only data distribution

Function

Capture data events in real time

Publish these data events: to a message queue for widespread delivery in XML format for widespread use

Log-basedcapture

User Application

DB2 UDB

VSAM

CA-IDMS

CaptureWebSphere

MQ WebSphere MQ Integrator Broker

Target DBs

ETL or other datapropagation Tool

JMS-aware Application

WebSphere Business Integration

Adabas

IMS

IBM Software Group

25

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)

CICS-madeVSAM

changes

IMSchanges

DB2 UDBfor z/OS changes

changes changes

CA-IDMSchanges

WebSphere MQ

Adabaschanges

IBM Software Group

26

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

Application

Data Source

IBM Software Group

27

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

Event Publishing for Business Intelligence

VSAM IMS

Information IntegratorEvent Publisher

CA-IMS

DataStage

XML Stage

Adabas

IBM Software Group

36

Event Publisher for IMS Overview

z/OS

MetadataCatalog

IIMMSSLogLog

Applications IMS

Cha

nges

IMS ActiveCCA

Changes

IMS

correlation service

Distribution service WebSphere MQ

Queue Manager

IMS RecoveryCCA

Cha

nges

CHANGES Changes

IBM Software Group

38

Event Publisher Example Message

Column names of the source table are specified as property values

IBM Software Group

39

Data Event Publishing at a Major Technology Reseller

OrderProcessing

PaymentReturn

BusinessReports

CICS transactions

processOrders,

Payments,Returns etc.

HTML Documents

PartnerApplicatio

nSystems

OracleODS

changes

IMS

changes WebSphere MQ

Partner Connectivity

Gateway

XML

IndianOperations

IBM Software Group

41

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

z/OSApplications

IMS

CriticalMission

CHANGES

DWH

Data Stage

WebSphere MQ XML Stage

Large Bank in TurkeyIBM Solution – Replication using Event Publisher for IMS and DataStage

TCP/IP

ServerODBC Client

IBM Software Group

42

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

HR

SAPSAP DB

DataStage TX .

CHANGES

WebSphere MQ

OrderProcessing

CallCenter

EmployeeAdmin

HRPortal

Siebel

SiebelDB

IMS

VSAM

IBM Software Group

43

Agenda

Integration



Portfolio overview




DataStage


IBM Software Group

44

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

IBM Software Group

45

SQL Replication

Nicknames

Staging Table

Log based

Trigger based

IMSDB2

Sybase

Oracle SQL Server

Informix

Admin

Ｃｏｎｔｒｏｌ

FederationEngine

DB2

Sybase

Oracle SQL Server

Informix

Teradata

Nicknames

Apply

CD1CD

CD1CD

CD1CD

Ｃｏｎｔｒｏｌ　

Capture

Trigger

Log

Broad set of sources and targets

Well suited to “fan out” requirements

Flexible scheduling, transformation, distribution

IBM Software Group

47

Q Replication

New replication architecture High throughput and low latency Multi-directional replication

Highly parallel apply process

Differentiated conflict detection and resolution

Control TablesControl Tables

Source

Admin

WebSphere MQ

Q Capture Q ApplyLogTarget

Informix Oracle

Nicknames

agentagentagent

** uni-directional only

StoredProcedure **

IBM Software Group

49

Many Models of Replication

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

High Availability(bi-directional)

Distribution(1 to many)

DB2

Oracle MS SQL Informix Sybase

Replication

Rollup(many to 1)

Balanced Workload

Replication

Live Backup

Reporting Tools

Production

Production 2Production 1

Replication

Peer To Peer(multi-directional)

Central Database

Regional Database 1

Regional Database 2

Replication

Information Management

50

How does Q Replication work ?

Log

I/U/D Transaction

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ Msg

Q Capture constructs transaction messages, at most one tran per msg

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG

MQ QMGR MQ QMGRSOURCE

SOURCE

Admin Queue

MQ

Channel


51


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ PUT

Only committed Transactions are written to MQ message

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

Admin Queue

MQ

Channel


52


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQCMIT

Q Capture issues MQCMIT to commit the transaction to the send queue and to update restart information per interval

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

Admin Queue

MQ

Channel


53


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

Admin Queue

MQ

Channel


54


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

The role of the browser thread is to browse the queue and analyze transaction dependencies

Admin Queue

MQ

Channel


55


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

Admin Queue

MQ

ChannelApply agents simply check the work queue for work to do.


56


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

rebuild SQL to replay the changes and execute them on the target, then, inserts DoneMSG table

Admin Queue

MQ

Channel


57


Log

Q Capture

Receive Queue

Send Queue

Restart Queue

MQ

Channel

TARGET

TARGET

AgentAgent

Browser

Q Apply

Agent

DONEMSG


SOURCE

The house keeping thread deletes MQ msg periodically by looking at the DONEMSG table.

Admin Queue

MQ

Channel

IBM Software Group

58

Replication Administration Replication Center GUI

Launchpads, Wizards, Online Help

Definitions, Operations, Monitoring

Command Line InterfaceScripts or interactive mode

Example:

Java API’s Typically used when replication is embedded

C:\asnclpREPL > CREATE QSUB USING REPLQMAP ... REPL > CREATE SUBSCRIPTION SET SETNAME ...REPL > CREATE MEMBER IN SETNAME ...

IBM Software Group

60

SQL Replication

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

Q Replication

CD2

CD1CD1

IBM Software Group

61

Continuous Availability – Basic Requirements 1

12

2

3

45

67

8

9

10

11

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


IBM Software Group

62

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.

Batch Application

Q Capture

Primary DatabasePrimary Database

Q Apply

Customer Access

Secondary DatabaseSecondary DatabaseDSNA DSNB

112

2

3

4567

8

9

10

11


IBM Software Group

63

Continuous Availability using Q Replication

▪ Q Replication provides a solution for continuous availability where the active secondary system is also available for other applications

Read/Write Applications

Q Capture

Primary DatabasePrimary Database

Q Apply

Q Apply

Q Capture

Read Only Applications

Secondary DatabaseSecondary DatabaseDSNA DSNB

Connection Available for FailoverPrimary Connection


IBM Software Group

64

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

IBM Software Group

68

Agenda

Integration



Portfolio overview




DataStage


IBM Software Group

69

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

IBM Software Group

70

Graphical Design Metaphor

IBM Software Group

72

Agenda

Integration



Portfolio overview




DataStage


IBM Software Group

73

THANK YOU!

IBM Software Group

74

Backup

IBM Software Group

75

PID 5724-C74WS II Advanced Ed. Unlimited

WS II Advanced Ed.

WS II Standard Ed.

WS II Replication

Ed.

WS II Developer Edition

WS II Event Publisher Ed.

WS II OmniFind Edition

Distributed (Linux, UNIX, Windows) Mainframe (IBM eServer zSeries)

PID 5655-M35WS II Classic Event Publisher

for VSAM

PID 5655-M38WS II Classic Event Publisher

for IMS

PID 5655-L88WS II Replication for z/OS

PID 5697-I82WS II Classic Federation for z/OS

PID 5724-C74

• Processor-based pricing except for Developer Edition which is priced by user

• Priced Connectors to access non-IBM sources

• Value Unit pricing Model

WebSphere II Packaging und PIDs

WS II Content Edition PID 5724-J31

PID 5655-I60DB2 DataPropagator for z/OS

PID 5655-M36WS II Event Publisher for DB2 UDB for z/OS

PID 5655-N56WS II Classic Event Publisher

for CA-IDMS

® ibm software group © 2003 ibm corporation ibms information integration solutions for z/os...

Documents

ibm software group

zos data warehousing

data warehouses

recurring data

redundancy of data slide

operational mainframe

views data mapper

physical data definitions