Bteq exits with wrong error code

General
Enthusiast

Bteq exits with wrong error code

Hi,

My bteq script has an update statement. semicolon is missing at the end of the update statement and the update fails. but the bteq is returning error code '0'. so am unable to catch the error when my process runs. i have a label defined 'SQLERROR' and am using . IF ERRORCODE<>0 then .GOTO SQLERROR. so when the update fails, it should go to SQLERROR label and exit with .quit 50, but thats not happening. It exits with ERRORCODE=0.  below is my code. can anyone help me with it.

BTEQ Script:

========

bteq << EOF

.set session transaction btet;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.logon XXXXX ;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.set errorout stdout;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.set width 254;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.SET ERRORLEVEL 3807 SEVERITY 0;

Drop table example.table1

.SET ERRORLEVEL 3807 SEVERITY 8;

CREATE  TABLE example.table1

AS(SELECT * FROM  example.table ) WITH DATA;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

UPdate  a from  example.table1 a, example.table2 b

set temp_fl=1

where a.GCI =b.GCI   

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.quit 0

.LABEL SQLERROR

 .quit 50

 EOF

LOG File:

======

BTEQ 13.10.00.06 Mon Dec 29 13:26:34 2014

+---------+---------+---------+---------+---------+---------+---------+----

.set session transaction btet;

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+----

.logon XXXX/XXXX,

 *** Logon successfully completed.

 *** Teradata Database Release is 13.10.07.35                  

 *** Teradata Database Version is 13.10.07.21                    

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+----

.set errorout stdout;

 *** Error messages now directed to STDOUT.

+---------+---------+---------+---------+---------+---------+---------+----

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+----

.set width 254;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.SET ERRORLEVEL 3807 SEVERITY 0;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

Drop table example.table1;

 *** Table has been dropped.

 *** Total elapsed time was 8 seconds.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.SET ERRORLEVEL 3807 SEVERITY 8;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

CREATE  TABLE example.table1

AS

(SELECT * FROM  example.table ) WITH DATA;

 *** Table has been created.

 *** Total elapsed time was 5 seconds.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

UPdate  a from  example.table1 a, example.table2 b

set temp_fl=1

where a.GCI =b.GCI  

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

.IF ERRORCODE <> 0 THEN .GOTO SQLERROR;

  $

 *** Failure 3706 Syntax error: expected something between a string or a Unicode character literal and '.'.

                Statement# 2, Info =204

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.quit 0

 *** You are now logged off from the DBC.

 *** Exiting BTEQ...

 *** RC (return code) = 0

Tags (2)
3 REPLIES
Senior Apprentice

Re: Bteq exits with wrong error code

Due to the missing semicolon the .IF is considered being part of the UPDATE statement, the 'Syntax  error' message clearly indicates this. So the UPDATE doesn't fail, it's just an invalid SQL statement.

And the following comand is ".quit 0" :-)

Enthusiast

Re: Bteq exits with wrong error code

technically the update is not hapening right. how do we catch such errors when the semicolon is missing. ( sorry for multiple posts. i replied the same in the other post too)

Fan

Re: Bteq exits with wrong error code

Hi All,

I am new to tera Data. I would like to  know about Database Log. Could you please share why we use Database Log and what is the use of this log. and also please explain the parameters in the Database log. 

Thanks In Adavance,