Chapter 4: SQL Reference

Message-related global variables

These global variables provide application programs with access to message information from the most recent message sent or received. They are discussed in Chapter 4, “SQL Reference.”

@@msgcorrelation

Contains correlation from last message sent or read.

MQSeries – MQ does not verify whether @@msgcorrelation consists of printable characters. Application programs should not rely on @@msgcorrelation being in the current server character set, and should only use @@msgcorrelation as a selector for subsequent messages. If @@msgcorrelation is to be returned to the application, convert it to a varbinary datatype.
TIBCO JMS – @@msgcorrelation contains the correlationId from the the most recent message sent or received.

@@msgheader

Contains message header information from the most recent message received. This variable’s format is in XML. For details about this format, see “<msgheader> and <msgproperties> documents”.

Functions that set @@msgheader include msgrecv and msgconsume.

For fields and descriptions about @@msgheader, see Table 4-1 for MQSeries and Table 4-2 for JMS.

**Table 4-1: MQSeries @@msgheader fields and descriptions**
Property name	Description
ApplIdentityData	Application data relating to identity.
ApplOriginData	Application data relating to origin.
CodedCharSetId	Numeric coded character set identifier.
CorrelId	Correlation identifier.
Encoding	Encoding of binary data in the message. Bit mask of flags in the Encoding field.
DecimalEncoding	This is the encoding for decimal numbers in the message payload, and is a synthesized property derived from the Encoding field. If: BigEndian – decimal numbers are big-endian LittleEndian – decimal numbers are little-endian Undefined – decimal numbers are not defined as either big-endian or little-endian
Feedback	Feedback status
FloatEncoding	This is the encoding for floating point numbers in the payload, and is a synthesized property derived from the Encoding field. If: BigEndian – floating point numbers are big-endian LittleEndian – floating point numbers are little-endian Undefined – floating point numbers are not defined as either big-endian or little-endian
Format	Format name of message data, this can be an MQ-defined format name or an application-defined format name.
GroupId	Group identifier
IntegerEncoding	This is the encoding for integers in the payload, and is a synthesized property that is derived from the Encoding field. If: BigEndian – integers are big endian LittleEndian – integers are little endian Undefined – the endianess of integers is undefined
LastMsgInGroup	If: true – message is the last message of a group false – message is not the last message of a group
MsgId	Message identifier
MsgInGroup	If: true – message is part of a group false – message is not part of a group
MsgSeqNumber	Message sequence number
MessageType	Message type in the form of a decimal number, unless: request – the message is a request message reply – the message is a reply message datagram – the message is a datagram message report – the message is a report message
NegativeActionNotification	This is a synthesized property, derived from the Report field. The receiving application should generate a negative-action notification (NAN) report. yes – receiving application should generate a NAN report message, and send it to the destinations specified in the ReplyToQ and ReplyToQMgr fields. no – receiving application should not generate a NAN report message.
Persistence	The persistence of the message. If: persistent – the message is a persistent message non-persistent – the message is a non-persistent message
PositiveActionNotification	This is a synthesized property derived from the Report field. The receiving application should generate a Positive Action Notification (PAN) report. If: yes – receiving application should generate a PAN report message, and send it to the destinations specified in the ReplyToQ and ReplyToQMgr fields. no – receiving application should not generate a PAN report message.
PutApplName	Name of application that put the message
PutApplType	Type of application that put the message
PutDate	Date when message was put
PutTime	Time when message was put
ReplyCorrelationId	A synthesized property, derived from the Report field. Denotes what to use as the correlation ID of the report message. msgId – the correlation ID of the report message should be set to the message ID of the received message. correlationId – the correlation ID of the report message should be set to the correlation ID of the received message.
ReplyMsgId	A synthesized property, derived from the Report field. Denotes what to use as the message ID of the report message. new – a new message ID should be used as the message ID of the report message. original – the message ID of the message received should be used as the message ID of the report message.
ReplyToQ	Name of reply queue
ReplyToQMgr	Name of the reply queue manager
Report	Report options from the message This is a bitmap of MQRO * flags.
UserIdentifier	User identifier

**Table 4-2: JMS @@msgheader fields and descriptions**
Property name	Description
correlation	Correlation ID from the message
destination	The name of the destination from the message
encoding	The encoding name of the message
messageid	The message ID from the message
mode	Delivery mode of the message. Values: persistent nonpersistent
priority	The message priority
redelivered	The redelivery status from the message
replyto	The replyto name from the message
timestamp	The message timestamp
ttl	The expiry from the message that indicates how long a message exists
type	The message type

@@msgid

Contains the ID of the most recent message sent or received.

MQ Series – MQ does not verify that the @@msgid consists of printable characters. Application programs should not rely on @@msgid being in the current server character set, and should only use @@msgid as a selector for subsequent messages. If @@msgid is returning to the application, it should be converted to a varbinary datatype.

Functions that set the variable are:

JMS – msgsend, msgpublish, msgrecv, msgconsume.
MQ Series – msgsend, msgrecv.

