All Forums Tools
jklee 40 posts Joined 07/06
02 Aug 2007
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: IntegratedDBError and Log Database: WorkDBDatabase User: etl_loader We are attempting to use TPUMP to load data to a table (Data1) in the IntegratedDB, butwe want the Error and Log tables to be created in the WorkDB. When we try to do thisTeradata 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 - Successful2) 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;

Fred 1096 posts Joined 08/04
03 Aug 2007

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?

davidpracy 22 posts Joined 09/05
05 Aug 2007

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.

sagargurujula 4 posts Joined 03/15
20 Mar 2015

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.

Fred 1096 posts Joined 08/04
21 Mar 2015

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".

You must sign in to leave a comment.