How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

Jobs
Not applicable

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

Tags (3)
5 REPLIES 5
Junior Contributor

Re: How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

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
Fan

Re: How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

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

Junior Contributor

Re: How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

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.

Fan

Re: How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

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

Junior Contributor

Re: How to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?

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.