All Forums Jobs
vishwas 1 post Joined 01/15
25 Jan 2015
How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

Hi,
I am new BTEQ and scripting. I am checking if my source data is updated or not before i am updating my petspace tables.
Sel 1 from SOURCE_TABLE
where cast(CMPGN_RUN_DATE as date)=current_date-1 and TRANS_DATE =current_date-1
group by 1;
.IF ACTIVITYCOUNT = 0 THEN .EXIT 99; -- 99 error code reserved for dependency
I want BTEQ to renitiate the SQL execution after 30 minutes if it errors out in 1st try. I am unable to find much information on such issues.
Regards

dnoeth 4628 posts Joined 11/04
25 Jan 2015

Better use EXISTS instead:

Sel 1 
where exists
 (
   select * from SOURCE_TABLE
   where cast(CMPGN_RUN_DATE as date)=current_date-1 and TRANS_DATE =current_date-1
 );

.IF ACTIVITYCOUNT = 0 THEN .GOTO runSQL

.HANG 1800 --  wait 1800 seconds

=1; -- repeat the previous select

.IF ACTIVITYCOUNT = 0 THEN .EXIT 99; -- 99 error code reserved for dependency

.LABEL runSQL

 

Dieter

sbdel 2 posts Joined 04/16
08 Apr 2016

9th April 2016
 
I understood to reinitiate the SQL using above menthod but what If I ant to repeat the sleep/pause before executing the SQL?
There's COMMAND .REPEAT in BTEQ but I'm not able to figure the solution
Can anyone please suggest

dnoeth 4628 posts Joined 11/04
09 Apr 2016

REPEAT is similar to "=", both repeat a SQL statement n times, the former the follwowing the latter the preceding.
They don't work with BTEQ commands like HANG.
 
If you create a simple C-XSP calling C's sleep you could do that in in a Stored Procedure.
But you should double check if you really need to poll information in a loop, there are techniques like Queue Table to push information.

Dieter

sbdel 2 posts Joined 04/16
11 Apr 2016

Hey, I want run the SQL only after a delay of 1 minute in BT ET .Can we run procedure in BT ET.
It would be great if you can share some sample of this requirement.
Thanks

dnoeth 4628 posts Joined 11/04
11 Apr 2016

You can run a SP within a transaction, but there's no reason to pause a transaction for a minute, transactions ahould always run as fast a possible, no user interaction and no pause. 

Dieter

You must sign in to leave a comment.