How to get the next value of sequence?

Question

Could somebody tell what is wrong with obvious query below:

db2 => select next value for schema_name.sequence_name;

As a result I'm getting:

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following
"schema_name.sequence_name".  Expected tokens may include:  "<table_expr>".
SQLSTATE=42601

Answer 1

You need to select from something. Another option is:

db2 => values next value for schema_name.sequence_name;

As you noted you can also use SYSIBM.SYSDUMMY1 or create a virtual table:

db2 => select next value for schema_name.sequence_name from ( values 1 ); 

Answer 2

db2 => select next value for schema_name.sequence_name from SYSIBM.SYSDUMMY1;

did the trick

Answer 3

In this query:

select next value for schema_name.sequence_name;

next value is creating problem because we can not make the spaces while writing a query. So other thing you can do is found the max of value and increase it by 1.

Below find query for that:

select max(value)+1 for schema_name.sequence_name;

This would be helpful for you.