conditional run of sql BTEQ script parts

Tools
Teradata Employee

conditional run of sql BTEQ script parts

Hi,

 

I'd like to split existing sql /bteq scripts into individual parts and be able to make them run conditionally, i.e. based on a input parameter value.

 

Now we have a script calculating a series of individual metrics (e.g. 1,2,3,4,5) and due to a need of recalculation (data corrections, fixes) we'd like to "run" only some of them - e.g. 1,3,5, and the rest (2,4) should be "skipped".
We don't prefer to split the script physically into individual scripts, we'd rather add some marks into existing scripts to enable this functionality. We need to move around current ETL process framework, we can't change it much. (SPs can't be used).

 

I have some preliminary ideas leveraging 2 functionality features:
1. BTEQ feature : .IF ACTIVITYCOUNT>0 THEN .GOTO NEXTSTEP
2. and to add "where TRUE=FALSE" condition just before the script is about to run (with variable replacement e.g. with perl) - to make TD optimizer to decide that it makes no sense to run this query at all - and then it's skipped.

 

Any experience on recalculation processes and conditional running is welcome plus suggestions for additional options to consider.

 

Thanks in advance

Ivan Klima


Accepted Solutions
Supporter

Re: conditional run of sql BTEQ script parts

you could also use a ref table where you store the steps to run

 

in bteq add

select *

from run_table_ref

where step = 1

and processed = null;

.if errorcode <>0 then .exit 8;

 .IF ACTIVITYCOUNT>0 THEN .GOTO NEXTSTEP;

your code for step 1...

 

update run_table_ref

set processed = 'Y'

where step = 1;

 

.label NEXTSTEP;

...

.label NEXTSTEP;

delete from run_table_ref

set processed = 'Y';

 

.exit 0;

1 ACCEPTED SOLUTION
5 REPLIES
Supporter

Re: conditional run of sql BTEQ script parts

you could also use a ref table where you store the steps to run

 

in bteq add

select *

from run_table_ref

where step = 1

and processed = null;

.if errorcode <>0 then .exit 8;

 .IF ACTIVITYCOUNT>0 THEN .GOTO NEXTSTEP;

your code for step 1...

 

update run_table_ref

set processed = 'Y'

where step = 1;

 

.label NEXTSTEP;

...

.label NEXTSTEP;

delete from run_table_ref

set processed = 'Y';

 

.exit 0;

Supporter

Re: conditional run of sql BTEQ script parts

P.S. you can extend this approach also for a fully restarable job... beside dedicate reruns

Teradata Employee

Re: conditional run of sql BTEQ script parts

In case it helps... Starting with the 14.00.00.21, 14.10.00.15, 15.00.00.06, 15.10.01.02 and 16.00.00.00 BTEQ versions, more than one condition can be used in a BTEQ .IF command. Also, the list of keywords was expanded to be able to test database warning codes as well as the return code from a BTEQ .OS command. The keyword set is now ACTIVITYCOUNT, ERRORCODE, ERRORLEVEL, WARNINGCODE and SYSTEMRETURNCODE.

 

 

For example, the following IF command can be used to detect whether the BTEQ .COMPILE command was completely successful (meaning not even any compiler warnings) or not:

.IF ( NOT ((ERRORCODE=0) AND (WARNINGCODE=0)) ) THEN .GOTO errorpath

 

And here's an example of detecting a missing file:

.OS test -e myfile.txt
.IF SYSTEMRETURNCODE != 0 THEN .GOTO missingfile

 

 

For more details, see BTEQ's 16.00 Reference Manual. Specifically, chapter 3's Status Value Keywords section describes all the above listed keywords. And chapter 5's .IF command section explains more about the syntax.

Teradata Employee

Re: conditional run of sql BTEQ script parts

Thanks ulrich.

Exactly, the combination of metadata table "run_table_ref" and lables is at leat one viable solution.

 

It looks like you have close experience with it. I have 2 additional questions then :

1. if you have a GUI to manage what parts of ETL to enable and run and which parts to disable/skip. Because I'd expect to have some Tree-like GUI to say, from this node on - recalculate.

2. if you have a general "strategy" when to recalculate, when to store previous data, when to delete them etc. I'd like to compare somebody's else approach to ours.

 

Regards

Ivan

Teradata Employee

Re: conditional run of sql BTEQ script parts

Thanks Suzanne. I'll consider these ones.

 

Regards

Ivan