Bteq script - error handling not working, need help

Database

Bteq script - error handling not working, need help

Hi,

Can anyone tell me why my script below is not working? The error handling part is not working.

bteq >> ../log/spend_analysis.log <
.SET ERROROUT STDOUT
.SET ERRORLEVEL 3807 SEVERITY 0
.SET ERRORLEVEL UNKNOWN SEVERITY 16
.SET FORMAT ON
.run file=login.sql
database $dbname;
.run file=spend_analysis.sql
.IF ERRORCODE <> 0 THEN .GOTO SCRIPT_FAILED;
.LOGOFF
.EXIT 0
.LABEL SCRIPT_FAILED
.REMARK 'Errors occured while executing the script.'
update control_wh_log
set status='FAILED',ended_at=cast(CURRENT_DATE as TimeStamp(0)) + ((CURRENT_TIME - time '00:00:00') hour to second(0));
.LOGOFF
.QUIT ERRORCODE
EOF

The above script is supposed to fail and update the row in control_wh_log with FAILED status. Somehow it is not executing the following part of the script.

.LABEL SCRIPT_FAILED
.REMARK 'Errors occured while executing the script.'
update control_wh_log
set status='FAILED',ended_at=cast(CURRENT_DATE as TimeStamp(0)) + ((CURRENT_TIME - time '00:00:00') hour to second(0));
.LOGOFF
.QUIT ERRORCODE

I looked at the log file which has the following lines besides the information for other successful runs of the queries.

*** Failure 6706 The string contains an untranslatable character.
Statement# 1, Info =0
*** Total elapsed time was 5 minutes and 52 seconds.

+---------+---------+---------+---------+---------+---------+---------+----
*** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+---------+---------+----
*** Exiting BTEQ...
*** RC (return code) = 16

Am I missing anything here? Another thing is, the script is not logging the SQLs into the log file even though I had the line ".SET FORMAT ON".

Any help is appreciated.

Thanks
7 REPLIES
Enthusiast

Re: Bteq script - error handling not working, need help

Hi

I suppose thet you run this bteq script within another script(shell)?.
So the problem is with: bteq >> ../log/spend_analysis.log < 0 THEN .GOTO SCRIPT_FAILED;
You have to implement error handling within the bteq part like this:

bteq <<[EOF] >> test.log

.logon database/user,password;

select current_date;

.if errorcode = 0 then .goto script_ok

.label script_failed
select 'ERROR';

.label script_ok
.logoff;
.quit;

[EOF]

Enter a mistake in select current_date e.g. select ccurrent_date and You will see that script will go to label script_failed. If select current_date is correct label script_failed will be omited.

If I misunderstood Your problem please provide more info
s_1
Enthusiast

Re: Bteq script - error handling not working, need help

using BTEQ Script,

any changes (insert/update/delete)-how to get ACTIVITYCOUNT 

every changes

how to get TABLE ACTIVITYCOUNT ?

Enthusiast

Re: Bteq script - error handling not working, need help

Hi,

You can use the below template to handle errors, while activity count is writted to log file based on the type of statement (Insert, Update, Delete)

SELECT 'STARTDTTM' AS A, CURRENT_TIMESTAMP;

--You code here

.IF ERRORCODE <> 0 THEN .QUIT 8

SELECT 'ENDDTTM' AS A, CURRENT_TIMESTAMP;

.IF ERRORCODE <> 0 THEN .QUIT 8

.LOGOFF
.QUIT 0
Khurram
s_1
Enthusiast

Re: Bteq script - error handling not working, need help

Thanks Khurram,

i need another script,

i have a job_status,target tables 

based upon the target loading depends upon job_status table 

i want first check with job_status table (it's already loading siply exit the script)

Regards

Ratnam

Enthusiast

Re: Bteq script - error handling not working, need help

Hi Ratnam,

Can you try with this:

select fields you want where tablename='target' and date='your date' and status='your status'

then check the activity count for the above.

.if activitycount <> 0 then .goto EXIT_FORGOOD;

.if activitycount = 0 then .goto label1

.label1

insert to target table;

.label EXIT_FORGOOD;

     .quit 99;

Cheers,

Raja

s_1
Enthusiast

Re: Bteq script - error handling not working, need help

Thanks Raja

Enthusiast

Re: Bteq script - error handling not working, need help

You are welcome. Always try to put logs for ease of tracking.

Cheers,

Raja