Use SQL scripts shipped with Teradata TPump to create a Monitor Interface Table (SysAdmin.TPumpStatusTbl) in the database where Teradata TPump maintains information about an import. Teradata TPump both reads commands from and updates status in the Monitor Interface Table.
This table is required in order to use the Teradata TPump Monitor functionality, but is otherwise optional. If the table does not exist, the worst that will happen is that Teradata TPump issues a warning message indicating this fact.
This table must be secure, so it is created by the DBA.
An SQL script tpumpar.csql is provided in the Teradata TPump installation that performs the appropriate setup. The tpumpar.csql script includes an action request.
The following table contains the monitor interface columns (other columns exist in order to support future functionality).
|Import||INTEGER||Part of primary index. The import number. (There may be multiple imports in a Teradata TPump job.|
|InitStartDate||DATE||The initial start date of the import.|
|InitStartTime||FLOAT||The initial start time of the import.|
|Complete||CHAR(1)||Y if this import is complete. (There may be multiple imports.)|
|CurrStartDate||DATE||The last date this import was started (may be a restart).|
|CurrStartTime||FLOAT||The last time this import was started (may be a restart).|
|LastUpdateDate||DATE||The last date this import updated the table.|
|LastUpdateTime||FLOAT||The last time this import updated the table.|
|LogDB||VARCHAR(32)||Part of primary index. The name of the log table database.|
|LogTable||VARCHAR(32)||Part of primary index. The name of the log table.|
|PeriodsDesired||INTEGER||Allows specifying the desired periodicity.|
|PleaseAbort||CHAR(1)||Set to Y to abort.|
|RecordsErrored||INTEGER||The number of records resulting in errors on the database.|
|RecordsOut||INTEGER||The number of statements sent to the database.|
|RecordsSkipped||INTEGER||The number of records skipped for apply conditions.|
|RecordsRejcted||INTEGER||The number of records rejected for bad data (on host)|
|RecordsRead||INTEGER||The number of records read.|
|RequestAction||CHAR(1)||Before processing any action request, a message will be logged stating that the requested action is being taken. The following action requests are permitted:
|RequestChange||CHAR(1)||Set to Y by the user so Teradata TPump will pick up the changes. Set to N by Teradata TPump after changes are accepted.|
|RestartCount||INTEGER||The number of times this import has been restarted.|
|StmtsDesired||INTEGER||The statement rate (if StmtsUnLimited is N)|
|StmtsUnLimited||CHAR(1)||Y if this import running without a statement rate limit.
If N, refer to StmtsDesired for the statement rate.
|UserName||VARCHAR(32)||The name of the user running the job. Used for security.|
Security concerns dictate that the SQL script to set up the Monitor Interface table for Teradata TPump monitoring also establishes a set of views and macros in addition to the TPumpStatusTbl. Although database administrators can access the table directly, using macros and views is recommended because they provide for security and ensure rational use of the table.
Without action on the part of the database administrator, no normal user can update the status of jobs. To grant controlled update access to the TPumpStatusTbl, a single command will suffice:
“GRANT EXEC ON TPumpMacro TO _____;”
The macros for Teradata TPump monitoring reside in the database TPumpMacro and SysAdmin.