IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

problem in select max when executing the transaction in para


IBM Mainframe Forums -> IMS DB/DC
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Renato Zangerolami

New User


Joined: 06 Dec 2019
Posts: 28
Location: Brazil

PostPosted: Tue Mar 02, 2021 2:42 am
Reply with quote

I have a problem when executing a select max when the transaction (IMS) is executed, that same transaction can be executed at the same time. after the select is made an insert.
Example:
Transaction 1
Code:
EXEC SQL MAX (FIELD)
            INTO: XXXXX
            FROM TABLE
           WHERE ......

Transaction 2
Code:
EXEC SQL MAX (FIELD)
            INTO: XXXXX
            FROM TABLE
            WHERE ......

both transactions are returning the same select MAX (FIELD) because they are running at the same time, so I'm having a problem with the insert because I have a duplicate key
Does anyone have any idea how to solve this?
Coded for you
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Tue Mar 02, 2021 3:16 am
Reply with quote

I believe this is a general logical problem with ANY DATABASE SYSTEM, not only IMS DB/DC.

You need to provide any type of blocking all other conflicting transactions from running while the first of them has not finished. The method of blocking may be different for various databases, but the idea must be logically the same for any of them.
Back to top
View user's profile Send private message
Renato Zangerolami

New User


Joined: 06 Dec 2019
Posts: 28
Location: Brazil

PostPosted: Tue Mar 02, 2021 5:34 pm
Reply with quote

I tried to use some options like
- WITH RR USE AND KEEP EXCLUSIVE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP UPDATE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP SHARE LOCKS (WITH RR AND RS)
But it didn't work very well

the best option that worked was using
EXEC SQL LOCK TABLE XXXX IN EXCLUSIVE MODE END-EXEC.
this is the first time I use this command, my only concern is with the delay this may cause, as this transaction processes thousands of accounts per day ...
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA

PostPosted: Tue Mar 02, 2021 7:48 pm
Reply with quote

Looks like a logical error.
Once you get the MAX( field) , you can simply check in DB if it exists and if yes then redo that MAX again to get latest value and do INSERT again.
OR ( This one is most preferred)
Explore if it supports transaction sequencing like we have in CICS by using ENQ and DEQ method which will work perfect and designed for such situations.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Tue Mar 02, 2021 9:33 pm
Reply with quote

AFAIK, in IMS DB/DC there is a command to perform such locking, at different levels:
LOCK DB
LOCK TRAN
UNLOCK DB
UNLOCK TRAN
etc.
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Tue Mar 02, 2021 9:49 pm
Reply with quote

Renato Zangerolami wrote:
I tried to use some options like
- WITH RR USE AND KEEP EXCLUSIVE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP UPDATE LOCKS (WITH RR AND RS)
- WITH RR USE AND KEEP SHARE LOCKS (WITH RR AND RS)
But it didn't work very well

the best option that worked was using
EXEC SQL LOCK TABLE XXXX IN EXCLUSIVE MODE END-EXEC.
this is the first time I use this command, my only concern is with the delay this may cause, as this transaction processes thousands of accounts per day ...

This looks to be one of acceptable methods. Some delay is possible, but this is inevitable (to avoid failures, or wrong results). You can minimize the delay by making the “distance” between LOCK TABLE, and UNLOCK TABLE as short as possible: LOCK it just before SELECT MAX, and UNLOCK it immediately after INSERT new_timestamp
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Tue Mar 02, 2021 10:58 pm
Reply with quote

no forum advice will fix a bad application design icon_cool.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> IMS DB/DC

 


Similar Topics
Topic Forum Replies
No new posts Executing DB2 SQL store procedure via... DB2 0
No new posts Associating a USERID with a CICS-Libe... CICS 0
No new posts Help needed in automation cics transa... CLIST & REXX 1
No new posts Inserting into table while open selec... DB2 1
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
Search our Forums:

Back to Top