 | Level: Intermediate William A. Matthews (cicsos2@us.ibm.com), Certified Consulting IT Specialist, WebSphere Message Broker, IBM
Carol Rigdon (rigdon@us.ibm.com), Certified Software IT Specialist, Data Management and DB2, IBM
13 Sep 2006 z/OS® DB2®
Introduction
The pictures above describe a common business problem: data is inserted or changed by an application, and this action represents a business event that affects other parts of the business, but there is no easy way to tie the data event to the needed follow-on processing. This article describes a solution using IBM® WebSphere® Information Integrator Event Publisher for DB2® V8.2,
WebSphere MQ V6, and WebSphere Message Broker V6. Each product corresponds to one of the pictures:
- WebSphere Information Integrator Event Publisher for DB2 captures the data change from the DB2 transaction log.
- WebSphere MQ transports the change to a queue.
- WebSphere Message Broker receives the change from the queue and processes it based on business requirements.
This process is a common requirement for many businesses: taking information from a change to a database table (insert, update, or delete),
and transforming that information into a form suitable for another application. This article shows how the IBM Data Event Publisher can capture the information and how
the IBM Advanced Enterprise Service Bus (WebSphere MQ plus WebSphere Message Broker V6) can transform that information for processing by another application. The article includes information on
getting started with the Data Event Publisher, plus a set of sample WebSphere Message Broker artifacts to illustrate how to manage the transformations.
The examples are also provided as a download so that you can try them out on WebSphere Message Broker V6.
A quick review of WebSphere Information Integrator Event Publisher
Changes to data often represent business events or transactions. For example, inserting a new segment in an IMS® Customer Master database is more than just storing bytes in a file.
The inserted data represents a new business relationship, and other business processes may need to be driven by this event, such as checking the customer's credit report,
sending a welcome letter, or notifying the closest branch office.
IBM Data Event Publishers for DB2, IMS, VSAM, IDMS, and Adabas capture the data event and publish the information in XML to one or more WebSphere MQ queues, which can be local or remote.
The receiving application processes the XML messages and takes appropriate action. You can use WebSphere Message Broker or other applications to process the XML.
The XML message can be customized to meet the requirements of the receiving application. You can filter the fields in the message to select only those fields you need.
For DB2 sources, you can further filter the fields by including only changed fields in the message and you can also filter based on content with a predicate.
You can receive one XML message for each DB2 transaction (which might affect multiple rows in different tables), or one XML message for each changed row.
The example in this article uses WebSphere Message Broker as the receiving application and WebSphere Information Integrator Event Publisher for DB2 V8.2.
You can also use WebSphere Data Event Publisher V9.1. Changes to a database are captured from the DB2 log by the Event Publisher and published as an XML message to a central WebSphere Message Broker, which then translates the XML message into formats that can be understood by different applications in the enterprise and in its partners and suppliers.
A quick review of WebSphere Message Broker transformations
One of the core strengths of WebSphere Message Broker is the ability to transform messages from one format to another. Messages can be delivered to WebSphere Message Broker in many different ways. Although WebSphere Message Broker is built on top of WebSphere MQ, it is also able to receive messages from non-MQ sources. WebSphere Message Broker can also deliver messages to both MQ
and non-MQ based systems. Since this article is focused on the integration of WebSphere Information Integrator , which also uses MQ, the example is also based on MQ messaging.
Messages can come in a variety of formats. They can be generic XML, XML based on a schema, fixed length fields, as seen with COBOL and C definitions as well as tagged and/or delimited messages.
With WebSphere Information Integrator Event Publisher, these messages are XML and are based on a WebSphere Information Integrator provided schema.
The process of transforming a message can use one or more of several different techniques such as a Compute node, a Mapping node, a JavaCompute or a XSLT node.
In this example, a Compute node and a Mapping node will be used. The example also makes use of one of the updated capabilities of the Mapping node, called "Map By Name," that dynamically determines what input fields are likely mapped to what output field.
Overview of activities
- WMQ activities
- Define Queues used to receive Event messages
- Define Queues used by WebSphere Message Broker to send transformed output
- WebSphere Information Integrator activities
- Define XML publications
- Start Q Capture
- WebSphere Message Broker activities
- Capture a sample XML publication from WebSphere Information Integrator Event Publisher
- Use the BuildXML message flow to produce an intermediate XML version based on the XML publication
- Design and build schemas for intermediate and final messages
- Define XML messages and schemas
- Build message sets
- Build message flows using the information produced by the BuildXML message flow
- Test
WebSphere MQ activities
- Create a WebSphere MQ Queue Manager. QMSAMP is used for this example.
- Define the queues needed for Event Publisher. These are the queue names used in the example (two are used only for testing).
- DEFINE QLOCAL('II_RESTARTQ') DEFPSIST(YES)
- DEFINE QLOCAL('II_ADMINQ') DEFPSIST(YES)
- DEFINE QLOCAL('II_EVENTQ') DEFPSIST(NO)
- DEFINE QLOCAL('II_EVENTQ2') DEFPSIST(YES)
- DEFINE QLOCAL('II_EVENTQ3') DEFPSIST(NO)
- Define the queue used by WebSphere Message Broker for the final output: DEFINE QLOCAL('OUTPUT') DEFPSIST(YES)
WebSphere Information Integrator activities
- Install DB2 V8.2 and add the license file for WebSphere Information Integrator.
- Open a DB2 command window (db2cmd from the command line)
- db2licm -a license-file-name
- If you do not already have a database, you can create one: db2sampl from the command line
- Modify the EMPLOYEE database to identify EMPNO as a key field: run db2sampk (located in the sqllib\samples\db2sampl directory)
- Set the SAMPLE database for logging:
- Open a DB2 command window (db2cmd from the command line)
- db2 update db cfg for sample using logretain recovery
- db2 backup database sample
- Start the Replication Center (db2rc from the command line). This is a Java™ application -- if you're on Linux® or UNIX®, you'll need an X-Windows® session.
- Choose Event Publishing on the Replication Center Launchpad
- Choose Step 1 Create Q Capture Control Tables.
- Take the default on the Getting started screen and click Next.
- Click on the list box next to Q Capture server and choose SAMPLE then click OK.
- Type your user ID and password and ASN for the Q Capture schema. Then click Next.
- Type QMSAMP for the Queue Manager, II_ADMINQ for the Administration queue, II_RESTARTQ for the Restart queue and click Finish.
- Click OK to run the generated SQL and then click Close to close the DB2 Message box.
- Choose Step 2 Create an XML publication
- Click Next to display the "Which Q Capture server and publishing queue map?" screen.
- If the Q Capture server and schema are not filled in, set them to SAMPLE and ASN.
- Click on the list box next to Publication Queue Map and then click on New
- Type QMAP for the Name on the General tab and then click on the Properties tab.
- Type II_EVENTQ2 for the Send queue and uncheck the box next to "Allow the Q Capture program to send heartbeat messages" and then click on OK.
- Click OK to run the generated SQL and the click Close to close the DB2 Message box.
- Click on OK again to return to the Create XML publications dialog and then click on Next.
- Click on the "Retrieve all" button to list the tables in the SAMPLE database, choose the EMPLOYEE table and click on OK.
- Click on Next four more times and then click on OK to run the generated SQL.
- Click Close to close the DB2 Message box.
- Choose Step 3 Start Q Capture.
- The Q Capture server should be SAMPLE. The Q Capture schema should be ASN. Click OK.
- On the Run Now or Save Command window, click on the list box for System name and then click on Add system.
- System name = hostname of your system (without the domain)
- Type your user ID and password (twice) and choose your operating system from the list
- Type the directory path where Q Capture will write its log file
- Click OK
- Click OK to run the generated command and then click Close to close the DB2 Message box.
- Close the Replication Launchpad and the Replication Center
- Make a change to the EMPLOYEE table so you can see the messages.
- Open a DB2 Command Window
- db2 connect to sample
- db2 insert into employee values('000999','Mickey',' ','Mouse','D21','5555','2006-01-26','Actor', 0,'M','2006-01-20',1000000.00,500.00,20000.00)
WebSphere Message Broker activities
The WebSphere Message Broker artifacts (message flows, message sets, and a sample input message) are provided in a download file called Event_PublisherFiles.zip.
The WebSphere Message Broker artifacts are provided as a Project Interchange zip file that allows the various projects to be easily imported into an existing WebSphere Message Broker Toolkit.
There is a Message Flow project with three message flows plus three message sets.
- The first message set is based on the XML publication. It is built using the schema provided by the Event Publisher.
- The second message Set is based on a simple XML message where the tag names are the same as the database Table column names.
You may use the BuildXML message flow to generate the XML file which can then be used to generate a schema.
- The third message set is the final result that will be sent to another application.
The three message flows are discussed in more detail later in this article.
- The BuildXML message flow will accept the XML publication message and will produce an XML message, used in building the second message set
as well as a group of ESQL SET statements that can be used to transform the XML publication to the XML message.
- The EventPublisher message flow is an example that will process an XML publication for the EMPLOYEE table from the SAMPLE database.
- The EventPublisher_MultipleTables message flow is an example that shows how multiple tables cane be managed by a single message flow. Only the path for the EMPLOYEE table is fully implemented.
BuildXML message flow
The BuildXML message flow is described first since it is designed to provide assistance with the creation of the intermediate XML message as well as providing sample code that can be used in a message flow that processes an XML publication. It is very simple. The basic functions provided in the Compute node are similar to those in the second message flow and are discussed at the end of this article.
The output XML message can be captured for reuse using RFHutil, available as part of WebSphere Message Broker SupportPac IH03. Here is an example of the generated XML message:
The BuildXML message flow also generates a trace file that contains information built in the Environment as well as the XML output message.
The Environment.ESQL field contains a series of SET statements that can be extracted, reformatted for readability and then used in the
EventPublisherFlow. The following is an example of the Environment information. The Set lines have been split for readability.
The other part of the Environment is where the information that has been extracted from the XML publication is stored.
Figure 1. Environment Trace
(0x03000000):ESQL = 'Set OutputRoot.MRM.Action = Action;
Set OutputRoot.MRM.Row_EMPLOYEE.EMPNO = Environment.EMPNO;
Set OutputRoot.MRM.Row_EMPLOYEE.BIRTHDATE = Environment.BIRTHDATE;
Set OutputRoot.MRM.Row_EMPLOYEE.BONUS = Environment.BONUS;
Set OutputRoot.MRM.Row_EMPLOYEE.COMM = Environment.COMM;
Set OutputRoot.MRM.Row_EMPLOYEE.EDLEVEL = Environment.EDLEVEL;
Set OutputRoot.MRM.Row_EMPLOYEE.FIRSTNME = Environment.FIRSTNME;
Set OutputRoot.MRM.Row_EMPLOYEE.HIREDATE = Environment.HIREDATE;
Set OutputRoot.MRM.Row_EMPLOYEE.JOB = Environment.JOB;
Set OutputRoot.MRM.Row_EMPLOYEE.LASTNAME = Environment.LASTNAME;
Set OutputRoot.MRM.Row_EMPLOYEE.MIDINIT = Environment.MIDINIT;
Set OutputRoot.MRM.Row_EMPLOYEE.PHONENO = Environment.PHONENO;
Set OutputRoot.MRM.Row_EMPLOYEE.SALARY = Environment.SALARY;
Set OutputRoot.MRM.Row_EMPLOYEE.SEX = Environment.SEX;
Set OutputRoot.MRM.Row_EMPLOYEE.WORKDEPT = Environment.WORKDEPT;'
(0x03000000):EMPNO = '000370'
(0x03000000):BIRTHDATE = DATE '1941-11-08'
(0x03000000):BONUS = 100.0
(0x03000000):COMM = 23.0
(0x03000000):EDLEVEL = 24
(0x03000000):FIRSTNME = 'Fred'
(0x03000000):HIREDATE = DATE '1968-01-01'
(0x03000000):JOB = 'Expert '
(0x03000000):LASTNAME = 'Matthews'
(0x03000000):MIDINIT = 'A'
(0x03000000):PHONENO = '1212'
(0x03000000):SALARY = 5000.0
(0x03000000):SEX = 'M'
(0x03000000):WORKDEPT = 'XN3'
|
Defining messages
The first part of the process is to understand the message that will be generated by the Event Publisher. The complete details are included in Chapter 27 of the "IBM DB2 Information Integrator Replication and Event Publishing Guide and Reference" manual.
The XML publication message has several sub sections. The only two of interest deal with messages that are the result of a change to a row of data ('rowOp') or the result of a transaction ('trans'),
which may combine multiple changes in a single unit of work. Figure 1 has a fragment of a XML publication for the EMPLOYEE database where a row was inserted using the DB2 command line. Each line
has been numbered. Of particular interest are the following pieces of information:
- Line 1: The attribute dbName identifies the name of the database
- Line 2: The element name will be either <rowOp> or <trans> The commit timestamp attribute (cmitTime) is in
ISO format and includes milliseconds. This requires that the message set be modified.
- Line 3: The name of the tag will be either <insertRow>, <updateRow> or <deleteRow>. In addition, the attribute srcName identifies the name of the table.
- Line 4-ff: The remainder of the XML message is a set of <col> tags with an attribute that identifies the column name.
Each <col> tag is followed by a tag that identifies the type of data and the contents of the column.
- Line 4-6: The first <col> tag is the index to the table, while the remainder of the entries are in alphabetic order.
Given that the structures following the <rowOp> or <trans> tag are identical along with the fact that the <col> tags
are in a specific order will make the ESQL in the Compute node very simple.
Figure 2. Part of a XML publication
01 <msg xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="mqcap.xsd"
version="1.0.0"
dbName="SAMPLE">
02 <rowOp authID="WMBADMIN" c
mitLSN="0000:0000:0000:0100:36fb"
cmitTime="2006-08-17T16:34:43.000001">
03 <insertRow subName="EMPLOYEE0001"
srcOwner="WMBADMIN"
srcName="EMPLOYEE">
04 <col name="EMPNO" isKey="1">
05 <char>000370</char>
06 </col>
07 <col name="BIRTHDATE">
08 <date>1941-11-08</date>
09 </col>
10 <col name="BONUS">
11 <decimal>100.0</decimal>
12 </col>
13 <col name="COMM">
14 <decimal>23.0</decimal>
15 </col>
16 <col name="EDLEVEL">
17 <smallint>24</smallint>
18 </col>
19 <col name="FIRSTNME">
20 <varchar>Fred</varchar>
21 </col>
|
Once a sample XML publication has been obtained, the next step is to build a sample XML file from which a schema can be generated.
To enable schema generation from an XML file in the WebSphere Message Broker toolkit, select Windows => Preferences => Workbench => Capabilities.
Expand XML Developer and check the boxes as shown below:
As described earlier, the following XML was created. The BuidXML message flow can be used for this task. The tag name <Row_EMPLOYEE> is an arbitrary choice and is intended to document
the table name. This xml file will be used to create a matching schema that will then be used to create a WebSphere Message Broker message set.
The first transformation will be from the XML publication schema to this intermediate schema.
Figure 3. Intermediate Message Structure
<msg>
<Row_EMPLOYEE>
<EMPNO>000370</EMPNO>
<BIRTHDATE>1941-11-08</BIRTHDATE>
<BONUS>100.0</BONUS>
<COMM>23.0</COMM>
<EDLEVEL>24</EDLEVEL>
<FIRSTNME>Fred</FIRSTNME>
<HIREDATE>1968-01-01</HIREDATE>
<JOB>Expert</JOB>
<LASTNAME>Matthews</LASTNAME>
<MIDINIT>A</MIDINIT>
<PHONENO>1212</PHONENO>
<SALARY>5000.0</SALARY>
<SEX>M</SEX>
<WORKDEPT>XN3</WORKDEPT>
</Row_EMPLOYEE>
</msg>
|
A schema for the final version of the output message is also needed. This message structure is normally prescribed by the receiving application. For this article, another arbitrary structure was built.
The following XML file was also used to generate a schema.
Figure 4. Final Message Structure
<?xml version="1.0" encoding="UTF-8" ?>
<SAMPLE>
<EMPLOYEE>
<Action>Add</Action>
<EmployeeNumber>000370</EmployeeNumber>
<Name>
<FirstName>Fred</FirstName>
<MiddleInitial>A</MiddleInitial>
<LastName>Matthews</LastName>
</Name>
<PersonalData>
<Sex>M</Sex>
<BirthDate>1941-11-08</BirthDate>
<EducationLevel>24</EducationLevel>
<PhoneExtension>1212</PhoneExtension>
</PersonalData>
<JobData>
<Department>XN3</Department>
<HireDate>1968-01-01</HireDate>
<JobDescription>Expert</JobDescription>
<Salary>5000.0</Salary>
<Commission>23.0</Commission>
<Bonus>345.00</Bonus>
</JobData>
</EMPLOYEE>
</SAMPLE>
|
Building message sets
Once schemas have been created or obtained for the input, intermediate, and final messages, they are used to create WebSphere Message Broker message sets for each.
Samples for each are also included in Event_PublisherFile.zip. The input message schema is mqcap.xsd. Since the commit timestamp on the input message includes milliseconds, when the message set is created, the default timestamp will need to be changed as shown in the following example, with ZZZ (Zone) replaced by SSSSSS (milliseconds). This message also has limited use of XML namespaces.
The intermediate message schema and the final message used in the example have o special requirements.
Building the message flows
The next two message flows are similar -- the first one only deals with the EMPLOYEE table, and the second message flow is designed to deal with multiple tables.
The Event Publisher message flow has an MQInput node, a Compute node, three Trace nodes, a Mapping node, and an MQOutput node.
The MQInput node is configured for the II_EVENT2 queue as well as specifying that the MRM parser is used and the associated message set information.
The Compute and Mapping nodes are discussed last. There are three Trace nodes. Each node is tracing information to a separate file.
One of the Trace nodes (between the Compute and Mapping nodes) is to let you see the results of the Compute node's transformation. The other Trace nodes are connected to the out1 and out2 terminals
of the Compute node. One is used to record any messages that do not belong to the EMPLOYEE table and the other is for messages other than an insert, update, or delete action.
The Output node only needs to have a MQ queue name identified.
Building the Compute node
The next four examples are the ESQL used in the Compute node. The Properties and MQMD folders are copied and the output Properties are updated for the intermediate message set.
The Event Publisher message may include a RFH2 header, but it is not needed in this example and is not copied.
Figure 5.1. ESQL - Initial Processing
01 Set OutputRoot.Properties = InputRoot.Properties;
02 Set OutputRoot.Properties.MessageSet = 'Event_Publisher_Middle_MessageSet';
03 Set OutputRoot.Properties.MessageType = 'msg';
04 Set OutputRoot.Properties.MessageFormat = 'XML1';
05 Set OutputRoot.MQMD = InputRoot.MQMD;
06-- There is an RFH2 header, but that is not needed and is ignored.
|
The intent of the message flow is to deal only with a specific table and only with one of three specific actions. The purpose of the following example is to make these specific checks and if any check fails,
then the message flow will be terminated. If the tablename is not EMPLOYEE, the original message is sent out via the out2 connection, as shown in lines 16-23.
A similar action is taken in lines 25-36 for the action option. In addition, several variables used in later sections are defined and initialized (lines 8-12).
Figure 5.2. ESQL - Input Validation
07-- -----------------------------------------------------------------Check Input
08-- Establish variables about the input message
09 DECLARE I INTEGER 1; -- Loop Counter for col tags
10 DECLARE H INTEGER 4; -- Loop Counter for ActionRows
11 Declare RowName CHAR FIELDNAME(InputBody.*[3]);
12 Declare ColName CHAR;
13-- The first action tag is the 4th element in the tree, may be more than one
14 Declare ActCnt Integer CARDINALITY(InputBody.{RowName}.*[]);
15-- Get the tablename if its NOT the one we want, send it to out2 and quit
16 Declare tablename CHAR 'INVALID';
17 Set tablename = InputBody.{RowName}.*[<].srcName;
18 If tablename <> 'EMPLOYEE' Then
19 Set Environment.tablename = tablename;
20 Set OutputRoot=InputRoot;
21 Propagate TO TERMINAL 'out2';
22 Return False;
23 End If;
24-- Get the Action if its not one we want send it to out1 and quit
25 Declare Action CHAR 'IGNORE';
26 Declare ActionRow CHAR FIELDNAME(InputBody.{RowName}.*[H]);
27 If ActionRow = 'insertRow' Then Set Action = 'ADD';
28 ElseIf ActionRow = 'updateRow' then Set Action = 'UPDATE';
29 ElseIf ActionRow = 'deleteRow' then Set Action = 'DELETE';
30 END IF;
31 If Action = 'IGNORE' Then
32 Set Environment.Action = Action;
33 Set OutputRoot=InputRoot;
34 Propagate TO TERMINAL 'out1';
35 Return False;
36 End If;
37-- ------------------------------------------------------------------End of Checking
|
The next section of ESQL will loop through the input message with a double loop. With a <rowOp> there will be a single action request. However, with a <trans> there may be multiple action requests. This ESQL will scan the message and will build a set of Environment definitions. The second part of each entry will use the <col name=....> Line 44 will obtain the actual value of the name attribute. Line 45 will use that variable as a dynamic name, by the use of a pair of ellipses, assigned to the Environment tree and obtain the value of the tag that follows.
Figure 5.3 - ESQL - Scanning Input Message
38-- We first loop through the input message and build a set of Environment values
39-- There may be multiple action rows - pointer is the .*[H]
40 WHILE H <= ActCnt DO
41 SET I = 1;
42 Declare ColCnt Integer CARDINALITY(InputBody.{RowName}.*[H].col[]);
43 WHILE I <= ColCnt DO
44 Set ColName = InputBody.{RowName}.*[H].col[I].name;
45 SET Environment.{ColName} = InputBody.{RowName}.*[H].col[I].*[<];
46 SET I = I + 1;
47 END WHILE;
48 SET H = H + 1;
49 END WHILE;
|
The final section of ESQL uses the Environment tree just built to construct the output message tree in the exact order defined in the schema and message set. The output message
will then be sent to a Mapping node where the final output will be built. This is the section of ESQL that would be replaced with the ESQL generated by the BuildXML message flow.
Figure 5.4 - ESQL - Transforming
50-- We now build the intermediate xml msg that will be used by the Mapping node
51 Set OutputRoot.MRM.Action = Action;
52-- 1st entry is the key - must set OutputRoot in the order defined in schema.
53 Set OutputRoot.MRM.Row_EMPLOYEE.EMPNO = Environment.EMPNO;
54 Set OutputRoot.MRM.Row_EMPLOYEE.BIRTHDATE = Environment.BIRTHDATE;
55 Set OutputRoot.MRM.Row_EMPLOYEE.BONUS = Environment.BONUS;
56 Set OutputRoot.MRM.Row_EMPLOYEE.COMM = Environment.COMM;
57 Set OutputRoot.MRM.Row_EMPLOYEE.EDLEVEL = Environment.EDLEVEL;
58 Set OutputRoot.MRM.Row_EMPLOYEE.FIRSTNME = Environment.FIRSTNME;
59 Set OutputRoot.MRM.Row_EMPLOYEE.HIREDATE = Environment.HIREDATE;
60 Set OutputRoot.MRM.Row_EMPLOYEE.JOB = Environment.JOB;
61 Set OutputRoot.MRM.Row_EMPLOYEE.LASTNAME = Environment.LASTNAME;
62 Set OutputRoot.MRM.Row_EMPLOYEE.MIDINIT = Environment.MIDINIT;
63 Set OutputRoot.MRM.Row_EMPLOYEE.PHONENO = Environment.PHONENO;
64 Set OutputRoot.MRM.Row_EMPLOYEE.SALARY = Environment.SALARY;
65 Set OutputRoot.MRM.Row_EMPLOYEE.SEX = Environment.SEX ;
66 Set OutputRoot.MRM.Row_EMPLOYEE.WORKDEPT = Environment.WORKDEPT;
67
68 RETURN TRUE;
69 END;
|
Building the Mapping node
The WebSphere Message Broker Mapping node provides a graphical environment for constructing a set of mappings between a source and target.
In addition to mapping between an input and output message, it also supports the use of relational databases. However, in order for the Mapping node to understand what is available, the input and output messages must be defined as message sets. A wizard is provided to guide you through the process. The initial steps are not shown. The process includes identifying the input and output messages to the wizard.
WebSphere Message Broker V6.0.0.2 (Fix Pack 2) has several features used in this example. When the mapping Editor is initially opened, the information about the output message is preloaded into the output Properties folder. There is also a new function called "Map By Name." To complete the mapping of the Properties folder, you simply highlight the Properties folder in the Source pane, right-click on the Properties folder in the Target pane, and select map By Name. Then use the defaults on the Map By Name screen and the remainder of the Properties folder will be mapped.
The next step is to do the same, with one difference, for the input and output messages. You still highlight the input and
right-click on the output and select Map By Name, as shown in the next example.
When the Map By Name screen is shown, change the second option to map item of same similar names.
The number of automatically generated mappings will depend on the similarity of the names. As shown in the next example, all but one of the fields were automatically mapped. The exception was the WORKDEPT (shown as the last input field) was not matched to the Department field in the target. This final mapping is accomplished by simply dragging the source field to the target field.
Thus, the final message is constructed with a minimum number of actions.
ESQL from the BuildXML message flow
The ESQL is almost identical with the EventPublisher message flow. The major differences are:
- Lines 2-4: A generic XML message is generated so information about a message set is not needed
- The ESQL that validates the tablename and action type is not needed
- Line 29: Generates the XML output message
- Lines 30-31: Builds the Set statements in Environment.ESQL
Figure 5.2 - ESQL - Input Validation
01 Set OutputRoot.Properties = InputRoot.Properties;
02 Set OutputRoot.Properties.MessageSet = ' ';
03 Set OutputRoot.Properties.MessageType = ' ';
04 Set OutputRoot.Properties.MessageFormat = ' ';
05 Set OutputRoot.MQMD = InputRoot.MQMD;
06-- There is an RFH2 header, but that is not needed and is ignored.
07-- -----------------------------------------------------------------Check Input
08-- Establish variables about the input message
09 DECLARE I INTEGER 1; -- Loop Counter for col tags
10 DECLARE H INTEGER 4; -- Loop Counter for ActionRows
11 Declare RowName CHAR FIELDNAME(InputBody.*[3]);
12 Declare ColName CHAR;
13-- The first action tag is the 4th element in the tree, may be more than one
14 Declare ActCnt Integer CARDINALITY(InputBody.{RowName}.*[]);
15-- Get the tablename to use in the XML output
16 Declare tablename CHAR 'INVALID';
17 Set tablename = 'Row_' || InputBody.{RowName}.*[<].srcName;
18-- ---------------------------------------------------------------End of Checking
19-- We first loop through the input message and build a set of Environment values
20-- as well as a generic XML
21-- There may be multiple action rows - pointer is the .*[H]
22 Set Environment.ESQL = 'Set OutputRoot.MRM.Action = Action;';
23 WHILE H <= ActCnt DO
24 SET I = 1;
25 Declare ColCnt Integer CARDINALITY(InputBody.{RowName}.*[H].col[]);
26 WHILE I <= ColCnt DO
27 Set ColName = InputBody.{RowName}.*[H].col[I].name;
28 SET Environment.{ColName} = InputBody.{RowName}.*[H].col[I].*[<];
29 Set OutputRoot.XML.msg.{tablename}.{ColName} = Environment.{ColName};
30 Set Environment.ESQL = Environment.ESQL ||'Set OutputRoot.MRM.'
31 ||tablename||'.'||ColName||' = Environment.'||ColName ||';';
32 SET I = I + 1;
33 END WHILE;
34 SET H = H + 1;
35 END WHILE;
36 RETURN TRUE;
|
Building the Event Publisher Multiple Tables message flow
This message flow has a MQInput node, Compute node, and three Trace nodes to process the original message and for each table, a Label node, Compute node, Mapping node, and MQOutput node.
The only path that is complete is for the EMPLOYEE table. There are only minor differences in the ESQL in this flow. The first Compute node processes the XML publication message and builds the
Environment entries. It also checks that the table name is one of a specific list. The original message is also passed. If the table name is not known, the message is sent to a trace node and is considered to be an error. If the action (insertRow, updateRow, deleteRow) is not valid, it is sent to another trace node and again is considered to be an error. Once the information from the original message is built in the Environment, the Compute node uses a Propagate to LABEL tablename. This branching capability requires the use of a Label node (shown in the second example) where the properties of that node contain the name of the table, spelled as received in the XML publication. If there is not a match, the message is sent automatically to a third trace file. This type of error should be found during the testing phase.
Every Label node's properties must match with the table name provided by the XML publication:
Label node
The following code is from the first Compute node. Since the original message will be passed, the change of the MessageSet information in the properties has been removed. Line 12 defines a list of valid table names. In lines 19-24, a check is made using that list to determine if the tablename from the XMP publication is valid. In line 53 the Propagate to LABEL tablename is used. If a matching Label node is not found then the message is automatically sent to the Out terminal. Since this is the final processing done by this Compute node, a Return False is used in Line 54.
Figure 6.1. ESQL: First Compute node
01 -- Copy the input message, but not the RFH2
02 Set OutputRoot.Properties = InputRoot.Properties;
03 Set OutputRoot.MQMD = InputRoot.MQMD;
04 Set OutputRoot.MRM = InputRoot.MRM;
05 -- --------------------------------------------------------------Check Input
06 -- Establish variables about the input message
07 DECLARE I INTEGER 1; -- Loop Counter for col tags
08 DECLARE H INTEGER 4; -- Loop Counter for ActionRows
09 Declare RowName CHAR FIELDNAME(InputBody.*[3]);
10 Declare ColName CHAR;
11 -- Establish a list of valid Table Names
12 Declare ValidTables CHAR 'CL_SCHED DEPARTMENT EMPLOYEE EMP_ACT EMP_RESUME';
13 -- The first action tag is the 4th element in the tree, may be more than one
14 Declare ActCnt Integer CARDINALITY(InputBody.{RowName}.*[]);
15 -- Get the tablename if its NOT the one we want, send it to out2 and quit
16 Declare tablename CHAR 'INVALID';
17 Set tablename = InputBody.{RowName}.*[<].srcName;
18 -- Handle as an error.
19 If Position(tablename IN ValidTables) = 0 Then
20 Set Environment.tablename = tablename;
21 Set OutputRoot=InputRoot;
22 Propagate TO TERMINAL 'out2';
23 Return False;
24 End If;
25 -- Get the Action if its not one we want send it to out1 and quit
26 Declare Action CHAR 'IGNORE';
27 Declare ActionRow CHAR FIELDNAME(InputBody.{RowName}.*[H]);
28 If ActionRow = 'insertRow' Then Set Action = 'ADD';
29 ElseIf ActionRow = 'updateRow' then Set Action = 'UPDATE';
30 ElseIf ActionRow = 'deleteRow' then Set Action = 'DELETE';
31 END IF;
32 If Action = 'IGNORE' Then
33 Set Environment.Action = Action;
34 Set OutputRoot=InputRoot;
35 Propagate TO TERMINAL 'out1';
36 Return False;
37 End If;
38 Set Environment.Action = Action;
39 -- ---------------------------------------------------------------End of Checking
40 -- We loop through the input message and build a set of Environment values
41 -- There may be multiple action rows - pointer is the .*[H]
42 WHILE H <= ActCnt DO
43 SET I = 1;
44 Declare ColCnt Integer CARDINALITY(InputBody.{RowName}.*[H].col[]);
45 WHILE I <= ColCnt DO
46 Set ColName = InputBody.{RowName}.*[H].col[I].name;
47 SET Environment.{ColName} = InputBody.{RowName}.*[H].col[I].*[<];
48 SET I = I + 1;
49 END WHILE;
50 SET H = H + 1;
51 END WHILE;
52 -- The exact spelling of the tablename must be set in the Label node
53 Propagate to LABEL tablename;
54 RETURN FALSE;
|
Each path for processing individual tables has the same requirements. In the message flow, the additional table paths are incomplete.
The ESQL will need to define the new message set information in the Properties. In addition, the ESQL to build the XML message defined
by that message set which is then passed to a Mapping node needs to be added. This code is generated via a Trace node by the BuildXML message flow.
Figure 6.2. ESQL: Second Compute node
01 CALL CopyMessageHeaders();
02 Set OutputRoot.Properties.MessageSet = 'Event_Publisher_Middle_MessageSet';
03 Set OutputRoot.Properties.MessageType = 'msg';
04 Set OutputRoot.Properties.MessageFormat = 'XML1';
05 -- We now build the intermediate xml msg that will be used by the Mapping node
06 Set OutputRoot.MRM.Action = Environment.Action;
07 -- 1st entry is the key - must set OutputRoot in the order defined in schema.
08 Set OutputRoot.MRM.Row_EMPLOYEE.EMPNO = Environment.EMPNO;
09 Set OutputRoot.MRM.Row_EMPLOYEE.BIRTHDATE = Environment.BIRTHDATE;
10 Set OutputRoot.MRM.Row_EMPLOYEE.BONUS = Environment.BONUS;
11 Set OutputRoot.MRM.Row_EMPLOYEE.COMM = Environment.COMM;
12 Set OutputRoot.MRM.Row_EMPLOYEE.EDLEVEL = Environment.EDLEVEL;
13 Set OutputRoot.MRM.Row_EMPLOYEE.FIRSTNME = Environment.FIRSTNME;
14 Set OutputRoot.MRM.Row_EMPLOYEE.HIREDATE = Environment.HIREDATE;
15 Set OutputRoot.MRM.Row_EMPLOYEE.JOB = Environment.JOB;
16 Set OutputRoot.MRM.Row_EMPLOYEE.LASTNAME = Environment.LASTNAME;
17 Set OutputRoot.MRM.Row_EMPLOYEE.MIDINIT = Environment.MIDINIT;
18 Set OutputRoot.MRM.Row_EMPLOYEE.PHONENO = Environment.PHONENO;
19 Set OutputRoot.MRM.Row_EMPLOYEE.SALARY = Environment.SALARY;
20 Set OutputRoot.MRM.Row_EMPLOYEE.SEX = Environment.SEX ;
21 Set OutputRoot.MRM.Row_EMPLOYEE.WORKDEPT = Environment.WORKDEPT;
22 RETURN TRUE;
|
Conclusion
The introduction showed three disconnected pictures that represented a business problem. The above picture shows the solution. Data events are captured, transported, and processed in a variety of ways to meet different business requirements. In addition to the functional WebSphere Message Broker message flow, other example message flows are provided to make building your own solution even easier.
Download | Description | Name | Size | Download method |
|---|
| Sample XML and XSD files to build WMB solution | Event_PublisherFiles.zip | 33KB | FTP|HTTP |
|---|
Resources -
Integrating applications using WebSphere Business Integration Message Broker V5, Part 2: Message set development
Learn how to define messages to be processed by WebSphere Message Broker.
-
What's new in WebSphere Message Broker V6
This article details the enhancements in the following areas:
installation and configuration, migration, transformation technology, message processing, performance, Web services support, administration and management, security, and samples.
-
Data federation with WebSphere Business Integration Message Broker and WebSphere Information Integrator
This article shows you how to implement data federation
using WebSphere Business Integration Message Broker with WebSphere Information Integrator or WebSphere Information Integrator Classic Federation (WebSphere II CF).
-
WebSphere Message Broker information center
A single Eclipse-based Web portal to all WebSphere Message Broker V6 documentation,
with conceptual, task, and reference information on installing, configuring, and using your WebSphere Message Broker environment.
-
WebSphere Message Broker documentation library
WebSphere Message Broker specifications and manuals.
-
WebSphere Message Broker product page
Product descriptions, product news, training information, support information, and more.
-
WebSphere Message Broker forum
Get answers to your technical questions and share your expertise with other WebSphere Message Broker users.
-
WebSphere Message Broker support page
Access to all support resources for WebSphere Message Broker.
-
WebSphere Information Integrator planning and sizing
How to plan your WebSphere Information Integrator installation.
-
WebSphere Data Event Publisher home page
WebSphere Data Event Publisher complements and extends your other process and data integration infrastructure investments by eliminating the hand
coding typically required to detect DB2 data changes made by operational applications and by removing data event capture processing from the operational application.
-
WebSphere Information Integrator Classic Event Publisher home page
Classic Event Publisher captures changes made to mainframe databases or files and publishes these changes
to WebSphere MQ. The changed-data events can be used by an application, tool or message broker to drive additional processing.
-
developerWorks Information Integrator zone
Expand your Information Integrator skills.
-
WebSphere Business Integration products page
For both business and technical users, a handy overview of all WebSphere Business Integration products
-
developerWorks WebSphere Business Integration zone
For developers, access to WebSphere Business Integration how-to articles, downloads, tutorials, education, product info, and more.
-
WebSphere forums
Product-specific forums where you can get answers to your technical questions and share your expertise with other WebSphere users.
-
Most popular WebSphere trial downloads
No-charge trial downloads for key WebSphere products.
-
Trial downloads for IBM software products
No-charge trial downloads for selected IBM® DB2®, Lotus®, Rational®, Tivoli®, and WebSphere® products.
-
Safari Bookshelf: e-library designed for developers
Complete search and download access to thousands of technical books for a one-time subscription fee. Free trial for new subscribers.
-
developerWorks technical events and Webcasts
Free technical sessions by IBM experts that can accelerate your learning curve and help you succeed in your most difficult software projects.
Sessions range from one-hour Webcasts to half-day and full-day live sessions in cities worldwide.
-
developerWorks blogs
Ongoing, free-form columns by software experts, to which you can add your comments. Check out Grady Booch's blog on software architecture.
About the authors | | | Bill Matthews is a Certified Consulting IT Specialist specializing in WebSphere Message Broker and WebSphere MQ. His background also includes CICS on both mainframe and distributed systems. He is currently working on a proof of technology for an advanced ESB using WebSphere Message Broker.
You can contact Bill at cicsos2@us.ibm.com. |
| | | Carol Rigdon is a Software IT Specialist specializing in DB2, DB2 replication, and WebSphere Information Integrator.
You can contact Carol at rigdon@us.ibm.com. |
Rate this page
| |
