Overview

As per client requirement, datapower will retrieve the authentication credential form DB2 database and store credential in datapower cache (this is one-time activity, every time datapower will take the credential from cache instead of database).

Since DataPower cannot directly cache the results of SQL calls. So I came up with a solution

  • If credential not exist in Datapower cache then read the credentials (Username, Encrypted Password, Company Name) from DB2 database and store all the credentials in Datapower cache.
  • Retrieve the credentials from datapower cache, if exist.

Solution Overview

Create Two Multiprotocol Gateway services.

Use a XSL transformation as part of a processing rule within the First MPGW and use a second loopback MPGW as a pass-through to call the back-end SQL database so the results can be cached by DataPower.

Since DataPower cannot directly cache the results of SQL calls, the solution would be to implement a loopback MPGW service to accept requests and call the back-end SQL database. By creating a document cache policy in the first MPGW matching the URL for the loopback MPG, we can cache the results of the call to the loopback MPG – effectively caches the result of the back-end SQL database call.

The second loopback MPG is called from an XSL transform action in the first MPGW using dp:url-open.


<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet exclude-result-prefixes="dp" extension-element-prefixes="dp"
xmlns:dp="http://www.datapower.com/extensions"
xmlns:fn="http://www.w3.org/2005/xpath-functions"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"><xsl:output version="1.0"
indent="yes" encoding="UTF-8" method="xml"/>

<!– Calling the RetrieveCallcreditCredentials_V1_MPG for getting the credential from database–>


<dp:summary xmlns=""><operation>xform</operation><description>Calling the RetrieveCallcreditCredentials MPG and storing in caching through document policy of xml manager</description></dp:summary>

<!– Getting the IP/Port of RetrieveCallcreditCredentials MPG –>


<xsl:variable select="127.0.0.1:1000/dbresult” name="URLOpen-service-path"/> <xsl:template match="/">

<!– Calling the RetrieveCallcreditCredentials MPG –>


<xsl:variable name="cachedbresult"> <dp:url-open timeout="120" target="{$URLOpen-service-path}"> </dp:url-open></xsl:variable> <xsl:message dp:type="NBSTechnical" dp:priority="debug"> Output from OPEN URL for RetrieveCallcreditCredentials service: <xsl:copy-of select="$cachedbresult"/> </xsl:message><xsl:choose >

<!– Success if Xpath Matches –>


<xsl:when test="$cachedbresult/*[local-name()='sql' and @*[local-name()='result' and normalize-space(.) = 'success']]">

<!–Extract the credential elements from the URL open result –>


<xsl:variable select="$cachedbresult/*[local-name()='sql']/*[local-name()='row']/*[local-name()='column']/*[(local-name()='value') and (../*[local-name()='name']/text()='USER_NAME')]" name="uname"/> <xsl:variable select="$cachedbresult/*[local-name()='sql']/*[local-name()='row']/*[local-name()='column']/*[(local-name()='value') and (../*[local-name()='name']/text()='PASSWORD')]" name="pwd"/> <xsl:variable select="$cachedbresult/*[local-name()='sql']/*[local-name()='row']/*[local-name()='column']/*[(local-name()='value') and (../*[local-name()='name']/text()='COMPANY_NAME')]" name="cname"/> <xsl:message dp:type="NBSLogging" dp:priority="debug"> UserName=<xsl:value-of select="$uname"/> Password=< xsl:value-of select="$pwd"/> COMPANYNAME=<xsl:value-of select="$cname"/> </xsl:message>

<!– SET THE CREDENTIAL VALUES IN CONTEXT VARIABLE –>


<dp:set-variable name=""var://context/callcredit/CompanyName"" value="$cname"/> <dp:set-variable name=""var://context/callcredit/UserName"" value="$uname"/> <dp:set-variable name=""var://context/callcredit/Password"" value="$pwd"/></xsl:when>

<!– Rest are specific failure conditions –>


<xsl:when test="not($cachedbresult)">

<!– Service did not respond or the URI is wrong –>


