Informatica
PowerExchange for MQ Series
HOW-TO | CIF CONSULT | Redouane BELBAHRI
Agenda
 Messaging Fundamentals
 Message Recovery
 Testing MQ
Agenda
1
Messaging Fundamentals
Messaging Fundamentals

WebSphere MQSeries enables application programs to
communicate with each other using messages and queues.

This form of communication is referred to as commercial
messaging.

There are two methods for applications to communicate:
– Fire and Forget
– Request/Response
Currently PowerExchange for Websphere MQ follows send-and-forget model, also known as ‘Datagram’, where
once the message is put in the target queue, no reply is sent to the source queue. ReplytoQ and ReplyToQmgr
ports are populated, but PowerCenter does not use these to send a reply to follow request-and-response
model.
Pre-requisites for working with MQ

Install the WebSphere MQ client
– Before you upgrade or install PowerExchange for WebSphere MQ, install
the WebSphere MQ client or WebSphere MQ server on the machine that
runs the PowerCenter Integration Service.

Configure Channels for Queue Managers
– If the WebSphere MQ system does not have a client-connection channel,
configure a channel for each queue manager you want to connect to.
WebSphere MQ does not allow you to connect to multiple queue managers
in a session.

The Queue should be on the Distributed environment not on
Mainframe
Application Communications
Fire and Forget
Put Record
Get Record
Program
A
Program
B
Record Queue
Request/Response
Put Record
Program
A
Get Record
Processed
Message
Get Record
Program
B
Record Queue
Reply to
Queue
Put Record
Processed
Message
What is a message?
Message = Header(s) + Application Data
Header
…
Application Data
A message consists of a header and the attached application data.
Headers typically contain elements like:
• Unique Message Id
• Routing information
• Message format
The following are examples of the data part of a message:
• A record from an indexed or flat file
• A row from a DB2 table
• Individual columns from DB2 tables
• Multiple rows or records
What is a Queue?
A queue is simply a place to put data.
This figure shows how
messaging works in the
simple case where the
program putting the
message and program
getting the message are
both on the same computer
and connected to the same
queue manager.
What is Queue Manager?

A queue manager is the subsystem software which controls access
to the individual queues assigned to it. The queue manager logs all
activity with each individual queue thus creating an audit trail.
Multiple queue managers can coexist with each other. The limiting
factor is the availability of system resources.
APPLICATIONS ANYWHERE
LOG/AUDIT
TRAIL
PUT
DATA
GET
DATA
QUEUES
QUEUE
MANAGER
COMMUNICATIONS
ACF2/SECURITY
TCP/IP
Publish/Subscribe
Subscriber
Publisher
Publish Data
Publish Data
Subscriber
Subscriber
Publisher
Publisher
Pub/Sub
Pub/Sub
Broker
Broker
(List Server)
Subscribe to
Published Data
WebSphere MQSeries
Message/Queuing
Subscriber
WebSphere MQ Sources

PowerCenter treats the message data component of a message
as a field containing message data. The PowerCenter
Integration Service can read message data in the following
formats:
– Flat file (fixed-width or delimited)
– XML
– COBOL
– Binary
WebSphere MQ Targets

The PowerCenter Integration Service can write data to the
message header and the message data fields of a WebSphere
MQ message. It can write message data to a WebSphere MQ
message in the following formats:
– Flat file (fixed-width or delimited)
– XML
– COBOL
– Binary
What are the differences between the server
and client connectivity to MQ

If MQ Server is on a different machine than PowerCenter server,
then MQ client should be installed on the machine hosting
PowerCenter.

If MQ server is on the same machine as PowerCenter, MQ client
is optional.
Can you send a reply to the MQ sender
using PowerCenter?

Currently PowerExchange for Websphere MQ follows send-andforget model, also known as ‘Datagram’, where once the
message is put in the target queue, no reply is sent to the
source queue. ReplytoQ and ReplyToQmgr ports are populated,
but PowerCenter does not use these to send a reply to follow
request-and-response model.

If the requirement is to send a reply back to the sender then
this has to be achieved on the MQ server with the data
populated in the ReplytoQ and ReplyToQmgr ports.
High Volume System Considerations

Persistent: The Queue Manager first writes the message out to
a log before it allows it to be visible in the queue. In a very high
volume flow, if this is not handled correctly, it can lead to
performance degradation and cause the logging to potentially
fill up the file system.

