| Author |
Message
|
| nheng |
 Posted: Wed Sep 30, 2009 9:04 pm Post subject: why AMQ 1/4 instead MsgId ? |
 |
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
CREATE PROCEDURE InsertSOALOG(IN MSGIDVal CHARACTER ,
IN SVRCODEVal CHARACTER ,
IN UIDVal CHARACTER,
IN PRODUCTCODEVal CHARACTER ,
IN DIRECTIONVal CHARACTER
)
BEGIN
DECLARE strESQL CHARACTER '';
DECLARE strVal CHARACTER '';
DECLARE strFld CHARACTER '';
SET strFld = strFld || ' UID ';
SET strVal = strVal || '''' || ValidateMand(UIDVal) || '''';
SET strFld = AddCommaAtLast(strFld);
SET strFld = strFld || ' SVRCODE ';
SET strVal = AddCommaAtLast(strVal);
SET strVal = strVal || '''' || ValidateMand(SVRCODEVal) || '''';
SET strFld = AddCommaAtLast(strFld);
SET strFld = strFld || ' PRODUCTCODE ';
SET strVal = AddCommaAtLast(strVal);
SET strVal = strVal || '''' || ValidateMand(PRODUCTCODEVal) || '''';
SET strFld = AddCommaAtLast(strFld);
SET strFld = strFld || ' DIRECTION ';
SET strVal = AddCommaAtLast(strVal);
SET strVal = strVal || '''' || ValidateMand(DIRECTIONVal) || '''';
SET strFld = AddCommaAtLast(strFld);
SET strFld = strFld || ' RQMSGID ';
SET strVal = AddCommaAtLast(strVal);
SET strVal = strVal || ValidateMand(MSGIDVal) ;
SET strESQL = strESQL || 'INSERT INTO Database.EAIMBDB.DB2INST1.SOATXNLOG( ';
SET strESQL = strESQL || strFld;
SET strESQL = strESQL || ' ) VALUES( ';
SET strESQL = strESQL || strVal;
SET strESQL = strESQL || ' );';
EVAL(strESQL);
--PASSTHRU strESQL TO Database.EAIMBDB ;
END;
Follow code as upper.this procedure be called as below.
DECLARE MsgIdval CHARACTER InputRoot.MQMD.MsgId;
CALL InsertSOALOG(MsgIdval ,..........);
QM name:EAIMBP.QM
First question.
Msgid field in database has value "AMQ EAIMBP.QM 1/4..." .
It should be X'21657498795249879849....'.Why do it not work?
Second question.
My code should be PASSTHRU replace EVAL or not ?
Last edited by nheng on Wed Sep 30, 2009 11:03 pm; edited 1 time in total |
|
| Back to top |
|
 |
