community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

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]

 

 

 

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.

Labels