using errorcode

Tools
Enthusiast

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 !!

4 REPLIES
Supporter

Re: using errorcode

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;

Enthusiast

Re: using errorcode

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 ?

Supporter

Re: using errorcode

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;

Senior Apprentice

Re: using errorcode

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