DSXchange

Hi All,

Netezza as Target Database

Trying to design a job where I want to insert records to a target table from a staging table using the Netezza Connector Stage.

As part of this I need to get the Next Value for a Sequence for every record and then load to the table.

I found this article but how can we write the user defined statement as we do not know the name of the TWT (Temporary Work Table)

"insert into [[table]] SELECT next value for sequence_name, COL1, COL2, COL3 from [[twt]]"

Any suggestions on how to do this in Datastage?

Thanks,
NV

I for one have no idea, being happily Netezza ignorant. In your shoes I'd start by asking your Netezza DBA as it really isn't a DataStage question.

It is a Datastage question as this needs to happen from the Netezza Connector stage

Is using the Surrogate Key Generator a recommended approach to avoid this scenario of using a Netezza Sequence?
My concern is since it File based (State File) and we could run the risk of file corruption and am also not sure of the performance impact?

nvalia wrote:It is a Datastage question as this needs to happen from the Netezza Connector stage

Just as an FYI, that part isn't really relevant. Your question to start with is how do you do it regardless of the tool, not how do you do it in DataStage. That aspect comes later and is usually not the tricky part. Do you not have a DBA to ask / willing to help?

Sure, the Sequence Generator is an option, that's what it is there for. Biggest question in my mind is concurrency - would you have multiple processes running simulataneously that would need access to that sequence?

Concurrency, No - I can create a separate State File for each dimension so only the associated job would access the corresponding Surrogate key and no other process would access that sequence at the same time

But again is this a good practice from Datastage perspective, to have say 10 State Files, one per Dimension?

You do want separate state files for each target, yes, just like each would typically have its own unique / dedicated sequence generator in the database. Also, see if this post helps with your TWT question, the last reply specifically.

Thanks Chulett.
I am following that in context of TWT and I have no issues with the access/security related side of things..just that using the Netezza Sequence from within Datastage seems to be a problem.

Your official support provider is also a resource you can leverage for help with this. They should be able to clarify the syntax / properties you'd need in the connector to make that all work.