The Information as a Service pattern

SOA is deployed to repurpose assets in order to expose their data or application logic for reuse throughout the enterprise via a protocol-agnostic exchange of data. One of the most valuable and pervasive pieces of an enterprise architecture is a relational database. The IBM® WebSphere® DataPower® XI50 Integration Appliance, with its hardened security, XML DoS protection, and near-wire speed performance, is able to serve as a viable broker for enterprise data stored in relational databases within an SOA. The DataPower Appliance enables the database to be viewed as a service endpoint accessible via HTTP protocols like SOAP and REST and even non-Web protocols like MQ and JMS. This SOA-driven pattern is referred to as Information as a Service (IaaS).

Beginning in the 3.7.1 firmware release, DataPower database support is much more sophisticated. Capabilities such as parameter markers and full support for stored procedure invocations enable greater use of native database technologies. Other enhancements enable direct interaction with DB2® pureXML, enabling XML to become the lingua franca and making the database a more integral participant in an ever-growing SOA. Figure 1 illustrates the flow of network traffic through the DataPower Appliance collaborating with a back-end database:

DataPower database support

Storage and retrieval of enterprise data can be handled on a DataPower Appliance during request, response, and error rule processing. DataPower has several ways to interact with configured SQL data sources. One popular method is the SQL Action that is triggered during rule processing. Another is direct support for executing statements within XSL transformations through the dp:sql-execute() extension function and <dp:sql-execute> extension element. While both methods support executing SQL and XQuery statements, their design and functionality make them different tools that provide flexibility when implementing an IaaS pattern.

To permit interaction with a database, the DataPower administrator must first define an SQL data source object within the appliance. A data source is a collection of information required to connect to a database instance, such as connection pooling properties, and the database host, server port, and authentication credentials. Configuration can be performed from a number of management interfaces where the administrator is able to selectively add, delete, edit, enable, and disable data sources. Datasource configurations, such as the one in Figure 2, are specified within a domain so that all service objects and stylesheets within the domain can use a defined data source:

Basics of database interaction in DataPower

The two most common methods to send data to and receive data from a database using a DataPower appliance are the SQL Action and XSL extensions.

The SQL Action

The SQL Action is added to a processing rule in the same way all other actions are specified:

The SQL Action is found under the Advanced Action menu and is highlighted in the processing rule in Figure 3 above. This action offers three ways to specify a query. The first is a static string directly defining the SQL or XQuery statement. Second is a query string held in a DataPower variable, likely created in preceding processing. The third is a query generated as the text output of a XSL transformation. Figure 4 shows the configuration of a SQL Action defined with a static SQL statement:

XSLT support

The ability to execute SQL queries in XSL stylesheets is available in the XI50 appliance through the dp:sql-execute() extension function. The 3.7.1 firmware release introduced the <dp:sql-execute> extension element, which extends the database-related capability of the appliance.

Enabling an IaaS pattern from within a XSLT stylesheet

The dp:sql-execute() function was the first enabler allowing SQL statements to be used directly within XSL transformations on a DataPower appliance. Listing 1 shows the use of the dp:sql-execute() function handling a typical database query that retrieves information about orders whose totals exceed a minimum cost from the ORDERS table:

<xsl:template match="/">
	<xsl:variable name="query">
		SELECT * FROM ORDERS WHERE customer_id = 
		<xsl:value-of select="$customer_id"/>
		AND total > <xsl:value-of select="$min_total"/>
	</xsl:variable>
	<xsl:variable name="result" select="dp:sql-execute('db2datasource',$query)" />
	<xsl:copy-of select="$result" />
</xsl:template>

This syntax mandates the entire statement (SQL and values) be serialized into a single string before invoking the dp:sql-execute() function. The serialization necessitates the use of the inline <value-of> element to retrieve the values that comprise the query. Performing an INSERT requires that XML be similarly serialized with the dp:serialize element. Consider the following input XML document and XSL stylesheet that inserts the data into the database:

<?xml version="1.0" encoding="UTF-8"?>
<order id="134">
	<customer id="32493">Sam</customer>
	<total>34.98</total>
	<product>XML for Noobies</product>
</order>

<xsl:template match="/">
	<xsl:variable name="query">
		INSERT INTO ORDERS VALUES 
		(<xsl:value-of select="./order/@id"/>,
		<xsl:value-of select="./order/customer/@id/>,
		<xsl:value-of select="./order/total/text()"/>,
		<dp:serialize omit-xml-decl="yes" select="./"/>)
	</xsl:variable>
 	<xsl:variable name="result" select="dp:sql-execute('db2datasource',$query)" />
	<xsl:copy-of select="$result" />
</xsl:template>

