DSXchange

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.

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.

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)