E162 Sybase Data Integration: Analyzing Your Options

1
E162 Sybase Data Integration Analyzing Your
Options

• Lynn Weatherwax
• Director of Engineering Data Integration
• Enterprise Solutions Division
• Lynn.Weatherwax_at_sybase.com

2
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Data Movement Bulk and Replication
• Real Solutions
• Conclusions
• Wrap-up and QA

3
Data Integration Challenge

• Every enterprise has multiple, disparate islands
  of data, due to
• Mergers and acquisitions
• Legacy mainframe applications
• Applications required to work with specific
  databases
• Definition of legacy application one that
  works

Adabas
Microsoft
Sybase
Sybase
Oracle
VSAM
Informix
DB2 UDB
4
You Could Migrate All Data and Apps to One
Database

• Sometimes this is feasible, but often there are
  time and restrictions
• And what about the next acquisition, or the next
  undiscovered information island?

Adabas
Microsoft
Sybase
My Centralized Database
Oracle
VSAM
Informix
DB2 UDB
5
Data Access Requirements

• Leverage existing hardware and software
  investment
• Ensure secure access
• Integrate legacy data and applications while they
  remain in place short-term or long-term
• Standards-based method for transparently
  aggregating the data, no programming required
• Components that work together, forming a
  complete, end-to-end solution
• Supported in standard client/server environments,
  yet fully e-business ready (support for XML,
  Java, etc.)

6
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Data Movement Bulk Copy and Replication
• Real Solutions
• Conclusions
• Wrap-up and QA

7
Customer ScenarioFinancial Services

• Problem Many groups need to access mainframe
  dataaccess from each must be managed, billed,
  monitored separately
• Data in DB2 and VSAM
• Many varied client applications
• Connections must be automatically diverted to
  correct mainframe transaction
• Must leverage existing data and many COBOL
  applications
• Goal Separate, secure, auditable and reliable
  mainframe access for each user

Mainframe VSAM and DB2 data
8
Customer ScenarioMedia Company
Data transfer, RPCs, data access
Problem Both ASE and Microsoft SQL Server are
installed. Before MS SQL Server 2000, ASE could
treat it like another ASE for data access and
transfer. This worked for MS SS 6.5 and 7.0 but
no longer works due to Microsoft interface
changes. Goal Continued transparent access from
ASE to Microsoft data.
Microsoft SQL Server 6.5, 7.0
Sybase ASE
Microsoft SQL Server 2000
Same situation applies for Rep Server, Open
Client apps
9
Customer ScenarioTelecommunications

• Problem Phone Service Two-Step
• Taking 20 minutes/call to help customer buy new
  options
• Customer data from IMS mainframe green screens
• Regional service data from distributed Informix
  databases
• Goal Faster Service
• Present all data at once in easy-to-use GUI
  application so customer service operators can
  provide faster service
• Must leverage existing application logic and data

Mainframe IMS customer data
Informix Regional service data
10
Customer ScenarioHealthcare

• Problem Takes too much time to locate
  claims/membership data and it cant be presented
  consistently
• Old claims/memberships on the mainframe in IMS
• Migrating to Trizetto Facets to manage new
  claims/memberships in ASE
• Must leverage existing application logic and data
• Goal Faster service - one method to access all
  claims and membership via touchtone access

Mainframe IMS claims/ memberships
Your information is in a different database, let
me transfer you
ASE claims/ memberships
11
Customer ScenarioVideo Conferencing

• Problem Migrating an application from Oracle to
  Sybase. Need
• Transparent access to both Oracle and Sybase
  tables from one application through various
  migration phases
• Minimal impact on DBAs, users
• Programming free data conversion
• Goal Virtual access to all the data as through
  it were in one database, and easy bulk copy of
  data from Oracle to Sybase

Oracle
Sybase
12
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Data Movement Bulk Copy and Replication
• Real Solutions
• Conclusions
• Wrap-up and QA

13
Two-Tier Data Access with Multiple Targets
ODBC driver
ODBC Applications
JDBC driver
DB2 UDB
ODBC driver
JDBC driver
Microsoft SQL Server
Java Application
Java Applications
ODBC driver
JDBC driver
Oracle

