Using database sequences (DB2)

[jeffbusch]

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]

[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.

[Bharat_andalam]

Please convert your workflow into INdb and then before target use the formula tool and add a column for the primary key and  populate with the next value.

[jeffbusch]

I agree that normally this would be the solution, but since I'm working in DB2 I don't have this option.

[jeffbusch]

To update on my progress and help anyone else who has the same issue:

I have successfully tested inserting a -1 as my unique ID and then adding a Post SQL statement that updates the -1s with the NEXT VALUE for the sequence.

This is a OK workaround, but for a large number of rows this will be slow, so I would still like to implement a true solution if possible.

[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.