Oracle plugin not reading parallel
Moderators: chulett, rschirm, roy
Oracle plugin not reading parallel
Hi,
My job reads data from Oracle table by using 4 node configuration, but data is being selected in only one node all other nodes are having just zero records.
issues is:
in datstage directore it shows like
GL001P,1: Output 0 produced 0 records.
GL001P,3: Output 0 produced 0 records.
GL001P,2: Output 0 produced 0 records.
GL001P,0: Output 0 produced 50130860 records.
may I know why above 3 nodes are not reading anything, actually job is using parallel degree in select query to make it fast.
My job reads data from Oracle table by using 4 node configuration, but data is being selected in only one node all other nodes are having just zero records.
issues is:
in datstage directore it shows like
GL001P,1: Output 0 produced 0 records.
GL001P,3: Output 0 produced 0 records.
GL001P,2: Output 0 produced 0 records.
GL001P,0: Output 0 produced 50130860 records.
may I know why above 3 nodes are not reading anything, actually job is using parallel degree in select query to make it fast.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Thanks for your response Ray.
how to program in stage to utilise 4 node configuration while reading data from oracle table partition,
Ray- are you trying to say though datastage has 4 node configuration but orace EE stage can't utilise parallelisam and it will read data with only one node.
Does it mean, by default it will just use one node, if yes.
Is there any work around to improve it.
Thanks for your inputs.
how to program in stage to utilise 4 node configuration while reading data from oracle table partition,
Ray- are you trying to say though datastage has 4 node configuration but orace EE stage can't utilise parallelisam and it will read data with only one node.
Does it mean, by default it will just use one node, if yes.
Is there any work around to improve it.
Thanks for your inputs.
if you see below log from director, it says it's using 4 nodes
main_program: This step has 1 dataset:
ds0: {op0[4p] (parallel GL001P)
=>
/opt/IBM/data/drm_fy_datamart/temp/GL001P.ds}
It has 1 operator:
op0[4p] {(parallel GL001P)
on nodes (
node1[op0,p0]
node2[op0,p1]
node3[op0,p2]
node4[op0,p3]
)}
It runs 4 processes on 4 nodes.
main_program: This step has 1 dataset:
ds0: {op0[4p] (parallel GL001P)
=>
/opt/IBM/data/drm_fy_datamart/temp/GL001P.ds}
It has 1 operator:
op0[4p] {(parallel GL001P)
on nodes (
node1[op0,p0]
node2[op0,p1]
node3[op0,p2]
node4[op0,p3]
)}
It runs 4 processes on 4 nodes.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 46
- Joined: Wed Jun 06, 2007 10:32 am
You can read a non partioned table in parallel using Oracle Connector Stage, by chosing the option enable parallel read- option and chosing the partioned read method -option
So when you chose Oracle connector stage it will read the oracle table in parallel depending on your node configuration.
Correct me if i am wrong.
So when you chose Oracle connector stage it will read the oracle table in parallel depending on your node configuration.
Correct me if i am wrong.
yup Chulett,
Tables has been partioned and I am reading from single partition.
but I am using parallel degree concept and trying to read prallel.
SELECT
/*+ PARALLEL(gl.tgltran, 35) */
CACCOUNT, EFFDATE, ENTITY, AU, APPLICATION_ID, SOURCE_ID, TXN_AMT, DEBIT_CREDIT, SERIAL_NUMBER, WORK_DATE, BATCH_NUMBER, SUBMIT_AU, DESCRIPTION, REROUTE_AU, REJECT_CACCOUNT, UISN_NUMBER, GLIDE_CREATE_DATE, CREATED_BY, CREATED_BY_NAME, BATCH_TYPE, LOAN_NUMBER, ORIG_CODE, REV_EFF_DATE, REV_FLAG FROM gl.tgltran PARTITION (#PartitionName#)
partiton table = gl.tgltran
Is this correct, giving the table name to partition table property
Tables has been partioned and I am reading from single partition.
but I am using parallel degree concept and trying to read prallel.
SELECT
/*+ PARALLEL(gl.tgltran, 35) */
CACCOUNT, EFFDATE, ENTITY, AU, APPLICATION_ID, SOURCE_ID, TXN_AMT, DEBIT_CREDIT, SERIAL_NUMBER, WORK_DATE, BATCH_NUMBER, SUBMIT_AU, DESCRIPTION, REROUTE_AU, REJECT_CACCOUNT, UISN_NUMBER, GLIDE_CREATE_DATE, CREATED_BY, CREATED_BY_NAME, BATCH_TYPE, LOAN_NUMBER, ORIG_CODE, REV_EFF_DATE, REV_FLAG FROM gl.tgltran PARTITION (#PartitionName#)
partiton table = gl.tgltran
Is this correct, giving the table name to partition table property