Oracle read and write rates

[hobocamp]

Greetings -

I've been in the forums all morning, and can't quite find a problem similar to the one I'm encountering.

I have a simple job that uses an Oracle connector to read a table for a few employee details. It also calls an Oracle procedure to generate a user id, for any records that don't have one.

The data is then sent through a transformer, and finally does a table updated.

The problem I've encountered is that it's (remotely) possible for the procedure to generate the same ID if two similar names come through within the same job run. (For example Tom Smith and Terry Smith would both generate smitt10, instead of smitt10 and smitt11.)

I've experimented with Record Count, Array Size, and Isolation level. But so far haven't been able to get the records to create unique IDs in the situation I illustrated above. I guess basically what I need is for the job to read a single record and commit it, before it brings the next record through the process. Any suggestions on how I might accomplish this?

Thanks in advance.
Tom

[Mike]

To summarize, you need 1 input record to go through the entire pipeline before you allow the next input record to enter the pipeline in order to prevent your stored procedure from generating the same user id for similar inputs.

I don't know if DataStage's wave concept can be applicable here, but it seems you want each input record to be its own unit of work, so using waves and end of wave markers seems like it could be a possibility.

Mike

[hobocamp]

Thanks Mike. Yep - your summary is exactly right. And I'd wondered if Waves might play into it. I'll keep that in mind for the future.

In regards to my current problem, I was able to solve it within the transformer.

I made the initial data selection with the Oracle stage (which as I mentioned can result in duplicate IDs). Then within the transformer, I used the time-honored tradition of the Current and Previous stage variables, along with a counter. So whenever I find a non-unique ID (the data comes in sorted), I simply increment the counter and concat it with the ID's root.

I'm going to mark this topic as resolved with a workaround.

Thanks again.

Tom

[hobocamp]

Thanks Mike. Yep - your summary is exactly right. And I'd wondered if Waves might play into it. I'll keep that in mind for the future.

In regards to my current problem, I was able to solve it within the transformer.

I made the initial data selection with the Oracle stage (which as I mentioned can result in duplicate IDs). Then within the transformer, I used the time-honored tradition of the Current and Previous stage variables, along with a counter. So whenever I find a non-unique ID (the data comes in sorted), I simply increment the counter and concat it with the ID's root.

I'm going to mark this topic as resolved with a workaround.

Thanks again.

Tom