Brief overview of WebSphere MQ
In its simplest form, WebSphere MQ is a method to exchange messages between two end points. It acts as an intermediary between two systems and provides value-added function such as reliability and transactional semantics.
Figure 1. WebSphere MQ for business integration
Whether you buy a book on amazon.com or enroll in e-business with ibm.com, the order event triggers a work flow of the information through multiple modules: user account management, billing, packaging and shipping, procurement, customer service, and partner services. The execution in triggered modules generates subsequent work flow. To meet reliability and scaling requirements, it's typical to have application modules on multiple machines.
If you're using the same software on all systems, for example the SAP stack, the software itself usually comes with workflow management features. If the modules are running in a homogeneous environment -- for example, Linux® machines, running WebSphere and Informix -- it's easier to change information using distributed queries or enterprise replication. On the other hand, if the application is running on heterogeneous systems -- such as combinations of WebSphere, DB2®, Oracle, and Informix -- programming and setup of distributed queries or replication becomes complex and in many cases won't meet application requirements .
WebSphere MQ is designed to address integration issues like this. It prefers no platform and enforces no paradigms: WebSphere MQ supports more than 80 platforms, and APIs in C, C++, Java™, Java Message Service (JMS), and Visual Basic. WebSphere MQ is also the mainstay for designing enterprise service bus (ESB) for Service Oriented Architecture (SOA).
WebSphere MQ provides a reliable store-and-forward mechanism so each module can send and receive messages to and from it. WebSphere MQ achieves this by persistent queues and APIs for programming. In addition, WebSphere MQ Message Broker -- another product in the WebSphere MQ product suite -- provides message routing and translation services. Simplicity of infrastructure means the applications must establish, for example, message formats and queue attributes. WebSphere MQ also supports publish and subscribe semantics for queues, making it easy to send a single message to multiple receivers and subscribing messages from queues by need, similar to mailing lists.
The applications predetermine queue names, messages, and message formats, just like two network applications agree on socket numbers. The application-to-application message exchange is asynchronous -- one application does not wait for the other application to receive the message. WebSphere MQ assures the message is stored reliably and have the message available for the target application. But it's the responsibility of the target application to receive the message from WebSphere MQ.
How do Informix (and other database servers) applications use WebSphere MQ?
Figure 2. MQ and IDS integration using an MQ Interface (MQI) or JMS interface
The applications have many input sources, such as: user entry, B2B transactions, workflow messages, data in the database. The order entry application in Figure 2 needs to store data in the Informix database and send and receive messages to WebSphere MQ. The application establishes connections with Informix and WMQ. In addition, the application uses a transaction manager to ensure the reliability of data exchange. For example, the order saved in the database has to be sent to a queue and marked as processed in the database. The order can be marked as processed only after WebSphere MQ receives the message successfully. Therefore, the interaction has to be transactionally protected.
The order entry application writes custom code to exchange messages from and to WebSphere MQ. The need for custom code development every time an application wants to interact with WebSphere MQ is costly. You need to train your programmers for it or hire consultants to develop, debug, and maintain this code, and modify it for new queues and applications. The data exchanged between the database and WebSphere MQ flows through the application -- not efficient for high volume data and necessitating a transaction manager.
IDS support for WebSphere MQ
Figure 3. Using Informix MQ functions
IDS provides SQL callable functions to read, receive, send, subscribe, and publish. These SQL callable functions expose WebSphere MQ features to IDS applications and integrate WebSphere MQ operations into IDS transactions. That is, the fate of a WebSphere MQ operation is tied to the fate of the transaction. If the transaction is rolled back, the operations on WebSphere MQ -- messages sent or received -- are rolled back. This is done by coordinating transactions at IDS and WebSphere MQ, not by compensating transactions. So, this is reliable with high performance.
Code for invoking MQ functions
Using IDS WebSphere MQ functionality, sending and receiving a message to and from a WebSphere MQ queue is simple, as you see in Listing 1:
Listing 1. Using Informix MQ functions in SQL and stored procedures.
select MQSend("CreditService", customerid || ":" || address || ":" || product ":" || orderid) from order_tab where customerid = 1234; insert into shipping_tab(shipping_msg) values(MQReceive()); create function get_my_order() returns int; define cust_msg lvarchar(2048); define customerid char(12); define address char(64); define product char(12); define corderid char(12); define snd_status int; -- Get the order from Order entry application. execute function MQReceive("OrderQueue") into cust_msg; let customerid = substr(cust_msg, 1, 12); let address = substr(cust_msg, 14, 77); let product = substr(cust_msg, 79, 90); let corderid = substr(cust_msg, 92, 103); insert into shipping_table(custid, addr, productid, orderid) Values(customerid, address, product, corderid); -- send the status to CRM application execute function MQSend("CRMQueue", corderid || ":IN-SHIPPING") into snd_status; return 1; end function;
Code for invoking MQ functions
When you roll back the transaction in Listing 2, the message received is restored in the queue
bookorder and the row will also be removed from
Listing 2. Multi-statement transaction with an MQ function
begin work; insert into shipping_tab(shipping_msg) values (MQReceive("bookorderservice")); rollback work; -- Undo previous statement including WMQ operation.
Use MQ functions in IDS applications
IDS provides functions exposing each interface WebSphere MQ provides -- read, receive, send, publish, subscribe, and unsubscribe -- and functions to send and receive large messages. The WebSphere MQ functions can be invoked anywhere a function can be used: values clause, projection list, query filters, stored procedures, and triggers. In addition, with IDS, you can map a WebSphere MQ queue into an IDS table. An insert on this table translates to a send operation to WebSphere MQ, and select translates to either read or receive.
While WebSphere MQ provides simple abstractions of queue and its operations, each operation comes with a number of options, such as message expiry time and retry count. IDS has abstracted these options into service and policy.
Service: Maps a queue, queue manager, and code set of the messages into the service. The table "informix".mqiservice stores the mappings. IDS.DEFAULT.SERVICE is mapped to system default queue manager, queue named IDS.DEFAULT.QUEUE, and default code set.
Policy: The policy defines the attributes such as priority and expiry date for each operation. The table "informix".mqipolicy stores 37 attributes for each policy. IDS.DEFAULT.POLICY is the default policy. Depending on your application environment, create one or more policies.
Correlation ID: When multiple applications share the same queue, you can control the interaction using a correlation ID of up to 48 bytes. Once the applications agree on the correlation ID for their messages, they can simply get messages matching their correlation ID. They work similar to filters or predicates in SQL queries. A correlation ID isn't mandatory and has no default value.
Table 1. List of MQ functions in IDS
|MQSend()||Send a string message to a queue|
|MQSendClob()||Send CLOB data to a queue|
|MQRead()||Read a string message in the queue into IDS without removing it from the queue.|
|MQReadClob()||Read a CLOB in the queue into IDS without removing it from the queue.|
|MQReceive()||Receive a string message in the queue into IDS and remove it from the queue.|
|MQReceiveClob()||Receive a CLOB in the queue into IDS and remove it from the queue.|
|MQSubscribe()||Subscribe to a topic.|
|MQUnSubscribe()||Unsubscribe from a previously subscribed topic.|
|MQPublish()()||Publish a message into a topic.|
|MQPublishClob()||Publish a CLOB into a topic.|
|CreateMQVTIRead()||Create a read Virtual Table Interface (VTI) table and map it to a queue.|
|CreateMQVTIReceive()||Create a receive VTI table and map it to a queue.|
|MQTrace()||Trace the execution of MQ functions.|
|MQVersion()||Get the version of MQ functions.|
Functions for sending from IDS to WebSphere MQ
Listing 3. MQSend and MQSendClob functions
MQSend(Service, Service_Policy, Message, CorrelationID); MQSendClob(Service, Service_Policy, ClobMessage, CorrelationID);
You can send a message up to 32739 bytes to a WebSphere MQ queue. For sending larger messages, use the CLOB datatype and the MQSendClob() function. MQSendClob() behaves the same as MQSend(), except that it takes CLOB as its message parameter instead of character type. Message and ClobMessage are the mandatory parameters. IDS sends the message to the queue managed by the queue manager using the policy in the Service record entry saved in "informix".mqiservice table.
Parameter interpretation for send functions
All four parameters are specified. When the four parameters are given, translation is straightforward.
MQSend(serviceparam, policyparam, messageparam, correlationparam)
is executed as given.
Here is the translation when one or more parameters are missing:
MQsend(messageparam)is translated to:
MQSend("IDS.DEFAULT.SERVICE", "IDS.DEFAULT_POLICY", messageparam, NULL);
MQsend(messageparam)is translated to:
MQSend("IDS.DEFAULT.SERVICE", "IDS.DEFAULT_POLICY", messageparam, NULL);
MQsend(serviceparam, policyparam, messageparam)is translated to:
MQSend(serviceparam, policyparam, messageparam, NULL);
Listing 4. Send function example
select MQSend("myservice", "mypolicy", orderid || ":" || address) FROM tab Where orderid = 12345;
All WebSphere MQ functions should be run within a transaction. In IDS, the SQL statements SELECT, UPDATE, DELETE, and INSERT automatically start a new transaction. Or, you can start a new transaction with a BEGIN WORK statement.
Simply executing the function gives an error. For example:
execute function MQSend("MyService", "<order><id>5</id><custid>6789</custid></order>");
IDS does not implicitly start a new transaction for the EXECUTE statement. So, you must start a transaction explicitly:
Listing 5. Explicit transaction
begin work; execute function MQSend("MyService", "<order><id>5</id><custid>6789</custid></order>"); commit work;
If the transaction is rolled back, all operations on WebSphere MQ are rolled back just as IDS rolls back its changes.
Listing 6. Transaction with rollback
begin work; insert into resultstab(sendval) value(MQSend("MyService", "<order><id><5</id><custid>6789 </custid></order>")); rollback work;
IDS functions for reading and receiving from WebSphere MQ
Listing 7. Read and receive functions
MQRead(Service, Policy, CorrelationID) returns lvarchar; MQReadClob(Service, Policy, CorrelationID) returns CLOB; MQReceive(Service, Policy, CorrelationID) returns lvarchar; MQReceiveClob(Service, Policy, CorrelationID) returns CLOB;
The read operation gets the message from the queue without deleting the message from the queue. The receive operation removes the message from the queue and get the message. These functions can be called with zero or more parameters. The parameters are interpreted similar to MQSend() above. The transactional behavior of receive functions is same as MQSend.
MQRead() and MQReceive() can return up to 32739 bytes. The maximum size of the message itself is a WebSphere MQ configuration parameter. The larger messages should be read or received as CLOB. For MQ, a message is a message. Depending on the length, IDS differentiates between messages to map the messages to datatypes.
If a correlation ID is given, WebSphere MQ get the next message in the queue with a matching correlation ID. Otherwise, a NULL message is returned. Policy determines the wait time when no applicable message is present on the queue. So, using a predefined correlation ID, multiple applications can share the same queue, and for different purposes.
Listing 8. Using read and receive functions in SQL
select mqread("SHIPING.SERVICE","My.DEFAULT.POLICY") from systables where tabid = 1; select mqreceive("SHIPING.SERVICE","My.DEFAULT.POLICY") from systables where tabid = 1;
Publish and subscribe functions
Listing 9. Read and receive functions
MQPublish(publisher_name, policyparam, message, topic, correlationid); MQPublishClob(publisher_name, policyparam, clob_param, topic, correlationid); MQSubscribe(subscriber_name, policy_name, topic); MQUnsubscribe(subscriber_name, policy_name, topic);
Publishing and subscribing to a queue is an effective configuration for exchanging information between multiple applications on multiple subjects. When an order entry has to go to a credit card application, shipping, CRM, and partner application, the order entry application publishes the order once to a queue. Target applications can then subscribe to the queue and obtain the message using either the read or receive function. Within this scheme, WebSphere MQ also supports categorizing messages into topics for finer control. For example, the order entry message can categorize the order into books, electronics, and clothing topics.
You have to configure the queue for publishing and define the topics. WebSphere MQ allows for defining topics statically or dynamically. The Message Broker provides the publish and subscribe features, and it has to be running in addition to queue manager. The Message Broker component provides message routing and message translation, easing business integration challenges.
Subscribe to a topic and specify the queue on which you want to receive the messages. When a publisher inserts a message on that topic into the queue, the WebSphere MQ broker routes the messages to all of the queues of each specified subscriber. The subscribers retrieve the message from the queue through read or receive functions.
"informix".mqipubsub table: Before using the publish and subscribe services, you have to setup this table. See The Informix dynamic server documentation, its schema, and examples.
The publisher name and subscriber names have to be defined in the "informix".mqipubsub table. Other parameters have been discussed earlier.
Listing 10. Publish and subscribe functions
select MQSubscribe(‘WeatherChannel’,"Weather") from systables where tabid = 1; select mqPublish("WeatherChannel", "<weather><zip>94501</zip><date>7/27/2006</date> <high>89</high><low>59</low></weather>","Weather") from systables where tabid = 1; select mqreceive("WeatherChannel","Weather") from systables where tabid = 1;
MQVersion() returns the current version of the WebSphere MQ blade in IDS.
MQTrace(trace_level, trace_file) enables you to trace the execution path of the WebSphere MQ functions and the
interaction between IDS and MQ. The tracing level can be from 10 to 50 – multiples of 10.
Listing 11. Example trace output
14:19:38 Trace ON level : 50 14:19:47 >>ENTER : mqSend<< 14:19:47 status:corrid is null 14:19:47 >>ENTER : MqOpen<< 14:19:47 status:MqOpen @ build_get_mq_cache() 14:19:47 >>ENTER : build_get_mq_cache<< 14:19:47 status:build_get_mq_cache @ mi_get_database_info() 14:19:47 status:build_get_mq_cache @ build_mq_service_cache() 14:19:47 >>ENTER : build_mq_service_cache<< 14:19:47 <<EXIT : build_mq_service_cache>>
MQ table mapping functions
Invoking the WebSphere MQ functions in IDS is easy, not the easiest way to use MQ. IDS can map a WebSphere MQ queue to an IDS table. Performing a SELECT statement on the table fetches the messages in the queue, and and doing an INSERT statement on the table sends the message. Using is discussed below. Other operations, like UPDATE and DELETE, are disallowed on the table.
Listing 12. Table to queue mapping functions
MQCreateVtiRead(readtable, servicename, policy, maxMessage) MQCreateVtiReceive(receivetable, servicename, policy, maxMessage)
SELECT on the read table imitates
MQRead(). It fetches the message without deleting it from the queue. Whereas SELECT on receive table, deletes the message on the queue as well. The maxMessage parameter determines the size of the column, but it also determines the type of column. Positive length creates an lvarchar column of maxMessage size. The maximum length of the message you can define is 32607. Use -1 as maxMessage to retrieve the message as a CLOB, and -2 to retrieve the message as BLOB.
Listing 13. MQ table to queue mapping functions
-- Create a READ table with max message length 4096. execute function MQCreateVTIREAD("myreadtable", "myservice", "mypolicy", 4096); -- Below is the table created by MQCreateVTIREAD() function. create table myreadtab ( msg lvarchar(4096), correlid varchar(24), topic varchar(40), qname varchar(48), msgid varchar(12), msgformat varchar(8)) using "informix".mq (SERVICE = "myservice", POLICY = "mypolicy", ACCESS = "READ"); -- Get the top 10 messages from the queue. SELECT first 10 * from myreadtable; -- INSERT a message into the table INSERT into myreadtable values("IBM:81.98;Volume:1020"); -- SELECT the first message matching correlation id SELECT FIRST 1 * from myreadtable where correlid = 'abc123'; IDS is aware of correlation id predicate and sends the correlation id request to MQ. WMQ matches to correlation ID and sends the matched message. -- create a table to transport BLOB data. execute function MQCreateVTIRECEIVE("mydoctable", "myservice", "mypolicy", -2); -- Below is the table created by MQCreateVTIRECEIVE() function. create table mydoctable ( msg BLOB, correlid varchar(24), topic varchar(40), qname varchar(48), msgid varchar(12), msgformat varchar(8)) using "informix".mq (SERVICE = "myservice", POLICY = "mypolicy", ACCESS = "RECEIVE"); execute function MQCreateVTIREAD("myreadtable", "myservice", "mypolicy", 4096); execute function MQCreateVTIREAD("myreadtable", "myservice", "mypolicy", 4096); INSERT into mydoctable(msg) select blobcol from ordertab; -- insert using blob, get through blob insert into mydoctable(msg) values(filetoblob("/etc/passwd", "client")); select lotofile(msg, '/tmp/blob.dat','client') from mydoctable;
MQ functions and transactions
Figure 4. Integration of MQ functions into IDS Transaction Manager
When you invoke any WebSphere MQ function exchanging a message with MQ, you must be within a transaction, implicit or explicit. To provide reliable interaction between IDS and MQ, transactions are necessary. When the commit is successful, the application needs all changes to data at IDS and WebSphere MQ to be persisted. And when the application rolls back, any operations at WebSphere MQ are rolled back just like operations on IDS are rolled back. IDS implicitly starts a transaction when you issue data manipulation language (DML) (UPDATE, DELETE, INSERT or SELECT) and data definition language (DDL) statements (CREATE statements). Or, you can explicitly start a new transaction with BEGIN WORK statements, and APIs like Java Database Connectivity (JDBC) start a new transaction when you turn autocommit off.
Note: The EXECUTE FUNCTION/PROCEDURE statement does not start one, so you need to start a transaction before invoking a WebSphere MQ function in an EXECUTE statement.
The transaction management is transparent to the application. The application simply uses WebSphere MQ functionality under a transaction, and IDS handles the commit or rollback coordination between IDS and WebSphere MQ using the open two-phase commit protocol. This is integrated into IDS Transaction Manager. IDS handles WebSphere MQ along with its distributed transactions involving other IDS instances. During the IDS-MQ interaction, IDS opens a connection to WebSphere MQ, and when the application invokes the fist WebSphere MQ function within a transaction, IDS begins a corresponding transaction at MQ. During commit or rollback, IDS Transaction Manager is aware of WebSphere MQ participation in the transaction and coordinates the transaction with it.
MQ functionality is provided with IDS, and the datablade is installed into $INFORMIXDIR/extend when you install IDS. You must register the datablade in the database where you to want to invoke MQ functions. Currently, only WebSphere MQ interaction with Informix-logged databases is supported. It is unsupported in ANSI and non-logged databases.
IDS communicates with WebSphere MQ using the server API. Therefore, WebSphere MQ needs to be installed on the same machine as the server. This WebSphere MQ can channel the messages to one or more remote WebSphere MQ servers. Each dynamic server instance can connect to only one WebSphere MQ Queue Manager.
Send in a request to the authors or to your favorite IBM Informix support team, if you want to use it in a non-logged or ANSI mode database.
Table 2. Platform support
|Informix Dynamic server||Supported Platforms||WebSphere MQ Version|
|10.00.xC3 and later||Solaris-32 bit * HP/UX(PA-RISK) -- 32bit * AIX-32bit * Windows-32bit||Needs V5.3 or later|
|10.00.xC4 and later||AIX-64 bit * HP/UX (PA-RISK) -- 64bit||Needs V6.0 or later|
|10.00.xC5 and later||Linux (Intel) -- 32 bit * Linux(pSeries) - 64bit * Solaris - 64bit||Needs C6.0 or later|
IDS WebSphere MQ functionality eliminates the need for custom code development for IDS applications interacting with MQ. Once you set up the queues, services, and policies, developers can use WebSphere MQ functions like other built-in functions in the development environment of their choice. Even better, set up the READ and RECEIVE tables, and get developers to SELECT from and INSERT into it. Spend the extra money and time you saved sipping your favorite drink ordered over an MQ.
- IBM Informix Dynamic Server V10.00 Information Center: Learn more about Informix.
- IBM Informix products page: Get more details about Informix products.
- WebSphere MQ - Product Overview: Learn more about WebSphere MQ.
- Learn about the Informix MQ Datablade available on alphaWorks.
- Listen to Webcast on IDS support for WebShphere MQ.
- WebSphere MQ developerworks zone: Increase your WebSphere MQ skills.
- Visit the developerWorks Informix Zone to read articles and tutorials and connect to other resources to expand your Informix skills.
- developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Download a free trial version of Informix Dynamic Server.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community.