Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Raffle application - how to do an unique select

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
myibuki

New User


Joined: 14 Sep 2007
Posts: 2
Location: brazil

PostPosted: Tue Oct 30, 2007 10:07 pm    Post subject: Raffle application - how to do an unique select
Reply with quote

Hi Fellows,

I got the following problem.

Our application in cobol will run in CICS. It makes a raffle, getting
a field from a record in a DB2 database and making a little calculation.

Basically it does the following :

1. selects an "access counter" from a field in a DB2 table
2. adds +1 to the "access counter" obtained before
3. updates this updated value in the "access counter"

This is done for each person who access the application.

This application will have some peak times of access when thousands of people will be using the same program.

Our doubt is if for example from 10.000 people per second suppose 10 people get the same number 29 from the select operation - this would be bad.

Would there be any way to make the application to always select exactly 1 unit for each person accessing the application ? Some people say we should find a way to lock the record in the DB2 but this would not avoid the select to get the same number 29 for the 10 people - it would
only avoid concurrent updates.

Would anybody help me about this?

Thanks in advance.
Back to top
View user's profile Send private message

UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 755
Location: Germany

PostPosted: Wed Oct 31, 2007 1:46 pm    Post subject:
Reply with quote

Hi !

I think, best would be to do your calculation on a matchless, unique content of a field. I would use a TimeStamp. It's unique.

Regards, UmeySan
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10256
Location: italy

PostPosted: Wed Oct 31, 2007 1:53 pm    Post subject: Reply to: Raffle application - how to do an unique select
Reply with quote

Quote:
10.000 people per second suppose


It might be interesting to find out how to configure/tune a system for such
transaction rate ?!?!? :-)
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Wed Oct 31, 2007 7:28 pm    Post subject:
Reply with quote

Rework your logic so that it does it like this:

1. Update table SET counter = counter + 1. This will lock the table.
2. Select from table the row that you just updated.

Update will lock the table and ensure only 1 process gets 29..

However, these types of counter schemes can lead to performance problems. They can lead to lock-wait bottlenecks. Where I work, we have several processes with chronic performance problems caused by this approach.

Of course, we also use the same technique in many transactions where it works just fine. So it all depends. Depends on how long your transactions execute for? How many of them execute? Do they all chase the same rows or pages? Are there really 10,000 hits per second? Consider using ROW level locking.

Umeysan has a good suggestion; consider using timestamps.

One problem with timestamps is that the key values generated cluster together in the same range and can cause hotspots. For that reason, we often use a "scambled" timestamp. Basically - the digits are reversed and this causes the key values to be evenly distributed throughout the range.

Of course if you need to ORDER BY timestamp, then a "scrambled" timestamp will address this requirement.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Wed Oct 31, 2007 7:30 pm    Post subject:
Reply with quote

Sorry mistake... my last words should have been...

...will NOT address this requirement.

I don't know how to edit posts yet...
Back to top
View user's profile Send private message
myibuki

New User


Joined: 14 Sep 2007
Posts: 2
Location: brazil

PostPosted: Tue Nov 06, 2007 9:56 pm    Post subject:
Reply with quote

Hi fellows,

Thanks for the replies, we will be using a mixed solution we got from the written above. Until now it will be a cursor for select for update of, like the following :

DECLARE XXXX CURSOR
FOR SELECT FIELD_P FROM TABLE_Y
WHERE FIELD_M = NNN
FOR UPDATE OF FIELD_P.

UPDATE FIELD_P FROM TABLE_Y
WHERE CURRENT OF XXXX.

Seems that it will work ....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm
No new posts TWS EQQYCAIN - not sure how to get ap... mbenaud IBM Tools 0 Wed Dec 14, 2016 8:45 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us