Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Tue Nov 04, 2003 7:13 pm Post subject: Lock via SQL
All,
This is the first time I am posting in DB2 forum as I have started using DB2 only recently (before, it was IDMS). Here is the situation.
I have a program P1 which accesses a table T1. The result is some rows from T1 which has a column having the key values for a table T2. Based on each of these key values of T2 a LOT of processing needs to be done. So, the present setup is to XCTL a program P2 as many times as there are rows of having key values of T2. (Actually, it may not be greater than 5, max.) For these individual processing of various key values of T2, unfortunately, sometimes the data integrity is lost. P2 updates table Tx before some other "version" of P2 should have done it. Clearly then, I need to ensure that the "versions" of P2 need to run serially despite being XCTLed by P1.
I have gone thru" some manuals and have put my findings as follows.
1. Define the table such as LOCKSIZE ROW.
2. Use LOCK TABLE.
3. Override isolation via SQL such as,
Code:
SELECT MAX(BONUS),MIN(BONUS),AVG(BONUS) INTO :MAX,:MIN,:AVGFROM DSN8710.EMP WITH RR ;
1 and 2 is absolutely impossible. (The table in question here will be the Tx table mentioned above.) So, my question is, Is option 3 the best to go ahead with? Or, any other option?
I cannot use CICS to request for ENQ. (Here comes the interesting part.) We use CSP (Cross System Product) for coding our COBOL programs for accessing DB2 tables via IMS screens. Interestingly, CALL CREATX (which is what I am referring to XCTL above) actually results into START command in CICS! I used XCTL for the benefit of general public without loss of main content (I hope.) _________________ ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Wed Nov 05, 2003 3:45 am Post subject:
cogito,
How about issuing the start command from program P1 with a interval time ?. So each version of p2 will start after a specified amount of time.you can specify the interval time in hours, minutes and seconds .Ideally I would issue with an interval of 90 sec (minute and half).
Code:
START TRANSID(P2) AFTER HOURS(0) MINUTES(1) SECONDS(30).
Check this link for a detailed explanation of the start command
Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Wed Nov 05, 2003 9:44 am Post subject:
Kolusu,
Perhaps you missed my point. I did not code START command. The CALL CREATX command in CSP translated into the START command. I do not have control over the START command!
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
