All Forums Tools
teradata.user 5 posts Joined 01/12
23 Jan 2012
using errorcode

 

Hi,

I have a situation in which i have more than 70 collect stats to be taken on tables. when i do this in sql assist by coding collect stats one after another separated by ";", for some of the tables, i get 3624 errorcode, i.e no stats is defined on the table. Thus my query procesing stops there. I have to manually remove that collect stats and submit the rest of the ones.

I want to write a bteq, wherein i want to check errorcode = 3624, and even if any collect stats given this error, i need to proceed to the next collect stats.

Any help in terms of psudocode is highly appreciated !!

 

 

ulrich 816 posts Joined 09/09
23 Jan 2012

There is also an option in sql assist to proceed in case of an error - but any kind of error...

Tools -> Options -> Query - un tick on Stop query execution...

but bteq is clearly the better approach.

pseudo code would be

collect stats on tableA column (colX);

.if Errorcode = 0 then .goto NEXT

.if Errorcode = 3624 then .goto NEXT;

.if Errocode <> 0 then .exit 8;

.Label NEXT;

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

teradata.user 5 posts Joined 01/12
24 Jan 2012

Hi Der, i agree to your solution in the bteq, but i have more than 100 collect stats. So, i need to code those many labels that would not be a very good idea, i believe.

Is there a way in bteq to supress a prticular return code and keep executing the subsequent statements ?

ulrich 816 posts Joined 09/09
25 Jan 2012

 

You can the the bteq manual!

The label can be always the same as BTEQ will only move forward in this script. So you can paste the four line above after each collect stats without changes.

You can generate the whole statement incl. the error checks and call this generated script.

I didn't test it but setting the ERRORLEVEL might work.

.SET ERRORLEVEL 3624 SEVERITY 4;

collect stats on tableA column (colX);

.if ERRORLEVEL >4 then .exit 8;

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

dnoeth 4628 posts Joined 11/04
25 Jan 2012

As Ulrich already wrote BTEQ ignores errors by default.

BTEQ returns the highest error level to the shell, if you want to ignore only 3624 you have to set the error level to zero:
.SET ERRORLEVEL 3624 SEVERITY 0;

Btw, it's much easier to control when you use an SP with a cursor for the collect statements. Then BTEQ is only used to CALL this SP.

Dieter

Dieter

You must sign in to leave a comment.