We have a situation.We want to insert data in same one single table with 50 instances.Single instance may have 100000 rows.Each of these 50 instances will read and write at same time and will be of different request_id.We are planing to have target table as PPI on request_id so that all instances will work independently.Is it possible or locking will be on table level only and partition will not help in this situation.
Could anybody give some good solution for this situation.
Please describe what you mean by an "instance"? And what is the goal of having 50 instances rather than feeding all the data to a single load utility execution?
On Teradata 15.10 partition level locking is a new feature. Prior to 15.10 any bulk write operation will be a table level lock. You could start all 50 of your jobs but they would be serialized by the database on a table level write lock.
Actually you may think it as if 50 different teams wants to load data with same userid in single table..also they wan to read this table at same time.wats the best way to achive this..is partition a solution as each team have unique request id
Locking level will be at the table only, however you can get performance improvement while accessing the rows using partition columns. Basically you can request all 50 requests/instance at the same time, However Teradata will process them in the order it was received all other requests will be queued.
Also I am not sure about partiton level lock introduced in 15.10, you may want to try that one.
You can use partitions to accomplish the loading part but only if your system is running Teradata 15.10.
All users may read as much as they want as long as they utilize access locks - specify in views or on the query or via set session isolation.
It is possible to have TPT read multiple files into a single load into the table. So if the users create files in a known place and a TPT job is run periodically to pick up any/all files currently available for loading then the effect can be achieved without each separate team running their own load.
@ToddAWalter do you know if I have to change some dbscontrol parameter like NoDot0Backdown to enable this partition level lock on TD 15.10? I am doing some tests and Teradata still doesn't use this lock to do multiple insert/selects into the same table.
Is the optimizer able to determine the partition to lock at parsing time? Think of it like static partition elimination on a SELECT.