Increase the Flexibility of MySQL-based SOA Frameworks with a Data Access Layer

1. Increase the Flexibility of MySQL-based SOA Frameworks with a Data Access Layer Mike Frost Product Manager DataDirect Technologies

2. DataDirect Technologies Overview MySQL Oracle DB2 SQL Server Sybase Informix Teradata XML Adabas IMS VSAM CICS IAM IDMS EDI ODBC JDBC ADO.NET XQuery Web Services • #1 independent supplier of data access technologies • Any application, any platform, any data source • Standards leader • Leading the market in addressing changing data access requirements • A rich history, strong backing: • 20 years in business • Formerly Intersolv, Merant, NEON • Independent operating company of Progress Software (Nasdaq: PRGS) • MySQL partner since 2007

3. Agenda • Where are we today? • SOA • Data access • Data access problems in SOA environments • Solving the problems • What does the future hold?

4. Agenda • Where are we today? • SOA • Data access • Data access problems in SOA environments • Solving the problems • What does the future hold?

5. The current SOA landscape • Many corporate IT organizations are evaluating, planning, or implementing SOA environments • Common framework for development of new applications • Leverages standards such as XML, SOAP, etc. • A collection of business goals drives this decision • Control IT costs • Increase overall business nimbleness and flexibility • Develop and deploy new applications faster • Reuse business logic common to multiple applications • Optimize use of hardware / software investments • Build bridges between heterogeneous environments

6. The SOA vision 11010100101010101010001010010101010100010011110000001111001010101 11010100101010101010001010010101010100010011110000001111001010101 1101010010101010101000101001010101010001001111000000111100101010 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 11010100101010101010001010010101010100010011110000001111001010101 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 11010100101010101010001010010101010100010011110000001111001010101 1101010010101010101000101001010101010001001111000000111010001110101011010100111010101000101010100011010100110101011101001101010010101110100100111001001101 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 11010100101010101010001010010101010100010011110000001111001010101 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011

7. The current data access landscape • Defined interfaces • OCI, ct-lib, etc. • ODBC • JDBC • ADO.NET • OLE DB • Interfaces have evolved – ground breaking changes are not functional capabilities • All of these interfaces do roughly do the same thing • Provide APIs to connect to data sources, issue queries, return data, etc. • Reuse legacy business knowledge

8. Data access in traditional application architectures MySQL

9. Data access in the real world DB2 / z/OS business logic Oracle MySQL MS SQL Server

10. Agenda • Where are we today? • SOA • Data access • Data access problems in SOA environments • Best practices for data access in SOA environments • What does the future hold?

11. Data access in a typical SOA architecture MySQL

12. What problems does this cause for SOA environments? • Performance / scalability requirements underachieved • “Best in class”/reusable services initially designed for 50 users might be deployed in a SOA environment with 500 users • Lack of understanding of the mechanics of data access • Deployment slowdowns • A small deployment headache quickly becomes a huge snafu as the number of services increases • Services typically built in a DBMS-centric fashion, limiting reusability • Higher system TCO • Reducing heterogeneity is time-consuming and difficult • Consolidation works only if identical or better performance can be achieved

13. Why does this happen? • Starting with what is easiest / most comfortable • What works for MySQL may not be right for other data sources (and vice-versa) • Data access is usually an afterthought or checklist item • “SOA experts” provide SOA guidelines – not data architects • “SOA-enablement” = expose applications as web services • SOA presents a very different approach…. • Loosely coupled • Stateless • Synchronous / Asynchronous • Message based • XML data interchange • “RPC” model …over the traditional data access model • Tightly coupled • Complex State Machine • Mostly synchronous • Connection based • Well defined API • Relational model driven • SELECT then Fetch model

14. Agenda • Where are we today? • SOA • Data access • Data access problems in SOA environments • Solving the problems • What does the future hold?

15. How can these problems be solved? Best Practices for data access in a SOA environment: • Decouple business logic and data access logic • Optimize the use of heterogeneous environments • Addressing persistent data access performance problems • Streamline data access with data integration techniques