<dp:set-variable name=""var://context/generated/ErrorCode"" value="'0x80urlop001'"/> <dp:set-variable name=""var://context/generated/ErrorSubCode"" value="'0x80urlop002'"/> <dp:reject> RetrieveCallcreditCredentials Service Call - no response received<</dp:reject> </xsl:when> <xsl:otherwise>

<!– General Failure condition –>


<dp:set-variable name=""var://context/generated/ErrorCode""
value="'0x80urlop001'"/>
<dp:set-variable name=""var://context/generated/ErrorSubCode"" value="'0x80urlop003'"/> <dp:reject>RetrieveCallcreditCredentials Service Call - Generic Failure</dp:reject> <xsl:message dp:type="NBSTechnical" dp:priority="error"> Request Message Rejected Due to Database connection or SQL Error <xsl:value-of select="concat('error generating: ', $cachedbresult/sql/message)"/> </xsl:message></xsl:otherwise></xsl:choose></xsl:template></xsl:stylesheet >

The loopback MPG uses an XSL transform to call the back-end SQL database using dp:url-open. This process sql results and return an XML node. Sample code section:

<?xml version="1.0" encoding="UTF-8"?>
< xsl:stylesheet exclude-result-prefixes="dp" extension-element-prefixes="dp" xmlns:dp="http://www.datapower.com/extensions" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0"><xsl:output version="1.0" indent="yes" encoding="UTF-8" method="xml"/>

<!– Retrieving the Credentials from database –>


<dp:summary xmlns=""><operation>xform</operation><description>Retrieving the Credentials from database</description></dp:summary><xsl:template match="/"><xsl:variable name="retrieveCredentialResponse"><dp:url-open timeout="120" target="sql://Callcredit_SQLDataSource/static?SELECT USER_NAME, PASSWORD,COMPANY_NAME FROM callcredit.Callcredit_Credentials order by PASSWORD_CHANGE_TIMESTAMP desc fetch first 1 row only with ur;"/></xsl:variable><xsl:message dp:type="NBSTechnical" dp:priority="debug"> Output from Executing Select statement: <xsl:copy-of select="$retrieveCredentialResponse"/> ;</xsl:message><xsl:choose>

<!– If DB response result is success copy the the response to send back–>


<xsl:when test="$retrieveCredentialResponse/*[local-name()='sql' and @*[local-name()='result' and normalize-space(.) = 'success']]"><xsl:copy-of select="$retrieveCredentialResponse"/></xsl:when>

<!– If DB response result is ‘error’ reject the transaction so that document caching could not take place–>


<xsl:when test="$retrieveCredentialResponse/*[local-name()='sql' and @*[local-name()='result' and normalize-space(.) = 'error']]"><dp:reject>Database Call - Sql Error</dp:reject></xsl:when>

<!– If DB response result is blank reject the transaction so that document caching could not take place–>


<xsl:when test="$retrieveCredentialResponse = ''">

<!– Service did not respond or the URI is wrong –>


<dp:reject>Database Call - no response received</dp:reject></xsl:when></xsl:choose></xsl:template></xsl:stylesheet>

Configuration Steps

Configure Document Cache and Document Cache Policy in XML Manager
In first MPG configure the following tabs in XML Manager:

1. Document Cache
2. Document cache policy

We will configure the maximum size 1000000 bytes i.e. 1 Mb for document cache:

We will set the policy type to fixed and give it a TTL value to 31708800 so that cache will not expire for 367 days.

Datapower and DB2 Integration
Create SQL data source object in datapower for connectivity with DB2 database.

Create Table in Database
SQL table will be created in database to store callcredit login credentials.

CREATE TABLE callcredit.Callcredit_Credentials
(ID    INTEGER GENERATED ALWAYS AS IDENTITY,
    USER_NAME              VARCHAR (100),
    PASSWORD                VARCHAR (200),
    PASSWORD_CHANGE_TIMESTAMP TIMESTAMP,
    COMPANY_NAME      VARCHAR (100)
);
PS: This table will contain two rows. One row with old and second row with new password.

1 comment on"Caching SQL results with WebSphere DataPower"

  1. Bhupinder Singh July 24, 2017

    Nice Blog, thanks for sharing the info

Join The Discussion

Your email address will not be published. Required fields are marked *