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