Skip to main content

Graphical database mapping using WebSphere Transformation Extender and WebSphere Message Broker

Stephen Cox (stephencox@uk.ibm.com), Software Engineer, Software Lab Services, IBM
Stephen Cox is a Software Engineer on the Software Lab Services team at the IBM Hursley Software Lab in the UK. His expertise includes WebSphere MQ and WebSphere Message Broker. He is an IBM Advisory Accredited Professional, certified in Systems Administration of both WebSphere MQ and WebSphere Message Broker, and he received a Bachelors degree with Honours in Computer Systems Engineering from the University of Kent in the UK. You can contact Stephen at stephencox@uk.ibm.com.
Ben Thompson (bthomps@uk.ibm.com), Senior IT Specialist, IBM
Ben Thompson is a Senior IT Specialist in IBM Software Group EMEA Laboratory Services in Hursley, UK. He has worked with distributed transactional middleware for seven years, and has extensive experience designing and implementing solutions using the WebSphere product portfolio with IBM customers worldwide. You can reach Ben at bthomps@uk.ibm.com.

Summary:  This article shows you how to model and graphically map data in a database using WebSphere Transformation Extender V8.1.0.3 and WebSphere Message Broker V6.1. The article describes a data replication scenario that uses both products to select and insert data at two geographical locations. The data is sent between the locations as a WebSphere MQ message in a canonical format using publish/subscribe.

Date:  07 Jan 2009
Level:  Intermediate
Activity:  623 views
Comments:  

Architectural description of the scenario

The scenario in this article shows you how to use IBM® WebSphere® Transformation Extender and IBM WebSphere Message Broker to recognise updates to a central database and distribute the information to other locations. Here are the functional steps involved:


Figure 1. Architectural overview of the scenario
Architectural overview of the scenario

  1. In Location A, an application makes a change to the Insertion Table in the database. As part of the environment preparation, an entry is made in the Insertion Table. For running further tests, a new row can be inserted in the Insertion Table from a DB2 Command Line prompt or equivalent. This simulates the behaviour of the Application.
  2. Database Triggers are created as part of the Environment preparation. These triggers detect INSERTs, UPDATEs and DELETE actions on the Insertion Table. When a change is detected a new row is inserted into the Event Table. This row contains fields which describe the kind of change which was made to the Insertion Table, and a key which is used to identify the relevant row which was altered in the Insertion Table.
  3. A WebSphere Message Broker Message Flow uses a TimeoutNotification node to start off a poll of the database Event Table every 30 seconds.
  4. Based upon data found in the Event Table, the flow then interrogates the Insertion Table. Two alternative flows are provided – one which uses the WebSphere Message Broker native graphical mapping node and another which uses a WebSphere TX map.
  5. The data which is gathered from the Event Table is mapped to a Canonical message format and is then distributed via Publish/Subscribe.
  6. A second message flow (in Location B) starts with an MQInput node and then inserts the data into another table, of a different structure. Two alternative flows are provided – one which uses the WebSphere Message Broker native graphical mapping node another which uses a WebSphere TX map.

Preparing the environment

The attached zip file is an eclipse Project Interchange file, whose projects contain all the files required to complete the scenario. Using the broker toolkit, import all the available projects into a single WebSphere Message Broker workspace. If you plan to use the WebSphere Transformation Extender files, you will need to have the Transformation Extender perspective activated in your toolkit as well. Before detailed discussion of the WebSphere TX and WebSphere Message Broker artifacts, the following steps should be executed to prepare the environment for running the scenario.

Creating the database

  1. Create the DB2 database, named WTXDB, which will be used to host the tables. Later instructions and artifacts assume a database of this name.

    Figure 2. Create a database using a DB2 Command Line Processor or equivalent
    Create a database using a DB2 Command Line Processor or equivalent

  2. Add an ODBC driver for the database using the method Control Panel, Administrative Tools, Data Sources (ODBC):

    Figure 3. Add an ODBC driver for the database
    Add an ODBC driver for the database

  3. Run the script which is named db2_create_script.ddl and located in the eclipse project named Scripts. This script will create the required database tables. Use a DB2 Command Line Processor and execute the script using the command: db2 -tf db2_create_script.ddl.
  4. Run the script which is named db2_load_data.ddl and located in the eclipse project named Scripts. This script will load some data into the EVENT.CUSTOMER and EVENT.ADDRESS tables for two fictitious customers which will be used later. Use a DB2 Command Line Processor and execute the script using the command db2 -tf db2_load_data.ddl.