The dp:sql-execute() function is a valuable tool for simple queries that are largely static, but statement serialization makes the function difficult to use when queries grow complex or large. The size of queries are also limited, which means static statements can only contain a small amount of data. Literal statements are also unable to take advantage of certain database performance optimizations. Additionally, the dp:sql-execute() function lacks support for parameter marking and stored procedures with output parameters. The <dp:sql-execute> extension element helps ameliorate these deficiencies of the dp:sql-execute() function. The extension element fills out the functional capabilities of DataPower as a database client, thus augmenting its ability to enable an IaaS architecture. This extension element adds the following features not available with the dp:sql-execute() function or SQL Action:

1. Parameter marker support (e.g. 'SELECT * FROM ORDERS WHERE CUSTOMER_ID = ?')
2. Support for stored procedures with INPUT, INPUT_OUTPUT and OUTPUT parameters

The <dp:sql-execute> element adds efficiency and maintainability to the SQL and XQuery statements that can be constructed within XSL stylesheets.

Parameter marker support with the <dp:sql-execute> element

Parameter marking makes dynamic SQL statements highly maintainable. Parameter markers separate the static and dynamic content that eventually combine to form a complete SQL statement. They serve as place-holders for values surrounded by the static pieces of an SQL statement. The values of the markers are set programmatically in dp:sql-execute elements during processing according to their definition in child <argument> nodes. When using the dp:sql-execute() function, an entire SQL statement needs to be serialized to a single string. The <dp:sql-execute> extension element and parameter markers enable SQL statements to be built logically. Listing 4 below shows how to use parameter markers to perform the same SELECT query shown above in Listing 1.

<xsl:template match="/">
	<xsl:variable name="statement">
		SELECT * FROM ORDERS WHERE customer_id = ? AND total > ?
	</xsl:variable>     
	<xsl:variable name="result">
		<dp:sql-execute source="'db2datasource'" statement="$statement">
			<arguments>
				<argument>
					<xsl:value-of select="$customer_id"/>
				</argument>
				<argument>
					<xsl:value-of select="$min_total"/>
				</argument>
			</arguments>
		</dp:sql-execute>
	</xsl:variable>
</xsl:template>

The <dp:sql-execute> element makes handling XML input easier, too. The stylesheet excerpt in Listing 6 below demonstrates a slightly more complex INSERT than that of Listing 3 ABOVE. This node iterates through the XML node <orders> from the input XML document in Listing 5 and inserts each order found into the ORDERS table. It demonstrates a number of important features, such as the use of the <xsl:copy-of> element for implicit XML serialization:

<?xml version="1.0" encoding="UTF-8"?>
<orders>
	<order id="134">
		<customer id="32493">Sam</customer>
		<total>34.98</total>
		<product>XML for Noobies</product>
	</order>
	<order id="209">
		<customer id="43245">Ron</customer>
		<total>25.22</total>
		<product>DataPower Hacks</product>
	</order>
</orders>

The following stylesheet uses the <dp:sql-execute> extension element to insert the data from Listing 5 into the database:

<xsl:template match="/orders">
	<xsl:variable name="statement">INSERT INTO ORDERS VALUES (?,?,?,?)</xsl:variable>
	<dp:sql-execute source="'db2datasource'" statement="$statement"> 
		<xsl:for-each select="order">
			<arguments>
				<argument>
					<xsl:value-of select=".@id"/>
				</argument>
				<argument>
					<xsl:value-of select="./customer/@id/>
				</argument>
				<argument>
					<xsl:value-of select="./total/text()"/>
				</argument>
				<argument>
					<xsl:copy-of select="."/>
				</argument>
			</arguments>
		</xsl:for-each>
	</dp:sql-execute>
</xsl:template>

While both of these examples could have been performed with the dp:sql-execute() extension function, their readability and maintainability within the stylesheet would be more difficult. Parameter marking helps you specify clean and efficient SQL statements, as well as enabling you to insert larger data sets.

Invoking Stored Procedures

Stored procedures are a mainstay in databases due to the procedural roots of enterprise architectures. Their importance has been recognized and incorporated into the <dp:sql-execute> element. Stored procedures enable the reuse of SQL statements. They can increase performance for queries that are run multiple times by enabling the database to avoid statement preparation with each query and instead cache any query optimization routines. The usage pattern of stored procedures within DataPower is similar to the examples in Listing 4 and Listing 6, and is demonstrated once again with parameter markers in Listing 7 below. Notice that stored procedures called from the <dp:sql-execute> extension element can specify the direction of a parameter via the mode attribute for arguments:

<xsl:template match="/">
	<xsl:variable name="result">
		<dp:sql-execute source="'db2datasource'" 
		                   statement="'{CALL GET_PRODUCT(?,?,?)}'">
			<arguments> 
				<argument type="SQL_INTEGER" mode="INPUT">
					<xsl:value-of select="@id"/>
				</argument>
				<argument type="SQL_VARCHAR" mode="INPUT_OUTPUT">
					<xsl:value-of select="@sku"/>
				</argument>
				<argument type="SQL_XML" mode="OUTPUT"/>
			</arguments>
		</dp:sql-execute> 
	</xsl:variable>
