Obtaining and exploiting meta data with MQ support in Data Virtualization Manager for z/OS
WebSphere® MQ is a message handling system that enables applications to communicate in a distributed environment across different operating systems and networks. Many organizations utilize WebSphere MQ as a transactional infrastructure to ensure timely delivery of data. It is highly scalable, reliable, and operates pretty much as a self-contained system. But what are the options when you want to understand or use the data for enhanced insight that is hidden in MQ? Without readily available SQL interfaces to MQ how do you access the data without changing the state of a queue or message?
The answer is IBM Data Virtualization Manager for z/OS (DVM), a z/OS based data virtualization server that enables mainframe data of any kind to be accessed or joined with other enterprise data (on or off mainframe) in real-time. One of the product’s capabilities is to enable pre-relational databases and files to be represented in a relational format, providing the ability to infer a schema to file structures that are not schema based, such as VSAM, flat files, and in this case data within MQ queues and messages via its JDBC or ODBC SQL interface.
As with any accessing any DVM data source, you start by creating a virtual table. You can explore more about creating virtual tables in this section of the DVM knowledge base. https://www.ibm.com/support/knowledgecenter/en/SS4NKG_1.1.0/havuga10/topics/hlr_ag_tsk_al_stu_source_library_create.html. Note that MQ is a new supported data source and is not currently listed in the data sources. When you are selecting your data source pick the MQ option when defining the virtual table that represents a queue. Like for any other table, you must define the structure of the data, and in the case of MQ, you must define the Queue Manager name and the Queue Name before finishing the definition of the virtual table.
Here is what you thus will see when the virtual table is edited later:
The SQL statement is simply this:
SELECT * FROM MQ_TRADE;
Now that you have created a virtual table utilizing data from a MQ queue, you can now use DVM’s advanced tooling to create virtual table rules that simplify managing and governing the data. These rules have a number of benefits including visibility into message volume, arrival frequency and the ability to monitor for anomalies that might portend fraudulent behavior. There is even the ability to create a virtual rule that allows you to decide whether or not you want to delete a message after it has been read to avoid sensitive data to unintentionally being shared. When you have enabled the VTB rule AVZMDLMQ use the SQL statement.
SELECT * FROM MDLMQ_MQ_TRADE;
the prefix MDLMQ_ will be stripped off the table name (i.e. you will work with table MQ_TRADE) and, through the power of the DVM Event Facility Management, you can get more information back:
- When the variable vtb.optbmqtc = 1 in the virtual rule, truncated MQ messages will also be delivered to the requestor, instead of resulting in a failing request. This should only happen when there are very long messages that do not fit in the internal buffer of DVM. The advantage is that the user will get some indication about the number of messages that will satisfy the request, even when not all data fields of the message will be available for analysis; for example, when the header information on really large messages might give sufficient during retrieval.
- When the variable vtb.optbmqdg = 1 in the virtual rule, the GET operations will be destructive (i.e. the messages will be gone after GET). This means that an MQ message is immediately deleted after it is processed, which for some types of messages might be a business requirement.
- When the variable vtb.optbmqim = 1 in the virtual rule, many additional columns will be added to the end of the returned MQ message. These columns start with a prefix MQMD_ and represent meta data about the queue message, like put date/time, put application name etc. While the overhead of acquiring this additional meta data is negligible. Business users with audit responsibility or capacity management responsibility will care less about the actual message contents but might be more interested in the arrival frequency of messages (e.g. peak times of message arrival) or with the identification of heavy message generating applications etc.
A useful side effect of the item number three above is that you can use these new additional columns like MQMD_PUTAPPLNAME in your queries. For example:
SELECT * FROM MDLMQ_MQ_TRADE WHERE MQMD_PUTAPPLNAME NOT = 'CSCORN2';
This will show you all queue messages that were not inserted by the application CSCORN2, which could be an indication for MQ PUTs that were coming from an unexpected queue user (maybe a fraudulent user). Note that a command like
SELECT * FROM MQ_TRADE WHERE MQMD_PUTAPPLNAME NOT = 'CSCORN2';
will result in
Error executing statement.
Invalid column reference MQMD_PUTAPPLNAME in SQL
This result is to be expected because MQMD_PUTAPPLNAME is not a column of table MQ_TRADE, rather only of table MDLMQ_MQ_TRADE.
These three options do not fall in a “one size fits all” approach. The virtual rule AVZMDLMQ allows for flexibility in setting different values for these options, depending e.g. upon queue name or user credentials or time-of-day. It is perfectly imaginable that a destructive read is only allowed for a senior data scientist who might be clearing out messages at a prescribed time each day. It may not be advised for other user profiles, and never outside the prescribed time window.
For anyone that regularly interacts with WebSphere MQ, you now have a new tool in IBM Data Virtualization Manager. DVM brings the simplicity of SQL to Websphere MQ and provides advanced options to unlock the value of the information within MQ queues and messages to business and technical users.