Achieving EII with ASE Content Integration Features

1. Achieving EII withASE Content Integration Features Lynn Weatherwax • Director of Engineering – Data Integration • Enterprise Solutions Division • Lynn.Weatherwax@sybase.com ISUG North American Innovation Tour 2002

2. Agenda Enterprise Information Integration (EII) Real Scenarios/Solutions The Data Access Options: • Two-tier Data Access • Multi-tiered Gateway Data Access • Transparent Data Access ASE Component Integration Services – A Deeper Look Data Movement – Bulk and Replication Conclusions Wrap-up and Q&A

3. Aberdeen Group DefinesEnterprise Information Integration Software “…software that combines disparate data sources at a corporate or enterprise level in order to support applications that present, analyze, or change the data in new ways “

4. EII Challenge • Every enterprise has multiple, disparate islands of data, due to • Mergers and acquisitions • Legacy mainframe applications • Applications required to work with specific databases Adabas Microsoft Sybase Sybase Flat files Oracle Informix DB2 UDB VSAM

5. 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 Flat files Oracle Informix DB2 UDB VSAM

6. Enterprise Information Integration 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.)

7. Enterprise Connect Data AccessData and Location Transparency Make all your enterprise data look like one database Any mainframe data Mainframe Connect ASE/CIS Oracle MS SQL Server Sybase DB2 UDB for OS/390, NT/UNIX AS/400 Informix ODBC-enabled databases Files Any Client Or Browser Distributed Specialty Data Store Toolkit Specialty Data Stores Proxy Tables

8. 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 any MF Data/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

9. XJS/390 Enterprise IntegratorQuickly Integrate the Mainframe Mainframe XML, HTML, and Scripts Any Client Or Browser “Gatewayless” configuration 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

10. Agenda Enterprise Information Integration (EII) Real Scenarios/Solutions Data Access Options ASE Component Integration Services – A Deeper Look Data Movement – Bulk Copy and Replication Conclusions Wrap-up and Q&A

11. 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

12. Customer SuccessFinancial Data Services 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 user groups accessing mainframe data

13. Customer ScenarioMedia Company Data transfer, RPCs, data access Problem: Both ASE and Microsoft SQL Server 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

14. Customer SuccessMedia Company 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

15. 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

16. Customer SuccessHealthcare 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

17. 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

18. Customer SuccessTelecommunications Solution: <2 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

19. 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

20. Customer SuccessVideo Conferencing Enterprise Connect Data Access Solution: 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 Oracle—Enterprise 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

21. Agenda Enterprise Information Integration (EII) Real Scenarios/Solutions Data Access Options ASE Component Integration Services – A Deeper Look Data Movement – Bulk Copy and Replication Conclusions Wrap-up and Q&A

22. Two-Tiered ApproachJDBC/ODBC Drivers Alone ODBC driver ODBC Applications JDBC driver DB2 UDB ODBC driver JDBC driver Microsoft SQL Server Java Application Java Applications ODBC driver JDBC driver Oracle • Disadvantages • Many drivers to manage – each slightly different • Little usage management/monitoring/control • No unified view or optimized access

23. 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

24. Mainframe Data Microsoft SQL Server Oracle AS/400 Transparent Data Access – Enterprise Information Integration ODBC, OLE-DB Applications ECDA Options 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

25. What EII Middleware Can Do For You that JDBC/ODBC Drivers Alone Cannot • 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 – reuse for data access with Replication • Full data source transparency: Location, SQL, Data Types • Functional compensation, location transparency • Automated metadata retrieval and setup • Optimized joins of disparate data without programming • Option for local data storage

26. Features to Look for in EII 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)

27. Features to Look for in EII 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

28. 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-Business Platform Enterprise Connect Any Client Or Browser • DB2 UDB • (OS/390, NT and UNIX, AS/400) • Oracle • Microsoft SQL Server • Informix • Other ODBC-compliant RDBMs

