I am new to Teradata. I am facing an issue while trying to load to the same TD target table from multiple staging tables simultaneously. I searched the net but got confusing inputs. Please help me clarify what my options are regarding the following. We are using TD 15.
I want to load the same target table by reading from multiple staging tables simultaneously. For e.g. one job will read from source table StoreA and load (update/insert) to target table T. Simultaneously there will be another job that will read from staging table StoreB and load (insert/update) to the same target table T at the same time. I want both the jobs to run simultaneously and update/insert the target T without locking. There is no DELETE required. Here is what I think my options are :
Note: As far as data is concerned each of the Staging tables will have data from different stores so there is no possibility that a job will attempt to overwrite another job's data while loading to the same target table simultaneously.
Please advise. Kindly let me know if additional information is required.
#1: The data is already in the system, so why trying to use TPump?
#2/#4: INSERT/SELECT and MERGE always applies a table lock, so the jobs will insert sequentially
#3: Unless it's a PI-based UPDATE it will apply a table-level lock, too
Why do you think that running those inserts sequentially will be much slower?
You should partition the table to avoid full-table-scans for every insert (a future release (15.10?) will finally implement partition level locking.)
Thanks for you reply dnoeth. So isnt there any way by which a table could be updated/inserted concurrently by multiple sessions in TD15 ?
So if I understand correctly for TD15, concurrent updates (to the same table) will be possible if its PI based
concurrent inserts will be possible if the table is partitioned ?
You could build a union view on the source tables and insert into the target table from the union view. As Dieter pointed out, you shouldn't need any utility to load the data once it's in the system.
TD15 or otherwise, updates have to respect any object locks in place at their level, table, row or access. The PI based insert will make the lock duration shorter, but won't eliminate the lock. The PI based update eliminates the need to re-hash the incoming data for distribution in the target table.
So are we saying that if its a PI "value" based update then it will be a row-level lock. Otherwise if the update is performed on the target table even by joining to a Staging table by applying a Join condition on the PI columns it will result in a table-lock ?
So to put it clearly,
Assume tables T1 and T2 both have a PI of column C1.
The following request will use a row-hash lock since its a "value" based update on the PI:
SET T2.C2 = new-value
WHERE T2.C1 = a-value;
The following request will NOT use a row-hash lock (it will use a table-level lock):
SET T2.C2 = T1.C2
WHERE T2.C1 = T1.C1;
The second update even though it uses a join to T1 on PI column C1 will still result in a table-lock ?
On a similar note, Having multiple insert queries fired to insert into the same table, will it result in dead lock by any means?