Configuring virtual table rules for IBM MQ

Edit online

Configure Data Virtualization Manager Server Event Facility (SEF) rules to support IBM MQ data.

About this task

You can configure VTB rule options to control the MQ data access feature. These options control inclusion of the MQ message descriptor meta data fields in the virtual tables, how to handle truncated messages, and whether to perform destructive reads. Sample VTB rule AVZMDLMQ documents these settings.
When accessing MQ data with sample rule AVZMDLMQ (or equivalent options) enabled, tables prefixed with MDLMQ_* are filtered, and the map name is extracted by removing the MDLMQ_ prefix. For example, the following query will execute the rule and query virtual table MQ_CSQ7_TRADE:
SELECT * FROM MDLMQ_MQ_CSQ7_TRADE

Use the following procedure to configure the sample rule AVZMDLMQ.

Note: Sample rule AVZMDLMQ is intended to be used as a model and may require customization. When customizing this rule, additional logic may need to be added if different VTB variable settings are required for different MQ queues.

Procedure

  1. Customize the Data Virtualization Manager configuration member (AVZSIN00) to enable virtual table rule events by configuring the SEFVTBEVENTS parameter in the member, as follows: 
    "MODIFY PARM NAME(SEFVTBEVENTS) VALUE(YES)"
  2. Access the VTB rules, as follows:
    1. In the Data Virtualization Manager server - Primary Option Menu, specify option E, Rules Mgmt.
    2. Specify option 2, SEF Rule Management.
    3. Enter VTB for Display Only the Ruleset Named.
  3. Customize the AVZMDLMQ rule, as follows:
    1. Specify S next to AVZMDLMQ to edit the rule.
    2. Update the rule options as needed. The following table describes the VTB rule options that support MQ data access.
      VTB variable Description Valid values
      vtb.optbmqdg Delete messages during retrieval. When set to 1, SQL queries will remove messages from the queue if ALL messages in the queue are successfully retrieved by the server.

      Retrieval of MQ messages will use non-browse (destructive) MQGET calls with syncpoint control. Once all messages are delivered to the server, they will be deleted from the queue. If a failure occurs before all messages are retrieved, an MQBACK call will be issued to restore messages to the queue that have been retrieved so far. Note that an MQCMIT will be issued and messages deleted if the IBM MQ syncpoint limit is reached. A failure after MQCMIT will not be able to restore messages as they have been permanently deleted.

      0 (Default)
      1
      vtb.optbmqim When set to 1 for an MQ virtual table, the MQ Series Message Descriptor (MQMD) meta data fields will be added to the virtual table as columns and returned with each result row. These columns are prefixed with the value MQMD_.

      0 (Default)
      1
      vtb.optbmqtc By default, a truncation error reading an IBM MQ message will result in a query failure. When set to 1, MQ Series access ignores truncated message warnings and returns data received.

      0 (Default)
      1
    3. Save your changes and exit the editor.
  4. Enable the rule by specifying E next to AVZMDLMQ and pressing Enter.
  5. Set the rule to Auto-enable by specifying A next to AVZMDLMQ and pressing Enter.
    Setting a rule to Auto-enable activates the rule automatically when the server is re-started.