Re: SQL Sequence use cases

On one hand that doesn't seem right but then I read this explanation from the z/OS DB2 9.1 reference which seems to fit here but isn't documented as part of db2 for i5/OS:

<snip>
In a data sharing environment, if the CACHE and NO ORDER options are in effect, multiple caches can be active simultaneously, and the requests for next value assignments from different DB2 members might not result in the assignment of values in strict numeric order. For example, if members DB2A and DB2B are using the same sequence, and DB2A gets the cache values 1 to 20 and DB2B gets the cache values 21 to 40, the actual order of values assigned would be 1,21,2 if DB2A requested for next value first, then DB2B requested, and then DB2A again requested. Therefore, to guarantee that sequence numbers are generated in strict numeric order among multiple DB2 members using the same sequence concurrently, specify the ORDER option.
<snip/>

That does sound like what you are experiencing and would be correct if the cached values are allocated on an application request basis. But, I don't see this behavior documented as part of DB2 for i5/OS so it is just a guess.

Pete


David Gibbs wrote:

Folks:

I'm having a bit of a struggle understanding the SQL Sequence business cases.

I understand the conceptual idea behind SQL Sequences ... but the behavior I'm observing in testing is kind of baffling me.

In interactive SQL (no commitment control), I created a SQL sequence in a schema starting at 1 with a cache value of 10.

I then run the following statement:
select (next value for TESTSEQ1) from sysibm/sysdummy1

I get a result of '1'.

If I run the same SQL statement again, I get the result '2'.

However, if I start interactive SQL in another job, and run the same statement I get a value of '11'.

From what I understand about SQL Sequences, you're supposed to use it to get a sequential number from SQL on an select, insert, or update statement.

What I'm not understanding is why the 'sequential' number isn't so sequential ... and very gappy.

I do know that I could use the no cache option to disable caching ... but I'm just trying to understand the business cases for the sequence concept.

Thanks!

david