Re: SQL Sequence use cases

Pete,

You are right regarding the CACHE verb. As per the V5R3 Reference Manual:
------------------------------

CACHE or NO CACHE

CACHE integer-constant Specifies the maximum number of sequence values
that are preallocated and kept in memory. Preallocating and storing
values in the cache improves performance. In certain situations, such
as system failure, all cached sequence values that have not been used
in committed statements are lost, and thus, will never be used. The
value specified for the CACHE option is the maximum number of sequence
values that could be lost in these situations. The minimum value that
can be specified is 2, and the maximum is the largest value that can
be represented as an integer.

NO CACHE Specifies that values for the sequence are not preallocated.
If NO CACHE is specified, the performance of the NEXT VALUE sequence
expression will be worse than if CACHE is specified.

Specifies whether to keep some preallocated values in memory.
Preallocating and storing values in the cache improves the performance
of the NEXT VALUE sequence expression. The default is CACHE 20.
------------------------------


There is a Centerfield bulletin (April 2006) with an article about
sequences, auto-increments and so on.

As per the business cases that David comments, I suppose that this
(the sequence object) could be seen as just another tool that SQL
provides. Myself, am a little uneasy about the storing of the data in
a *DTAARA (something that sometimes gets overlooked when restoring or
saving). Also, the fact that I can't get a guarantee of the sequence
can give some problems (think, for example, invoice numbers).

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Nov 25, 2009 at 3:12 PM, Pete Helgren <Pete@xxxxxxxxxx> wrote:

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.