</xsl:template>

Stored procedures with multiple result sets

Stored procedures can return multiple result sets as a result of execution; all SQL methods in the 3.7.1 firmware support this ability. There is no difference when calling a stored procedure that returns one result set when compared to a one that returns multiple result sets. However, additional metadata is returned with the result sets returned from invoking stored procedures, as compared to the results returned from standard SQL/XQuery statements. The additional metadata includes:

1. All INPUT_OUTPUT or OUTPUT parameters.
2. <resultSet> nodes that encapsulate discrete result sets
3. <metadata> nodes within <resultSet> nodes to describe the structure of the result set

For example, a stored procedure could return the following XML:

<sql result="success">
	<parameter position="1">value</parameter>
	<parameter position="2" isNull="true"/>
	...
	<resultSet>
		<metadata>
			<columnInfo index="index" nullable="nullable" name="name" 
				precision="precision" scale="scale" type="type"/>
			... 
		</metadata>
		<row>
			<column>
				<name>name1</name>
				<value>value1</value>
			</column>
			<column>
				<name>name2</name>
				<value isNull="true"/>
			</column> 
			...
		</row>
		...
	</resultSet>
	<resultSet>
		<metadata>
		...
		</metadata>
		<row>
		...
		</row>
		...
	</resultSet>
	... 
</sql>

Differentiating NULL values from zero-length strings

In the result set displayed in Listing 8 above, notice the isNull attribute in one of the value nodes. This attribute differentiates between a value that is explicitlyNULL from a non-NULL value that is a zero-length string.

Putting everything we've learned into a final example

Assume that the following data about a company's DataPower Appliances is in the ASSETS table:

A stored procedure in the database adds assets and returns the new total number of assets of the same model that are owned by the company. The stored procedure is created with the following SQL:

CREATE PROCEDURE ADD_DATAPOWER_ASSET
	@model 		varchar(4),
	@location 	varchar(4),
	@serial    	varchar(9),
	@count 		int OUTPUT,
BEGIN TRANSACTION
	INSERT ASSETS (MODEL, LOCATION, SERIAL)
   		VALUES (@model, @location, @serial)
	SELECT @count = COUNT(*) FROM ASSETS WHERE MODEL = @model
COMMIT TRANSACTION

A stylesheet invokes this procedure through the <dp:sql-execute> element to add a XI50 asset. The machine was just procured and has not been installed in a location yet, so the value of the location column is temporarily set to null:

<xsl:template match="/">
	<xsl:variable name="result">
		<dp:sql-execute source="'db2datasource'" 
			statement="'CALL ADD_DATAPOWER_ASSET(?, ?, ?, ?)'"/>
			<arguments>
				<argument type="SQL_VARCHAR" mode="INPUT ">
					XI50
				</argument>
				<argument type="SQL_VARCHAR" mode="INPUT" isNull="true" />
				<argument type="SQL_VARCHAR" mode="INPUT">
					6987-2541
				</argument>
				<argument type="SQL_INTEGER" mode="OUTPUT" />
			</arguments>
		<dp:sql-execute>
	</xsl:variable>
</xsl:template>

This call returns the following XML for post-processing in the result variable. Listing 11 shows the resulting XML with the updated count of XI50 assets returned in the OUTPUT parameter:

<sql result="success">
	<parameter position="4">3</parameter>
</sql>

The table now contains the new asset:

Conclusion

The DataPower Appliance offers great flexibility to an SOA, as its unique position in the network enables it to efficiently handle the requests, processing, security, and validation of enterprise data. The enhancement of database functionality in the DataPower XI50, such as the <dp:sql-execute> extension element, enables the X150 to further streamline the flow of data to and from a database.

This overview may get you excited for the possibilities of using DataPower to fulfill Web service requests and maintenance of enterprise data. The IBM Data Studio team has realized the power of this scenario and developed Data Web Services as a feature of IBM Data Studio Developer. This tool helps you quickly expose SQL statements as Web services, and generates artifacts that you can deploy on a DataPower appliance, thus enabling the IaaS pattern. The developerWorks article What’s new in IBM Data Studio Developer V1.2 can help you understand just how easy it can be to use IBM Data Studio and DataPower together.

Resources

• Download DataPower Integration Appliance XI50 documentation
  Includes documentation for the dp:sql-execute() function function and <dp:sql-execute> element.
  
  
• WebSphere DataPower SOA Appliance Handbook
  Retail book from amazon.com.
  
  
• DataPower SOA Appliance Redpapers
  Includes in-depth technical examples.
  
  
