Error 3668 and Retry Command

Database
Fan

Error 3668 and Retry Command

Hi,

I have sometimes an error Failure 3668 A table listed in the Dictionary is not present when trying an Insert

Sometimes an error Failure 3803 Table '...' already exists when trying a Create MultiSet Table

That's because there are a lot of operations with sessions creting and new sessions, to fast.

So I'm trying to skip these errrors but I'm really not sure of this code, in this case everything is happy, but if I have an error on the Create Multiset table will the retry works ? and if I have an error 3668 will the retry works ? It have to works in this case and not for other errors. Thanks for your help !

.SET Retry ON;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
Create MultiSet Table ....

) Primary Index ( .. )
;
 *** Table has been created.
 *** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET Retry OFF;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.if errorcode <> 0 then .goto err_create
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET Retry ON;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
Insert Into ...
Select
...
From ( ... )   
;
 *** Insert completed. 51453 rows added.
 *** Total elapsed time was 1 second.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.if errorcode = 0 then .goto ins_ok
.goto ins_ok
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.if errorcode <> 3668 then .goto err_ctrl
 *** Skipped.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET Retry OFF;
 *** Skipped.


Accepted Solutions
Teradata Employee

Re: Error 3668 and Retry Command

Yes, VOLATILE tables are only available within the session that creates them, so concurrent sessions can use the same TableName with no conflict.

If your job always does a new LOGON, there's no need to attempt a DROP prior to CREATE either.

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Error 3668 and Retry Command

Multiple concurrent sessions creating, using, and dropping the same table sounds like a recipe for losing data.

 

Instead of trying to work around the issue via retry, why not look to avoid the problem? For example, why not use VOLATILE or GLOBAL TEMPORARY tables?

Highlighted
Fan

Re: Error 3668 and Retry Command

Hi, thanks for your answer. If I use VOLATILE tables, tables with the same name can be used in multiple sessions ? So I can use the same method:

Drop Volatile table > Create Volatile table > Insert > ...

Do you think it will avoid the problem of concurrent sessions ?

 

Thanks

Teradata Employee

Re: Error 3668 and Retry Command

Yes, VOLATILE tables are only available within the session that creates them, so concurrent sessions can use the same TableName with no conflict.

If your job always does a new LOGON, there's no need to attempt a DROP prior to CREATE either.

Fan

Re: Error 3668 and Retry Command

Thanks a lot. I will have to make the drop because tables can be created and deleted with the same name in one sessions.

Bye.