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
 

 

Is there a problem with creating 999 sequential objects

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
xsray

New User


Joined: 16 Sep 2008
Posts: 19
Location: illinois

PostPosted: Thu Jan 21, 2010 1:49 am    Post subject: Is there a problem with creating 999 sequential objects
Reply with quote

we have a consistant contention issue on a table that many apps access to get a unique number. the process obtains a random number between 1 and 999, then uses that number to read a row on the table which contains the last used unique number in that range. I would like to eliminate this contention by using sequential objects, this would mean creating 999 objects. Each object would start at a specified value. We have several tables partitioned on the returned number. Is there a problem with creating 999 sequential objects?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jan 21, 2010 6:00 am    Post subject:
Reply with quote

Hello,

Why do you believe doing basically the sme thing with sequential objects would eliminate the contention?

Creating mutiple new "next available" tables might help. Changing to row-level locking instead of page-level locking might help more. . .
Back to top
View user's profile Send private message
xsray

New User


Joined: 16 Sep 2008
Posts: 19
Location: illinois

PostPosted: Thu Jan 21, 2010 8:58 pm    Post subject:
Reply with quote

according to the DB2 Manual, sequential objects will eliminate any locking and contention, also, the table is already at row level locking. Your thought on multiple tables sounds like a good possibility, thanks.
We process on average 40 transactions a second that need a unique number.

from manual

"The use of sequences can avoid the lock contention problems that can
| result when applications implement their own sequences, such as in a
| one-row table that contains a sequence number that each transaction must
| increment. With DB2 sequences, many users can access and increment the
| sequence concurrently without waiting. DB2 does not wait for a transaction
| that has incremented a sequence to commit before allowing another
| transaction to increment the sequence again. "
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jan 22, 2010 9:43 pm    Post subject:
Reply with quote

the problem with 999 sequences is that (AFAIK) you can't use them in one static SQL statement.
Thus you would need to code 999 SQL statements and execute the appropiate one depending on your first randm number.
or code 1 giant SQL statement with a case .
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jan 22, 2010 9:58 pm    Post subject:
Reply with quote

you could solve it with one sequence

X = nextval(sequence)
unique nr : mod(x,1000) * 1.000.000.000 + x/1000

and your inserts nicely spreading over the partitions.
only it will be always at the end of each partition (if clustering index is also uniquenr )
Back to top
View user's profile Send private message
xsray

New User


Joined: 16 Sep 2008
Posts: 19
Location: illinois

PostPosted: Wed Feb 03, 2010 9:19 pm    Post subject: Reply to: Is there a problem with creating 999 sequential ob
Reply with quote

Thanks Guy, that seems to work nicely.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Feb 04, 2010 9:33 pm    Post subject:
Reply with quote

ur welcome, thx for the feedback
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm


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