Creating/updating an unique number for each call of the sequ

[splayer]

I would like to create an unique if for each to my sequencer. I'll be creating a data set file which will initially have 1 value of 0. For every call of the sequencer, it'll be incremented by 1. I thought of a initialization job
which will create the file with 0 value first. I'll have another job which will read and update the dataset. This second job will be the first thing called in the sequencer.

I would like to hear other ideas if anybody has any. Thanks.

[splayer]

Correction: I would like to create an unique ID for each call to my sequencer.

[kumar_s]

We have such one but not exactly the same. We maintain a count in a table. A .SQL file been called each time, which will update the table, by fetching the max of the count and add 1 and update the same.

[ray.wurlod]

You could use either of the SDK Key Management routines invoked via a Routine activity. These emulate a sequence in a database; the next value self-initializes to 1 on the first call, and otherwise is preserved in a record (whose key is the sequence name) in a Repository table called SDKSequences.

[splayer]

ray, where can I read up more about this? I looked in all PDFs but no luck. I am using PX not Server version so my solution has to work in PX version. I searched on this site as well but couldn't find anyone doing something similar.

[vmcburney]

Two methods: set the ID in each sequence job or set it in the sequence job that calls other sequence jobs.

Use a User Variables stage as this is a good way to organise your sequence job code. Inside this stage create a new user variable and in the derivation field call the SDK key routine, passing a code that is unique for each sequence job, and you will get a unique code back again. The routine will handle the retrieval, incrementing and saving of the codes.

Sequence jobs call both parallel and server jobs, most of the entry fields in sequence jobs accept DataStage BASIC code. This lets you call SDK routines from within stages like the User Variables stage.

[splayer]

vmcburney, thank you for your response. I wanted to know where I can read up more about SDK sequences. I couldn't find it in the PDFs.

I have only 1 sequence job (I call it sequencer). This job ties together all my jobs. I'll be calling this sequence job multiple times for different data files and I need to keep track of which file is associated with which call. That's why I need a unique number for each call be stored somewhere that can be tracked.

[ray.wurlod]

The routines are supplied with DataStage. They are documented in the Routines branch of your Repository.

[splayer]

This works. I used KeyMgtGetNextValueConcurrent("MySeqName"). But here is the problem. Is there any way to reset the value to start from a certain value? I know that it stores this value internally.

I can probably write a routine using the KeyMgtGetNextValueConcurrent routine. Any ideas/comments would be appreciated.

[splayer]

Can we update the SDKSequences table?

[ray.wurlod]

Yes.

Search to find examples of the requisite SQL statement.

[RamonaReed]

This works. I used KeyMgtGetNextValueConcurrent("MySeqName"). But here is the problem. Is there any way to reset the value to start from a certain value? I know that it stores this value internally.

I can probably write a routine using the KeyMgtGetNextValueConcurrent routine. Any ideas/comments would be appreciated.

You can reset the value by the using the following:

InitializeKey("Your_Key", 0)

You can place any number where I have placed the zero

[ray.wurlod]

InitializeKey is one of yours, Ramona, it does not come out of the box. It may have been sourced from DSXchange, but it's not part of "standard" DataStage.

[praveenchandra]

my 2 cents

in our application, we have used the keymgtGetnextvalue , but
the key and value are stored in an hashed file
can some one explain what is better, using a table inside Datastage ( I do not know anything about it ) or a hashed file

thanks
Praveen