Get nextval from sequence using dynamic SQL

[jefftyzzer]

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 A_SEQUENCE 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

[jefftyzzer]

Oops: Where I have

'VALUES NEXTVAL FOR A_SEQUENCE';--

it should read

DECLARE V_SEQ_QRY CHAR(29) DEFAULT 'VALUES NEXTVAL FOR A_SEQUENCE';--

--Jeff

[Serge Rielau]

In DB2 9.5 you can do a dynamic SET statement:
'SET ? = NEXT VALUES FOR myseq'
and then PREPARE/ EXECUTE INTO.
In DB2 9.7 I think you can use a straight:
EXECUTE IMMEDIATE 'SET ? = NEXT VALUE FOR seq' INTO myvar;

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

[jefftyzzer]

OK, something to look forward to ;-). It sounds like the approach I've
presented here is the one I need to use as long as I'm using v8. Fair
enough.

Thanks,

--Jeff