16. 1. Decouple data access logic from business logic • Can be done today with any standards-based data access • Use ODBC, JDBC, ADO.NET, or any other API • Multiple technologies exist to facilitate this architecture today • Leverage and focus skills of existing developers • Allows application developers to focus on application development • Leaves data access in the hands of the data access experts • Applications built faster and better • Selection of the right database middleware is essential for success • Build all applications with a strong foundation in data access • “A chain is only as strong as its weakest link”

17. Consider changing this approach… Shipping Application Invoicing Application Contracts Application MySQL Data Access layer

18. …to this approach: a Data Access Layer Shipping Application Invoicing Application Contracts Application MySQL Data Access Layer ODBC, JDBC, ADO.NET, etc.

19. Key to building a successful Data Access Layer High quality database middleware • Reliability • Consistency across data sources • High performance & scalability • True conformance to standards • Robust feature and functionality support

20. What about SOA environments? MySQL

21. From Data Access Layer to Data Access Service • It is possible to “SOA-enable” the data access layer • In a SOA environment, the data access layer becomes a data access service • Services can now access data presented as a web service • Avoid accessing data directly inside every service • Forces business logic experts to become data access experts • Results in extremely complicated deployment scenarios • Reduces scalability and performance • One of the reasons to adopt SOA is to share “best in class” services across a set of applications • Accessing data in an optimized manner IS a “best in class” service • A data access service is “best in class”, scalable, easy to deploy, and reusable

22. 2. Optimize the use of heterogeneous environments • Tendency to reduce complexity by standardizing on as few environments/data sources as we can imagine • Typically on those environments/data sources we know best or offer the lowest TCO • Consolidation is often seen as a way to cut IT costs • But many IT flexibility problems start because of unforeseen narrowness in the consolidated architecture • These problems waste many of the cost savings of consolidation • Optimizing can help minimize consolidation pain • Reliability of new, consolidated architecture • Difficulty achieving identical or better performance • Inconsistency of implementation due to slight differences in data source capabilities • Higher, rather than lower, system TCO

23. Don’t reduce complexity just for the sake of doing so Shipping Application Invoicing Application Contracts Application Data Access Layer OCI Oracle

24. SOA environments can handle heterogeneity if data access is decoupled DB2 / z/OS business logic Oracle MySQL MS SQL Server

25. Optimization vs. Consolidation? • First question – does the existing system work? • If so, first strategy is to assume the SOA principle of ‘reuse’ should apply – in particular if the system is complex • Migrating and consolidating a complex working system onto an alternative system will cost orders of magnitude more than you think it will • Most “unsolvable” problems will be encountered only after you are in the details or are at deployment time • If consolidation seems to be the answer, assume that achieving identical performance is the first goal and benchmark* extensively before agreeing to consolidate

26. 3. Addressing persistent data access performance problems The SOA Data Access Service Lifecycle: • Design Database Schema (tables, partitions, indexes, etc) • Design architecture of the application framework • Design individual applications/services • Build/Test/Deploy individual applications/services • Load Test • Panic: performance is not acceptable – how do I fix it?

27. Data access code must be written well to avoid performance bottlenecks • Standards-based APIs provide numerous ways to do the exact same thing • Documentation and coding guidelines that address performance are difficult to find • Even if found, they may be too general to be helpful • Bad coding techniques account for many of the data access performance problems people encounter • Education is the key to avoiding some of the most common, yet avoidable blunders • Simple techniques can improve performance by > 100%

28. Example: Calling a Stored Procedure • Examine every API call … example below performs well over 100% faster “{call getMikesBeerInventory ( ‘Hellbender Barleywine’, ‘Pisgah Brewing’, 2008)}” or “{call getMikesBeerInventory (?, ?, ?)}”

29. Understand the lifecycle of solving data access performance problems • The “bottleneck” will start in the database • Design an efficient schema and tune the database • The “bottleneck” will move to the application or service • Understand the performance issues surrounding database middleware and then design/tune performance-aware data access code • The “bottleneck” will shift to your data access middleware • Choose database middleware that can be tuned with runtime tuning options • Adjust those options to match your application and environment

