DSXchange

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.

That's how the Oracle Enterprise stage works, unless the table is a partitioned table and you've programmed that knowledge into the stage.

actually I am reading data from only one partition by using where caluse.

and I have used parallel degree concept in select query but here it should share load across nodes right.

How can I make data shared between all 4 nodes to improve performance

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.

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.

Yes, but Oracle only gives a single stream from the result set.

Have you set the Partition Table property?

Have you tried using the Oracle Connector stage (new in version 8.1)?

oracle enterprise stage by default uses single node to read the data .If you want to read in parallel then your table should be partitioned and you have select that option in stage .

Ray ,

How different oracle connector to oracle enterprise stage ?

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.

I am using datastage 8.0.1 and it doesn't have oracle connector stage.

Is there any other way to implement parallel concept here.

yeah, partition table property has been set and given table name there.

Your table has to be partitioned and you need to be reading from multiple partitions. Sounded to me like you've constrained your query to a single partition.

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

Your parallel hint gets you a parallel query - parallelism within the database - but has no bearing on DataStage or the nodes it uses. 35 seems a wee bit high to me, btw.

how to do that parallelisam in Datastage for oracle plugin in v8.0.1