IBM Power Ideas Portal
This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).
Shape the future of IBM!
We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:
Search existing ideas
Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,
Post your ideas
Post an idea.
Get feedback from the IBM team and other customers to refine your idea.
Follow the idea through the IBM Ideas process.
Specific links you will want to bookmark for future use
Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.
IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.
ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.
Db2 for i Development
IBM Power Systems Development
Db2 for i Development
IBM Power Systems Development
The situation you are describing appears to be about the locking that is performed when allocating the next sequence value. Sequences must acquire an exclusive lock on the *DTAARA object to serialize multiple competing jobs asking for new values. No matter how sequences are implemented, this lock requirement must exist.
The CACHE value that is part of the sequence definition can help performance by allocating a block of sequence numbers to a job when a request is made. Rather than only having one value provided to a job, a block of values is allocated from the sequence, This cache is maintained by SQL and used as future requests from that job are made. This lowers the number of lock requests needed by a job, causing less interference with other jobs. For example, if the CACHE value is set to 100, when a job requests a sequence value 100 values are taken from the sequence. The job will not need to lock the *DTAARA again until all 100 values have been consumed.
The downside of this CACHE behavior is that if a job does not use all of its values, the unused values are lost.
Now for the questions we have.
What is the full definition of the sequence that you are using?
select * from qsys2.syssequences where sequence_schema = 'mylib' and sequence_name = 'myseq';
How many jobs do you generally have competing for sequence values?
Would using an IDENTITY column in your table be an alternative that fits your application?
While it is unlikely that we would implement a new version of sequences, we are interested in ideas that can help improve the performance of applications that use them.
Db2 for i development team
IBM Power Systems Development
The problem is not about obtaining the sequence numbers. The problem is the lock of the underlying IBM i objects used for DB2 sequences, that are data areas. When you run several jobs in parallel using the same DB2 sequence on an IBM i, you find that some of the jobs are blocked by the locks produced by the competing jobs. The resulting degradation of performance is significant when you work on big volumes of data.
We provide caching ability for sequences for improved performance. Are you aware of this? If so, is there a reason it does not satisfy your needs?
From the SQL Reference https://www.ibm.com/docs/en/i/7.5?topic=statements-create-sequence :
Caching sequence numbers: A range of sequence numbers can be kept in memory for fast access. When an application accesses a sequence that can allocate the next sequence number from the cache, the sequence number allocation can happen quickly. However, if an application accesses a sequence that cannot allocate the next sequence number from the cache, the sequence number allocation will require an update to the *DTAARA object.
Choosing a high value for CACHE allows faster access to more successive sequence numbers. However, in the event of a failure, all sequence values in the cache are lost. If the NO CACHE option is used, the values of the sequence are not stored in the sequence cache. In this case every access to the sequence requires an update to the *DTAARA object. The choice of the value for CACHE should be made keeping the trade-off between performance and application requirements in mind.
Db2 for i development team
IBM Power Systems Development