Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    11

    Unanswered: URGENT!!!!!! DB2 Stored Procedures!

    Hi,
    I'm trying to implement the following process:

    When a record is inserted into a table, a trigger is invoked which calls a stored procedure, passing it a table locator (a pointer to the record in memory which has been inserted). This SP then formats the record and calls another routine which writes the formatted record to a remote q (MQSeries q). But there is no commit in the whole process, only the application which inserts the record into the table issues the commit. The problem here is that the messages are getting stuck in the xmit q, and according to MQ Support, it's presumably because they have not been committed. Has anyone faced the same issue and if so, were they able to resolve it or is this something which cannot be done?

    THIS IS AN URGENT ISSUE, so pls reply asap.

    Maxdp.
    ******

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: URGENT!!!!!! DB2 Stored Procedures!

    Originally posted by maxdp
    Hi,
    I'm trying to implement the following process:

    When a record is inserted into a table, a trigger is invoked which calls a stored procedure, passing it a table locator (a pointer to the record in memory which has been inserted). This SP then formats the record and calls another routine which writes the formatted record to a remote q (MQSeries q). But there is no commit in the whole process, only the application which inserts the record into the table issues the commit. The problem here is that the messages are getting stuck in the xmit q, and according to MQ Support, it's presumably because they have not been committed. Has anyone faced the same issue and if so, were they able to resolve it or is this something which cannot be done?

    THIS IS AN URGENT ISSUE, so pls reply asap.

    Maxdp.
    ******
    As far as I understand, MQ commit has nothing to do with the database transaction commit. So I guess you should commit the MQ messages, and it will not affect the database transaction...

  3. #3
    Join Date
    Dec 2003
    Posts
    11
    hi n_i, the problem is that i cannot issue the mq commit - the reason being that the process has been designed such that the record shud be rolled back from both the queue and the table if the record inserting program (into the table) encounters any error. if i issue a commit upon putting a msg into the q, then it cannot be rolled back later. and i cannot issue a db2 commit in the sp either since the effect is the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •