Get Netezza Sequence Number in Job

nvalia · Post by **nvalia** » Wed Jan 21, 2015 10:10 am

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

chulett · Post by **chulett** » Wed Jan 21, 2015 1:10 pm

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.

nvalia · Post by **nvalia** » Wed Jan 21, 2015 3:19 pm

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?

chulett · Post by **chulett** » Wed Jan 21, 2015 3:30 pm

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?

nvalia · Post by **nvalia** » Wed Jan 21, 2015 3:44 pm

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?

chulett · Post by **chulett** » Wed Jan 21, 2015 3:46 pm

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.

nvalia · Post by **nvalia** » Wed Jan 21, 2015 3:53 pm

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.

chulett · Post by **chulett** » Wed Jan 21, 2015 4:33 pm

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.