DSXchange

Hi,

We were using a DataStage job to update a certain table. We were using the DS Transform "KeyMgtGetNextValue" to generate a unique Id whenever there were new records to be inserted.

Now, a web application has been designed, also through which new records might be inserted. So, we have colloborate between the DataStage job and the web application to create unique Ids whenever new records are inserted.

How can it be designed so that, DataStage job and the web application create a unique Ids in sequence whenever a record is inserted? The web application has an Oracle Sequence created for that column to generate the unique Ids in a sequence. Can that Sequence or anyother technique be used in DataStage to handle this scenario?

Please share your ideas...

Thanks.

DataStage can use the sequence. Specify user-defined SQL in which you provide sequencename.NEXTVAL rather than a parameter marker for the key column.

You both either need to use the Sequence object or you both need to leverage the KeyMgmnt routine. You can't mix the two. We have the 'SOA Edition' of DataStage, so were able to create a web service that allowed another service to pull numbers from the same source that the ETL jobs use.

Without that, you'll need to switch the ETL to using the Sequence object.

hello as your web application is using oracle sequence and datastage was already using KeyMgmnt routine,runtime sync is difficult. so as said by ray query your database and increment the value accordingly to create the new number, this logic need to be implemented for both web services and datastage.

Difficult? Impossible.

The overall application is still in development phase. So things can be very well changed.

We would replace the usage of DS "KeyMgtGetNextValue" by using the Oracle's sequence.

As mentioned by Ray, if we use the sequencename.NEXTVAL in the user-defined SQL, then what should be specified in the derivation of the transformer. Can it be left empty("")? Irrespective of the derivation give there, would it take the value from the user-defined SQL?

You would need to drop all mention of the 'serial' field from the link. Make sure you Annotate this so others know what is going on.

What Craig said. The column whose value will be generated by calling the sequence does not appear in (the final part of) the job design at all. For this example only columns c2, c3 and c4 would occur on the input link of the stage writing to the database.

This will probably muddy the conversation, but...

Do you need the generated unique key anywhere else while processing the particular row? Like inserting children with the new generated key for the parent? If so, you need to go out to Oracle and retrieve the next sequence via a reference lookup from a transformer and then use it like you are using the value from KeyMgtGetnextValue. And this will also kill your performance, but sometimes that's all you can do. Sometimes a good DBA can help with the performance of getting sequences, like caching up more than one at time.

If the inserted row is the only row needing that generated key, then do as Ray and Craig say.

-Craig

Thanks for your post Ray and chulett. We were able to implement it.

Craig, we would be using this generated unique key only for insertion into one table. So, we could do it the way, Ray had said. But I would also like to undertand the technique that you have told. How could I generate a sequence number from the reference link for each incoming record. How could I join each generated sequence number to the primary flow of records?

[this post intentionally left blank in deference to the requested Craig]

Add a reference lookup from an Oracle OCI stage to the appropriate transformer stage.

Use "User-defined SQL Query" something like this:

SELECT SEQUENCE_NAME.nextval FROM dual

And the column meta data is just one column to hold the next sequence returned from the query.

On the lookup link in the transformer, there is no column derivation and the column IS NOT marked as a key value. It looks a bit odd, but correct.

Now you have the sequence value to use as you like.

But, as I said earlier, it will kill your performance. It will be one of the slowest links in your job, or at least I hope it will be.

-Craig