All Forums Teradata Applications
rohit.lalwani1 8 posts Joined 08/13
13 Aug 2013
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.. 

dnoeth 4628 posts Joined 11/04
13 Aug 2013

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

Dieter

CarlosAL 512 posts Joined 04/08
13 Aug 2013

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.

dnoeth 4628 posts Joined 11/04
13 Aug 2013

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

Dieter

CarlosAL 512 posts Joined 04/08
13 Aug 2013

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.

vishaman 16 posts Joined 05/13
23 Jul 2014

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

 

 

You must sign in to leave a comment.