All Forums Database
Jaguar 25 posts Joined 05/06
25 May 2006
can we run bteq script/stored procedure from mload?

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.

j355ga 100 posts Joined 12/05
25 May 2006

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')

Jeff

Jaguar 25 posts Joined 05/06
26 May 2006

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 ABORTAre 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 manualThanks in advance

stami27-2406 22 posts Joined 02/06
26 May 2006

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 skipxDROP TABLE MY_DB.ET_MY_TABLE;greetingsstami27

Jaguar 25 posts Joined 05/06
26 May 2006

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

stami27-2406 22 posts Joined 02/06
26 May 2006

Hi Jaguar,in the official documentation of Database V2R6.1Book 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_2WHERE table_1.x1 = table_2.x2; and so on..I have not tried it so far. We have yet the Database V2R5.01hope this helpgood luckstami

BBR2 96 posts Joined 12/04
29 May 2006

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

Jaguar 25 posts Joined 05/06
30 May 2006

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

j355ga 100 posts Joined 12/05
30 May 2006

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"

Jeff

You must sign in to leave a comment.