MQ Functions on DB2 for LUW V8.2

[Philip Nelson]

Folks,

I'm wondering if anyone out there actually has a working implementation of
the MQ SQL functions, and if so if they can offer some advice to a poor
struggling MQ novice.

I've got MQ set up and working (I've used it to test DB2 II Q Rep).

I've installed the MQ AMI, as supplied in sqllib/cfg/mq. I've set the
recommended AMT path variable (AMT_DATA_PATH=/home/db2inst1/sqllib/cfg/mq),
and passed it through to DB2 using DB2ENVLIST (and remembered to start the
instance).

I already have a queue manager (lets call it MYQUEUE).

I've run -

enable_MQFunctions -n DBTST001 -u db2inst1 -p password -v 0pc -q MYQUEUE

This seemed to work.

When I call -

VALUES DB2MQ.MQSEND('Testing 123')

I get -

SQL0443N Routine "DB2MQ.MQSEND" (specific name "MQSND1") has returned an
error SQLSTATE with diagnostic text "rc=110:amSesOpen". SQLSTATE=38H01

Anyone tell me what I'm doing wrong ?

I suspect I need to set up some MQ queues manually, and then define them to
DB2 in the xml files in cfg/mq, but I don't know how.

Thanks

Phil

[amurchis]

SQL0443N means that the UDF itself has returned an SQLSTATE, in this
case 38H01. Most SQLSTATEs in the 38xxx range are user-defined; that
is, depending on the routine you're calling, the state can mean
something different, an d. Unfortunately, you'd need someone who is
more familiar with the MQ functions then I am (or, if you have them, the
MQ function definitions and try looking up that SQLSTATE) to figure out
what it means.

Off hand, the MQSEND function has set that SQLSTATE, and given it an
associated diagnostic test of "rc=110:amSesOpen". This is actually
coming from the function itself and is not part of the standard
DB2-Engine produced SQLSTATES or test. I'd hazard a guess that
"asSesOpen" has something to do with a failure opening an MQ session,
but I don't know what a return code of 110 means.

Someone in charge of the MQ functions (or, hopefully, the documentation)
would have to tell you what those mean. I honestly don't know -- I'm
just trying to provide a bit of an overview of what's happening from a
processing point of view. Although I understand that those functions
are shipped with DB2, they are not part of the core engine or processing
of DB2 itself, which I believe most IBMers who follow and respond to
posts on this forum are part of, and as such they are not as likely to
have a direct idea themselves about why this error is being returned.

I think I've tracked down someone who might know, though, and forwarded
them the text of your message. If they reply to me, I'll post it here.

Philip Nelson wrote:

Folks,

I'm wondering if anyone out there actually has a working implementation of
the MQ SQL functions, and if so if they can offer some advice to a poor
struggling MQ novice.

I've got MQ set up and working (I've used it to test DB2 II Q Rep).

I've installed the MQ AMI, as supplied in sqllib/cfg/mq. I've set the
recommended AMT path variable (AMT_DATA_PATH=/home/db2inst1/sqllib/cfg/mq),
and passed it through to DB2 using DB2ENVLIST (and remembered to start the
instance).

I already have a queue manager (lets call it MYQUEUE).

I've run -

enable_MQFunctions -n DBTST001 -u db2inst1 -p password -v 0pc -q MYQUEUE

This seemed to work.

When I call -

VALUES DB2MQ.MQSEND('Testing 123')

I get -

SQL0443N Routine "DB2MQ.MQSEND" (specific name "MQSND1") has returned an
error SQLSTATE with diagnostic text "rc=110:amSesOpen". SQLSTATE=38H01

Anyone tell me what I'm doing wrong ?

I suspect I need to set up some MQ queues manually, and then define them to
DB2 in the xml files in cfg/mq, but I don't know how.

Thanks

Phil

[amurchis]

I managed to get a reply rather quickly. His reply:
========================================
The most likely cause of 110 is that the queue manager was not started.
This could be due to the queue manager the customer thought was using
is not actually the one that's being used, by default parameters, in
this case.
Unless they intended to use 1pc MQ functions, they shouldn't not use '-q
MYQUEUE' option in enable_MQFunctions. It's for 1PC mq functions only.
That could cause the confusion or misleading queue manager.

Hope this helps.
========================================

As do I... I'm going to be honest and say I don't understand about 99%
of the MQ stuff, so hopefully that makes some sense.
Philip Nelson wrote:

Folks,

I'm wondering if anyone out there actually has a working implementation of
the MQ SQL functions, and if so if they can offer some advice to a poor
struggling MQ novice.

I've got MQ set up and working (I've used it to test DB2 II Q Rep).

I've installed the MQ AMI, as supplied in sqllib/cfg/mq. I've set the
recommended AMT path variable (AMT_DATA_PATH=/home/db2inst1/sqllib/cfg/mq),
and passed it through to DB2 using DB2ENVLIST (and remembered to start the
instance).

I already have a queue manager (lets call it MYQUEUE).

I've run -

enable_MQFunctions -n DBTST001 -u db2inst1 -p password -v 0pc -q MYQUEUE

This seemed to work.

When I call -

VALUES DB2MQ.MQSEND('Testing 123')

I get -

SQL0443N Routine "DB2MQ.MQSEND" (specific name "MQSND1") has returned an
error SQLSTATE with diagnostic text "rc=110:amSesOpen". SQLSTATE=38H01

Anyone tell me what I'm doing wrong ?

I suspect I need to set up some MQ queues manually, and then define them to
DB2 in the xml files in cfg/mq, but I don't know how.

Thanks

Phil