Processing SEPA payments using DB2 pureXML and WebSphere Transformation Extender

Storage and retrieval of the payment message

WebSphere® Transformation Extender and the WTX Pack for SEPA provide all of the transformation and validation processes necessary for interaction with the European Payments Council implementation for SEPA payments. This article shows how to use DB2® pureXML™ to implement a complete solution for the storage of valid SEPA payments, and also shows how validation of the original document can be performed when a payment rejection or return is processed.

Danielle Vann (vannd@us.ibm.com), WTX Advisory Software Engineer, IBM

author photoDanielle Vann works in the IBM Boca Raton facility on the WTX team. As an advisory engineer, Danielle is involved with many of the WTX products and is team lead on the WTX Pack for SEPA.



Jim Hobler (jhobler@us.ibm.com), WTX Staff Software Engineer, IBM

Jim HoblerJim Hobler works in the IBM Boca Raton facility on the WTX B2B team. He specializes in application development using DB2 and other databases.



09 April 2009

What is WebSphere Transformation Extender (WTX)?

WTX is the IBM universal data transformation and validation engine. WTX is a true many-to-many transformation design and development tool, with an easy-to-use, drag-and-drop interface. WTX solutions are also cross-platform. Once developed, they can be deployed to a variety of operating systems such as Windows®, Linux®, UNIX®, USS, and MVS™. Included in WTX are flexible data "adapters" for popular database engines; Enterprise Resources Planning (ERP) applications; Customer Relationship Management (CRM) application; and file or data formats such as XML, HTML, S/MIME, and COBOL Copybooks.

What is the WebSphere Transformation Extender Pack for SEPA?

The WebSphere Transformation Extender Pack for SEPA performs extended validation of the rules specified by the European Payments Council (EPC) and features codeless development and universal reuse and deployment across a wide range of platforms. The WebSphere Transformation Extender Pack for SEPA also converts between SEPA format and the formats required by your internal applications and business systems, such as DTAUS, MINOS, CFONB, and BACS.

For more information about the WTX product line, please see http://www.ibm.com/software/integration/wtx

What is DB2 pureXML?

DB2 pureXML supports the storage, management, validation, and search capability for XML data in its native hierarchical format. Developers no longer need to write code to "shred" (decompose) XML into individual fields, or attempt to store the XML in large strings or CLOB data types. Using pureXML, you can search XML in the database using SQL/XML. DB2 pureXML can also validate your XML against a schema at INSERT or UPDATE. The Resources section below contains more information on pureXML.

Introduction

This article will demonstrate how you can:

  • Use WTX to store valid SEPA Payments Clearing and Settlement message (pacs.008.001.01) documents in a DB2 pureXML database.
  • How to retrieve these 'original' payment documents for creation and validation of the reject or return messages.
  • How to call the WTX SEPA Pack extended validation using the retrieved output original SEPA data.

Prerequisites

The software required for the work outlined in this article includes:

  • WTX Design Studio 8.2.0.3 or greater.
  • DB2 Version 9.1 or greater. You may download and use the free DB2 Express C product for this article if you do not plan to use this example on the z/OS® platforms.
  • WTX Command Server 8.2.0.3 or greater if you wish to run the WTX maps on UNIX platforms
  • WTX Pack for SEPA. This prerequisite is required only if you wish to run the final example: Running the SEPA Validation Framework.

WTX Design Studio requires Microsoft Windows (2000, XP, 2003 Server, or Vista).


Storage of the payment in the DB2 pureXML database

In this article, we will demonstrate how to insert SEPA data directly into a DB2 database using a simple WTX map. In an actual implementation, this map will be called after validating the payment using the WebSphere Transformation Extender Pack for SEPA.

Sample database setup

Download WTXSEPAExample.zip and unzip into a temporary location. This will create a directory (folder) named WTXSEPAExample containing the Eclipse project, maps, trees, and sample data for this article. The examples assume this directory will be created in the c:\temp directory. Adjust the instructions below as needed. To execute these commands on a Windows machine, you can use the Command Editor of the DB2 Control Center or a DB2 command window. The examples below use the latter. If you are setting up your database on a z/OS system, please use the instructions labeled z/OS database setup instructions.

Windows and UNIX database setup instructions

To open a DB2 command window, launch the MS-DOS command prompt, change to the downloaded directory, and issue db2cmd and then db2. See Listing 1.

Listing 1. Opening the command window
cd c:\temp\WTXSEPAExample
db2cmd
db2

At the DB2 command prompt, issue the following statement in Listing 2 to create the database.

Listing 2. Creating a database for storing XML data
create database SEPA using codeset utf-8 territory US

The schemas need to be registered, but first the application heap size needs to be increased due to the size of the schemas. Issue the following commands after the database is created, as shown in Listing 3:

Listing 3. Increase the application heap size
connect to SEPA

UPDATE DB CFG USING APPLHEAPSZ 40000
UPDATE DB CFG USING CATALOGCACHE_SZ 100000

The download package includes the UNIFI schemas that are used by SEPA. Register these schemas by executing the code in Listing 4 in the open DB2 command window:

