All Forums Tools
rjam 2 posts Joined 12/10
11 Sep 2013
Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

 is this possible.I think as Update creates a Table level locking this scenario is not possible.
 
Please provide your valuable thoughts.
 
Thanks in Advance !!!

M.Saeed Khurram 544 posts Joined 09/12
12 Sep 2013

As you know that Update Operator uses table level locks, it is not possible for an update operator to load the same table concurrently. 
further when the apply statement starts the both update instances will try to get lock on the same table and the script will result in a failure!
 

Khurram

rjam 2 posts Joined 12/10
12 Sep 2013

Cant we handle this by setting up SLEEP and TENACITY  interval when we run.

feinholz 1234 posts Joined 05/08
12 Sep 2013

Yes. That will be one way of queuing up jobs using the Update operator when multiple jobs want to target the same table.
(I was assuming the original question implied you wanted to jobs at the same time to load the same table, which cannot be done.)

--SteveF

sreehere 2 posts Joined 03/13
06 Feb 2014

Hi,
 
Is it possible to execute a 'Delete standalone' using update operator and another Update in the same TPT job
Define delete_operator
(

Type Update

Schema *

Attributes

(

integer maxsessions 

,integer minsessions 

,integer maxdecimaldigits

,varchar targettable 

,varchar tdpid 

,varchar username

,varchar userpassword 

,varchar errortable1

,varchar errortable2

,varchar logtable

,varchar worktable 

,varchar workingdatabase 

,varchar DeleteTask = 'Y'

);

 

Apply step_Delete
('Delete from tablex where col1 > 123;')

To Operator (Delete_Operator [1]);

);
 

 

Define Operator Export_Operator

Description 'TPT  Export Operator'

Type Export

Schema Schema

Attributes

(

integer maxsessions 

,integer minsessions 

,integer maxdecimaldigits

,varchar tdpid 

,varchar username

,varchar userpassword 

,varchar workingdatabase

,varchar SelectStmt = 'Select col1, col2 ...

 

                       From tablex

 Where col1 = 456;'

);

 

Define Operator Update_Operator

Description 'TPT  Update Operator'

Type Update

Schema xyz

Attributes

(integer maxsessions 

,integer minsessions 

,integer maxdecimaldigits

,varchar targettable 

,varchar tdpid 

,varchar username

,varchar userpassword 

,varchar errortable1

,varchar errortable2

,varchar logtable

,varchar worktable 

,varchar workingdatabase 

);

 

Step Apply_Update

(Apply

('Update  tablex 

              Set col3 = 'abc'

 where col1 = :col1

                and col2 = :col2

                and col3 = :col3;'

            )

 To Operator (Update_Operator [1])

 Select * From Operator (Export_Operator [1]);

);

 

 

Job is failing with error

 

 RDBMS error 2550: MLoad error: bad internal status - Delete Mload Started while Import Mload suspended

 

 

Please suggest a workaround

 

feinholz 1234 posts Joined 05/08
06 Feb 2014

I believe Teradata is giving you the answer.  :)
Teradata locks the table while these operations are taking place.
So, the answer is no. You cannot have 2 TPT jobs using the MultiLoad protocol accessing the same table at the same time.
 

--SteveF

You must sign in to leave a comment.