Increment a sequence twice in DB2

Question

I need to get the next value of a sequence twice in DB2 (version 9.1). Rather than executing the following twice:

SELECT nextval FOR schema.sequence AS id
FROM dual

I would like to do something like:

SELECT nextval FOR schema.sequence AS id1,
       nextval FOR schema.sequence AS id2
FROM dual

Except the above only increments it once:

ID1         ID2        
----------- -----------
        643         643

  1 record(s) selected.

Am I forced to just query twice, or is there a way to increment it twice in 1 query?

Answer 1

Ok, it's a hack, but the following works... I would love to see a cleaner answer though, so please post an answer if you know one!

SELECT nextval FOR schema.sequence AS id
FROM (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual) temp

Which results with:

ID         
-----------
        669
        670

  2 record(s) selected.

Answer 2

there is no sequence nor dual table in DB2

Answer 3

heyho,

unfortunately i can't contribute any more to your sequence question than you already did yourself.

... but to your "generic table" remark:

in db2 (at least as of v7.1) there exists a table called sysibm.sysdummy1 with the same function as dual in oracle or mysql:

db2inst1:/> db2 select current_timestamp from sysibm.sysdummy1

1
--------------------------
1970-12-31-23.59.59.000000

  1 record(s) selected.

the result of a describe on this table looks like this (in v9.5):

db2inst1:/> db2 describe table sysibm.sysdummy1

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
IBMREQD                         SYSIBM    CHARACTER                    1     0 No

  1 record(s) selected.

good luck and: keep on databashing!

Answer 4

Just because I'm picking on you Mike :-P, here's another solution:

VALUES NEXT VALUE FOR schema.sequence, NEXT VALUE FOR schema.sequence;

It will work if you do it as two rows, like here. But if you do it as one row:

VALUES (NEXT VALUE FOR schema.sequence, NEXT VALUE FOR schema.sequence);

it will return the same sequence value. :-(