Teradata Connector Read/Write issue

[shershahkhan]

I have to redesign a shared container, and in the shared container i have to first insert in a table through Teradata connector stage and then export some data once the insert is compete, but in Teradata connector i can't read and write at the same time. Is there any way i can insert the data first and then trigger the read in another TD stage which should wait for the insert to be completed first in the same shared container?

[hamzaqk]

i guess you can split the job into two one for insert and one for export and then handle this in the sequence? or write a trigger ??

[shershahkhan]

Constraint is i have to use the same shared container

[ray.wurlod]

Insert then select is not permitted in the one stage or the one job (or, therefore, the one shared container) anywhere in a parallel job, because it is a "blocking operation" that thwarts pipeline parallelism.

[toshea]

It should be possible to do what you need using two Teradata Connectors in the same shared container. Have the first Teradata Connector do the Insert and add a reject link. On the reject tab, specify that Success rows should be sent down the reject link. Connect the other end of the reject link to your lookup. Is the lookup reading from the same table into which you inserted? If so, you may want to set the lookup Isolation level to Read uncommitted so that it can do a dirty read for the row that was inserted. The connector that inserted the row will hold a write lock on that row until its transaction commits. If you don't want to do a dirty lookup, then set the Transaction Record count in the first connector such that it will commit immediately after inserting, because the lookup will block on that row until the transaction commits.

Another solution is to do the INSERT and SELECT in the same Teradata Connector. Draw the connector on the canvas as a sparse lookup, and put both the INSERT and the SELECT in the Select statement property with a semi-colon separating the two statements. The connector will execute both statements together as a multi-statement request. Ignore any warning indicator from the stage gui, since the gui is not expecting you to put INSERT syntax in the Select statement field.

[shershahkhan]

Thanks toshea i already did the reject link solution, but its giving a problem, here is what i did in the job

1) Insert into a temp table(Natural Keys)
2) In the after SQL, generate Surogate key for these natural keys in a specific way, then update some tables
3) Reject the rows on success
4) Lookup the surogate key values for the rejected rows(Which were inserted in the after SQL part)(Doing a sprase lookup here)

the result is not coming correctly each time, because the reject rows are thrown after the FASTLOAD is done and sometime before the point (2) above, so for some of the keys surogates keys are not found in the lookup, i also tried to get exclusive lock in the before SQL on the lookup table but also got some issues with it.

I will try the option Readcommit etc and will see if that get the job into working or not.

[toshea]

You cannot use Bulk mode for the reasons you just mentioned:

- The connector does not know which rows were successfully inserted until the end of the job when the load completes.
- The connector sends the success rows down the reject link before executing any After SQL.

Why are you using Bulk mode? How many rows are we talking about? Is it possible to use Immediate mode? Also, is it possible to do both the Insert and surrogate key generation in the User-defined SQL property instead of doing the surrogate key generation in After SQL?

I'm not sure if there's a way to do it with Bulk mode other than doing the Insert and lookup in separate jobs executed in sequence. One thing you could try is having the After SQL insert into a queue table after the surrogate key generation, and the lookup can query that queue table. The lookup's query will block until a row appears in the queue table, so you know the surrogate key generation is done when the lookup completes.

[toshea]

Another thing you could try is to have the first connector acquire an exclusive lock on the lookup table in Before SQL, and have it release that lock in After SQL after the surrogate key generation. The second connector's lookup will block until the first connector releases its lock. In order for this to work, you would need to use Teradata transaction mode in the first connector. When you use ANSI mode, the connector explicitly does a COMMIT at the end of any After SQL, and that commit would release your exclusive lock. But when you use Teradata mode, commits are implicit after each statement. You can start an explicit transaction by issuing a BEGIN TRANSACTION in the Before SQL before you acquire your exclusive lock on the lookup table. You would do your surrogate key generation in the After SQL followed by an END TRANSACTION to release the lock.