29. Other ASE Content Integration Options • Content Management Package • Enables access to data in file system files • XML Management Package • Enables searches on XML documents • Enables Java and EJB component support in ASE • Enhanced Full Text Search SDS Option • Search document content – you can add support for up to 200 popular file formats • Organizes results into topic clusters based on themes • Query-by-example

30. Agenda The Enterprise Information Integration Challenge Real Scenarios/Solutions Data Access Options ASE Component Integration Services – A Deeper Look Data Movement – Bulk Copy and Replication Conclusions Wrap-up and Q&A

31. ASE Component Integration ServicesA Deeper Look • ASE Component Integration Services providing underlying technology for the integration we’ve been discussing. Basic steps for setup: • Set up remote server • Set up remote logins • Create proxy tables – map to remote tables, views, RPCs, files • Query proxy tables just like regular tables

32. Remote Server Definition • Servers are added to ASE via sp_addserver: • sp_addserver <servername> [, <serverclass> [, <netname> ]] • <serverclass> defaults to “ASEnterprise” • ASEnterprise • ASAnywhere • ASIQ • sql_server • direct_connect • Sds • db2 • <netname> defaults to <servername>

33. Remote Login Information • By default, CIS uses client’s name, password from TDS loginrec when making a connection to remote server • Alternate external login information provided via sp_addexternlogin: sp_addexternlogin <servername>, <loginname>, <ext_loginname>, <ext_password>, <rolename> • Remote logins can be mapped one-to-one (each user), by role, or many-to-one

34. Setting up a Remote Server • sp_addserver S2, ASEnterprise, S2 • sp_addexternlogin S2, steve, vic, harley • grant connect to public • connect to S2 Remote Server (S2) ASE CIS Login: vic Passwd: harley Login: steve Passwd: olson Client

35. Proxy Tables • A proxy table is a local table that has been mapped to an object external to ASE • Serves as a ‘pointer’ to an object in another location. • Object can be: • Table, view or stored procedure on a remote server • Directory or file for file system access (ASE 12.5) • No data is stored locally in proxy tables • Proxy tables are defined by metadata and are used to access the external object as if it were a local table

36. Creating Proxy Tables • Create a mapping to an existing table at a remote site • Create existing table t2 (<column list>) at ‘server.dbname.ownername.objectname’ • Create proxy_table t3 at ‘server.dbname.ownername.objectname’

37. Setting up Remote Tables • With 2 remote servers already defined (MyASE & MyOracle) map the remote tables using ‘CREATE PROXY_TABLE’ Create proxy_table employees at ‘MyASE.Employees.dbo.employees Create proxy_table salary at ‘MyOracle…salary • NOTE: The setup steps can also be done through Sybase Central

38. Accessing Remote Data • Once setup, proxy tables are used like any local table Select lastname, firstname, salary from employees where e.id=s.id and salary > 100000 • ASE/CIS breaks up the query, sends the appropriate pieces to the appropriate remote server, merges the data together, and returns it to the client

39. Agenda Enterprise Information Integration Real Scenarios and Solutions Data Access Options ASE Component Integration Services – A Deeper Look Data Movement – Bulk and Replication Conclusions Wrap-up and Q&A

40. Bulk Copy Options • ASE/CIS or Enterprise Connect • “select into” • DirectConnect Component Feature • Transfer • Express Transfer

41. 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

42. Agenda Enterprise Information Integration (EII) Real Scenarios/Solutions Data Access Options ASE Component Integration Services – A Deeper Look Data Movement – Bulk and Replication Conclusions Wrap-up and Q&A

43. Benefits of Sybase EII Middleware Enterprise Connect Data Access and Mainframe Connect 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

44. Agenda Enterprise Information Integration (EII) Real Scenarios/Solutions Data Access Options ASE Component Integration Services – A Deeper Look Data Movement – Bulk and Replication Conclusions Wrap-up and Q&A

45. 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

46. 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 TechWave’s Technology Boardwalk

47. Achieving EII withASE Content Integration Features Lynn Weatherwax • Director of Engineering – Data Integration • Enterprise Solutions Division • Lynn.Weatherwax@sybase.com ISUG North American Innovation Tour 2002