Search code examples
sqldb2db2-luwdatabase-sequence

Why isn't NEXT VALUE allowed in CREATE FUNCTION statements?

I've been trying to create a function that uses a sequencer to return a new character ID. However it seems that DB2 doesn't allow it in CREATE FUNCTION statements. I am struggling to understand why they would do that, the only documentation they have on it is:

NEXT VALUE and PREVIOUS VALUE expressions cannot be specified (SQLSTATE 428F9) in the following places:

Is there any alternative way of accomplishing the following that will be thread safe and not processing intensive?

CREATE SEQUENCE qgpl.someid AS BIGINT CACHE 100;

And the function:

CREATE OR REPLACE FUNCTION qgpl.GetNextMandateNumber ()
RETURNS CHAR(35)
BEGIN
  RETURN RIGHT('00000000000000000000000000000000000' ||
    VARCHAR(NEXT VALUE FOR qgpl.someid), 35);
END;

The best alternative I thought of would be to have a extra table that will contain the current sequence number. And then just adjust the function to use that instead of the sequencer (I would select the current value under Read stability isolation level to make sure it's multi-thread safe).

Solution

  • I am still not sure why NEXT VALUE isn't allowed with CREATE FUNCTION, but after mustaccio showed me the DIGITS() built-in function I will be using that as a solution as it is much neater than my RIGHT() built-in function usage.

    Creating the sequencer:

    CREATE SEQUENCE someid AS DECIMAL(35) CACHE 100

    An example usage:

    VALUES (DIGITS(NEXT VALUE FOR someid))

    Result:

    "00000000000000000000000000000000001"