30. How database middleware works conceptually

31. What’s really going on?

32. Database middleware can make a differencein terms of throughput

33. Database middleware can determine how well an application or service will scale

34. 4. Streamline data access with data integration techniques • Most SOA environments begin with simple services doing simple things • Looking up an order using an order id • As the environment expands and the comfort level increases, the complexity of services increases • Retrieve an incoming EDI order, validate client information in internal system and retrieve customer number, place order in another internal system using customer number, generate and send receipt back to client via EDI • Now “accessing” several data sources in a single service, an EDI feed, a data source that stores the client information, a data source where the order is placed, and then another EDI request to send a response

35. A typical SOA-based data integration problem The web server needs to fetch user’s personal data, stock holdings and live stock data to compile a report to send back to the user The user submits a request for a report about their stock holdings A public service offers live (delayed) stock prices

36. Some obvious pieces … MySQL

37. Solve complex data integration problems with XQuery MySQL XQJ + XQuery Implementation

38. Advanced services require multiple operations to access data • The overhead involved to sequentially process data inside services is extensive • At some point, data is usually marshaled to/from an XML format • Data must be transformed from one data model (XML for example) to another (relational for example) • Code must be written to do these transformations of data • Multiple invocations of data access services are needed to fulfill the request • Thinking differently, it is possible to design to integrate the data using XQuery instead of sequentially access the different data stores

39. Why integrate data with XQuery? • Simplifies coding • Encapsulates business logic and data integration logic into a single step • Improves performance • No marshalling/unmarshalling of data • Optimized access to data • Optimized joining/integration of data

40. Agenda • Where are we today? • SOA • Data access • Data access problems in SOA environments • Solving the problems • What does the future hold?

41. What does the future hold for data access in SOA environments? • Emerging standards will transform data access services into robust data application services (DAS) • Organizations will begin to regard implementation of a DAS as key to their IT infrastructure • Database vendors and third party ISVs will begin to compete at offering a complete DAS solution • Some are already offer solutions that are just a layer on top of existing database middleware components (some low-quality) • Since DAS represents a new abstraction layer, it makes more sense to optimize the connectivity between the DAS and back-end data sources • High quality DAS implementations will allow organizations to maximize their SOA investment

42. What sorts of standards? • SDO (Service Data Objects) • Unified and consistent data access to heterogeneous data sources • Simplified programming model for application programmers • Enables SOA-enabled applications and services to work consistently across heterogeneous data sources • SDO is an emerging standard that can form the foundation for a DAS solution • Supported by IBM, BEA, Oracle, SAP, Sybase and... • Version 2 of the specification has been released with implementations in C++, Java, Perl, .NET, and… • Large standards group run through OASIS • Other standards and specifications

43. The SOA vision… combined with DAS 11010100101010101010001010010101010100010011110000001111001010101 11010100101010101010001010010101010100010011110000001111001010101 1101010010101010101000101001010101010001001111000000111100101010 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 11010100101010101010001010010101010100010011110000001111001010101 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 11010100101010101010001010010101010100010011110000001111001010101 1101010010101010101000101001010101010001001111000000111010001110101011010100111010101000101010100011010100110101011101001101010010101110100100111001001101 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 11010100101010101010001010010101010100010011110000001111001010101 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 Data Application Service 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011 101010001010110111010101000001010110101111011000101000010011001010111010100101001010111100100101010110010100011010101000101011011101010100000101011010111101100010100001001100101011

44. What should you do? • Make data access a first class citizen of any SOA environment • Don’t wait for “magic beans” to build your data access environment the right way - apply the Best Practices explained here today • Decouple business logic and data access logic • Optimize the use of heterogeneous environments • Addressing persistent data access performance problems • Streamline data access with data integration techniques • Keep an eye on emerging standards that can form the foundation of a complete data services solution

45. Questions?