Hi!
I've got very stupid problem using STP stage - when I use the STP stage to call an Oracle procedure the procedure (probably) hangs or maybe it's better to say runs forever. When I call the same procedure (which takes no paramters and just makes some selects on database) from Oracle client it runs perfectly. So the question is are there any known differences between calling a storeproc from inside of Data Stage and from Oracle client?
More details:
Stored Procedure Name is: DATA_STAGE_API.DISTRIBUTE_XXX
Procedure type is defined as: Transform
Generate procedure call: NO
Stored procedure statement is: BEGIN DATA_STAGE_API.DISTRIBUTE_XXX(); END;
Transaction isolation level: None
Regards.
Pawel Janowski
STP stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 6
- Joined: Thu Mar 27, 2008 7:18 am
Thx,
Details about the job design:
- the overall design is very simple - I want to load data from flat files to staging tables and then redistribute data to target tables using the stored procedure (without any parameters)
- and so I use STP stage (at the end of that sequence) - in a job activity I pass the name of the server job which consists of STP Stage with output passed to sequential file stage (but this is just error code and message text).
While debugging I found that the procedure stucks on simple selects (as SELECT Count(*) from XXX) - those select executed from Oracle client work fine and pretty fast.
Any ideas, pls?
Regards.
Pawel
Details about the job design:
- the overall design is very simple - I want to load data from flat files to staging tables and then redistribute data to target tables using the stored procedure (without any parameters)
- and so I use STP stage (at the end of that sequence) - in a job activity I pass the name of the server job which consists of STP Stage with output passed to sequential file stage (but this is just error code and message text).
While debugging I found that the procedure stucks on simple selects (as SELECT Count(*) from XXX) - those select executed from Oracle client work fine and pretty fast.
Any ideas, pls?
Regards.
Pawel
Pawel Janowski
-
- Participant
- Posts: 6
- Joined: Thu Mar 27, 2008 7:18 am
Problem cause
Hi!
The problem was outside of DS - the stored procedure was sometimes inefficient.
Pawel
The problem was outside of DS - the stored procedure was sometimes inefficient.
Pawel
Pawel Janowski