• What’s new in IBM Data Studio Developer V1.2
  Whether you are a developer or an application-centric DBA, Data Studio Developer has many new features that can get your job done faster and easier, including unique problem isolation and impact analysis capabilities, enhancements to static SQL development and administration, Web services, and much more.
  
  
• DataPower developer resources page
  Technical resources to help you use Websphere DataPower SOA Appliances to simplify, secure, and accelerate XML and Web services deployments within an SOA.
  
  
• Websphere DataPower SOA Appliances product page
  Product descriptions, product news, training information, support information, and more.
  
  
• Websphere DataPower SOA Appliances product library
  Product announcements, case studies, white papers, and more.
  
  
• Websphere DataPower SOA Appliances support
  A searchable database of support problems and their solutions, plus downloads, fixes, problem tracking, and more.
  
  
• Redbook: IBM WebSphere DataPower SOA Appliances, Part I: Overview and getting started
  DataPower SOA appliances are purpose-built, easy-to-deploy network devices that simplify, secure, and accelerate your XML and Web services deployments while extending your SOA infrastructure. This IBM Redbook describes DataPower architecture, use cases, deployment scenarios, and implementation details, as well as best practices for SOA message-oriented architecture in a production ESB legacy environment.
  
  
• Redbook: IBM WebSphere DataPower SOA Appliances Part II: Authentication and Authorization
  This IBM Redbook includes the following DataPower authentication and authorization topics: basic concepts, creating policies, using Tivoli Access Manager, and using LDAP directories.
  
  
• Redbook: IBM WebSphere DataPower SOA Appliances Part III: XML Security Guide
  This IBM Redbook describes how to use a DataPower appliance to secure incoming Web Services within an SOA environment, how to integrate your DataPower appliance with WebSphere Message Broker, and how to protect against security attacks by implementing the XML Denial of Service (XDoS) provided by DataPower appliances.
  
  
• Redbook: IBM WebSphere DataPower SOA Appliances Part IV: Management and Governance
  This IBM Redbook describes how to integrate a DataPower appliance with other products such as WebSphere Registry and Repository, IBM Tivoli Composite Application Manager for SOA, and Tivoli Composite Application Manager System Edition.
  
  
• WebSphere SOA solutions developer resources page
  Get technical resources for WebSphere SOA solutions.
  
  
• developerWorks SOA and Web services zone
  Technical resources for evaluating, planning, designing, and implementing solutions that involve SOA and Web services.
  
  
• developerWorks WebSphere Business Integration zone
  For developers, access to WebSphere Business Integration how-to articles, downloads, tutorials, education, product info, and more.
  
  
• WebSphere Business Integration products page
  For both business and technical users, a handy overview of all WebSphere Business Integration products
  
  
• WebSphere forums
  Product-specific forums where you can get answers to your technical questions and share your expertise with other WebSphere users.
  
  
• Most popular WebSphere trial downloads
  No-charge trial downloads for key WebSphere products.
  
  
• Technical books from IBM Press
  Convenient online ordering through Barnes & Noble.
  
  
• developerWorks technical events and Webcasts
  Free technical sessions by IBM experts that can accelerate your learning curve and help you succeed in your most difficult software projects. Sessions range from one-hour Webcasts to half-day and full-day live sessions in cities worldwide.
  
  

About the authors

Bob Callaway is a software engineer with the WebSphere Technology Institute. He participates in research and development in the integration of middleware and application-aware networking technologies. Before joining the WSTI, Bob worked with the xSeries xPert Team on BladeCenter opportunities. He holds three degrees from North Carolina State University: BS in Electrical Engineering, BS in Computer Engineering, and MS in Computer Networking. He is currently pursuing the Ph.D. degree in Computer Engineering at N.C. State, with a concentration in application-aware networking.

Doug Williams is a software engineer on the IBM WebSphere DataPower SOA Appliances team. Doug recently graduated from the University of North Carolina with a BS in Computer Science.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• The Information as a Service pattern
• DataPower database support
• Basics of database interaction in DataPower
• Enabling an IaaS pattern from within a XSLT stylesheet
• Putting everything we've learned into a final example
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

Rational Tester for SOA Quality is an automated web services testing and SOA testing tool that supports Web services SOAP, HTTP(S), JMS, WS-Security, UDDI, and RESTful standards.

---

• Try: The no-charge Rational Service Tester for SOA Quality helps quality assurance professionals validate web service based SOA application..
• Article: See the whats new with Rational Service Tester for SOA Quality, and its Generic Service Client, which provides a single client to interact with any SOA service type.
• Tutorial: Get hands-on experience with this Transport for SOAP feature tutorial using Rational Tester for SOA Quality.
• Buy: Rational Service Tester for SOA Quality

My developerWorks community

Dig deeper into WebSphere on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers