| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| BIP2231E: Problem with Atomic Stored procs |
« View previous topic :: View next topic » |
| Author |
Message
|
| inder |
 Posted: Wed Jul 09, 2003 10:05 am Post subject: BIP2231E: Problem with Atomic Stored procs |
 |
|
Apprentice
Joined: 24 Mar 2003
Posts: 49
Location: USA
|
Hi,
I have a problem with the handling of stored proc errors. I am using a stored procedure to update some tables on DB2/AS400 database. I made the stored procedure Atomic to matain data integrity. But some other updates in the message flow need not be transactional.
The problem is when ever there is an exception from the stored procedure, the message is put in to a error queue for error handling and moved forward. Up to this point every thing is fine. But for the next messages when the stored proc is invoked, I am getting following error resulting in all correponding messages put in to error queue
| Quote: |
DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
DatabaseException BIP2322E: Database error: SQL State '57014'; Native Error Code '-1044'; Error Text '[IBM][CLI Driver][AS] SQL1044N Processing was cancelled due to an interrupt. SQLSTATE=57014
'.
The error has the following diagnostic information: SQL State '57014' SQL Native Error Code '-1044' SQL Error Text '[IBM][CLI Driver][AS] SQL1044N Processing was cancelled due to an i
'
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
|
All the properties of message flow are at default properties. I am wondering is there any thing to do with the definition of stored proc as atomic or some settings in message flow?
I am running on WMQI CSD 4
Regards
Inder |
|
| Back to top |
|
 |
| Craig B |
| Posted: Fri Jul 11, 2003 1:11 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
From what you have described then it is possible that the ATOMIC keyword is causing the problems in your scenario. Since global coordination is not supported with iSeries databases, then your transactions will be coordinated by the broker. When a message is processed by your message flow then a unit of work will be started with the database manager, and a separate unit of work will be started for the MQSeries work. During normal processing then when the message flow completes successfully a commit will be issued to the database unit of work and a separate commit will be issued on the MQSeries unit of work.
During failure processing the committing and scope of Units of work is dependent on how you have wired your message flow and what attributes you have specified. In your description you say that everything is using defaults, and you handle your error down a failure path. This means that the message flow will not do a transactional rollback and want to commit its unit of work for both the database and MQSeries. However, when you use the atomic keyword, you are giving the stored procedure the power to roll itself back as a separate unit of work. This goes against the broker coordination model that your message flow expects. When you get a failing message flow because the stored procedure fails, then the message flow expects to issue a commit or rollback to its database connection. But in this case, part of the unit of work has already been rolled back by an external source (the stored procedure). This could be causing the interrupted state that your database connection gets itself into after this error.
Without considering the complications of an iSeries connection, I think generally the use of external transactional elements in stored procedures is not advised when they are called from WMQI. Im sure I have read somewhere that the use of such keyword as commit and rollback should not be used, and this seems to cover the case for the use of Atomic.
If the removing of the Atomic keyword does not rectify the scenario then you should also check the limitations/restrictions of the iSeries database connectivity that are documented on :
http://www-3.ibm.com/software/integration/websphere/mqplatforms/supported.html
These seem to be :
| Quote: |
Support for access of user databases on iSeries servers
Pre req levels
- WMQI: v2.1 CSD 04
- Connectivity by DB2 Connect : DB2 v7.2 FixPak 7
- OS/400 V5R1 with PTFs SI03013 and SI04047 or V5R2 (no PTFs)
Restrictions
- Large database Objects (LOBs) are not supported.
- The broker database may not reside on the iSeries server.
- Globally coordinated ('XA') transactions are not supported.
- Database stored procedures on the iSeries may be called when using DB2 Connect but not with Client Access.
- New Era of Networks nodes may not access the iSeries databases.
|
_________________
Regards
Craig |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
