Friends,

Within a store procedure, I need to get the next value for a sequence,
but I want the (schema) reference to the sequence to be inherited from
a SET SCHEMA that's called before the SP is called, not from who
created the SP (note the all-important "INHERIT SPECIAL REGISTERS"
pragma. In other words, I can't do something as simple as

VALUES NEXTVAL FOR EMPLOYER_MERGE_REQUEST_TEMP_SEQ INTO V_SEQVAL;--

What I have instead is a sequence of statements involving a cursor,
i.e.,

CREATE PROCEDURE DYN_SEQ_TEST
LANGUAGE SQL
SPECIFIC DYN_SEQ_TEST
INHERIT SPECIAL REGISTERS
BEGIN

-- Declare simple variables

DECLARE V_SEQVAL BIGINT DEFAULT 0;--

'VALUES NEXTVAL FOR A_SEQUENCE';--

-- Declare cursor

DECLARE C_SEQ_QRY CURSOR FOR S_SEQ_QRY;--

-- Do the stuff

PREPARE S_SEQ_QRY FROM V_SEQ_QRY;--
OPEN C_SEQ_QRY;--
FETCH C_SEQ_QRY INTO V_SEQVAL;--
CLOSE C_SEQ_QRY;--

END;

That seems like an awful lot of overhead just to assign a variable via
dynamic SQL. Anyone have a better suggestion?

Environment: "DB2 v8.1.1.96", "s050811", "U803920", and FixPak "10";
AIX 5.3)

--Jeff