All Forums Analytics
Noman Riaz 7 posts Joined 02/15
04 Feb 2015
Parallel Loads to same target table

hi Guys,
 
i have target dimension table DIM_OUTLET which we are loading with TPT on upsert mode.
we have 10 different data sources which are loading data to this dim_outlet.
we want to update this Dimension in every hour, but the bottleneck is that this table get stuck in Mload as more than one source try to load data in this table.
is there any way to avoid such locking or Mload on that table. so that we can run these jobs in parallel.

dnoeth 4628 posts Joined 11/04
04 Feb 2015

You're directly loading into the target table?
Are the sources flat files?
Do they arrive at the same time?
What' s the size of the target and the updated percentage?
 
Maybe switching to STREAM or SQL MERGE

Dieter

Noman Riaz 7 posts Joined 02/15
05 Feb 2015

there are some calculations/ lookups involved too, so we have made seperate ETL jobs for them.
each job take around 30 mins to load to various dimensions, most of time i think around 10 mins would be for dim_outlet.
we have a time shift of 10 - 15 min between 10 sources, but sometimes the second job tries to insert into same dimension table which is being loaded already causing job failure.
 
that why we are looking for parallel load into same table in different jobs.
 

Noman Riaz 7 posts Joined 02/15
05 Feb 2015

and source is a DB2 database tables not flat files
 

VandeBergB 182 posts Joined 09/06
06 Feb 2015

You can't load the same table with multiple jobs at the same time, the first job to run will get a write lock and the remaining jobs will queue up waiting for the preceding write lock to be released.  
In order to achieve what it sounds like you're trying to do, you could load each job into it's own target table, "in parallel", with the calculations etc and then run a final job on TD to complete an ANSI-Merge into the target table
Cheers

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.