View previous topic :: View next topic
|
Author |
Message |
anshuljain26
New User
Joined: 04 Apr 2010 Posts: 37 Location: Chandigarh
|
|
|
|
HI,
I am facing following issue in CICS txn:
1)Table A is being updated by Prog A
2)The details from Prog A are passed on a MQ where another transaction Kcics off,where Prog B will perform SELECT on table A to fetch the complete details of the Primary key passed from Prog A over MQ.
3)Now the issue is, Prog B is unable to find the details in table A, as the commit is happening in same UOW. Therefore SQLCODE = 100, is returned.
Please pass me suggestions to resolve the issue.
What i can think of is start additional transaction after prog B, which will help in giving enough time to Committ the changes.
Please suggest better and efficient solutions.Thanks. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
pgm a inserts db2 data
pgm a puts Mq
pgm b starts because of mq
pgm b could not start, because the mq item could not have been there,
until pgm a made a commit. but the commit appears to only be a 1 resource (MQS) commit instead of a multiple resource (RRSAF) commit. otherwise, the db2 data would have been committed.
i imagine you are not using rrsaf.
i also assume pgm a is making multiple inserts and multiple puts.
pgm a is not designed properly. |
|
Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
|
|
|
|
Quote: |
pgm b could not start, because the mq item could not have been there,
until pgm a made a commit. but the commit appears to only be a 1 resource (MQS) commit instead of a multiple resource (RRSAF) commit. otherwise, the db2 data would have been committed.
|
IF the MQ message has been put with MQPMO-NO-SYNCPOINT, then it is not part of the recoverable UOW and is effectively committed. In such a case, no DB2 commit will have bene performed.
Garry. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
good post Garry. |
|
Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
|
|
|
|
Thanks Dick.
Garry. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Garry,
you are welcome.
after reading your post, I realized my previous post did not help at all,
i had just reiterated the problem.
you proposed a possible solution, which is what we should be doing. |
|
Back to top |
|
|
anshuljain26
New User
Joined: 04 Apr 2010 Posts: 37 Location: Chandigarh
|
|
|
|
Thanks Dick & Garry.
What i can see from above discussuin is to diasable the parameter MQPMO-NO-SYNCPOINT and put the message on MQ with a Syncpoint option set to true.
But, the issue is also PGM A's data is for lot of applications and don't know if everyone supporting their APP, will agree. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
describe how pgma is being invoked by all these other applications.
without knowing, can not provide a suggestion.
first guess would be - it does not matter.
but, sorta depends on what is going on. |
|
Back to top |
|
|
anshuljain26
New User
Joined: 04 Apr 2010 Posts: 37 Location: Chandigarh
|
|
|
|
Thanks all.
MQPMO-SYNCPOINT is been issued while 'PUT' ing the message over MQ.
Secondly, The issue is not periodic rather spasmodic.
Kindly, Please suggest.
Thanks. |
|
Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
|
|
|
|
Is the DB2 commit happening in ProgA, and at what stage? This commit must happen before ProgB tries to access the database.
How is the 2nd task initiated?
If using MQ trigger, then you a should EXEC SQL COMMIT in ProgA before the MQPUT(1) - otherwise MQ may trigger the 2nd task before the 1st task has committed.
Similarly, if using EXEC CICS START TRANSID(), issue the EXEC SQL COMMIT before starting the 2nd task.
In either case, and depending on processing, the 2nd task executes independently of the 1st and can 'overtake' it. This could explain why the issue is seen as sporadic.
Garry. |
|
Back to top |
|
|
marpana
New User
Joined: 10 Jun 2005 Posts: 13 Location: London, UK
|
|
|
|
Hi Garry,
I work with Anshul .. thanks for your replies. They have been helpful. What you have said makes sense. Second task overtaking the first one is the only possible explanation for what we are seeing. Here are the details of our flow.
1) CICS transaction TRNA (started) executes program PGMA to process say a payment advice.
2) PGMA links to another module PGMB which inserts the advice details into TABLEB. After the inserts, PGMB writes the key fields for the advice to a MQ. Here ends PGMB and control is transferred back to PGMA. After PGMB returns to PGMA, it just performs update on status table and returns to CICS.
There is no explicit commit but the implicit commit will be taken as part of transaction TRNA returning control back to CICS.
3) PGMC is triggered by message put on MQ and PGMC reads and processes the advices from the MQ. PGMC first inserts the key fields into TABLEC and then proceeds to get the advice details by reading TABLEB.
most of the advices are being processed fine, but randomly about 15% advices in a day, are hitting 'row not found' in PGMC's attempt to read from TABLEB. We can see that the data is there on TABLEC as well as TABLEB.
Here is an example of one advice with its timestamp details on tables
TABLEB
PAYMN_REF CAPTURED_TMSTAMP
NNNDM2I21838888 2011-07-08-07.19.15.813839
TABLEC
PA_PAY_REF CREATE_TMSTAMP
NNNDM2I21838888 2011-07-08-07.19.15.848185
Hope this helps.
Could you advise then would it help if we introduced code to handle the 'not found' condition to retry select on TABLEB say 3-4 times? could that help give that little extra time for the first task to finish?
unfortunately this hasnt been happening in test environment and hence we have no means of proving any fix.
(PS : we tried introcuding a CICS delay (against the recommended standards at our place) but it caused the MQ to become full and PGMB and PGMA started failing... that had to be backed out ) . |
|
Back to top |
|
|
Garry Carroll
Senior Member
Joined: 08 May 2006 Posts: 1193 Location: Dublin, Ireland
|
|
|
|
Hi,
I wouldn't favour handling a 'not found' to try a re-select. How many times would you re-try? The 2nd task could be re-dispatched several times before the commit happens, depending on workload.
You say:
Quote: |
After PGMB returns to PGMA, it just performs update on status table and returns to CICS.
|
If I understand your description, you are relying on implicit commit on normal task termination? My choice would be to explicitly issue EXEC CICS SYNCPOINT to commit the DB2 work and then issue MQPUT(1) with MQPMO-NO-SYNCPOINT. This way you can be sure that the DB2 is ready for the MQ message driven transaction to be triggered.
Garry. |
|
Back to top |
|
|
|