I am helping our Data Migrator guys with some processes and it appears that one of the tables he will update uses a DB2 sequence(?) I'm not exactly sure how that works but the gist is that using the statement VALUES( NEXT VALUE FOR dbname.tbname); it requests the next available sequence number and increments. If I execute that statement in a native SQL tool, it returns the value of the next sequence number (and increments it.) If I stick that same statement into SQL passthru, the sequence is incremented but it does not return any value. I need to be able to see that value for some further processing.

Has anyone used this statement for DB2 (or other) and is there a trick to capturing that returned value in WF (either as a returned data set or in a return code or variable?)

Darin:
I've not used this is DB2, but in Oracle, you do a SELECT .CURRVAL FROM DUAL to get the current value. There might be something similar for DB2. And then, you put the SQL ti increment and the SQL to retrieve into one transaction (to avoid the possibility of getting the wrong value back).

What I was saying is that there is no separate SQL for the retrieval and for the increment. DB2 knows what that statement is supposed to do and does everything necessary in a single step. It works in a native SQL tool, but WF does not seem to return the value, but does increment it. I guess I could possibly create a stored procedure which would require WF to explicitly wait for a returned value from the procedure. Never used a stored procedure, so I don't quite know how that works. Will have to look into that.

Darin,

I've never used DB2 sequnces, but the DB2 documentation implies that you can use these in a standard select statement. So if you wanted to see the sequence value you just created you could do:

select PREVIOUS VALUE FOR my_sequence from any_table

EricH