Listing 4. Registering the SEPA schemas
register xmlschema 'pacs.002.001.02.xsd' from 'schemas/pacs.002.001.02.xsd' \
         as SEPADMIN.pacs_002_001_02
complete xmlschema SEPADMIN.pacs_002_001_02
register xmlschema 'pacs.008.001.01.xsd' from 'schemas/pacs.008.001.01.xsd' \
         as SEPADMIN.pacs_008_001_01
complete xmlschema SEPADMIN.pacs_008_001_01

Note: If you receive the error DB21107E, "An error has occurred when accessing the file," check that you first changed the directory to the example directory (see Listing 1).

The table will be created using one XML data type column, a column that will hold the document ID, and another column to hold comments. The CREATE TABLE statement is executed using Listing 5:

Listing 5. Creating tables for XML data
create table SEPADMIN.SEPAMESSAGES \
(ID varchar(60) PRIMARY KEY NOT NULL, COMMENT VARCHAR(500), DATA XML)

z/OS database setup instructions

For z/OS, you can use SPUFI, Admin Tool, or DataStudio to enter the DDL for creating the database and registering the schemas.

Listing 6. DDL for creating the sample database on z/OS system
CREATE DATABASE SEPA;

The download package includes the UNIFI schemas that are used by SEPA. Register these schemas using the code in Listing 7:

Listing 7. Registering the SEPA schemas on z/OS system
REGISTER XMLSCHEMA 'pacs.002.001.02.xsd' FROM file://pacs.002.001.02.xsd AS 
SEPADMIN.pacs_002_001_02
COMPLETE

REGISTER XMLSCHEMA  pacs.008.001.01.xsd FROM file://pacs.008.001.01.xsd' AS
SEPADMIN.pacs_008_001_01
COMPLETE

The table will be created using one XML data type column, a column that will hold the document ID, and another column to hold comments. The CREATE TABLE statement is executed using Listing 8:

Listing 8. Creating tables for XML data on a z/OS system
create table SEPADMIN.SEPAMESSAGES(ID varchar(60) PRIMARY KEY NOT NULL, COMMENT 
VARCHAR(500), DATA XML)

Steps to enter the payment documents in the DB2 pureXML database

The zip file you extracted above contains the following WTX objects and sample SEPA data. Use WTX to populate the database created above.

  • WTX Type trees
  • WTX Map source
  • WTX Database (MDQ) file
  1. Open the SEPA.mdq file in the WTX Database Interface Designer.
    Figure 1. WTX Database Interface Designer
    Figure 1. WTX Database Interface Designer
  2. Connection to the database is accomplished with either native connection drivers, or through the use of ODBC. Refer to the documentation for the WTX Database Interface Designer for more details. This example uses the native DB2 connection.
    1. Right click on the SEPA database name and select Edit.
    2. Expand the Database Source This field is a drop-down menu. Select the database created above (SEPA, unless another name was used). Also edit the Security sections for your DB2 User ID and Password. If you are running a local copy of DB2 on a PC (for example, a DB2 Express installation), you may be able to clear the User ID and Password fields, depending on how you installed DB2.
      Figure 2. Data Source and Security Settings Dialog
      Figure 2. Data Source and Security Settings Dialog (PNG)
    3. Select OK to exit from the configuration dialog.
    4. Test the database connection by expanding the SEPA database (+) and double click on the Tables section. This will open a Generate Type Tree from Tables dialog if your connection settings were correct.
      Figure 3. Generate Type Tree Dialog shows complete list of tables if the database connection was successful
      Figure 3. Generate Type Tree Dialog shows complete list of tables if the database connection was successful (PNG)
    5. Save the SEPA.mdq and close out of the WTX Database Interface Designer.
  3. Open WTX Design Studio and import the WTX Project included in the zip file. To do this, select File... Import and then select General... Existing Projects into your workspace. Browse to the example directory created (WTXSEPAExample) and then select Next. The Import Projects dialog will appear with the project WTXSEPAExample selected. Click Finish. To confirm the project was created successfully, expand Maps in the WTX Extender Navigator. The import was successful if the map "sepa_db2.mms" appears.
  4. Open the sepa_db2.mms WTX source map in Design Studio and select the data_insert map. This map inserts the data into your database. The input card (InputXML) contains the valid SEPA XML document as a source, and the output card (DBInsert) will map the input into the database with the correct settings.
    Figure 4. The data_insert map is shown
    Figure 4. The data_insert map is shown (PNG)
  5. If desired, edit the input card (InputXML) to change the input file to a valid SEPA payment message of your choice. The current settings will enter the sample data included with the example into your DB2 pureXML database without any map card editing necessary.
  6. Build the map by selecting Map from the toolbar, and then Build. Note: If you run the map on a platform other than Windows, select the Build for Specific Platform option in the Maps menu.
  7. If it is running on a non-Windows operating system, you must deploy the maps, schemas, and data using binary transfer.
  8. To run the map, select Run from the Map menu. This map can be used in any ESB system, such as the WTX Launcher, or WebSphere Message Broker, to monitor incoming data and then automatically enter the data into the database. Running the maps on a UNIX platform will require using the WTX Command Server. Please refer to the WTX documentation for specific instructions related to running on non-Windows platforms.
    Figure 5. Building and running the WTX map
    Figure 5. Building and running the WTX map (PNG)
  9. After the map has run successfully, the payment has been added to the database. This can be verified using the DB2 Command Editor or the CLI editor and selecting all of the records in the SEPAMESSAGES table, as shown in Listing 9. If the map did not run, check the trace file data_insert.dbl for more information.
