Using WebSphere DataPower SOA Appliances to enable the Information as a Service pattern

WebSphere DataPower SOA Appliances are purpose-built, easy-to-deploy network devices that simplify, secure, and accelerate XML and Web services deployments within an SOA. The WebSphere DataPower XI50 Integration Appliance can efficiently communicate with enterprise relational databases, enabling it to serve as a broker in an Information as a Service (IaaS) pattern. The DataPower XI50's database connectivity makes it a compelling option when creating services that expose enterprise data. Examples in this article show you how the DataPower XI50 can efficiently communicate with enterprise relational databases, enabling it to serve as a broker in an Information as a Service (IaaS) pattern. Examples include the dp:sql-execute() extension function and <dp:sql-execute> extension element with parameter markers, stored procedures, and multiple result sets.

Share:

Bob Callaway (rcallawa@us.ibm.com), Software Engineer, IBM

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 (dougswil@us.ibm.com), Software Engineer, IBM

Doug WilliamsDoug 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.



30 December 2008

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:

Figure 1. DataPower harnessing native database XML support
Figure 1. DataPower harnessing native database XML support

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.

The following databases are supported by DataPower:

  • Oracle®
  • Sybase®
  • Microsoft® SQL Server
  • DB2®

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:

Figure 2. DataPower datasource configuration
Figure 2. DataPower datasource configuration

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:

Figure 3. SQL Action within a processing rule
Figure 3. SQL Action within a processing rule

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:

Figure 4. SQL Action configuration
Figure 4. SQL Action configuration

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:

Listing 1. Basic use of dp:sql-execute()
<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:

Listing 2. Sample input XML
<?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>
Listing 3. Using dp:sql-execute() for data insertion
<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.

Listing 4. Using <dp:sql-execute> to SELECT data
<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:

Listing 5. Input XML with multiple values
<?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:

Listing 6. Using <dp:sql-execute> to for data insertion
<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:

Listing 7. Parameter marking with Stored Procedures
<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:

Listing 8. XML with multiple result sets
<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:

Table 1. ASSET table in a database
MODELLOCATIONSERIAL
XA35B5024777-8643
XS40B5025908-1324
XS40B5005823-0923
XI50B5006021-3452
XI50B5036341-2934

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:

Listing 9. Stored procedure
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:

Listing 10. XSL stylesheet
<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:

Listing 11. Results
<sql result="success">
	<parameter position="4">3</parameter>
</sql>

The table now contains the new asset:

Table 2. Updated ASSET table
MODELLOCATIONSERIAL
XA35B5024777-8643
XS40B5025908-1324
XS40B5005823-0923
XI50B5006021-3452
XI50B5036341-2934
XI50NULL6987-2541

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=361866
ArticleTitle=Using WebSphere DataPower SOA Appliances to enable the Information as a Service pattern
publish-date=12302008