The MQSeries Functions can be used in a wide variety of scenarios. This section will review some of the more common scenarios, including Basic Messaging, Application Connectivity and Data Publication.
The most basic form of messaging with the MQSeries DB2 Functions occurs when all database applications connect to the same DB2 server. Clients may be local to the database server or distributed in a network environment.
In a simple scenario, Client A invokes the MQSEND function to send a user-defined string to the default service location. The MQSeries functions are then executed within DB2 on the database server. At some later time, Client B invokes the MQRECEIVE function to remove the message at the head of the queue defined by the default service and return it to the client. Again, the MQSeries functions to perform this work are executed by DB2.
Database clients can use simple messaging in a number of ways. Some common uses for messaging are:
The following scenario extends the simple scenario described above to incorporate remote messaging. That is, a message is sent between Machine A and Machine B. The sequence of steps is as follows:
Using MQSEND, a DB2 user or developer chooses what data to send, where to send it, and when it will be sent. In the industry this is commonly called "Send and Forget," meaning that the sender just sends a message, relying on the guaranteed delivery protocols of MQSeries to ensure that the message reaches its destination. The following examples illustrate this.
Example 4: To send a user-defined string to the service point myPlace with the policy highPriority:
VALUES DB2MQ.MQSEND('myplace','highPriority','test')
Here, the policy highPriority refers to a policy defined in the AMI Repository that sets the MQSeries priority to the highest level and perhaps adjusts other qualities of service, such as persistence, as well.
The message content may be composed of any legal combination of SQL and user-specified data. This includes nested functions, operators, and casts. For instance, given a table EMPLOYEE, with VARCHAR columns LASTNAME, FIRSTNAME, and DEPARTMENT, to send a message containing this information for each employee in DEPARTMENT 5LGA you would do the following:
Example 5:
SELECT DB2MQ.MQSEND(LASTNAME || ' ' || FIRSTNAME || ' ' || DEPARTMENT) FROM EMPLOYEE WHERE DEPARTMENT = '5LGA'
If this table also had an integer AGE column, it could be included as follows:
Example 6:
SELECT DB2MQ.MQSEND
(LASTNAME || ' ' || FIRSTNAME || ' ' || DEPARTMENT|| ' ' || char(AGE))
FROM EMPLOYEE
WHERE DEPARTMENT = '5LGA'
If the table EMPLOYEE had a column RESUME of type CLOB instead of an AGE column, then a message containing the information for each employee in DEPARTMENT 5LGA could be sent out with the following:
Example 7:
SELECT DB2MQ.MQSEND
(clob(LASTNAME) || ' ' || clob(FIRSTNAME) || ' ' ||
clob(DEPARTMENT) || ' ' || RESUME))
FROM EMPLOYEE
WHERE DEPARTMENT = '5LGA'
Example 8:
Finally, the following example shows how message content may be derived using any valid SQL expression. Given a second table DEPT containing VARCHAR columns DEPT_NO and DEPT_NAME, messages can be sent that contain employee LASTNAME and DEPT_NAME:
Example 8:
SELECT DB2MQ.MQSEND(e.LASTNAME || ' ' || d.DEPTNAME) FROM EMPLOYEE e, DEPT d WHERE e.DEPARTMENT = d.DEPTNAME
The MQSeries DB2 Functions allow messages to be either received or read. The difference between reading and receiving is that reading returns the message at the head of a queue without removing it from the queue, while receiving operations cause the message to be removed from the queue. A message retrieved using a receive operation can only be retrieved once, while a message retrieved using a read operation allows the same message to be retrieved many times. The following examples demonstrate this:
Example 8:
VALUES DB2MQ.MQREAD()
This example returns a VARCHAR string containing the message at the head of queue defined by the default service using the default quality of service policy. It is important to note that if no messages are available to be read, a null value will be returned. The queue is not changed by this operation.
Example 9:
VALUES DB2MQ.MQRECEIVE('Employee_Changes')
The above example shows how a message can be removed from the head of the queue defined by the Employee_Changes service using the default policy.
One very powerful feature of DB2 is the ability to generate a table from a user-defined (or DB2-provided) function. You can exploit this table function feature to allow the contents of a queue to be materialized as a DB2 table. The following example demonstrates the simplest form of this:
Example 10:
SELECT t.* FROM table ( DB2MQ.MQREADALL()) t
This query returns a table consisting of all of the messages in the queue defined by the default service and the metadata about these messages. While the full definition of the table structure returned is defined in the Appendix, the first column reflects the contents of the message and the remaining columns contain the metadata. To return just the messages, the example could be rewritten:
Example 11:
SELECT t.MSG FROM table (DB2MQ.MQREADALL()) t
The table returned by a table function is no different from a table retrieved from the database directly. This means that you can use this table in a wide variety of ways. For instance, you can join the contents of the table with another table or count the number of messages in a queue:
Example 12:
SELECT t.MSG, e.LASTNAME
FROM table (DB2MQ.MQREADALL() ) t, EMPLOYEE e
WHERE t.MSG = e.LASTNAME
Example 13:
SELECT COUNT(*) FROM table (DB2MQ.MQREADALL()) t
You can also hide the fact that the source of the table is a queue by creating a view over a table function. For instance, the following example creates a view called NEW_EMP over the queue referred to by the service named NEW_EMPLOYEES:
Example 14:
CREATE VIEW NEW_EMP (msg) AS SELECT t.msg FROM table (DB2MQ.MQREADALL()) t
In this case, the view is defined with only a single column containing an entire message. If messages are simply structured, for instance containing two fields of fixed length, it is straightforward to use the DB2 built-in functions to parse the message into the two columns. For example, if you know that messages sent to a particular queue always contain an 18-character last name followed by an 18-character first name, then you can define a view containing each field as a separate column as follows:
Example 15:
CREATE VIEW NEW_EMP2 AS SELECT left(t.msg,18) AS LNAME, right(t.msg,18) AS FNAME FROM table(DB2MQ.MQREADALL()) t
A new feature of the DB2 Stored Procedure Builder, the MQSeries Assist Wizard, can be used to create new DB2 table functions and views that will map delimited message structures to columns.
Finally, it is often desirable to store the contents of one or more messages into the database. This may be done using the full power of SQL to manipulate and store message content. Perhaps the simplest example of this is:
Example 16:
INSERT INTO MESSAGES SELECT t.msg FROM table (DB2MQ.MQRECEIVEALL()) t
Given a table MESSAGES, with a single column of VARCHAR(2000), the statement above will insert the messages from the default service queue into the table. This technique can be embellished to cover a very wide variety of circumstances.
Application integration is a common element in many solutions. Whether integrating a purchased application into an existing infrastructure or just integrating a newly developed application into an existing environment, we are often faced with the task of glueing a heterogeneous collection of subsystems together to form a working whole. MQSeries is commonly viewed as an essential tool for integrating applications. Accessible in most hardware, software, and language environments, MQSeries provides the means to interconnect a very heterogeneous collection of applications.
This section will discuss some application integration scenarios and how they may be used with DB2. As the topic is quite broad, a comprehensive treatment of Application Integration is beyond the scope of this work. Therefore, the focus is on just two simple topics: Request/Reply communications, and MQSeries Integrator and Publish/Subscribe.
The Request/Reply (R/R) communications method is a very common technique for one application to request the services of another. One way to do this is for the requester to send a message to the service provider requesting some work to be performed. Once the work has been completed, the provider may decide to send results (or just a confirmation of completion) back to the requestor. But using the basic messaging techniques described above, there is nothing that connects the sender's request with the service provider's response. Unless the requester waits for a reply before continuing, some mechanism must be used to associate each reply with its request. Rather than force the developer to create such a mechanism, MQSeries provides a correlation identifier that allows the correlation of messages in an exchange.
While there are a number of ways in which this mechanism could be used, the simplest is for the requestor to mark a message with a known correlation identifier using, for instance, the following:
Example 17:
DB2MQ.MQSEND ('myRequester','myPolicy','SendStatus:cust1','Req1')
This statement adds a final parameter Req1 to the MQSEND statement from above to indicate the correlation identifier for the request.
To receive a reply to this specific request, use the corresponding MQRECEIVE statement to selectively retrieve the first message defined by the indicated service that matches this correlation identifier as follows:
Example 18:
DB2MQ.MQRECEIVE('myReceiver','myPolicy','Req1')
If the application servicing the request is busy and the requestor issues the above MQRECEIVE before the reply is sent, then no messages matching this correlation identifier will be found.
To receive both the service request and the correlation identifier a statement like the following is used:
Example 19:
SELECT msg, correlid FROM
table (DB2MQ.MQRECEIVEALL('aServiceProvider','myPolicy',1)) t
This returns the message and correlation identifier of the first request from the service aServiceProvider.
Once the service has been performed, it sends the reply message to the queue described by aRequester. Meanwhile, the service requester could have been doing other work. In fact, there is no guarantee that the initial service request will be responded to within a set time. Application level timeouts such as this must be managed by the developer; the requester must poll to detect the presence of the reply.
The advantage of such time-independent asynchronous processing is that the requester and service provider execute completely independently of one another. This can be used both to accommodate environments in which applications are only intermittently connected and more batch-oriented environments in which multiple requests or replies are aggregated before processing. This kind of aggregation is often used in data warehouse environments to periodically update a data warehouse or operational data store.
Another common scenario in application integration is for one application to notify other applications about events of interest. This is easily done by sending a message to a queue monitored by another application. The contents of the message can be a user-defined string or can be composed from database columns. Often a simple message is all that needs to be sent using the MQSEND function. When such messages need to be sent concurrently to multiple recipients, the Distribution List facility of the MQSeries AMI can be used.
A distribution list is defined using the AMI Administration tool. A distribution list comprises a list of individual services. A message sent to a distribution list is forwarded to every service defined within the list. This is especially useful when it is known that a few services will always be interested in every message. The following example shows sending of a message to the distribution list interestedParties:
Example 20:
DB2MQ.MQSEND('interestedParties','information of general interest');
When more control over the messages that particular services should receive is required, a Publish/Subscribe capability is needed. Publish/Subscribe systems typically provide a scalable, secure environment in which many subscribers can register to receive messages from multiple publishers. To support this capability the MQPublish interface can be used, in conjunction with MQSeries Integrator or the MQSeries Publish/Subscribe facility.
MQPublish allows users to optionally specify a topic to be associated with a message. Topics allow a subscriber to more clearly specify the messages to be accepted. The sequence of steps is as follows:
To publish this data using all the defaults and a null topic, you would use the following statement:
Example 21:
SELECT DB2MQ.MQPUBLISH
(LASTNAME || ' ' || FIRSTNAME || ' ' ||
DEPARTMENT|| ' ' ||char(AGE))
FROM EMPLOYEE
WHERE DEPARTMENT = '5LGA'
Fully specifying all the parameters and simplifying the message to contain only the LASTNAME the statement would look like:
Example 22:
SELECT DB2MQ.MQPUBLISH('HR_INFO_PUB', 'SPECIAL_POLICY', LASTNAME,
'ALL_EMP:5LGA', 'MANAGER')
FROM EMPLOYEE
WHERE DEPARTMENT = '5LGA'
This statement publishes messages to the HR_INFO_PUB publication service using the SPECIAL_POLICY service. The messages indicate that the sender is the MANAGER topic. The topic string demonstrates that multiple topics, concatenated using a ':' can be specified. In this example, the use of two topics allows subscribers to register for either ALL_EMP or just 5LGA to receive these messages.
To receive published messages, you must first register your interest in messages containing a given topic and indicate the name of the subscriber service that messages should be sent to. It is important to note that an AMI subscriber service defines a broker service and a receiver service. The broker service is how the subscriber communicates with the publish/subscribe broker and the receiver service is where messages matching the subscription request will be sent. The following statement registers an interest in the topic ALL_EMP.
Example 23:
DB2MQ.MQSUBSCRIBE('aSubscriber', 'ALL_EMP')
Once an application has subscribed, messages published with the topic ALL_EMP will be forwarded to the receiver service defined by the subscriber service. An application can have multiple concurrent subscriptions. To obtain the messages that meet your subscription, any of the standard message retrieval functions can be used. For instance if the subscriber service aSubscriber defines the receiver service to be aSubscriberReceiver then the following statement will non-destructively read the first message:
Example 24:
DB2MQ.MQREAD('aSubscriberReceiver')
To determine both the messages and the topics that they were published under, you would use one of the table functions. The following statement would receive the first five messages from aSubscriberReceiver and display both the message and the topic:
Example 25:
SELECT t.msg, t.topic
FROM table (DB2MQ.MQRECEIVEALL('aSubscriberReceiver',5)) t
To read all of the messages with the topic ALL_EMP, you can leverage the power of SQL to issue:
Example 26:
SELECT t.msg FROM table (DB2MQ.MQREADALL('aSubscriberReceiver')) t
WHERE t.topic = 'ALL_EMP'
When you are no longer interested in subscribing to a particular topic you must explicitly unsubscribe using a statement such as:
Example 27:
DB2MQ.MQUNSUBSCRIBE('aSubscriber', 'ALL_EMP')
Once this statement is issued the publish/subscribe broker will no longer deliver messages matching this subscription.
Another important technique in database messaging is automated publication. Using the trigger facility within DB2, you can automatically publish messages as part of a trigger invocation. While other techniques exist for automated data publication, the trigger-based approach allows administrators or developers great freedom in constructing the message content and flexibility in defining the trigger actions. As with any use of triggers, attention must be paid to the frequency and cost of execution. The following examples demonstrate how triggers may be used with the MQSeries DB2 Functions.
The example below shows how easy it is to publish a message each time a new employee is hired. Any users or applications subscribing to the HR_INFO_PUB service with a registered interest in NEW_EMP will receive a message containing the date, name and department of each new employee.
Example 28:
CREATE TRIGGER new_employee AFTER INSERT ON employee REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
VALUES DB2MQ.MQPUBLISH('HR_INFO_PUB&', 'NEW_EMP',
current date || ' ' || LASTNAME || ' ' || DEPARTMENT)