I am working on developing Teradata TPT sripts. I am using TPT user reference guide to start with and have faced couple of issues while executing TPT scripts. I would like to know the teradata standards /recommendation of using Databases for these error, work, log tables.
I have tried below 2 different scenarios ->
1) Developed TPT script with error tables, work tables without providing database names along with them. -> The connection database was used to create error tables and my script failed with error "User doesnot have create table privilege in database". This is valid scenario as my id dont have create access to STAGE database, only DBA can create tables there.
2) Developed TPT script with error tables suffixed with databasename. -> I was able to run this script and error tables created in the databases provided by me. I have used WORK database where my id have create/drop privileges.
I understand this is totaly based on my id privileges but would like to understand are there any recommendation from Teradata on different types of databases to be used while developing TD utility scripts? there must be some standards specified while setting up enterprise DW. Please provide some pointers on using/setting up temp/work databases in Teradata.
It's a good practice to have specific Database for data integration (TEMP, WORK, STAGING, you name it).
Keep in mind once you script has been achieved and tested, it will fail rarely.
Thus I don't bother to have another integration Database for logs and errors, I let them be in the same database.
They are automaticaly suffixed by _ET / _UV / _RL.
You should look into it the WorkingDatabase parameter by the way.
Also, recurrent data integration should not be tied to a user account, but to an application account.
User account is ok for development or owned and personnal tasks.