I'm using TTU 16.20 and Teradata 16.20.
I'm moving data from Redshift to Teradata I'm using the ODBC Operator to pull data from Redshift and then the Inserter Operator to insert into a table which I've created.
I've found that when the table is created with a NOPI the transaction deadlocks. If I give my table a primary index then there are no issues. The script and data are the same.
We'd like to be able to insert into NOPI tables to move the data because during our DDL conversion we can only create a primary index if the distribution on Redshift has a key. If the distribution is ALL, EVEN or AUTO then we can't determine good primary index from the Redshift table easily.
So I'm thinking this is a bug?
Here's the error from the private log:
InsertOperator: TPT10508: RDBMS error 2631: Transaction ABORTed due to deadlock.
InsertOperator: TPT14117: An error occurred while inserting row <26> into the database by Instance <1>
ODBCOperator: disconnecting sessions
I don't know why you get a deadlock, but Inserter Operator is the slowest possible way to load data, because it's single rows inserts.
When you switch to Stream you can apply a high PACK factor and then even a single session is faster than lots of Inserter session.
Thank you for the response Dieter. We have options to use the stream and load operators and those are working great.
A company requested the Inserter operator because they are seeing decent performance for what they need by adjusting the min and max sessions and it also has the advantage of not taking up utility slots. We also added the Update operator at their request which they can use if there is a primary index.
I'm using a Teradata 16.20 VM system and my min and max sessions are set to 1 and 16 respectively. Perhaps this is the issue because this session only has two virtual amps, but I thought Teradata would set the sessions to a max of 2 as a result.
So we can use the Stream and Load operators to load into this NOPI table, but when we use Inserter we get the deadlock condition. All three scripts use the ODBC operator to get the data from Redshift, but the Inserter operator appears to be the one which has this issue.
If you have any suggestions I would appreciate it. Thank you Dieter!!!
Sorry for taking so long to respond. All I can say is that our largest customer has requested it and according to them they are able to get good performance. We give them options to use Selector, Stream, Update or Load operators. I think they might have been uninformed about load slot being used with the stream operator so we'll inform them
As far as the deadlock issue goes, the only thing I'll add is that we don't experience this issue on Teradata 15.10, but do experience it on 16.00 and 16.10. We'll have our customer test on their larger system and it may not be an issue or we'll direct them to the stream opeartor.
Thank you for your response I appreciate it!