1 / 61

E162 Sybase Data Integration: Analyzing Your Options

E162 Sybase Data Integration: Analyzing Your Options. Lynn Weatherwax Director of Engineering – Data Integration Enterprise Solutions Division Lynn.Weatherwax@sybase.com. Agenda. The Data Integration Challenge Real Scenarios The Data Access Options: Two-tier Data Access

coen
Download Presentation

E162 Sybase Data Integration: Analyzing Your Options

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. E162 Sybase Data Integration: Analyzing Your Options • Lynn Weatherwax • Director of Engineering – Data Integration • Enterprise Solutions Division • Lynn.Weatherwax@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 Q&A

  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 Q&A

  7. Customer ScenarioFinancial Services • Problem: Many groups need to access mainframe data—access 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 can’t 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 Q&A

  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 don’t 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 app’s 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 Integrate with MainframeCONNECT Access to MF Data and Applications (Server Options and XJS/390) MQSeries No data is too complex to web or message enable!

  21. Mainframe Data Microsoft SQL Server Oracle AS/400 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 Make all your enterprise data look like one database 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

  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 Make all your enterprise data look like one database 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

  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 Combination Quickly extend the reach of MFC to Web Services and MQ Series Leverage existing MFC integration investment 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

  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 Q&A

  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 report]{to | from} 'secondaryname userid password'; with {insert | replace | truncate} into tablename;sourceselectstatement

  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 <column_list> into existing table <newtablename> from <source_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 • Decentralization • Locate the rightinformation where it is needed • Consolidation • Provide a corporate overview of distributed data CRM-sales, customer Financial Information 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 Primary Sites Replicate Targets 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 Q&A

  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

More Related