DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
sriven786
Participant



Joined: 08 Nov 2017
Posts: 37

Points: 285

Post Posted: Thu Apr 04, 2019 5:40 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Unix
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

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54501
Location: Sydney, Australia
Points: 295558

Post Posted: Thu Apr 04, 2019 11:56 am Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
sriven786
Participant



Joined: 08 Nov 2017
Posts: 37

Points: 285

Post Posted: Fri Apr 05, 2019 1:40 am Reply with quote    Back to top    

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)
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours