Here's one way - create a bteq step that runs first (before the mload):
.logon....
abort 'Error table exists - cannot continue'
where 0 < (select count(*) from dbc.tables where tablename='ET_MY_TABLE'
and databasename='MY_DB')
Hi,
Appreciate your help..I tried out using the .ABORT command in bteq and use the same command as you had given but i got the following error
*** Error: Unrecognized command ABORT
Are there are any settings which need to be turned on for abort?
Can you provide me with the syntax for the command..I could not find a detail level syntax for this command in the Teradata manual
Thanks in advance
Hi Jaguar, I think u can manage it with the ACTIVITYCOUNT if u want:
Here is a suggetion:
select count(*) from dbc.tables where tablename='ET_MY_TABLE'
and databasename='MY_DB';
.if ERRORCODE <> 0 then .QUIT ERRORCODE
.IF ACTIVITYCOUNT = 0 THEN .GOTO skipx
...
.LABEL skipx
DROP TABLE MY_DB.ET_MY_TABLE;
greetings
stami27
Thanks Stami,
But i think .Abort option works out better for me as i don't want to drop the tables...I just need to ensure that no error tables are created before i proceed with executing the mload script for loading the table..
Can you please let me know the syntax for .ABORT command?
Thanks
Hi Jaguar,
in the official documentation of Database V2R6.1
Book SQL Reference: Data Manipulation Statements side 193 ff.,
Chapter 3: SQL Data Manipulation Language Statement Syntax,
is ABORT described with some examples like:
ABORT FROM table_1,table_2
WHERE table_1.x1 = table_2.x2;
and so on..
I have not tried it so far.
We have yet the Database V2R5.01
hope this help
good luck
stami
I read some time back having DDL in a MULTILOAD script may affect its restartability. Also it is a good idea that the mload script drops all error and UV tables.
I think if these tables are present then ETL will have to invistigate why they have failed.
Sometimes I have used suggestions in the post to circumvent the error tables problem.
Vinay
Hi Vinay,
Can you elaborate in detail as to how to go about resolving the issue if you have error tables that are created?
Thanks in advance
The error you are having with abort is simple - Don't prefix the ABORT with a dot "."
There are two different ABORT commands. One is SQL based and works with any Teradata SQL interface. The other ABORT is the .ABORT command supported in BTEQ channel attached systems.
For the most part you can use the SQL ABORT command just as you would the SELECT command. You can read more about the ABORT command in the NCR manual "SQL Reference Data Manipulation Statements"
I need to run a bteq/stored procedure script from a a mload script..I know i can use the .SYSTEM command to run a os command..
My Requirement :
Actually Mload application error table is creating problem for us. I am using mload script to load data. If the error table is created by mload with any reason such duplicate rows, etc..And when the job runs next time it fails the job because of the existance of this error table.
Please let me know can we force mload script to check the existance of Application error table in the same script and signal a message or something like that. I think it checks in the next run whether there is any Application error table exists or not.
I deeply appreciate your assistance.