Using database sequences (DB2)

We need to use database sequences for unique IDs since they need to be shared between multiple processes, including database stored procedures.

I can select a single NEXT VALUE from the sequence in an Input tool, but it only returns one, not one per row in my data stream.

I think I need to add the correct syntax* to my Output tool, but I can't figure out how.

I investigated Dynamic Replace, but I think it will put the command in as a text string, not in the SQL itself.

SELECT

*NEXT VALUE FOR [SCHEMA].[SEQUENCE NAME],

COLUMN2,

COLUMN3,

etc

FROM [SCHEMA].[TABLE]

Output

Accepted answers

All comments

Bharat_andalam

Hi Jeff

I would like to know whether you want to load from source to target or pulling into the source.

jeffbusch

The goal is to be able to use the sequence in the INSERT to the target.

I may have not have made it clear, this is for building primary keys in the target table.

However, as I said, there are other processes in the database that use the same sequence to build primary keys in other tables so it needs to be shared across processes.

NicolasFabre81

I have the same issue while trying to insert in my postgreSQL table using indb tools :

My Id_rupture has a default value made with a sequence to generate the unique IDs.

Is there any way to have the sequence play his role ?

Branka

I used formula in DB to add sequence.

Capture.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703