05-18-2016 01:22 AM
For as long as I can remember (years) we have had trouble replacing tables in Teradata. When I joined the team the practice was to use a new table name. Now that we've upgraded to 6.4 during UAT I tested the Replace function. It appears that some times it works and some times it doesn't.
Now I am working on a recurring campaign and really need this functionality. I do have a work around which is to include a process node that will drop the table for me. This shouldn't be necessary.
The error message states there was an error writing to the database. I've looked at the logs and the DROP TABLE statement appears to be missing in the cases where there is a failure. Even this morning I changed the table name manually to ensure the campaign complete. Just now I delete the occurrence and re-ran with success. But why did it fail yesterday? What was different?
I've tried some tests tonight which didn't fail. I created a diagram with a single select node to a comm (with export) and an export node that wrote to two different tables. I ran it, re-ran it, ran it again, changed the table names, ran it twice again, removed the export node (leaving only comm node with its export), ran it again, scheduled it... and it ran without issue.
Our IT team tried to figure it out. There's a SAS track that's been open for weeks. I was hoping someone in the community may know a nuance that may be causing this.
05-18-2016 03:12 AM
I don't have answer for you.
But any Teradata site I've encountered, the DBA was certainly not keen on having users and applications recreate tables. Perhaps for a good reason.
So, is it possible to configure so that truncates and the inserts the same table instead? That is considered more RDBMS best practice I guess.
05-18-2016 09:23 AM
Thanks LinusH for your feedback. Our DBA's are sticklers for access. However, in the SAS CI logs I do not even see a DROP TABLE statement attempted. If access to drop a table was restricted I would expect an error (no permissions).
05-19-2016 05:45 AM
Hi, thanks for using the CI SAS Support Community!
We would like to help you directly with this issue. When a problem requires individual investigation and diagnosis, this is best handled by our Technical Support team.
If you haven't done so already, please raise a Technical Support track, following these Guidelines