• Benefits
• Drivers usually available from database vendors
• Minimal moving parts (if you dont count the
  drivers!)

14
Two-Tier Data Access with Multiple Targets via
Application Server
JDBC driver
Java Applications
DB2 UDB
JDBC driver
Microsoft SQL Server
Application Server
JDBC driver
Oracle
15
Multi-Tiered Gateway Data Access
Gateway
Single server, multiple services to
various targets configured for each apps specific
needs
DB2 Mainframe RPCs
ODBC Applications
ODBC driver
JDBC driver
Microsoft SQL Server
Java Applications

• Benefits
• Simplified client - fewer ODBC/JDBC drivers
• Advanced OS/390 data access
• Monitoring, Auditing, sophisticated access
  control
• Customized gateway behavior by application or
  user
• Easy bulk data copy with automatic datatype
  conversion

Oracle
AS/400
16
What a Gateway Tier Can Do For You that JDBC/ODBC
Drivers Alone Cannot

• Data type and SQL transformation
• Client control and management (especially
  important on the mainframe)
• Client idle timeout
• Redirection to transaction by id or app name
• Security mappings
• Auditing/monitoring
• Flexibility ultimately provides consistent
  interface for other middleware (Replication,
  Application Server)

17
Features to Look for In Gateway Tier Middleware

• Advanced Mainframe Access
• APIs for access to all mainframe data and
  applications
• Mainframe as client accessing distributed servers
• Advanced Transaction Routing
• Optimized SQL access to DB2
• Consistent Access and Data Movement
• SQL and data type transformation
• Quick and easy bulk data transfer
• Option for Transparent Data Access with other
  targets

18
Features to Look for In Multi-Tier Gateway
Middleware

• Open and Developer Friendly
• Advanced stored procedure support
• Internationalized
• Standards-based client access JDBC, ODBC, ANSI
  SQL
• Supports SQL DML and DDL heterogeneously
• Secure and Manageable
• Transaction-based security options
• Encrypted data in flight from client to gateway
• Multiple options to control mainframe connections

19
Mainframe ConnectExtending the Mainframe
Mainframe
Any Client
Gatewayless configuration
DB2
Dynamic Accessto DB2 (DB2 UDB for OS/390 Option)
Enterprise Connect
VSAM IMS Datacom IDMS Adabas
Access to MF Data and Applications (Server
Options and XJS/390)
Sybase ASE
Direct Connect for OS/390
Replication Server
Mainframe Applications, and Transactions
Mainframe as a Client(Client Options)
Enterprise Portals, App Servers
20
XJS/390Quickly Integrate the Mainframe
Mainframe XML, HTML, and Scripts
Any Client Or Browser
ASE/CIS
DB2
CICS Programs, Data HTTP, MQ
VSAM IMS Datacom IDMS
Sequential Adabas
ASE
IMS Programs, Data, HTTP, MQ
Replication Server
Direct Connect for OS/390
Mainframe Applications, and Transactions
Enterprise Portals, App Servers
Navigate CICS HostBridge Transactions
MQSeries
MQSeries
Access to MF Data and Applications (Server
Options and XJS/390)
Integrate with MainframeCONNECT
No data is too complex to web or message enable!
21
Transparent Data Access
ODBC, OLE-DB Applications
Transparent Data Access Middleware
Gateway(s)
ODBC driver
Java Applications
JDBC driver
Proxy Tables

• Benefits
• Full data and location transparency
• Optimized cross-database joins
• Multi-tier stored procedure support
• Strong security
• Simplified bulk data copy with automatic data
  type conversion
• Simplified clients - fewer ODBC/JDBC drivers

22
What Transparent Data Access Can Do For You that
JDBC/ODBC Drivers Alone Cannot

• Everything the Multi-Tier middleware can do, plus
• Full data source transparency Location, SQL,
  Data Types
• Automated metadata retrieval and setup
• Optimized joins of disparate data without
  programming
• Option for local data storage

23
Features to Look for in Transparent Access
Middleware

