MQSeries.net :: View topic - Problem with inserting data from Compute node

accosun · Posted: Fri Mar 06, 2009 7:22 am Post subject:

Hello everybody!

I'd like to describe a problem we faced some time ago. May be someone will give me an advice, what is the reason for this problem.

So... We have very complex transaction processing in our bank, and for the moment we use WMB v5.x running on AIX machine. The rate of messages is not very high (about 100 - 200 per hour), but, each transaction takes about a minute to be processed. We use hundreds of message flows and subflows in our solution. These messages are generateted by some (3-5) external applications, and these messages come in bulks of for example 10 msg. It is not neccessary, that all mesages in a bulk should be processed successfully. MsgId is generated by queue managers (and they should be unique) for these applications. We have 4 execution groups and in each EG we have one copy of nesessary flows. No additional instances of MF!

At the particular point of processing, we need to insert some audit data from message to remote external DB2 database (table MQSISIS.CONTEXT_DATA, Msg_ID field is a primary key), working on a remote AIX machine. You can find fragment of ESQL expression below:

PASSTHRU ('INSERT INTO Context_Data ( Msg_ID, '||
'Msg_Format, '||
'Msg_Version, '||
'Channel_ID, '||
'Channel_Function, '||
........
'UsrDfnd4, '||
'UsrDfnd5, '||
'General_Txt,TRAN_DATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)',
CAST(OutputRoot.MQMD.MsgId AS BLOB), .........)

Everything is set up correctly, it works fine on the testing system and also on production system until the rate of the messges is icreased. Then the problem appears....

The first sign of problem - MF fails to insert one (any) message from the bulk to external DB. Error message is :

[IBM][CLI Driver][DB2/6000] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "MQSISIS.CONTEXT_DATA" from having duplicate rows for those columns. SQLSTATE=23505 '

As I've said earlier, this is not a big problem, when one message from the bulk is failed, so the processing continues...

But very soon two messages from the bulk failed with the same error, then 3, 4 ... When the number of failed messages increses to 8-9 we have to reboot the boker. After rebooting system works fine again for some time, until the decribed problem appear again.

UNFORTUNATELLY, because of some regulatons, we cannot use any debug tools (user trace, db trace, etc) on the production system to find out, what is the reason for this error.

BUT, if we use a special procedure for MsgId generation (either at application side or broker side), the problem is gone. Too bad, that managers don't want to use this procedure anywhere in the code.

Do anyone have an idea about it?

Thank you, Alexander