Non-persistent messages: are immediately visible in the queue
for processing, but unlike persistent messages, if the Queue
Manager or server crashes they cannot be recovered. To handle
this type of flow volume. PowerCenter workflows can be
configured to run in a Grid environment.
MQ Message Format

When reading an MQ Series message insure that the posted
message is of format: “MQSTR”

JMS client can send a message in MQHRF2 format which is not
readable by PowerCenter.

Note: to change in JMS
– Destination destination
=jmsTemplate102.getDestinationResolver().resolveDestinationName(sessio
n, queueName, isPubSubDomain);
– ((MQQueue)destination).setTargetClient(JMSC.MQJMS_CLIENT_NONJMS_
MQ);
Does PowerExchange for MQSeries support messages with
MQ RFH and RFH2 header formats?

PowerExchange for MQSeries does not support MQ RFH and
RFH2 header formats.

A feature request (CR 188295) has been submitted to address
this in a future release of PowerCenter.
What are "Rows per Message" and "Message Format"
options in a static MQ target definition?

When you configure a flat file or XML target with a static MQ
queue connection the following options are available:
– Rows per message
– Message Format

Rows per message
– the Rows per message' indicates the number of rows that would constitute
a single message.

Message Format
– The Message Format is the PowerCenter server to load data in the specific
MQ format (such as MQSTR).
Associated Source Qualifier

You create an associated source qualifier in a mapping
depending on the WebSphere MQ message data format.

You can create the following types of associated source qualifier
transformations for WebSphere MQ message data:
– Source Qualifier transformation for flat file message data
– Normalizer transformation for COBOL message data
Static WebSphere MQ Target Definitions

Use a static WebSphere MQ target definition in a mapping
when you only want to write message data to the WebSphere
MQ target.

When you use a static WebSphere MQ target definition, you can
pass data in flat file format to the WebSphere MQ message data
field. The message header fields in the target remain static. No
data passes to the message header fields from the pipeline.

When you use a static WebSphere MQ target, use a flat file
target definition for the message data and configure the session
to write messages to a message queue. The PowerCenter
Integration Service does not maintain transactional consistency.
Agenda
2
2
Message Recovery
Does PowerExchange for MQSeries allow
graph recovery and roll forward?

PowerCenter supports recovery and roll back for MQSeries
mappings.

Guaranteed Message Delivery (GMD) which can be enabled for
mappings that are configured with destructive read mode.
WebSphere MQ Series Process

PowerCenter Integration Service can recover unprocessed messages from a
failed session. When you enable message recovery for a real-time session,
the PowerCenter Integration Service stores source messages or message IDs
in a recovery file, recovery table, or recovery queue.
Extraction
Application 1
Load
Application 2
MSQ
MSQ
Transformation
Load
Load
Flat Files
Load
MSQ
Recovery Queue
How is real-time session recovery information
handled by PowerCenter?

PowerCenter 8.1.x (or below) uses file-based recovery.
The Integration Service writes real-time session recovery
information to a local cache. When you enable the recovery
option, the Integration Service stores all the messages it reads
from the messaging source in a local cache before processing
the messages for the target.
In recovery run, messages in cache are processed first and then
normal processing resumes from the real-time source. At times
messages are re-processed from recovery cache, resulting in
duplicates in the target.

Effective in PowerCenter 8.5.1, the Integration Service writes
real-time session recovery information to a database table. The
table helps maintain data integrity during recovery so no data is
lost or duplicated. Messages are processed and committed to
target first and then acknowledged back to the source system.
Rules and Guidelines for Message
Recovery

The Integration Service fails sessions that have message recovery enabled and
contain any of the following conditions:
– The source definition is the master source for a Joiner transformation.
– You configure multiple source definitions to run concurrently for the same target
load order group.
– The mapping contains an XML target definition.
– You edit the recovery file or the mapping before you restart the session and you run
a session with a recovery
– strategy of Restart or Resume.
– The Integration Service cannot connect to the recovery queue or recovery topic.
– The Integration Service does not write the recovery message to the recovery queue
or recovery topic.

If the number of messages that the Integration Service reads or writes from the
message queue exceeds the message size limit, increase the message size limit or
decrease the flush latency.
Cleanup Using Session Recovery

