Saving messages into DB2
To write messages directly from WebSphere MQ to DB2, you need to first set up an ODBC connection to the target database.
To add a new ODBC connection, you have to open the ODBC data source configuration in the Windows Control Panel.
Click on Add… to create a new ODBC connection to your database:
Figure 34. ODBC Data Source Administrator
Since you want to connect to your default DB2 instance, select IBM DB2 ODBC DRIVER – DB2COPY1:
Figure 35. Creating a new ODBC data source
In the next window, enter
LOGAPPDB as the
data source's name, then click on the Add button:
Figure 36. Creating a new ODBC data source
You now need to enter the user name and password WMB will use to connect to the database. Make sure you select the check box to Save password.
Figure 37. Creating a new ODBC Data Source
In the next pane, enter the database name, its host name, and port.
Since WebSphere Message Broker and DB2 reside on the same machine, you
localhost as host name. After you
click on OK, you can close all the open ODBC windows and return
to the WebSphere Message Broker Toolkit.
Figure 38. Creating a new ODBC data source
After configuring the database connection, you need to inform WebSphere Message Broker of the database table layout. To do this, you can directly import the database layout from DB2 by adding a connection to the Message Broker Toolkit. In the Broker application development view, switch to the Database Explorer tab and create a new database connection. A window will pop up, where you can specify connection parameters.
After filling out the form with our parameters, you can click on Finish.
Figure 39. Creating a database connection in WebSphere Message Broker Toolkit
You are now connected to the database. Next, you have to import database definitions into the toolkit to enable message mappings to database tables. Right-click on the project list, and select New > Database Definition:
Figure 40. New database definition
A new window opens. The project list is empty because the project list does not contain any data design projects. Click on New to add a data design project that will hold the database definitions.
Name the new project
and click on Next:
Figure 41. New data design project
You do not need to select any references to other projects, so click on Finish:
Figure 42. New data design project
After creating the data design project, you are taken back to the database definition window. The name of your newly created project is already selected. Make sure you select the correct DB2 version:
Figure 43. New Database Definition
In the next window, select the database connection you created earlier:
Figure 44. Creating a database connection in WebSphere Message Broker Toolkit
Now, enter the user ID and password to connect to the database:
Figure 45. New database definition
WMB Toolkit connects to the database and retrieves a list of all schemas. Select the LOGAPP schema:
Figure 46. New database definition
In the next window, select at least the table definitions:
Figure 47. New database definition
After importing the table definitions, you can use the tables with message flows.
Now, you can create a new message flow that reads messages from the message queue (AL_XMLQUEUE) and stores them into DB2.
The name of the flow will be storageFlow. It consists of three nodes, an MQInput node, configured to read messages from AL_XMLQUEUE, followed by a ResetContentDescriptor node, and a DatabaseInsert node. Figure 48 shows the flow layout:
Figure 48. Message flow to store messages into DB2
Click on the DatabaseInsert node (named Backend in Figure 48), and switch to its basic properties. Enter the name of the previously created ODBC data source (LOGAPPDB). Double-click on the new DatabaseInsert node. A new window appears, asking you about the input and output message formats. Select Record as the input message type and your database table LOGS as the output target:
Figure 49. New message map for the storage flow
In the mapping window, you can drag and drop the elements from the left to a table column on the right. Since WMB does not know about the new pureXML features of DB2 and its ability to store XML, you cannot drag a complex type to a column. By doing this, the toolkit would create a submapping, assuming you want to map certain portions of the complex type into a single column. The toolkit expects you to concatenate or summarize sub elements to fit into a single database column.
To map the entire element with all sub elements to a database column,
you need to use a function that serializes the entire document. This
can be done using
esql:asbitstream($source/Record), as shown
in Figure 50:
Figure 50. Message map view
You are finished developing all the necessary message flow nodes, and the project should be able to run on a Message Broker.
Note: Deploying your project into Message Broker is not covered in this tutorial. To get information on how to create a Broker Archive (BAR) and deploy it in Message Broker, please refer to the WebSphere Message Broker Information Center.