Can TPUMP load to one db and put error/log tables in another?

Tools
Enthusiast

Can TPUMP load to one db and put error/log tables in another?

Loader: Error and Log Tables Different Database

Question:

Can TPUMP load to one database and create the error and log tables in a different database? If so what are the permissions required between the database user, target database and error/log database?

Background:

We have 2 databases, one for our integrated data and a work area:

Target Database: IntegratedDB

Error and Log Database: WorkDB

Database User: etl_loader

We are attempting to use TPUMP to load data to a table (Data1) in the IntegratedDB, but

we want the Error and Log tables to be created in the WorkDB. When we try to do this

Teradata throws a rights error at us.

ERROR:

**** 10:41:16 UTY8001 RDBMS failure in Packing Test: 3523, An owner referenced by user does not have SELECT WITH GRANT OPTION access to IntegratedDB.Data1.

Tests that we have performed:

1) TPUMP creates the Error Tables in the same database - Successful

2) Add rights – ALL Failed:

Grant rights to Database User:
Grant Select, Update, Delete, Insert On IntegratedDB To etl_loader With Grant option;
Grant rights to Error/Log Database
Grant Select, Update, Delete, Insert on IntegratedDB to WorkDB With Grant option;

4 REPLIES
Teradata Employee

Re: Can TPUMP load to one db and put error/log tables in another?

TPUMP creates and uses macros to process the data. If the macros are to be placed in a "third party" database, then that database must be granted DML rights (SELECT, INSERT, UPDATE, DELETE) WITH GRANT OPTION on the target tables or databases. The macro database can be explicitly specified (MACRODB in BEGIN LOAD), or it will default to the database in which the LOGTABLE is defined.

But you appear to be saying you tried the appropriate GRANT (among others) and it still failed. Did you get the same error?
Enthusiast

Re: Can TPUMP load to one db and put error/log tables in another?

Yes TPUMP can load to one database and create the error and log tables in a different databases. This would be the preferred way to do it.

It would be easier to help if you included excerpts from your Tpump script / macros.

Re: Can TPUMP load to one db and put error/log tables in another?

Hi Fred

Thanks for the explaination.

Could you please explain what do you mean by "third party" db mentioned in your comment.

Also can you explain why do we need to grant authority to macro db?I mean what is the purpose.

I am new to this.Searched alot for the TPUMP flow.

It would be very helpful if you reply to my above queries.

Thanks

Teradata Employee

Re: Can TPUMP load to one db and put error/log tables in another?

By "third party", I mean the macros are not in the user database for the username that TPump / Stream is logged on with, nor are they in the same database as the target table; they are in a "third" database.

Let's say TPumpUser logs on and wants to modify TableDB.MyTable but place the macros in MacroDB. Obviously TPumpUser needs CREATE MACRO right on MacroDB, and rights to execute the statements in the body of the macros, or the CREATE will fail. And as the creator, TPumpUser will be given EXEC (macro) right automatically.

But at execution time, the only right a macro caller (TPumpUser) needs is EXEC on the macro. It's the rights of the "owner" MacroDB on TableDB.MyTable (or TableDB) that are checked for executing the statements within the macro. And because the caller is not the owner, MacroDB is attempting to provide (indirect) access to another user; that is only permitted if MacroDB holds the rights "WITH GRANT OPTION".