Db2 Connector Stage Write Partitioning Method
Posted: Thu Apr 04, 2019 5:40 am
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.
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.