• Internet-Ready
• XML storage/indexing/ querying/retrieval
• Java stored procedure and EJB support
• SSL-enabled
• Security
• Advanced security options such as row-level
  security
• Encrypted data in flight (SSL)

24
Features to Look for in Transparent Access
Middleware

• Open and Developer Friendly
• Full stored procedure support
• Internationalized
• Standards-based JDBC, ODBC, XML, ANSI SQL
• Supports SQL DML and DDL commands heterogeneously
• Customizable for each application and for
  additional targets

25
Enterprise ConnectData and Location Transparency
Adabas DB2 UDB IDMS
IMS VSAM Datacom
Enterprise Connect
Mainframe Connect
Oracle Informix MS SQL Server Sybase DB2 UDB for
OS/390, NT/UNIX AS/400 ODBC-enabled databases
Any Client Or Browser
Distributed
Specialty Data Stores
Toolkit
Proxy Tables

• Make all your enterprise data look like one
  database

26
Enterprise Connect Data Access

• Read/write access to all supported databases
• Broad client support JDBC, ODBC, Open Client
• Data access for other Sybase middleware
• Replication Server
• Enterprise Application Server
• Enterprise Portal
• E-Biz Integrator

Enterprise Connect
Any Client Or Browser

• DB2 UDB
• (OS/390, NT and UNIX, AS/400)
• Oracle
• Microsoft SQL Server
• Informix
• Other ODBC-compliant RDBMs

27
Middleware Management and Configuration

• Basic Database Connectivity
• Remote start, stop, configure
• Multi-connection management
• Console with status, logging/ tracing,
  database-ready log for performance analysis
• Setting Up Virtual Data Access
• Proxy table and remote server definition and
  management
• Map client user ids and security to user accounts
  on remote servers
• Define datatype mappings

Sybase Middleware Management and Configuration
Tools
Corporate Data
Enterprise Connect Mainframe Connect
28
Management Tools
DirectConnect Manager Client Idle Timeout setting
and other features
29
Combine Enterprise Connect with Mainframe Connect
When you need transparent access and advanced
mainframe APIs
Adabas DB2 UDB IDMS
IMS VSAM Datacom
Enterprise Connect
Mainframe Connect
Any Client Or Browser
Oracle Informix MS SQL Server Sybase DB2 UDB for
OS/390, NT/UNIX AS/400 ODBC-enabled databases
Distributed
Specialty Data Stores
Toolkit
Proxy Tables

• Make all your enterprise data look like one
  database

30
When You Need more than JDBC/ODBC
31
When You Need More
32
When You Need More
33
Which OS/390 Option Is Best for You?

• Enterprise Connect DB2 UDB (DRDA) option
• DB2 SQL access only no need for other data
  targets
• DB2 access without CICS
• Low-cost, lower end solution
• A simpler installation w/o Sybase software on the
  mainframe
• Access to DB2 running on Unix/NT as well as on
  the mainframe

34
Which OS/390 Option Is Best for You?

• Mainframe Connect
• APIs for customized, high performance RPC access
  to data in CICS/IMS/MVS using COBOL, Assembler
• DB2 SQL access out of the box requires CICS
• SNA connectivity
• Mainframe as client
• Gatewayless for ODBC, JDBC or Open Client apps
• Transaction routing and security mapping

35
Which OS/390 Option Is Best for You?

• XJS/390
• Access other mainframe data including VSAM with
  minimal programming
• Data returned in XML format
• Quickest web application and web services
  integration
• Mainframe-based integration with MQ Series

36
Mainframe Connect and XJS/390Complementary
Solutions
Mainframe Connect Dynamic SQL Access to DB2 SNA
to mainframe connectivity requirements APIs for
traditional mainframe application integration
using languages like COBOL
XJS/390 Access to other mainframe data such as
VSAM with minimal programming Data returned in
XML format Web application integration and Web
Services Mainframe-based integration with MQ
Series

• Combination
• Quickly extend the reach of MFC to Web Services
  and MQ Series
• Leverage existing MFC integration investment

