Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

Tools
N/A

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 !!!

5 REPLIES

Re: Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

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
N/A

Re: Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

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

Teradata Employee

Re: Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

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

Re: Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

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

Teradata Employee

Re: Can we load the same table concurrently using TPT Update operator by setting seperate checkpoint files

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