Gone are the days of storing XML data as a Character Large Object (CLOB) or shredding to relational tables. The problem was that after you decomposed an XML document, stored it and then put it back together, you did not end up with what you started with. Gone are the days of the inability of SQL statements to search and sort huge amount of data like the XQuery statements do. It took much longer to process queries (shredded and non-shredded), and to add fields to schema with a relational data server than those with the hybrid data server.
To reduce storage time and requirements, IBM has come up with DB2® 9, a hybrid database system that can store both structured and unstructured data in their respective formats. The integrity of XML documents is preserved, and they do not need to be shredded or decomposed. The user can switch from SQL to XQuery when the data is huge.
You can access both relational and XML data but you can also mix these within a single request more quickly than you would access either data type even as the data volumes grow. More and more data are being retained for a longer period of time as part of regulatory compliance.
By combining these data types, IBM has made it possible, with its hybrid XML/relational storage mechanisms, to reduce substantially data storage requirements and the time to process XQuery and SQL queries. You can create and manage much larger database systems by putting multiple table spaces under a single table, divide data across multiple machines and organize data by dimension all by partitioning mechanisms.
To enhance security, DB2 9 provides an innovative label-based access control (LBAC) that assigns user permissions to each row, or a table consisting of multiple table spaces across different machines. It allows you manage data load or backup by partition. However DB2 9 has limitations that we can overcome with Web services. We need to take a look at each limitation and possible solutions for it.
The user has no way of knowing if he should use SQL or XQuery to form queries. He does not know the amount of relevant data he wants to query. He needs to find out if the data volume is huge.
I suggest Web Service Data Analysis to advise the user what statement format to use based on how the system analyzes the data across the machines, organized by dimension, or in multiple table spaces under a table. If the analysis shows that the data or a portion of the data to be queried is huge, it would send a message suggesting XQuery statements as a preferred method.
What if the user is more fluent with SQL statements and has little or no working knowledge of the XQuery statements? What if the user speaks XQuery and is not comfortable with SQL statements? What if the user is more fluent with SQL for a different database management system that he has migrated to DB2 9?
This decision maker may not be able to afford to wait to learn the XQuery, as he may be required to make instant decisions. DB2 9 is unable to translate from SQL queries to those of XQuery and vice versa, and check if the translated SQL or XQuery queries have been optimized to consume least resources.
SQL queries not well formed have been known to slow down performance of a relational database. This is also true for improperly formed XQuery statements embedded within the well-formed SQL statements. Even with a hybrid data server, improperly formed queries will not perform as well.
Before we translate either SQL or XQuery statements into another, or embed the XQuery statements into the SQL queries, we need to check the syntax of each. It is obvious SQL and XQuery have differences in syntactic and scoping conventions.
Compared to a typical relational system catalog, XML schema information is often more complex and subject to change. XQuery may operate over multiple documents forming to different schemas or to multiple versions of a schema. Some documents may not have a schema.
Hence, we need to create three child Web services for its parent Web service Translator. They are:
- Web service SQL checker
- Web service XQuery checker
- Web service embedded XQuery checker
Once the checker has verified that SQL or XQuery statements are well formed on the hybrid database server, the next step is to send the results as input into one of the following child Web services:
- Web service SQL-XQuery Translator
- Web service XQuery-SQL Compiler
- Web service Embedded XQuery Compiler
The SQL-XQuery Translator maps SQL to XQuery. If the output gives a list of translation issues, they must be resolved. The SQL and XQuery have differences in syntactic and scoping conventions.
While the XQuery-SQL compiler will process the translation faster, it is more likely to produce portions of the SQL code that can give the same outputs. The compiler should have a mechanism to detect portions of the SQL code that appears to give redundant results. This means downsizing of the SQL code will be necessary. There should be a mechanism to suggest what portions of the SQL code can perform the best in consuming scarce resources without resulting in system overloads even during the abrupt changes in network traffic.
The Embedded XQuery Compiler is designed to replace certain portions of the translated SQL statements into the XQuery counterparts. It is particularly useful and more feasible in handling huge data that the SQL cannot even on a hybrid database system.
For all three child Web services, feedback will be necessary until all translation issues have been resolved in meeting user requirements. Change management must be in place to increase productivity.
Web service Performance Analyzer
The user has no way of determining if the differences between the SQL and XQuery statements appears to be marginal in consuming like resources. DB2 9 does not have the capability to conduct various testing types to determine which query type is optimal: SQL or XQuery. It is unable to provide information on the impact of disk fragmentation on the execution of the statements by partition and across the machines.
After the translation issues have been resolved, the final step is create Web service Performance Analyzer that the developer could use to compare the performance of SQL and XQuery codes in a non-production environment before they are actually executed on huge data. The query performance with separated data must prevent scans of irrelevant data.
Here is the partial list of performance testing types. When the developer executes a testing type, he may find problems that other types could not reveal. Of these tests, volume and stress testing should be conducted to ensure that the queries in a production environment will not result in system overloads.
Table 1. Performance testing types
| Type | Questions |
|---|---|
| Functional testing | Is the query working properly? Are the results the end users expected? What about business executives? Which query type (SQL or XQuery) show better results? |
| Volume testing | Are the queries working properly under heavy production volumes of data? How much volume each query type can handle? |
| Stress testing | Will low resources create problems? Which query type shows it can handle resource stress better than the others? |
| Backup and recovery testing | Are the backup and recovery processes working properly? How fast can each query type recover from expected and unexpected downtimes? |
| Configuration testing | Are the processes of each query type working properly with the updated version of the operating systems and associated Web service applications? Which query type shows better configuration? |
| Documentation testing | Is online documentation sufficient to assist system administrators and users understand how to use Web services to check the amount of relevant data, check the syntactic and scoping conventions of the queries, translate from one query type to another and resolve translation issues? |
| Load testing | Can the system handle sudden changes in traffic? |
| Information assurance testing | Can the Web services meet the criteria of confidentiality, integrity and availability? |
| Security access control | Can authorized users access the LBAC designated rows or tables as specified? |
| Back-end interface testing | Will the interface of queries with back-end applications and databases work properly? |
Developing Web services to translate SQL into XQuery and vice versa requires collaborative efforts from a team of developers, business analysts, system administrators, and potential users. It requires planning ahead to create, test, and deploy Web services to analyze data, to automate translation and test performance of the results under varying conditions. More than one performance test should be conducted, as one testing type may reveal problems that some other types did not. Of these tests, volume and stress testing should be conducted to ensure that the queries in a production environment will not result in system overloads.
You will find that resolving the issues makes your job of developing Web services to analyze data, to translate queries and to perform tests much easier. You can use IBM Rational RequisitePro to manage a hierarchy of requirements. You can use IBM Rational ClearCase to increase productivity by reducing build/release cycle times through more efficient change management.
Learn
- The entire series by Judith M. Myerson offers information on how to work with Web services in enterprise-wide SOAs.
- Judith M. Myerson's series, Use SLAs in a Web services context, has details on service-level agreements.
- Get details on IBM Relational ClearQuest and ClearCase.
- Get more information on DB2 9 pureXML and storage compression
.
- Read Judith M. Myerson's The Complete Book of Middleware, which focuses on the essential principles and priorities of system design and emphasizes the new requirements brought forward by the rise of e-commerce and distributed integrated systems.
- Get the business insight and the technical know-how to ensure successful systems integration by reading Enterprise Systems Integration, Second Edition.
- Go into the nuts and bolts of developing a service-level agreement in this IBM Redbook for Domino administrators.
- Visit the Developer Bookstore for a comprehensive listing of technical books, including hundreds of Web services titles.
- Want more? The developerWorks SOA and Web services zone hosts hundreds of informative articles and introductory, intermediate, and advanced tutorials on how to develop Web services applications.
Get products and technologies
- Get installation details on IBM WebSphere Application Server.
- Access Web services knowledge, tools, and skills with Speed-start Web services, which offers the latest Java-based software development tools and middleware from IBM (trial editions), plus online tutorials and articles, and an online technical forum.
- IBM trial products for
download: Build your next development project with IBM trial software,
available for download directly from developerWorks.
Discuss
- developerWorks blogs: Get involved in
the developerWorks community.
Judith M. Myerson is a systems architect and engineer. Her areas of interest include middeware technologies, enterprise-wide systems, database technologies, application development, network management, distributed systems, component-based technologies, and project management. You can contact her at jmyerson@bellatlantic.net.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
