Max number of sequences in a DB

[Joachim Tuchel]

Hi there,

this may seem like a stupid or academic question. But how many SEQUENCEs can I create in DB2 10.5 LUW?

I'm asking because we think about using DB2 Sequences for incrementing business values where each individual user of a web based system can create their own numbering scheme (=SEQUENCE). So if the limit is more like a few thousands, this is probably going to be a bad design decision over the next few years. If we're talking about hundreds of thousands or even more, the anticipated debt is much lower...


Thanks

Joachim

[v***@gmail.com]

You can check it with something like

parse arg base user using
call rxfuncadd 'sqlexec', 'db2ar', 'sqlexec'
call rxfuncadd 'sqldbs', 'db2ar', 'sqldbs'
base = 'xxx'
user = 'yyy'
pwd = 'zzz'
call sqlexek 'connect to 'base' user' user ' using ' pwd
'db2 connect to 'base' user' user ' using ' pwd
'db2 UPDATE COMMAND OPTIONS USING C OFF'
do i = 1 to 10000000
cmd = 'db2 create sequence xyz'i
say cmd
cmd
end
'db2 rollback'
'db2 terminate'
return

But I think it dubious design. They are out of transactions and no gaps are not guaranteed.

[v***@gmail.com]

so far
...

db2 create sequence xyz966066
DB20000I The SQL command completed successfully.

db2 create sequence xyz966067
DB20000I The SQL command completed successfully.

db2 create sequence xyz966068
DB20000I The SQL command completed successfully.

db2 create sequence xyz966069
DB20000I The SQL command completed successfully.

db2 create sequence xyz966070
DB20000I The SQL command completed successfully.

db2 create sequence xyz966071
DB20000I The SQL command completed successfully.

db2 create sequence xyz966072
DB20000I The SQL command completed successfully.

db2 create sequence xyz966073
DB20000I The SQL command completed successfully.

db2 create sequence xyz966074
DB20000I The SQL command completed successfully.

db2 create sequence xyz966075
DB20000I The SQL command completed successfully.

db2 create sequence xyz966076
DB20000I The SQL command completed successfully.

... and continues

[Joachim Tuchel]

Post by v***@gmail.com
You can check it with something like
parse arg base user using
call rxfuncadd 'sqlexec', 'db2ar', 'sqlexec'
call rxfuncadd 'sqldbs', 'db2ar', 'sqldbs'
base = 'xxx'
user = 'yyy'
pwd = 'zzz'
call sqlexek 'connect to 'base' user' user ' using ' pwd
'db2 connect to 'base' user' user ' using ' pwd
'db2 UPDATE COMMAND OPTIONS USING C OFF'
do i = 1 to 10000000
cmd = 'db2 create sequence xyz'i
say cmd
cmd
end
'db2 rollback'
'db2 terminate'
return

okay, I could've had this idea ;-) Thanks for trying. So how far did you get? Looks like 100'000 aren't a problem...

Post by v***@gmail.com
But I think it dubious design. They are out of transactions and no gaps are not guaranteed.

Yes, that was my second worry. When does a sequence get incremented? When you select a next value (I guess this is it) or on the next commit. Both present their own problems.

So it's probably best to think about an application-side implementation. The no-gaps thing is tricky...

[b***@gmail.com]

Post by Joachim Tuchel

Post by v***@gmail.com
You can check it with something like
parse arg base user using
call rxfuncadd 'sqlexec', 'db2ar', 'sqlexec'
call rxfuncadd 'sqldbs', 'db2ar', 'sqldbs'
base = 'xxx'
user = 'yyy'
pwd = 'zzz'
call sqlexek 'connect to 'base' user' user ' using ' pwd
'db2 connect to 'base' user' user ' using ' pwd
'db2 UPDATE COMMAND OPTIONS USING C OFF'
do i = 1 to 10000000
cmd = 'db2 create sequence xyz'i
say cmd
cmd
end
'db2 rollback'
'db2 terminate'
return

okay, I could've had this idea ;-) Thanks for trying. So how far did you get? Looks like 100'000 aren't a problem...

Post by v***@gmail.com
But I think it dubious design. They are out of transactions and no gaps are not guaranteed.

Yes, that was my second worry. When does a sequence get incremented? When you select a next value (I guess this is it) or on the next commit. Both present their own problems.
So it's probably best to think about an application-side implementation. The no-gaps thing is tricky...

And do a db2look with 100,000+ sequences, etc etc etc yes this would seem to be doable but not really a good working solution in my mind... watch out for downstream effects.

-B

[v***@gmail.com]

so far

db2 create sequence xyz3616301
DB20000I The SQL command completed successfully.

db2 create sequence xyz3616302
DB20000I The SQL command completed successfully.
...
and continues.

When you execute something like

'update ttt set fff=seq',id printString,'.nextval where id=',id printString
or
"update ttt set fff=seq"+id.toString()+".nextval where id="+ id.toString()

then your program rollbacks by error or intention, field value rollbacks but sequence values does not rollbacks.

I think 'update ttt set fff=fff+1 where id=?' much better.

[Jeremy Rickard]

This is such an interesting question!

Yes, sequences are incremented by the NEXTVAL call.

Regarding limits on the number of sequences, there is no limit defined in the manual. You could ask IBM of course, but FWIW my *guess* is there is no limit as such, but each sequence definition (and permissions) will of course increase the size of the catalog a little bit.

More significantly, each referenced sequence will be loaded into memory when referenced. IBM implemented sequences very late in DB2, but when they did they did a great job - meaning they are architected to be highly parallel and high performing. So, by default (cache size of 20) on first reference DB2 will pre-allocate 20 sequence numbers into contiguous memory. That's 20 x 4 bytes per referenced sequence assuming an INTEGER data type. This pool allows 20 parallel application processes to simultaneously grab an unique sequence value without contention.

So, if you go with the sequence defaults, you will use about 80 bytes of memory, per sequence, about 8GB of memory for 100,000 sequences (if that's the goal) if over time you reference them all! Of course in partial mitigation you can reduce the cache size to 1, probably OK for your scenario.

Will the memory get deallocated without deactivating the database? I don't know but suspect that it won't. So that's another reason to be wary on this design.


Jeremy