All Forums Database
bheemsen 2 posts Joined 05/10
21 Mar 2011
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 < 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

kostek 29 posts Joined 11/09
24 Mar 2011

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@ir@m 35 posts Joined 05/13
11 Dec 2013

using BTEQ Script,
any changes (insert/update/delete)-how to get ACTIVITYCOUNT 
every changes
how to get TABLE ACTIVITYCOUNT ?
 

M.Saeed Khurram 544 posts Joined 09/12
11 Dec 2013

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@ir@m 35 posts Joined 05/13
11 Dec 2013

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
 

Raja_KT 1246 posts Joined 07/09
11 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

s@ir@m 35 posts Joined 05/13
18 Dec 2013

Thanks Raja

Raja_KT 1246 posts Joined 07/09
18 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.