Calling Stored Procedure without loosing parellelism

[jacksimpk]

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

[chulett]

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.

I'll be curious what others have to say but to me this is a perfectly valid solution and one I've put in place multiple times. I typically have a multi-instance server job as one of the 'Utility' jobs that just truncates a table and the InvocationId is the name of the table to truncate. Works well everywhere I've used it so far.

Food for thought.

[kumar_s]

Why not Truncating the table on Before job subroutine with a sqlplus call?

[kumar_s]

I was searching for something and replied to this post on reflex without noticing that the post is an year old.