@@msgproperties

Contains message properties information from the most recent message received. This variable’s format is in XML. For details about this format, see “<msgheader> and <msgproperties> documents”.

JMS – the @@msgproperties are the user properties from the message.
MQ Series – if:
- The message contains one or more MQRF headers, the name-value pairs in the MQRF headers and inserted into @@msgproperties.
- Since the name-value pairs in the MQRF header can have non-unique names, the names are made unique by appending a “_ddd”, where ddd is an integer extension for uniqueness.
  
  For instance, a MQRF header with these topics:
```
MQPSTopic   */baseball
MQPSTopic   */baseball/world series
MQPSTopic   */sports
```
  Results in these properties in @@msgproperties:
```
MQPSTopic     */baseball
MQPSTopic_1   */baseball/world series
MPQSTopic_2   */sports
```

Functions that set the variable are:

JMS – msgrecv, msgconsume
MQ Series – msgrecv

The list below lists RFH name-value pairs that are extracted from the RF header if they are present.

MQPSCommand	MQPSPubOpts	MQPSStreamName
MQPSCompCode	MQPSPubTime	MQPSStringData
MQPSCorrelId	MQPSQMgrName	MQPSSubIdentity
MQPSDelOpts	MQPSQName	MQPSSubName
MQPSErrorId	MQPSReason	MQPSSubUserData
MQPSErrorPos	MQPSReasonText	MQPSSubUserData
MQPSIntData	MQPSRegOpts	MQPSTopic
MQPSParmId	MQPSSeqNum	MQPSUserId

Unrecognized names are ignored. If the value is quoted (“) in the RF header, the surrounding quotes are removed. In a quoted value, if there are escaped quotes (“”) within the value, doubled quotes are replaced by a single quote.

@@msgreplyqmgr

MQSeries only – contains the ReplyToQmgr name of the last message read.

@@msgreplytoinfo

Contains the name (provider_url, queue_name, topic_name, user_name) of the topic or queue name used to receive the next message. Can be a permanent or temporary destination.

Functions that set the variable are:

JMS – msgsend, msgpublish.
MQSeries – msgsend.

@@msgschema

TIBCO JMS only – contains the schema of the message or a null value. Contains the value of the Adaptive Server property ase_message_body_schema. For more information, see the description of the schema option in msgsend and msgpublish.

Functions that set the variable are: msgsend, msgpublish.

@@msgstatus

Contains either the integer error code of the service provider exception, or zero, if the last operation did not raise an exception.

Functions that set the variable are: msgsend, msgpublish, msgrecv, msgconsume.

@@msgstatusinfo

Contains either the error message of the service provider exception, or zero, if the last msgsend, msgpublish, msgrecv, or msgconsume raised an exception, or an empty string.

MQ Series – contains provider error message of last messaging operation. The MQ client libraries do not provide localized error messages, so you see an error message such as:

MQ API call failed with reason code '%s' (%d)

The “%s” is substituted with the MQ mnemonic for the MQ reason code.

The “%d” is substituted with the decimal MQ reason code.

Functions that set the variable are:

JMS – msgsend, msgpublish, msgrecv, msgconsume.
MQ Series – msgsend, msgrecv.

@@msgtimestamp

Contains the timestamp included in the message last sent.

Functions that set the variable are: msgsend, msgpublish.

Examples

Example 1

MQSeries only – shows request/reply messaging using both @@msgreplytoinfo and @@msgcorrelation:

Session 1 (requester)	Session 2 (receiver)
`select msgsend('sender msessage', 'ibm_mq:channel1/TCP/host1(5678)' + '?qmgr=QM1' + ',queue=Q100', option 'msgType=request', message property 'correlationId=0x123456' + 'replyToQueue=Q200')`
	`select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)' + '?qmgr=QM1' + ',queue=Q100') select msgsend('receiver reply', @@msgreplytoinfo, option 'msgType=reply' message property 'correlationId=' + @@msgcorrelation)`
`select msgrecv( 'ibm_mq:channel1/TCP/host1(5678)' + '?qmgr=QM1' + ',queue=Q200' option 'timeout=30ss', + 'correlationID=0x123456')`

In this example:

Session 1 sends the request message to Q100, and expects the reply messsage on Q200. It sets the correlation to 0x123456.
Session 2 reads a message from Q100, sends a reply message to Q200, and specifies the correlation to 0x123456. The reply queue is obtained from the message that was just read.
Session 1 reads the reply message from Q200, wanting only message with correlation 0x123456.

Usage

These global variables are char datatypes, of length 16384.
You can remove trailing blanks using rtrim.
@@msgreplytoinfo contains reply destination information from the message header. It is formatted as an endpoint, as described in msgsend:

JMS only – The password is not included in the value of @@msgreplytoinfo. To use this destination as an argument in a subsequent msgsend or msgrecv call, add:
```
password=<your password>
```

<msgheader> and <msgproperties> documents

View this book as PDF