Enable WebSphere MQ messaging in Informix Dynamic Server applications

MQing made easy

The IBM® WebSphere® Message Queue (MQ) software suite provides reliable messaging for distributed, heterogeneous applications to exchange information, delegate jobs, coordinate events, and offer services. When Informix® applications use WMQ, you write custom code, manage multiple connections, and route data through your application. Informix Dynamic Server (IDS) Version 10.00.UC3 introduces built-in support for Informix applications to interact with WebSphere MQ using SQL callable functions with two-phase commit support. This eliminates development overhead, and encapsulates integration complexity.

Share:

Keshava Murthy (rkeshav@us.ibm.com), Architect, IBM, Software Group

Keshava MurthyKeshava Murthy is the architect of IBM Informix Dynamic Server's (IDS) SQL and Optimizer components, leading feature development for new releases. He has worked on various relational and object relational databases. He has developed features in SQL, RTREE, distributed queries, heterogeneous transaction management, and extensibility components of IDS. Together with Sitaram Vemulapalli, he developed MQ functionality in IDS. He has worked with key ISVs and customers to enable embedding and application development on Informix. Keshav holds a Bachelor's degree in computer science and engineering from the University of Mysore, India.



Sitaram Vemulapalli (sitaramv@us.ibm.com), Software Engineer, IBM, Software Group

Sitaram VemulapalliSitaram Vemulapalli is a developer of IBM Informix Dynamic Server's SQL and Extensibility component. He has been working on the product for more than eight years. He has developed features in various components of the server including backup and restore, SQL, and extensibility. On the platform engineering team, he ported IDS to Linux 64bit on zSeries, HP-UX on IA64, and DEC 64bit. He is currently enhancing the distributed query feature in IDS. Sitaram holds a Master's degree from the Indian Institute of Science, Bangalore, India.



21 September 2006

Also available in Russian

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
WebSphere MQ introduction

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
IDS and MQ integration with custom coding

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
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 shipping_tab.

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.

Basics

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
Function NameDescription
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;

Utility functions

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
IDS and MQ transactional integration

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.


Environment

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.


Platform support

Table 2. Platform support
Informix Dynamic serverSupported PlatformsWebSphere MQ Version
10.00.xC3 and laterSolaris-32 bit * HP/UX(PA-RISK) -- 32bit * AIX-32bit * Windows-32bitNeeds V5.3 or later
10.00.xC4 and laterAIX-64 bit * HP/UX (PA-RISK) -- 64bitNeeds V6.0 or later
10.00.xC5 and laterLinux (Intel) -- 32 bit * Linux(pSeries) - 64bit * Solaris - 64bitNeeds C6.0 or later

Summary

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere
ArticleID=161131
ArticleTitle=Enable WebSphere MQ messaging in Informix Dynamic Server applications
publish-date=09212006