Surrogate Key Generation using DS routine

deepa_shenoy · Post by **deepa_shenoy** » Fri Sep 25, 2009 1:13 am

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

chulett · Post by **chulett** » Fri Sep 25, 2009 7:01 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 25, 2009 6:15 pm

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

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

chulett · Post by **chulett** » Fri Sep 25, 2009 6:18 pm

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

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

deepa_shenoy · Post by **deepa_shenoy** » Mon Sep 28, 2009 9:33 am

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

chulett · Post by **chulett** » Mon Sep 28, 2009 10:24 am

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.

deepa_shenoy · Post by **deepa_shenoy** » Mon Sep 28, 2009 11:38 pm

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

ArndW · Post by **ArndW** » Tue Sep 29, 2009 12:42 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 29, 2009 12:47 am

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.

deepa_shenoy · Post by **deepa_shenoy** » Tue Sep 29, 2009 2:53 am

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

ArndW · Post by **ArndW** » Tue Sep 29, 2009 3:28 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 29, 2009 5:05 am

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.

deepa_shenoy · Post by **deepa_shenoy** » Tue Sep 29, 2009 6:11 am

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.

chulett · Post by **chulett** » Tue Sep 29, 2009 7:37 am

'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.