37
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Data Movement Bulk Copy and Replication
• Real Solutions
• Conclusions
• Wrap-up and QA

38
Options for Bulk Copy and Replication

• Bulk Copy
• Gateway Multi-Tier DirectConnect Transfer
  feature
• Transparent Access Enterprise Connect select
  into
• Near Real-time Replication
• Replication Server
• Replication Server Options for heterogeneous data
  distribution

39
DirectConnect Transfer for Bulk Copy

• Copy data from one DirectConnect target to
  another
• Available in Enterprise Connect and Mainframe
  Connect DirectConnects
• Submit statement via any tool that works w/ DC.
• Transfer syntax
• transfer with reportto from 'secondaryname
  userid password'
• with insert replace truncate
• into tablenamesourceselectstatement

40
Transfer Examples DB2 to Microsoft
ECDA UDB Option (AKA DirectConnect for DRDA)
transfer to MS-NYC lynn pwd with insert into
MSSTableA select from DB2TableA
DB2TableA
Send statement via any client connection to
DirectConnect Can be scheduled via your favorite
scheduler
MSSTableA
Server MS-NYC
ECDA Microsoft SQL Server Option (AKA
DirectConnect for MS SS)
41
Transfer Examples Mainframe VSAM Data to
Sybase ASE
transfer to ASENY lynn pwd with replace into
ASETableA exec VSDT
Mainframe Connect DirectConnect for OS/390
OS/390
VSDT
VSAM data
RPC VSDT built with Open Server Connect API on
the mainframe to extract VSAM data
ASETableA
Sybase ASE ASENY
42
Enterprise Connect select into

• Set up proxy tables for source and target tables
• Issue standard select into to move data from
  one table to the other
• Option to create new table on the fly, or
  select into existing table
• Optimized bulk inserts used for some targets
• Syntax for select into existing table
• select ltcolumn_listgt into existing table
  ltnewtablenamegt from ltsource_table(s) where...

43
With Enterprise Connect Transparent Bulk Copy
Using select into
Oracle Option
Enterprise Connect (AKA ASE/CIS)
Select into existing table DB2TableA from
OracleTableA
OracleTableA
Proxy tables for OracleTableA and DB2TableA
DB2 UDB Option
DB2TableA
44
Replication and Data DistributionAnother Benefit
of Data Integration MW
CRM-sales, customer Financial Information

• Decentralization
• Locate the rightinformation where it is needed
• Consolidation
• Provide a corporate overview of distributed data

Dealers
MS SQL Server
RepServer
RepServer
ASE
RepServer
ASE
RepServer
Oracle
RepServer
ASE
New York Consolidation

45
Replication and Data Distribution

• Continuous Secure Replication of changed data
• Near real time bi-directional distribution of
  database changes
• Transactional data movement
• Guaranteed Delivery
• Fault tolerance with store and forward queues
• Transactional structure and commit order
  guaranteed

46
Data Distribution across the Enterprise
Replicate Targets
Primary Sites
Replication Agent
Replication Server

• Targets
• ASE
• ASA

• Other Targets
• Oracle
• Microsoft
• DB2/UDB
• Informix
• AS/400
• ODBC targets
• Mainframe
• DB2

• Sources
• Sybase ASE, ASA
• Oracle
• Microsoft SQL Server
• DB2 UDB
• Informix

• Mainframe Sources
• DB2 UDB for OS/390
• Replication Toolkit for MVS

47
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Data Movement Bulk Copy and Replication
• Real Solutions
• Conclusions
• Wrap-up and QA

