1. Forum
  2. Usenet
  3. COMP.DATABASES.IBM-DB2

  • Max number of sequences in a DB

    From Joachim Tuchel@21:1/5 to All on Fri Jun 9 11:36:45 2017
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to All on Sat Jun 10 23:32:51 2017
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to All on Sun Jun 11 09:00:26 2017
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Joachim Tuchel@21:1/5 to All on Mon Jun 12 01:13:46 2017
    Am Sonntag, 11. Juni 2017 08:32:54 UTC+2 schrieb [email protected]:
    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...

    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...

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to Joachim Tuchel on Mon Jun 12 07:25:19 2017
    On Monday, June 12, 2017 at 4:13:49 AM UTC-4, Joachim Tuchel wrote:
    Am Sonntag, 11. Juni 2017 08:32:54 UTC+2 schrieb [email protected]:
    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...

    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From [email protected]@21:1/5 to All on Mon Jun 12 12:26:11 2017
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jeremy Rickard@21:1/5 to All on Tue Jun 13 00:39:37 2017
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)