Using ORACLE PARALLEL DML on OCI Stage

[eoyylo]

Hi my problem is this :
I must load a large volumes of data from a flat file into an Oracle Table directly (without a transformer stage) like this :


[FLAT FILE]-------------------->[OCI8]

I try to use an Oracle HINT directive into the Before page and into the SQL-User Defined page of stage:

[BEFORE PAGE] : ALTER SESSION ENABLE PARALLEL DML
[SQL-USER DEFINED] : INSERT /*+APPEND PARALLEL (BULK,5)*/ INTO ......

During the execution of job I monitored the sessions opened on Oracle server end I verify the only one session was opened.

What is the rigth way to use the PARALLEL DML feature of ORACLE 9.x?

Thanks a lot.
by[?]

[chulett]

Just out of curiousity, have you considered using the Bulk Loader? It supports both the 'direct' and 'parallel' options, I believe.

-craig

[ray.wurlod]

I would expect PARALLEL (BULK,5) to require five output streams, not one. You could do this with a Transformer stage, with perhaps round robin allocation of input rows to output links.
MOD(@INROWNUM,5) = 0
MOD(@INROWNUM,5) = 1
MOD(@INROWNUM,5) = 2
MOD(@INROWNUM,5) = 3
MOD(@INROWNUM,5) = 4



Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[mhester]

You could also use the Link Partitioner stage (active stage) if you are at a version of DS that supports it. This will allow you to specify up to 64 output links. You can also choose the method (round robin, hash etc...) partition the incoming data. You could then use the Link Collector (active stage), if so desired, to collect the links and create one output link.

Regards,

Michael Hester

[ray.wurlod]

There are many things that one can do once using Parallel Extender. The fact that the OP had gone to the trouble to code the hint in an OCI stage suggested to me that this was a server job, not a parallel job.