Creating the WMQ queues

  1. From a WMQ runmqsc prompt, create a queue named APPLICATION1 and a queue named OUT, both on the Message Broker Queue Manager:

    Figure 4. Create the queue which will be used as the subscription point, and a queue for an output message
    Create the queue which will be used as the subscription point, and a queue for an output message

Creating the subscription

The first Message Flow distributes the message using a Publication node. In order for the second Message Flow (in Location B) to receive the message, a subscription on the correct topic needs to be registered. The easiest method to do this is to use the RFHUtil tool. This tool is available as a free download as part of Support Pac IH03. Use RFHUtil to register on the Topic:

  1. Switch to the PubSub tab of RFHUtil
  2. Select the Sub radio button
  3. Set the Topic to Create
  4. Set the Queue Manager to Connect to to QM1 (or whatever your broker's queue manager is called)
  5. Set the Subscription Queue to APPLICATION1
  6. Click the Process Request button

Figure 5. Using RFHUtil to Subscribe on the Topic called Create
Using RFHUtil to Subscribe on the Topic called Create

Using Publish/Subscribe in this manner allows the easy extension of the scenario – if other locations also required a copy of the data, additional subscriptions could be made, without changing the first message flow which makes the publication. You can check that the subscription worked successfully by switching to the Broker Administration Perspective of the Message Broker Toolkit. Connect to your runtime broker and double click the Subscriptions level of the Broker Topology:


Figure 6. Open the Subscriptions from the Broker Administration Perspective
Open the Subscriptions from the Broker Administration Perspective

Clicking Query from the resulting Subscriptions window should show the new Subscription on the topic Create:


Figure 7. Subscription on the Topic named Create
Subscription on the Topic named Create

Exploring the Message Flows

You should have already imported ProjectInterchange.zip into a WebSphere Message Broker workspace. After importing all of the projects, you can deploy and run the completed scenario. No further development is required to run it. The next two sections describe how the artifacts were developed. For instructions on running the scenario, see Running the scenario. The Project Interchange file provides two Message Flow Projects – one to simulate Location A and one to simulate Location B. Each Flow Project contains two message flows, each of which provides the same function, but one executes using a WebSphere TX mapping, and the other uses a WebSphere Message Broker mapping.

QueryMessageFlowProject contains WMBQueryFlow.msgflow and WTXQueryFlow.msgflow:


Figure 8. WMBQueryFlow.msgflow
WMBQueryFlow.msgflow

Figure 9. WTXQueryFlow.msgflow
WTXQueryFlow.msgflow

InsertMessageFlowProject contains WMBInsertFlow.msgflow and WTXInsertFlow.msgflow:


Figure 10. WMBInsertFlow.msgflow
WMBInsertFlow.msgflow

Figure 11. WTXInsertFlow.msgflow
WTXInsertFlow.msgflow

Exploring the WebSphere Message Broker maps

The Message Flow implementation which uses native WebSphere Message Broker mappings consists of four discreet map implementations. Each of these maps makes use of database tables. WebSphere Message Broker provides a facility to import database definitions directly into the WebSphere Message Broker Toolkit. These database definitions are then used to develop the maps used at runtime. A database definition can be created by right-clicking a message flow project in the Broker Application Development perspective, as shown in Figure 12:


Figure 12. Creating a new Database Definition from the WebSphere Message Broker Toolkit
Creating a new Database Definition from the WebSphere Message Broker Toolkit

This opens a configuration panel where database connection parameters are specified. The database connection uses JDBC to connect to the specified database instance and query table properties, extracting the definition.


Figure 13. Create a new Data Definition Project if needed
Create a new Data Definition Project if needed

Create a new Data Design project if one is needed. It is used to store all the connection and database structure definitions. This project will automatically be added as a Project Reference to the Message Flow project from where the dialog was started. If the data definitions are required in further Message Flow Projects, you can add the data definition project as a dependency. Next, define the connection parameters that are used to connect to DB2. Typical values are filled in by default in the wizard, as shown in Figure 14 below, but they may need to be changed to match your customized environment.


Figure 14. Define database connection information
Define database connection information

The tooling uses JDBC to create a connection to the database instance and query the data definitions. When the broker runtime actually executes the maps, it looks for an ODBC connection to the database, which must also be created ahead of time. Once queried, the wizard will prompt you for the relevant schemas in which your databases are defined. For this scenario check the APP1 and EVENT schemas.


Figure 15. Select required schemas
Select required schemas

In the Next panel, shown below in Figure 16 of the Database Elements, you need only select the Tables checkbox. All databases tables contained within these schemas will be queried and their design imported.


Figure 16. Database elements panel of the New Database Definition File wizard
Database elements panel of the New Database Definition File wizard

Any Message Flow Project that references this Data Design Project can use a mapping node to access the database tables. Once generated these table definitions, along with message sets generated to describe the message structure, are used to create maps within the tooling. Maps are generated by selecting the relevant source and destination targets when first creating the map implementation. Additional source and destinations may be added once inside the map definition.


Figure 17. Adding Sources And Destinations
Adding Sources And Destinations

Having selected map sources and target, as shown in Figure 17, the mapping will open. Drag and drop from the left hand source data to the right hand target data to generate mappings.


Figure 18. Mapping from WMBQueryFlow_SelectEvents
Mapping from WMBQueryFlow_SelectEvents

The first mapping in the query flow selects all of the events with an EVENT_STATUS of 0 in the Event table. The database selection criteria can be seen for the $db:select variable on the left hand side of the Map Script. The return data from this select is then used in the right hand side of the Map Script, assigning data into the output message. This assignment can be completed by dragging and dropping from the source data to the destination targets, however the $db:select statement requires additional input (by selecting the $db:select option) to define the parameters used. As the $target is placed within a loop, multiple messages may be generated, one for each row returned by the SELECT statement. The second node in the WMBQueryFlow flow shown above in Figure 8 makes three database SELECT statements. Therefore after creating the map, you must add additional sources for the two extra SELECT statements that could not be selected when creating the map initially:


Figure 19. Ensure all sources have been added to the map
Ensure all sources have been added to the map

The map WMBQueryFlow_SelectAndBuildXML itself constructs a canonical message from the data in the three tables and outputs the message for further processing:


Figure 20. WMBQueryFlow_SelectAndBuildXML map
WMBQueryFlow_SelectAndBuildXML map

The first select on the STOCK table selects only the record that is referenced by the input $source message. The additional two SELECT statements make use of the return value from the first SELECT statement to extract the correct information from two other tables for the CUSTOMER and ADDRESS. Each of these SELECTs is shown in the map in Figure 20 above. Finally the query flow must update the EVENT table to indicate that the event has been processed:


Figure 21. The Event status is updated by the WMBQueryFlow_UpdateEvent map
The Event status is updated by the WMBQueryFlow_UpdateEvent map

This is achieved through a DB Update node which passes the original input message through unchanged, and sets the EVENT_STATUS to 1 for the EVENT_ID stored during the original SELECT on the Event table in the flow’s LocalEnvironment scratchpad area.

The last map used in this scenario occurs once distribution of the canonical message has occurred between Location A and Location B. The map WMBInsertFlow_Insert, shown in Figure 22 below, inserts the data from the canonical message into an application-specific database structure, and builds an application-specific XML structure for output to queue OUT. The map builds a single XML output message with data extracted from the input message. It also performs a database insert, using the same data. As the address element is defined as a repeating element it is ensured that only the first Address element is used when performing the database insert:


Figure 22. WMBInsertFlow_Insert map inserts into an application specific database structure
WMBInsertFlow_Insert map inserts into an application specific database structure

Explore the WebSphere TX Maps

The sample involves two discreet parts:

  • Taking data from database tables as input to a WebSphere TX map
  • Inserting data into a database table as an output from a WebSphere TX map

One of the components installed as part of the WebSphere TX Design Studio is the graphical tool named Database Interface Designer. This tool provides a Map Developer with an easy method of interrogating database structures, for the generation of related Type Trees so that databases can be used as the source or the target in a graphical map. The Database Interface Designer displays the contents of Database/Query Files" which are given the file extension .MDQ. The sample with this document provides two separate MDQ files:

  • Database_QueryFile1, which defines three tables and three queries used to demonstrate how to SELECT data (Figure 23).
  • Database_QueryFile2, which defines a table used to demonstrate how to INSERT data (Figure 24).

Figure 23. Database_QueryFile1 shown in the Database Interface Designer
Database_QueryFile1 shown in the Database Interface Designer


Figure 24. Database_QueryFile2 shown in the Database Interface Designer
Database_QueryFile2 shown in the Database Interface Designer

Database_QueryFile1 displays information about how the WebSphere TX Map locates the database, the tables within the database which the map interacts with, and defines some queries that the map would like to execute. The tables that are displayed all reside in a database schema named EVENT. These tables were used to generate Type Tree definitions using the Generate Type Tree dialog:


Figure 25. Generate Type Tree dialog from Database Interface Designer
Generate Type Tree dialog from Database Interface Designer

All three tables were used to generate a single Type Tree file, named Database_Query1.mtt (by clicking on the Generate button in the dialog shown above). The resulting Type Tree file contains structures (Types) to describe the tables, rows and columns contained in the database. Three other generated Type Trees are provided which are the result of defining Queries. A query is constructed in the Database Interface Designer through the specification of a SELECT statement. The SELECT statement can be enriched with Variables, which can then be substituted when the map is executed at runtime. In this way, dynamic queries based upon data inbound to a map can be constructed and executed as part of a map’s function. Figure 26 shows the definition of a query:


Figure 26. Defining a Query in the Database Interface Designer
Defining a Query in the Database Interface Designer

The query named QUERY.ADDRESS contains a WHERE clause which uses a defined variable named CID1. The "#" sign notation is used to denote variables within a Query. Having specified a Query which contains a variable, you will see that the variable is automatically added at the base of the Database/Query Files structure, displayed in the left window, as shown in Figure 23 above. Double-click on any defined variable to specify its default value. You must first define default values for variables before you can generate a Type Tree for a Query. The definition of default values is shown below:


Figure 27. Dialog for defining defaults (overridden at runtime) for Variables
Dialog for defining defaults (overridden at runtime) for Variables

Figure 27 above shows each of three variables all given the default value of zero. These variables all symbolise items which are of INTEGER type in the database. Remember that if you are specifying defaults for items of VARCHAR type, it is necessary to specify single quotation marks around the entries in the Value column of the table. Once you have defined a Query and the associated variable(s) in the Database/Query File, you can generate a Type Tree. The type tree will symbolise the query graphically when it is invoked in a WebSphere TX map. In order to create the type tree, right click the Query and select the Generate Tree option, as shown below:


Figure 28. Generating a Type Tree from a Query in the Database Interface Designer
Generating a Type Tree from a Query in the Database Interface Designer

The resulting dialog lets you manipulate some of the properties of the type tree that will be created. You can use the same type tree to format content for other adapters (such as writing the data to a file), so in these circumstances, you may want to tweak some of the options presented here. The default values from the wizard are perfectly appropriate for this use case, as shown below:


Figure 29. Generate Type Tree dialog used with default values
Generate Type Tree dialog used with default values

When you click OK, the type tree should be generated. If you see a warning as shown below in Figure 30, then it is likely that the definition of default values for variables in the query was not performed correctly for the associated data type of the column in the database to which they refer:


Figure 30. Possible error message when generating a type tree from a query
Possible error message when generating a type tree from a query

Return to the step in Figure 27 and make sure you that the default values you inserted were of a numeric datatype. The scenario uses a different type tree for each query:

  • QUERY.ADDRESS.mtt
  • QUERY.CUSTOMER.mtt
  • QUERY.STOCK.mtt

Having considered the Database Interface Designer and the type trees which it generates, now let us consider the graphical maps which utilise them. All of the maps used for the scenario are incorporated into a single source map file, named MapSourceFile1.mms. This map file is found in the WebSphere Transformation Extender project, named ExtenderProject which should already have been imported into your workspace from the Project Interchange file named ProjectInterchange.zip.


Figure 31. MapSourceFile1 displayed in the Transformation Extender Perspective of the Message Broker Toolkit
MapSourceFile1 displayed in the Transformation Extender Perspective of the Message Broker Toolkit

MapSourceFile1 defines five executable maps. First we will consider the maps which take data from the WTXDB database via Queries. These maps are named:

  • MapQuery
  • MapQueryAddress
  • MapQueryCustomer
  • MapQueryStock

MapQuery uses the other three executable maps (MapQueryAddress, MapQueryCustomer and MapQueryStock). MapQuery takes a single input file, which is parsed against its first input card, named MapQuery_In1 and shown in Figure 32, and produces two output cards named MapQuery_Out1 and MapQuery_Out2:


Figure 32. First Input card of MapQuery, named MapQuery_In1
First Input card of MapQuery, named MapQuery_In1

Warning about map testing

The maps included with the scenario have been explicitly set up with dynamic overrides, which means they will execute within the wider message flow scenario, but may not run standalone unless altered slightly regarding the input files they are set to read from. Be aware of this if you plan to test and run the three dependent WebSphere TX maps individually. For example, the output cards of these three maps have been set to deliberately not create output files when successfully executed, so if you decide to run them on their own and want to examine the data they produce, then select TargetRule => PUT => Target => Transaction => OnSuccess and edit change the output card settings property from !Create to Create. If you change the settings, remember to rebuild the map before executing it.

When executed using the "Run Map" button in the Toolkit, the output from the second card writes an output file named FinalOutput.xml. MapQuery_Out1 consists of a single text field and is really a scratchpad. This field is then used in the construction of the main output, which is produced by the second output card. In the wider scenario, when this map is executed from a WebSphere Message Broker message flow, it is the output from the second card which is passed back to the message flow. The Input card MapQuery_In1 receives XML data as shown below:


Listing 3. Input message generated by WebSphere Message Broker and sent as input to WebSphere TX MapQuery

<NS1:EventMessage xmlns:NS1="http://com.ibm.issw">
 <databasePrimaryKey>1</databasePrimaryKey>
 <databaseTable>stock</databaseTable>
 <typeOfTrigger>create</typeOfTrigger>
 <event_id>1</event_id>
</NS1:EventMessage>

In the wider scenario this message is generated by the Message Broker flow as a representation of an entry in the EVENTSTORE table, which has been located through polling the database. The data includes a field named databasePrimaryKey, which is used to locate a row from the database table named STOCK. The scenario starts when an update is made to the STOCK table. This update triggers an entry to be made in the event table, which is picked up by the message flow.

The message flow runs the WebSphere TX map MapQuery in order to locate the original data which was entered in the STOCK table. The stock trade information in the STOCK table includes a CustId field which can be used to locate information about the customer who made the trade. This customer information is gathered using two other queries – one from the table named CUSTOMER and one from the table named ADDRESS. All of this information is compiled into a single message using the second output card of MapQuery, named MapQuery_Out2. This output card is shown below:


Figure 33. Second output card of MapQuery, named MapQuery_Out2
Second output card of MapQuery, named MapQuery_Out2

Each of the output fields has its value set by running one of the executable maps using the RUN function. This function runs a map, allowing the dynamic override of the map’s input variable. This input variable is then used in the SQL SELECT statements executed by the map's queries, which were defined in the Database Interface Designer as described earlier in the article. Let us examine the RUN function used by the map rule for the FirstName field in some more detail, as shown below:


Listing 4. Map rule for the FirstName field, demonstrating how the RUN function can be used for dynamic overrides

= RUN("MapQueryCustomer"," -ID1 '-VAR PKEY=" + NUMBERTOTEXT(MapQuery_Out1) + "' -OE1")

The run function takes two parameters, separated by a comma. The first parameter is the name of the map being executed (in the example from Listing 4 this is MapQueryCustomer). The second parameter provides a set of Execution Commands. This example contains two separate execution commands:

  • -ID1 specifies overrides for the Input Database card 1. The variable PKEY is set to the value from the preceding scratchpad output card named MapQuery_Out1.
  • -OE1 specifies that the Output should be Echoed back from card 1, and set as the output value for the RUN function (rather than the RUN function’s return code).

The output message has sections relating to the customer, the address and the stock trade, and is structured as an XML output as shown below:


Listing 5. Output from card MapQuery_Out2, XML file FinalOutput.xml in Canonical format

<can:CanonicalMessage xmlns:can="http://com.ibm.issw.canonical">
 <Customer>
  <FirstName>Joe</FirstName>
  <LastName>Bloggs</LastName>
  <CountryCode>UK</CountryCode>
 </Customer>
 <Address>
  <City>London</City>
  <PostCode>SE11</PostCode>
 </Address>
 <Stock>
  <Company>IBM</Company>
  <StrikePrice>130</StrikePrice>
  <Units>100</Units>
 </Stock>
</can:CanonicalMessage>

The output message in Listing 5 above is produced in an example Canonical format. In the wider scenario, this message is produced and then distributed to a series of applications, which in real-life could be located in different geographies and implemented using a wide variety of technologies. The distribution process is achieved in the sample via WebSphere Message Broker Publish/Subscribe. The WebSphere Message Broker message flow uses a Publication node to send copies of the message to any application registered as a subscriber on the topic named Create. This relatively simple scenario could easily be extended to add additional subscribers. The message in the canonical XML message format is written to a queue named APPLICATION1, and is then taken from the queue by a second message flow, named WTXInsertFlow.msgflow, shown earlier in Figure 12. This message flow uses the input data to insert a row into a database table by calling a WebSphere TX map from a WebSphere TX primitive node. In the sample, this INSERT is executed into a table named REPLICA, defined in the APP1 database schema. For the simplicity of running the sample, this table is defined in the same database named WTXDB. The INSERT is executed by the WebSphere TX map named MapInsertApplication. This map actually has two purposes:

  • Map the data to a different application specific XML format
  • Insert the data into the table named REPLICA

These two jobs are carried out by the output cards named MapInsertApplication_Out1, shown below in Figure 34, and MapInsertApplication_Out2 respectively.


Figure 34. First output card of MapInsertApplication, named MapInsertApplication_Out1. The application specific XML message is created by a series of simple mappings
First output card of MapInsertApplication, named MapInsertApplication_Out1.  The application specific XML message is created by a series of simple mappings

The mapping of the database insert becomes particularly easy to achieve because the structure of the type tree for the REPLICA table has already been generated as a result of the work done earlier in the Database Interface Designer.


Figure 35. Second output card of MapInsertApplication, named MapInsertApplication_Out2, inserts a new row in the REPLICA table.
Second output card of MapInsertApplication, named MapInsertApplication_Out2, inserts a new row in the REPLICA table.

Running the Scenario

In order to execute the scenario, if you have not already done so, follow the instructions above in the section Preparing the Environment. These steps create the database with which the products interact, named WTXDB. WTXDB contains five tables:

  • CUSTOMER: Information about the customer.
  • ADDRESS: Address information for all customers. It is linked to the CUSTOMER table using a foreign key.
  • STOCK: Information about stock trades which should be executed on behalf of a customer. It is linked to the CUSTOMER table using a foreign key.
  • EVENTSTORE: This table is automatically updated by a database trigger, whenever a new stock trade is inserted. This table is polled by the message flows to determine new trades which need to be replicated. Once processed, the EVENT_STATUS field is updated so that trades are only replicated once.
  • REPLICA: This table simulates the store for replicated data in LOCATION B

The Project Interchange file provides two alternative Broker Archive (BAR) files which contain the artifacts for deployment to the runtime broker: WMBMapping.bar and WTXMapping.bar. Depending on whether you are testing the WebSphere Message Broker map or the WebSphere TX map, choose one of the BAR files and deploy it to your runtime broker. Be careful to only have one file deployed at any given time, otherwise you will not be able to guarantee which message flow executes.

By default, the Message Broker flows are set up to poll the EVENTSTORE table every 5 seconds. You may wish to use the Message Broker graphical debugger in order to step through the message flows one node at a time so that you can examine the actions of each mapping that has been discussed. If there are no new entries in the STOCK table, then the flows will simply continue to poll until an entry is found. If you place a breakpoint in the message flow immediately after the input node TimeoutNotification, and then choose to "Step Over" the next node, the flow will only progress beyond the mapping node SelectEvents if an entry has been discovered in the EVENTSTORE table. So, to start the execution of the scenario, you will need to mimic the actions of the application in Location A, shown in Figure 1, which puts a trade into the STOCK table. The CUSTOMER and ADDRESS tables have already been set up with a couple of customers (Joe Bloggs and John Doe) and their addresses. Open a DB2 Command Line Processor window, connect to WTXDB, and insert a stock trade using this SQL statement:


Listing 6. SQL statement for inserting a stock trade into the STOCK table

INSERT INTO EVENT.STOCK(CUSTID, COMPANY, STRIKEPRICE, UNITS) values(1, 'IBM', 130, 100)

If you had the visual debugger attached, then next time you step through the message flow, the row in the EVENTSTORE table will be discovered, and the flow will continue to execute its mappings. In Figure 36 below, the message flow is shown paused after the mapping to canonical form has been done, but before the EVENTSTORE table has been updated:


Figure 36. Message flow WMBQueryFlow paused in the visual debugger.
Message flow WMBQueryFlow paused in the visual debugger.

At this stage of the flow, the logical tree represents the canonical message describing the customer, address and stock trade information, and is shown below:


Figure 37. Logical tree shown in the visual debugger.
Logical tree shown in the visual debugger.

The LocalEnvironment's WildcardMatch property has been used to store a pointer value to the EVENT_ID, so that the EVENTSTORE table can be successfully updated by the UpdateEvent node. This has been achieved by ensuring that the earlier mappings in the message flow copy the field from input to output, and also by ensuring that the Mapping node's Message Mode property is configured to "LocalEnvironment And Message":


Figure 38. Mapping mode property of the mapping node.
Mapping mode property of the mapping node.

After the first flow finishes executing, check the EVENTSTORE database table, and you should see the relevant row in the table has now had its column EVENT_STATUS updated to the value 1, as shown below:


Figure 39. EVENTSTORE table once the stock trade has been processed in LOCATION A.
EVENTSTORE table once the stock trade has been processed in LOCATION A.

The second message flow, WMBInsertFlow is driven by the message sent to the queue APPLICATION1 by the Publication node at the end of the first message flow. Figure 40 shows the flow paused between the MQInput node and the mapping node which inserts the data from the canonical message into the REPLICA table:


Figure 40. Message flow WMBInsertFlow paused in the visual debugger.
Message flow WMBInsertFlow paused in the visual debugger.

Once the flow has run to completion, check that the data from the trade has successfully been inserted in the REPLICA table, as shown below:


Figure 41. REPLICA table once the stock trade has been processed in LOCATION B.
REPLICA table once the stock trade has been processed in LOCATION B.

Also check that an output message has been written to the queue OUT, as shown below:


Listing 7. Output message of WMBInsertFlow taken from the queue named OUT

<NS1:Application1 xmlns:NS1="http://com.ibm.issw.app1">
 <FirstName>Joe</FirstName>
 <LastName>Bloggs</LastName>
 <City>London</City>
 <PostCode>SE11</PostCode>
 <CountryCode>UK</CountryCode>
 <Company>IBM</Company>
 <StrikePrice>130.00</StrikePrice>
 <Units>100</Units>
</NS1:Application1>

The WebSphere Message Broker message flows which utilise WebSphere TX maps can be tested in exactly the same fashion. To try this out, remove the deployed children from the broker and then deploy the other broker archive, WTXMapping.bar.

Conclusion

This article has demonstrated some dynamic, graphical techniques for interacting with databases using both WebSphere Message Broker and WebSphere Transformation Extender.



Download

DescriptionNameSizeDownload method
Project Interchange fileProjectInterchange.zip360 KBHTTP

Information about download methods


Resources

About the authors

Stephen Cox is a Software Engineer on the Software Lab Services team at the IBM Hursley Software Lab in the UK. His expertise includes WebSphere MQ and WebSphere Message Broker. He is an IBM Advisory Accredited Professional, certified in Systems Administration of both WebSphere MQ and WebSphere Message Broker, and he received a Bachelors degree with Honours in Computer Systems Engineering from the University of Kent in the UK. You can contact Stephen at stephencox@uk.ibm.com.

Ben Thompson is a Senior IT Specialist in IBM Software Group EMEA Laboratory Services in Hursley, UK. He has worked with distributed transactional middleware for seven years, and has extensive experience designing and implementing solutions using the WebSphere product portfolio with IBM customers worldwide. You can reach Ben at bthomps@uk.ibm.com.

Comments



Trademarks  |  My developerWorks terms and conditions

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=362526
ArticleTitle=Graphical database mapping using WebSphere Transformation Extender and WebSphere Message Broker
publish-date=01072009
author1-email=stephencox@uk.ibm.com
author1-email-cc=
author2-email=bthomps@uk.ibm.com
author2-email-cc=

My developerWorks community

Tags

Help
Use the search field to find all types of content in My developerWorks with that tag.

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).

Use the search field to find all types of content in My developerWorks with that tag. 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).

Rate a product. Write a review.

Special offers