Testing Status Values - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

BTEQ tracks several status values that reflect the results of the most recently submitted Teradata SQL request and OS command. The values can be tested by using their associated keyword as part of IF and ELSEIF commands' conditions. Some of these values can also be used as arguments for other commands such as EXIT and EXITONDELAY. The values are:

Keyword Description
ACTIVITYCOUNT Indicates the number of rows affected by the last submitted request. For a data-returning request, this is the number of rows returned to BTEQ from the database. The value is 0 or a positive integer. The maximum value is 4,294,967,295 when connected to a 14.00(or earlier) database. Starting with the 14.10 database, the maximum increases to 18,446,744,073,709,551,615.
BTEQRETURNCODE Indicates the current value BTEQ will use as its exit RC. This can differ from ERRORLEVEL when there have been user errors made. For example, it may get set to 2 for an attempt to use the COMPILE command without first logging on.
DELETECOUNT Indicates the number of rows deleted when the last submitted request was for a MERGE statement. The value is 0 or a positive integer. The maximum value is 18,446,744,073,709,551,615.
ERRORCODE Indicates the error code the database returned for the last submitted request. The value is 0 or a positive integer. A zero signifies the request completed without error. The set ERRORCODE value can change during the course of a REPEATED request. Only the value for the final repeated request is available to use.
ERRORLEVEL Indicates the highest severity level value encountered as mapped to each returned non-zero ERRORCODE value. The value is 0 or a positive integer. The maximum value is 2147483647. If all prior-returned ERRORCODE values have been 0, then ERRORLEVEL will also be 0. ERRORLEVEL does not reset to zero, as ERRORCODE will, when a submitted request does not result in an error. So if ERRORLEVEL needs to be tested against the last request submitted, ERRORCODE should be tested first, to see whether the request was successful or not. ERRORLEVEL is used as BTEQ's default EXIT RC value when no other value gets specified to be used.
INSERTCOUNT Indicates the number of rows inserted when the last submitted request was for a MERGE statement. The value is 0 or a positive integer. The maximum value is 18,446,744,073,709,551,615.
UPDATECOUNT Indicates the number of rows updated when the last submitted request was for a MERGE statement. The value is 0 or a positive integer. The maximum value is 18,446,744,073,709,551,615.
WARNINGCODE Indicates the warning code the database returned for the last submitted request. The value is 0 or a positive integer. It can only be set to a value greater than 0 when its associated SQL request is successful. Otherwise, ERRORCODE is set. The set of unique DBS warning codes does not overlap with the set of unique DBS error codes. The ERRORONDBSWARNING command can be used to tell BTEQ to treat DBS warnings as though they were DBS errors. When ERRORONDBSWARNING is ON, WARNINGCODE as well as ERRORCODE gets set when the database returns a non-zero warning code. Additionally, ERRORLEVEL will get changed when the severity level associated with the code is higher in value than the pre-existing ERRORLEVEL value.
SYSTEMRETURNCODE Indicates the return value for the last processed OS or TSO command. This can be a negative or positive value. Interpretation of non-zero values is left up to the user. They do not necessarily mean the system call made actually failed. The keyword can be used in IF command conditions for all flavors of BTEQ. When its value changes, BTEQ's default EXIT RC is not altered. However, the keyword can be used as the EXIT command's argument - but only when using Windows BTEQ as DOS and PowerShell support negative value return codes. UNIX shells and z/OS JCL do not.