Calling Stored Procedure without loosing parellelism
Posted: Thu Aug 06, 2009 3:35 pm
I'm trying to load and Oracle 10g table using Datastage 8.0.1. My problem comes in when I try to load in replace mode the DBA's have set the privleges so user accounts cannot truncate tables. To truncate a table I have to run a stored procedure.
I solved this problem by using the "Open Command" option in the Oracle Enterprise stage. But as with many things I neglacted to think about parellelism. The stage run the truncation procedure once per node. So in production its tring to trunc the table 4 times simultaineously. This does not work real well as one can imagine.
I've looked into using the Stored procedure stage but it seems like if I put it in my job I'll loose parellelism and have to rehash the partitions to load in parellel.
My current alternative is to build an independant job to preceed my oracle load that will run sequencially. And whose only function is to truncate the table. It seems like there should be a better solution than that.
thanks to any pointers or suggestions,
Jack
I solved this problem by using the "Open Command" option in the Oracle Enterprise stage. But as with many things I neglacted to think about parellelism. The stage run the truncation procedure once per node. So in production its tring to trunc the table 4 times simultaineously. This does not work real well as one can imagine.
I've looked into using the Stored procedure stage but it seems like if I put it in my job I'll loose parellelism and have to rehash the partitions to load in parellel.
My current alternative is to build an independant job to preceed my oracle load that will run sequencially. And whose only function is to truncate the table. It seems like there should be a better solution than that.
thanks to any pointers or suggestions,
Jack