Passing SEQUENCE NUMBER start from local variable RPGLE -> DB2

Question

im trying to use a number stored on a local varibale on my RPGLE program as the start number for a DB2 sequence. Is this posible?. Something like:

Exec SQL
   create sequence MySequence
   start with :MYVAR
   increment by 1
   no maxvalue
   no cycle;

is a no-no for the compiler. Any ideas?.

Last, using DB2 how can i get the LAST number of the sequence after using it? I need to save that number to a DTAARA for keeping.

Thanks in advance,

Answer 1

You can't use a host variable in the start with clause of the create sequence statement. But that statement can be dynamically prepared. So you could do this:

dcl-s myvar          Packed(5:0);
dcl-s stmt           Varchar(512);

stmt = 'create sequence MySequence ' +
          'start with ' + %char(myvar) +
          'increment by 1 ' +
          'no maxvalue ' +
          'no cycle';
exec sql execute immediate :stmt;

Generally concatenating an SQL statement like this is an SQL injection risk, but if myvar is a numeric variable, then, in this case, there is no risk of injection.