If your mapping contains an MQSeries source, you can enable
Destructive Read and session recovery in the session properties
to ensure queue cleanup.

During the session, the PowerCenter Server removes messages
from the source queue after writing messages to the recovery
cache folder.

TheDestructive Read option does not delete rows that do not
meet the Source Filter condition
Recovery cache file names

Recovery cache file names use the following format:
– pmgmd_[sourcestate][sessionstate][targetstate]_<repository
GUID>_<workflow ID>_<folder ID>_<session ID>_<transformation
ID>_<partition ID>_<group ID>_<checkpoint ID>_<sequence number>.dat

Example
– pmgmd_sourcestate_50fc0313_9762_4670_98e9_7e2e5b7a93b8_268_5_1
91_61_0_0_1_1.dat
– In this example:

50fc0313_9762_4670_98e9_7e2e5b7a93b8 is the Repository GUID

268 is the workflow ID

5 is the folder ID

191 is the session ID

61 is the transformation ID
Recovery cache file names

To determine the name of the workflow use the workflow ID in the following
query:
– SELECT WORKFLOW_NAME FROM REP_WORKFLOWS WHERE
WORKFLOW_ID=268 AND SUBJECT_ID=5

To determine the name of the session and folder use the session ID in the
following query:
– SELECT SESSION_NAME, SUBJECT_AREA FROM REP_LOAD_SESSIONS
WHERE SESSION_ID=191 AND SUBJECT_ID=5
Recovery Types

Internally, recovery can be divided into two types: incremental recovery and
full load recovery.

In incremental load recovery, the Integration Service uses the ROW_COUNT
information in the PM_RECOVERY table to determine the number of rows
committed to the target. When the session is recovered, previously loaded
rows will be skipped. Thus, incremental recovery ensures that there are no
duplicates loaded to the target. The Integration Service performs
incremental recovery for relational, normal load targets.

In full load recovery, data is loaded from the beginning during the recovery
run. The Integration Service performs full load recovery for relational bulk
load, flat file (including regular, MQSeries, and external loader), XML, and
SAP BW targets. Recovery for non-relational targets is done using the
pmgmd file created at the $PMCacheDir cache directory.
Recovery and Truncation

During the recovery session run, the Integration Service ignores the Truncate
Target Table option for relational normal load targets, since the load will
continue from the last commit point. For relational bulk load targets the
Integration Service allows the Truncate Target Table option in both normal
and recovery run. Please see Table 1 for truncation and data load behavior in
case of bulk and normal relational targets.

At the end of a successful session run the Integration Service deletes all
recovery information in the target database and in $PMCacheDir.
The Integration Service always resets the recovery information when it runs
the session normally, regardless of whether the previous session run
succeeded or failed.
Recovery Scenarios (1/2)

Recovery can be performed under the following scenarios:
– The workflow suspends. If the workflow suspends due to session failure,
you can recover the failed session and resume the workflow.
– The workflow fails. If the workflow fails as a result of session failure, you
can recover the session and run the rest of the workflow.
– The workflow completes, but a session fails. If the workflow completes, but
a session fails, you can recover the session alone without running the rest
of the workflow.

In order to run a recovery session, the following options are available in the
Workflow Manager:
– Recover Task. Use this option when you want to recover a single session.
– Recover Workflow From Task. Use this option when you want to recover the
session and continue the workflow.
Recovery Scenarios (2/2)

The following options are available in the Workflow Monitor:
– Resume/Recover. Use this option to resume a suspended workflow in
recovery mode.
– Recover Workflow From Task. Use this option when you want to recover the
session and continue the workflow.
– Recover Task. Use this option when you want to recover a single session.
Unrecoverable Sessions

PowerCenter does not support session recovery in the following cases:
– The session uses non-pass-through partitioning. All partition points must
be pass-through partitioning unless the partition point passes data to an
Aggregator or Rank transformation. If Enable Recovery is enabled in the
session properties, you can only select the default partition types for each
partition point.
– The session uses database partitioning. PowerCenter does not support
recovery for a session that uses database partitioning.
– You want to debug the mapping. You cannot perform recovery from the
Debugger.
– You want to perform a test load. You cannot perform a test load if the
session is enabled for recovery. If you enable recovery, the Workflow
Manager disables the Test Load option.
What are the failure scenarios with JMS or MQ
source with session recovery?

