This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
*NEXT VALUE FOR [SCHEMA].[SEQUENCE NAME],
I would like to know whether you want to load from source to target or pulling into the source.
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.
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.
I agree that normally this would be the solution, but since I'm working in DB2 I don't have this option.
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.