Listing 9. Query the SEPA database
				SELECT * from SEPADMIN.SEPAMESSAGES
Figure 6. Using the DB2 Command Editor to view the inserted data
Figure 6. Using the DB2 Command Editor to view the inserted data (PNG)
Figure 7. DB2 Command Editor query results
Figure 7. DB2 Command Editor query results (PNG)

Retrieval of the original payment from the DB2 pureXML database

Retrieval of the original SEPA payment is essential for creating a reject or return message in SEPA. We will demonstrate this by passing the Transaction ID for the message that is being rejected or returned to a WTX map.

Steps to retrieve the original payment document from the DB2 pureXML database

  1. If you have not done so already, follow steps 1 – 3 from the above example.
  2. Open the sepa_db2.mms WTX source map in Design Studio and select the query_txid map.
    Figure 8. Opening the ‘query_txid’ map
    Figure 8. Opening the ‘query_txid’ map (PNG)
  3. Edit the TxID input card, changing the Echo input to a valid transaction ID to look up in the database. This value is the Credit Transfer Transaction Information\Transaction ID element in the original payment message, and relates to the Transaction Information and Status\Original Transaction ID in the reject message. The default value of 'pacs008\3' is provided for you. This transaction will be found if the insert data example above was performed using the sample data provided.
  4. Build and run the 'query_txid' map. The output card 2 data will contain the entire XML message for the transaction ID request.

Running the SEPA validation framework using the output of the sample map (Optional – must have the WebSphere Transformation Extender Pack for SEPA installed)

The output of the sample 'query_txid' map can be used in the WebSphere Transformation Extender Pack for SEPA validation. The WebSphere Transformation Extender Pack for SEPA will do extended validation of the SEPA document.

As part of that validation for rejected or returned data, the WebSphere Transformation Extender Pack for SEPA must obtain the original payment message, and this is what the 'query_txid' map has done for us.

This example assumes you have installed the WTX Pack for SEPA. This includes building all the maps and copying 'xliff.dtd' into the mapsandschemas directory.

In this example, a separate map has been created to call to the SEPA validation framework map. For a live implementation, this map would be eliminated, and after successful retrieval of the original data from the database, the query_txid map could call the WebSphere Transformation Extender Pack for SEPA validation directly.

Steps to call the Pack for SEPA validation map:

  1. Open the 'sepa_db2.mms' WTX source map in Design Studio and select the Call_Validation map.

    Note: This example assumes you have the WTX SEPA Pack version 4.2.3 installed in "C:\IBM\WebSphere Transformation Extender 8.2." If you have a newer version or it is installed in a different location, modify the Output card CallSEPAValidation to reflect those differences.
  2. Input card 1 contains the contents of the original data to be validated. This is a SEPA Reject message (pacs.002.001.02). Input card 2 is the message you retrieved from running the 'query_txid' map above. If desired, these defaults can be changed for these maps. For this example, there are SEPA validation errors in the sample reject message. If there were no errors, then the WTX SEPA validation map would not return anything.
  3. The output for this map is a file that will return your results.
  4. Build and run the Call_Validation.

    Note: If you receive the error "Fail function aborted map," then you may not have the WTX SEPA pack, or the location specified in step 1 above is incorrect. If you receive "One or more inputs are invalid," confirm you copied xliff.dtd into the mapsandschemas directory.
  5. The results of the SEPA validation will be located in the same directory as the sepvalid.mmc map (where the Pack for SEPA was installed). The default name of the output file is 'seperror.xml.' The output from running the sample data will look like this if viewed in a browser:
    Figure 9. Results from the WebSphere Transformation Extender Pack for SEPA Validation map
    Figure 9. Results from the WebSphere Transformation Extender Pack for SEPA Validation map (PNG)

Summary

Using WTX and DB2 pureXML, you can enhance the processing and storage of SEPA data. This article described how to set up the database, how to store SEPA messages, and how to retrieve those messages by using the unique transaction identification.

Now that you have your SEPA data stored, there are many other ways to enhance the SEPA processes. When implementing these maps into a system using WTX Launcher or WebSphere Message Broker, you can automatically insert payments into the database when they are received and validated. When payment rejects or returns are received, the system can be set up to retrieve the Transaction ID and pass the value to the sample 'query_txid' map.

Acknowledgments

Thanks to Christian Daser, Kirsten Ann Larsen, Susan Malaika, Cindy Saracco, and Guogen Zhang for their comments and assistance with this paper.


Download

DescriptionNameSize
Sample files for this articleWTXSEPAExample.zip15KB

Resources

Learn

Get products and technologies

  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

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 Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere
ArticleID=380952
ArticleTitle=Processing SEPA payments using DB2 pureXML and WebSphere Transformation Extender
publish-date=04092009