The session reads the message from the, writes to the targets. The session
then updates the recovery queue with the message ID of the just written
message and issues a commit to both the message written and the recovery
queue update. Once the recovery queue is updated and target committed,
the message is removed from the source JMS or MQ queue.

Failure scenarios during the above process:
Failure scenarios during the above
process

The target goes down before commit:
– When the session restarts, it does not find the message ID committed in
the target recovery queue. So the message is re-read from the queue and
process goes on as usual.

The target goes down after issue of commit:
– The session would have removed the message from the source as commit is
done. Upon restart, the session would find the committed message in the
target and process continues reading next available messages.

The source goes down after read and before target commit:
– Upon restart, the session would re-read the message afresh as the message
is not present in the target recovery queue.
Failure scenarios during the above
process

The source goes down after read, after commit to target and before delete of
source message:
– The session would have failed after commit but before delete message from
the source. Upon restart, the session would read the target recovery queue,
find the message still exists in the source but already committed to target.
The session would just delete the message from the source.

The source or target JMS is already down before the session starts:
– The session connects to source and target upon init. Recovery queue is out
of the picture as session would fail during init of connection itself.
Controlling Queue Clean-up with
Destructive Read

If you select Destructive Read, and the mapping contains an associated
source qualifier, the PowerCenter Integration Service ignores the Destructive
Read option. To remove messages from the source queue for mappings that
contain an associated source qualifier, use RemoveMsg(TRUE).

Use RemoveMsg(TRUE) when a mapping contains an associated source
qualifier.

RemoveMsg(TRUE) removes messages from the queue after committing all
data to the target.

For example, enter the following filter condition:
– RemoveMsg(TRUE)
Agenda
3
Testing MQ
Test Using SOAPUI
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:x-axabe:retail:pc:mqpoc:MQService" xmlns:urn1="urn:x-axa-be:retail:pc:mqpoc:service">
<soapenv:Header/>
<soapenv:Body>
<urn:sendXmlRequestWrapper>
<sendXmlInputMessage>
<urn1:xml><![CDATA[<start>hello</start>]]></urn1:xml>
</sendXmlInputMessage>
</urn:sendXmlRequestWrapper>
</soapenv:Body>
</soapenv:Envelope>

Use this template: <![CDATA[ xml file in string format]]>
Testing using RFHUTIL

In DEV
– http://www-01.ibm.com/support/docview.wss?uid=swg24000637

RFHUTIL allow you to put msg in a professional manner but it does also
some msg conversion and high level message browsing

Excellent tool for any EAI/Middleware developer on Top of MQ
Test connection between MQ client
and MQ server outside Informatica

Use the amqsputc sample program to put a message on the queue at the
server workstation.
– Go to the /usr/mqm/samp/bin directory, which contains these IBM sample
programs.
– Put a message on the queue at the server using the ./amqsputc myQueue
myQManagerName command.
- The Sample amqsput0 start target queue is myQueue message is displayed.
– Type some message text on one or more lines, followed by a blank line.
- The Sample amqsput0 end message is displayed.
- Your message is now on the queue and the command prompt is displayed again.

Use the amqsgetc sample program to get the message from the queue back
to the client
– To get the message from the queue located on the server, enter the /amqsgetc
myQueue myQManagerName command.
– The sample program starts and your message is displayed. After a pause, the sample
ends and the command prompt is displayed again.
Overriding Connection Attributes in the
Parameter File

If you use a session parameter to define a connection for a source or target,
you can override the connection attributes in the parameter file. Use the
$QueueConnectionName session parameter.

When you define a connection in the parameter file, the Integration Service
searches for specific, user-defined session parameters that define the
connection attributes.
– For example, you create a Message Queue connection parameter called
$QueueConnectionMyMQ and define it in the “[s_MySession]” section in the
parameter file. The Integration Service searches this section of the parameter file
for the “rows per message” parameter,
$Param_QueueConnectionMyMQ_Rows_Per_Message.
Is it possible to use pmrep createConnection to
create QUEUE connections in the repository?

No, currently it is not supported to create QUEUE connections using the
pmrep createConnection command. The QUEUE connections will have to be
created from the Workflow Manager tool.
Many Thanks for you time today!