0

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

Community
  • 1
  • 1
Adrian Bannister
  • 523
  • 4
  • 11
  • Is there any reason you can't use the concatenated expression itself wherever you planned to invoke your function? Have you seen the [built-in function `DIGITS()`](https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000795.html), by the way? – mustaccio Jul 21 '16 at 14:09
  • I could use it where we are using the sequencer, but I wasn't happy with having the bulky RIGHT BiF everywhere we use this. How ever I did not know about the DIGITS function, it's a much neater way to do it. Thanks for the information. I'll use that to solve my problem. – Adrian Bannister Jul 22 '16 at 04:27

1 Answers1

0

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"
Community
  • 1
  • 1
Adrian Bannister
  • 523
  • 4
  • 11
  • Please wait a moment: what are you using this value for? Do you need a strictly increasing, no-gap sequence? Then not only will you need a separate table, [there's quite a bit more to worry about](http://stackoverflow.com/questions/24184749/sql-server-unique-composite-key-of-two-field-with-second-field-auto-increment/24196374#24196374) (SQL Server, but the problem is universal). If you don't care about what happens when the db crashes or the insert is rolled back, then this is probably fine. – Clockwork-Muse Jul 24 '16 at 05:22
  • Thanks for the comment Clockwork-Muse. We do not require a no-gap sequence, so we will be fine with using a sequencer. – Adrian Bannister Jul 25 '16 at 07:33