48
Customer SuccessFinancial Data Services
Problem Each user group needs separate,
auditable mainframe transaction access Solution
Mainframe Connect
Mainframe Connect
Strong COBOL shop OS/390 DB2 VSAM
Customer has now set up over 900 separate
DirectConnect services to accommodate the many
different needs of the separate user groups in
accessing mainframe data
49
Customer SuccessMedia Company
Problem ASE needs continued access to Microsoft
SQL Server 2000 Solution Enterprise Connect
Microsoft SQL Server option (DirectConnect for
Microsoft SQL Server
Sybase ASE
DirectConnect for Microsoft SQL Server For
access and data transfer
Microsoft SQL Server 7.0, 2000
Also works for Replication Server, Open Client
apps
50
Customer SuccessHealthcare
Problem Old claims/membership data on mainframe
new claims membership data in ASE Solution
Enterprise Connect and Mainframe Connect provide
consistent access to all data
Claims/ Membership application
Enterprise Connect Mainframe Connect

• OS/390 IMS
• ASE

saves us time and money by allowing us to bring
the information back to our Interactive Voice
Response system, so many of our members and
providers do not have to wait to talk to an
agent. We were also able to avoid having to
convert the older IMS data as we moved to our new
ASE based system. David Steck, Blue Cross
Blue Shield
51
Customer SuccessTelco
Problem Takes 20 minutes/call using mainframe
green screens and separate application for
local data Solution lt2 minutes/call with GUI
customer service application transparently
accessing mainframe and distributed data
Enterprise Connect Mainframe Connect
GUI Customer Service application.

• OS/390 IMS customer data
• Informix local telephone service data

52
Customer SuccessVideo Conferencing
Problem Need a quick, programming-free,
transparent tool for data migration Solution
Enterprise Connect Data Access
Enterprise Connect Data Access

• Oracle
• Sybase

Enterprise Connect displayed Oracle data in
proxy tables that looked like Sybase database
tables. Sybase eliminated training costs by
shielding us from learning OracleEnterprise
Connect managed data type conversions and it went
very smoothly. We doubled our concurrent users
without any outages or performance degradation.
Ed Wirpsza, V-SPAN
53
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Real Solutions
• Data Movement Bulk and Replication
• Conclusions
• Wrap-up and QA

54
Enterprise Data Integration

• Available options go far beyond ODBC/JDBC drivers
  to
• Unify, aggregate, and distribute heterogeneous
  data
• Retrieve and query XML data from multiple
  databases
• Easily implement Java stored procedures working
  with heterogeneous data
• Automatically retrieve metadata and set up
  transparent access to new targets
• Manage and control access by many users
• Provide secure access to data across the network

55
Enterprise Connect and Mainframe Connect -
Benefits

• Save Money by
• Using existing hardware/software investment
• Leveraging existing skills within EAI environment
• Use existing SQL, Java, C skills to unify and
  extend systems
• Less re-training, proven solution less risk
• Leveraging Sybase support for heterogeneous
  environments
• Pre-tested, pre-certified with each new OS, DB
  version
• Reduced Development and QA effort
• Standards-based middleware that can grow with
  your enterprise

56
Why Sybase?

• Proven, mature middleware in production use today
  by many customers world-wide
• Goes far beyond ODBC/JDBC drivers for transparent
  data access and distribution
• Minimal programming allows developers to focus
  on core business competencies

57
Agenda

• The Data Integration Challenge
• Real Scenarios
• The Data Access Options
• Two-tier Data Access
• Multi-tiered Gateway Data Access
• Transparent Data Access
• Real Solutions
• Data Movement Bulk and Replication
• Conclusions
• Wrap-up and QA

58
For More Information

• Product Documentation - see Enterprise Connect
  Data Access, Mainframe Connect, and ASE Component
  Integration Services
• http//www.sybase.com/support/manuals/
• Product Information and White Papers
• http//www.sybase.com/products/middleware
• Newsgroups
• sybase.public.gateways
• sybase.public.omni

59
Sybase Developer Network (SDN)

• Additional Resources for Developers/DBAs
• Single point of access to developer software,
  services, and up-to-date technical information
• White papers and documentation
• Collaboration with other developers and Sybase
  engineers
• Code samples and beta programs
• Technical recordings
• Free software
• Join today www.sybase.com/developer or visit
  SDN at TechWaves Technology Boardwalk

60
Questions?

• QA

61
Session No. E162 Sybase Data Integration
Analyzing Your Options

• Lynn Weatherwax
• Director of Engineering Data Integration
• Enterprise Solutions Division
• Lynn.Weatherwax_at_sybase.com