| rekarm01 |
| Posted: Wed Sep 30, 2009 9:57 pm Post subject: Re: why AMQ 1/4 instead MsgId ? |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
Please put code between [code]...[/code] tags, to preserve the indenting; it's easier to read that way.
| nheng wrote: |
First question.
Msgid field in database has value "AMQ EAIMBP.QM 1/4..." .
It should be X'21657498795249879849....'.Why do it not work? |
What does ValidateMand() do?
| nheng wrote: |
Second question.
My code should be PASSTHRU replace EVAL or not? |
Yes. |
|
| Back to top |
|
|
| nheng |
| Posted: Wed Sep 30, 2009 10:13 pm Post subject: as |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
| ValidateMand() = If it is blank or null or '' it wil throw exception.if it has value will return original value. |
|
| Back to top |
|
|
| nheng |
| Posted: Thu Oct 01, 2009 12:59 am Post subject: as |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
| Nobody can't solve this.. |
|
| Back to top |
|
|
| Luke |
| Posted: Thu Oct 01, 2009 1:07 am Post subject: Re: as |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
| nheng wrote: |
First question.
Msgid field in database has value "AMQ EAIMBP.QM 1/4..." .
It should be X'21657498795249879849....'.Why do it not work? |
Looks like somewhere you are CASTing the MsgId to CHAR and specifying a CCSID ... I'd guess you want to CAST without CCSID to get a character representation of the field .... this has been discussed several times before on this forum. |
|
| Back to top |
|
|
| nheng |
| Posted: Thu Oct 01, 2009 1:16 am Post subject: Re: as |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
| Luke wrote: |
| nheng wrote: |
First question.
Msgid field in database has value "AMQ EAIMBP.QM 1/4..." .
It should be X'21657498795249879849....'.Why do it not work? |
Looks like somewhere you are CASTing the MsgId to CHAR and specifying a CCSID ... I'd guess you want to CAST without CCSID to get a character representation of the field .... this has been discussed several times before on this forum. |
Thank for reply.
DECLARE MSGVal CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER CCSID 1208);
or
DECLARE MSGVal CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER CSSID InputRoot.MQMD.CodedCharSetId);
or
DECLARE MSGVal CHARACTER 'XXXXXX';
All not work.I alway find everything in this forum before post somthing.
CREATE COMPUTE MODULE Insert_Compute
BEGIN
DECLARE MSGVal CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER CCSID 1208);
CALL InsertSOALOG(IN MSGIDVal CHARACTER , ....);
)
END MODULE;
CREATE PROCEDURE InsertSOALOG(IN MSGIDVal CHARACTER , ......)
BEGIN
END; |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 01, 2009 1:44 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Quote: |
First question.
Msgid field in database has value "AMQ EAIMBP.QM 1/4..." .
It should be X'21657498795249879849....'.Why do it not work?
|
The MsgId field in the MQMD is a BINARY field. The 'AMQ EAIMBP...' you are seeing is a TEXT representation of that field. It looks like the MQMD.MsgID field was left blank when the original message was sent and WMQ did its normal thing and fills one in for you.
If you are expecting a specific HEX string then it looks like the sender has not setup the message and the various options before the message was sent.
You can mess around/experiment/test with the various fields in the MQMD using the rfhutil application and send the message into your broker with the changes.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Luke |
| Posted: Thu Oct 01, 2009 1:55 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
If you want a caharacter representation that looks like this:
X'21657498795249879849....'
Then code:
| Code: |
| DECLARE MSGVal CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER); |
If you specify CCSID, it will try and interpret the binary as a representation of the specified codepage, and you'll get results like:
'AMQ EAIMBP.QM 1/4.. '
Hope that helps ... |
|
| Back to top |
|
|
| nheng |
| Posted: Thu Oct 01, 2009 2:34 am Post subject: |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
| Luke wrote: |
If you want a caharacter representation that looks like this:
X'21657498795249879849....'
Then code:
| Code: |
| DECLARE MSGVal CHARACTER CAST(InputRoot.MQMD.MsgId AS CHARACTER); |
If you specify CCSID, it will try and interpret the binary as a representation of the specified codepage, and you'll get results like:
'AMQ EAIMBP.QM 1/4.. '
Hope that helps ... |
Thank a lot
But it doesn't work
Has exception as below:
[IBM][CLI Driver][DB2/AIX64] SQL0103N The numeric literal "414d51204541494d4250352e514d20204abc416e20026804" is not valid. SQLSTATE=42604 ' (CHARACTER) |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 01, 2009 3:02 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Three points.
1) The MQMD.MsgId field is 20 bytes long. Your sample string is longer than that.
2) What is the data tyoe & size of the DB field where you are trying to do the DB operation.
3) Have you looked up what the SQLState value 42604 actually means? A simple google for "DB2 SQLSTATE 42604" brings a plethora of answers.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Luke |
| Posted: Thu Oct 01, 2009 4:32 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
| smdavies99 wrote: |
1) The MQMD.MsgId field is 20 bytes long. Your sample string is longer than that.
|
Isn't it 24 bytes? I think it's 24 bytes as a BLOB, but when CAST to a character represntation, it should be 48 bytes (without the X' and ' at start and end).
Point 2 is very relevant I think though ...
nheng, it is not a number! |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 01, 2009 5:00 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Luke,
Sorry, my mistake. It is indeed 24 bytes for the MsgId field (and CorrelID)
Your last point
| Quote: |
nheng, it is not a number!
|
Is also very relevant. As I said before it is a binary field.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| nheng |
| Posted: Thu Oct 01, 2009 7:06 pm Post subject: |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
| smdavies99 wrote: |
Luke,
Sorry, my mistake. It is indeed 24 bytes for the MsgId field (and CorrelID)
Your last point
| Quote: |
nheng, it is not a number!
|
Is also very relevant. As I said before it is a binary field. |
Thank u very much for reply.This problem has beed solved
DECLARE MSGIDVal CHARACTER SUBSTRING(InputRoot.MQMD.MsgId FROM 3 FOR 48 ) ;
and add single quote in
SET strVal = strVal || '''' || ValidateMand(MSGIDVal) || '''' ;
Result in DB:414d51204541494d4250352e514d20204abc416e20022702 |
|
| Back to top |
|
|
|
|
