DSXchange

Hi All,

I am completely new to DataStage and I am yet to learn the basics.

I would like to know how to generate surrogate keys in a sequential order using DS Routine. Do we use KeyMgtGetNextValue()? If yes, how exactly do we go about using it in the Transformer stage?

I used a Stage Variable, but am unable to give the routine-KeyMgtGetNextValue() in its derivations column.

Regards,
Deepa

Yes, that is exactly what that routine does. How were you 'unable to give the routine'? It should work fine there or any other transformer derivation.

Not in a parallel job using a parallel Transformer stage, however.

The Key Management routines are suited only to server or BASIC Transformer stages.

Dang, missed that it was a Parallel question. [sigh]

As with most things PX, you've got a stage for that.

Thanks guys!!

I realised its possible only in Server Jobs and not in Parallel Jobs.

Is there any simple efficient method to generate Surrogate Keys in sequence, in a parallel job, without using Database Sequence??

I tried using the Flat File option but it generates surrogate keys like 1,1001,2001,2002,3001,4001,5001 etc which I am guessing is because of the 8 node configuration.
Can I use the Flat File option and get 1,2,3,4.. as the sequence?

Thanks in advance.

Deepa

What 8.x version do you have? There is something new in 8.1 (from what I recall) that will allow the generation of a surrogate key with a call to a new function in the Transformer stage. There's also this FAQ posting if you want to do it in a manual fashion.

Hi,

Thanks for the input. I am trying the other method too.

I am using version 8.1. Is there any routine available here?

Regards,
Deepa

Don't use a routine to do this in PX. Either the surrogate key generator or you can also use functionality for surrogate keys built into the transform stage.

The function is NextSurrogateKey() and requires you to have created a state file or to use a database sequence. Full details are to be found in the manual.

Hey,

I used the manual method, but the records are not in order.

When I use the Sorter Stage, the records are still inserted into the target table in a jumbled manner. How to resolve this?

Thanks in advance.

Regards,
Deepa

It is not quite clear what you are having problems with. Surrogate key generation is unique across all nodes and ordered within each node. If you have multiple nodes and write to a sequential target your ordering will not sequential. Either run on 1 node or put a sort on the surrogate key into your job if the ordering is important.

It is a fundamental tenet of database technology that the database engine determines the order of row storage, not you. If you want sorted values when you query the table, specify this requirement in the query.

I used a Sorter and in this stage, I changed the Partitioning in the Input tab to Round Robin, and selected the key based on which it should sort.

This resulted in ordered records in the target table.

'Ordered records in the target table'?

As noted, there's really no such thing and it is generally a waste of time to sort the data before writing it to a database.