Stored Procedure to target stage Logic

angelnelson · Post by **angelnelson** » Tue Sep 09, 2008 8:26 am

Hi,

I am calling a stored procedure which will be executed for each row in the source in my job. Based on the return values of this procedure, the records will be inserted/updated into the target table.

I have a situation like the link from procedure to target should work on row by row. That is, the procedure will return the first row value, based on this insert/update will be performed. After oracle commit, Stored procedure will process the second row and so on.

I thought of doing it in a loop activity by calling the stored procedure as a seperate job. But afraid about the performance since to process a single row a job would be called.

Kindly give your valuable suggestions on the different ways of solving it.

Your help is highly appreciated.

Thanks in advance..

ray.wurlod · Post by **ray.wurlod** » Tue Sep 09, 2008 3:06 pm

The obvious question is "why not just use a SELECT"?

angelnelson · Post by **angelnelson** » Tue Sep 09, 2008 10:36 pm

ray.wurlod wrote:The obvious question is "why not just use a SELECT"? ...

Hi Ray,

Thanks for your reply.
I am migrating the OWB code into Datastage 8. My requirement is to use the existing oracle procedures. The existing procedure will be doing the SCD logic Type 1 or type 2.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 09, 2008 11:26 pm

Resist silly requirements. Either you're migrating the OWB code into DataStage or you're not. Which is it?
You have version 8. Therefore you have the DataStage SCD stage, which can do both Type 1 and Type 2.

fredy · Post by **fredy** » Wed Sep 10, 2008 4:25 am

ray.wurlod wrote:Resist silly requirements. Either you're migrating the OWB code into DataStage or you're not. Which is it?
You have version 8. Therefore you have the DataStage SCD stage, which can do both Type 1 ...

Ray, Thanks a lot for your explanation.

Even if i implement stored procedure logic in datastage, i am facing one problem.
My source is having multiple records for a single id in the target. When the first record comes, it should be inserted into the target. If any more record with the same id comes from the source then, the first inserted record should be updated by the new incoming record.

In a single run, the above logic needs to be implemented.

Kindly suggest me if there is a way to implement it.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 10, 2008 5:51 am

The SCD stage takes care of all of that, by using memory-mapped copies of the dimension tables. Read Vincent's blog or the IBM collateral to see how it's done.

angelnelson · Post by **angelnelson** » Mon Sep 15, 2008 4:18 am

ray.wurlod wrote:The SCD stage takes care of all of that, by using memory-mapped copies of the dimension tables. Read Vincent's blog or the IBM collateral to see how it's done. ...

Hi Ray,
Thank you very much for your response. Now I am trying to solve it by SCD stage.
My requirement is as follows:
"If dimension record found, then check for TYPE2 column. If this doesnt change then update the existing record with new one. Else maintain the history and insert the new record. If dimension record is not found then insert the new record."
Is this possible with SCD stage?
Kindly give me your suggestions.

ray.wurlod · Post by **ray.wurlod** » Mon Sep 15, 2008 4:36 am

Just mark the column as "Type 2 change". You will also need some way to identify the expired record - either a date or a flag.

Code: Select all

           Dimension
             |
             |
             V
source ---> SCD_stage ---> Fact
             |
             | 
             V
           Dimension