Getting Last Generated SK from Surrogate Key Stage

jasonchan · Post by **jasonchan** » Wed Oct 13, 2004 10:58 pm

Hi all,

I'd like to know what's the best way to get last generated SK for the SK stage. This will be used as the "Start Value" property of the SK stage.

I searched through the fourm, and found someone is doing a SQL query
over the table, and get the max. value of the S.K. and then get this value
back to DS through DSExecute & DSSetParam.

Is it a good way? Any other better ones?

Thanks very much.

ray.wurlod · Post by **ray.wurlod** » Wed Oct 13, 2004 11:19 pm

Welcome aboard! :D

Getting the current maximum value from the database is the only totally safe way.

Some advise the code-based solution you propounded; others suggest using a DataStage job to load the result in to a hashed file. The advantage of this approach is that you can have a separate record for each table; use the table name for the key. It can be looked up in a later DataStage job, or read using DataStage BASIC, whichever you prefer.

Another option is to preserve a persistent of the most recent SK loaded by DataStage somewhere in or near DataStage, but this does not offer protection against others loading new SK values by other means, for example using a Sequence.