How to handle failure statement in Bteq ?

Teradata Applications
Enthusiast

How to handle failure statement in Bteq ?

Hi ,

I am trying to create a bteq  script which run multiple Insert statements through sql file. 

Supose i i have a file like abc.sql, In that i hv 10 Insert statements like

(Insert .... ;

Insert... ;)

now i run this file in Bteq using .RUN FILE abc.sql

my concern is if any of the insert statement failed for any reason (like syntactical issue ,spool issue,TDWM exception issue) the process will not stop and keep running till the last statement in the file.  So if there is any 1 insrt  failed still  i hv 9 records in my table.. 

Any Suggestion will be helpful Thanks in Advance.. 

5 REPLIES
Senior Apprentice

Re: How to handle failure statement in Bteq ?

You might add .IF ERRORCODE <> 0 THEN .QUIT after each insert or .SET MAXERROR = 1; before .RUN FILE.

Both will stop after the first error.

If you want to run this as all or nothing you must add use a transaction:

.SET MAXERROR = 1;
BT;
.RUN FILE...
ET;

Dieter

Junior Supporter

Re: How to handle failure statement in Bteq ?

Dieter:

I think the answer is the opposite (e.g.: default bteq behaviour). The OP wants the inserts NOT to stop on error...

Cheers.

Carlos.

Senior Apprentice

Re: How to handle failure statement in Bteq ?

Hi Carlos,

the default in BTEQ is to ignore any error and go on with processing, only SQL Assistant stops by default :-)

Dieter

Junior Supporter

Re: How to handle failure statement in Bteq ?

Yep, I knew that ;-)

I misunderstood the OP. I thought he wanted NOT to stop the inserts.

If that's not the case, it may be worth to take a look at 'SET RETRY OFF' and 'SET REPEATSTOP ON' (in case of inserting rows from a file).

Cheers.

Carlos.

Teradata Employee

Re: How to handle failure statement in Bteq ?

Hi 

I have series of explain DDL statment in a bteq file ,when i run this file ,it errors out after explaining some DDL's 

*** Error: reading input, unable to find the end of a request.

     On line 1357, the request has exceeded the maximum

     of 1048500 bytes.

 *** The input is flushed.

 *** Exiting BTEQ...

 *** RC (return code) = 8

No matter what i do ,it still keeps coming out , I tried it without setting any bteq enviornment ( with no .set command)

I even tried  ....

 .SET MAXERROR 100;

but it keeps coming out with the same error ...how can i make bteq ignore the error and move to the next step ? and can i possibel redirect the failed statement to a different file ?

By the way ,this is how my script now looks like  ...


 set -vx

LOGFILE=/invalid_views/testrun/testrun_$date.log

bteq << EOF >> $LOGFILE 2>&1

.logon tpId/user,Passwd

.SET MAXERROR 100;

.run file= final_file.bteq;

.quit

EOF