Managing sequential unique keys

vnspn · Post by **vnspn** » Fri Jul 27, 2007 3:26 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 27, 2007 3:37 pm

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

chulett · Post by **chulett** » Fri Jul 27, 2007 4:49 pm

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.

sachin1 · Post by **sachin1** » Mon Jul 30, 2007 7:06 am

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.

chulett · Post by **chulett** » Mon Jul 30, 2007 7:52 am

Difficult? Impossible.

vnspn · Post by **vnspn** » Mon Jul 30, 2007 9:12 am

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?

chulett · Post by **chulett** » Mon Jul 30, 2007 9:15 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 30, 2007 3:32 pm

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.

Code: Select all

INSERT INTO table (c1,c2,c3,c4) VALUES myseq.NEXTVAL,:1,:2,:3

For this example only columns c2, c3 and c4 would occur on the input link of the stage writing to the database.

crouse · Post by **crouse** » Mon Jul 30, 2007 4:10 pm

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

vnspn · Post by **vnspn** » Tue Jul 31, 2007 12:53 pm

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?

chulett · Post by **chulett** » Tue Jul 31, 2007 1:14 pm

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

crouse · Post by **crouse** » Tue Jul 31, 2007 1:20 pm

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

DSXchange

Managing sequential unique keys

Managing sequential unique keys

Re: Managing sequential unique keys