title: Why isn't NEXT VALUE allowed in CREATE FUNCTION statements?
body: <p>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:</p>
  <blockquote>
  <p>NEXT VALUE and PREVIOUS VALUE expressions cannot be specified (SQLSTATE 428F9) in the following places:</p>
  <ul>
  <li>...</li>
  <li>CREATE FUNCTION statement</li>
  <li>...
  <a href="https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023464.html" rel="nofollow noreferrer">https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023464.html</a></li>
  </ul>
  </blockquote>
  <p>Is there any alternative way of accomplishing the following that will be thread safe and not processing intensive?</p>
  <pre><code>CREATE SEQUENCE qgpl.someid AS BIGINT CACHE 100;
  </code></pre>
  <p>And the function:</p>
  <pre><code>CREATE OR REPLACE FUNCTION qgpl.GetNextMandateNumber ()
  RETURNS CHAR(35)
  BEGIN
    RETURN RIGHT('00000000000000000000000000000000000' ||
      VARCHAR(NEXT VALUE FOR qgpl.someid), 35);
  END;
  </code></pre>
  <p>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).</p>