Db2 Connector Stage Write Partitioning Method

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Db2 Connector Stage Write Partitioning Method

Post by sriven786 »

Need Experts advise.

We are updating a Db2 Table (Around 200 Columns) based on Where clause which are indexed.

Input: Sequential File
Output: Db2 Connector Stage
Operation: update
Input_File --> Transformer --> Db2 Connector Stage.

Update Statement: Update Table_name set col_1 = ORCHESTRATE.Col_1,
Col_2=ORCHESTRATE.Col_2, .... Col_212=ORCHESTRATE.Col_212

Db2 Connector Stage Partition is currently set to Hash Partitioning on Key Columns (No Sort is performed on these Columns) but the Table has Hash Partition by different Column.

In this case, which partition will be good for db2 Connector Stage (Write: Update Action) ?.

For hash Partitioning, do we need to sort on those 2 Columns?, If we perform Sort in the Transformer on the Key Columns and then use Hash Partition, will it improve performance?.

Appreciate any suggestions as currently we are facing performance issues (Average daily updates is around 500 K and and the job runs for 2 To 3 hours 50 Rows per Second on average.
Venkata Srini
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Why aren't you using DB2 partitioning? This is one of the eight algorithms available in DataStage. You will need to name the partition table.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

Thanks Ray for the Reply. As mentioned, the Table is Hash Partitioned by Different Column (Col: 1) and the Updates in this Job are done by different Columns (Column 12: Column 18 and). And we have Index on Column_12 and Column_18 still the updates are taking longer time (we are updating around 200+ Columns)
Post Reply