ERRORLEVEL - Basic Teradata Query - 16.20

Basic Teradata Query Reference

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

Purpose

This control enables users to specify how database errors map to severity levels.

The severity levels can be used to determine a course of action based on the severity of subsequent SQL failures.

If the ERRORLEVEL command has not been used, its value is ON by default.

Syntax



where the following is true:

errno
A specific error number
n
The assigned severity level where n can be any integer from 0 to 2147483647. An error code mapped to a severity value of 0 will cause that error to be ignored by BTEQ.
ON
OFF
Enable or disable the error level function.
UNKNOWN
Designation of other BTEQ error codes not defined by default mapping or by the user.

Usage Notes

It is common practice to use a BTEQ script to query the status of the last Teradata SQL statement and obtain information such as the number of rows involved, and any error code that might have been returned by Teradata Database. The ERRORLEVEL command can extend that functionality by assigning a severity level to each Teradata SQL error code.

Also, use the ERRORLEVEL command to assign severity levels to the return codes that BTEQ returns to the operating system when a job completes. This way, ambiguous system-assigned error numbers can be correlated to meaningful levels of error severity for alerting users. (For more information on using return codes in this manner, see Error Handling.

The ERRORLEVEL command includes one or more error items. Each error item consists of: either the word UNKNOWN, a single error number, or a parenthetical list of error numbers, followed by the word SEVERITY, followed by an integer value. The integer value is the new error level value for the listed error numbers.

(The error code of UNKNOWN accommodates any other BTEQ error codes that are not defined.)

For Mainframe BTEQ, in addition to the ON and OFF ERRORLEVEL command options, the BTEQ error level function can be enabled or disabled using the ERRORLEVEL environment variable in the JCL PARM in BTEQ STEP.

For example, to set ERRORLEVEL=OFF at startup, code the ERRORLEVEL environment variable in z/OS PARM as below:

//BTEQ00   EXEC PGM=BTQMAIN,PARM='ENVAR(ERRORLEVEL=OFF)/'

The following example redefines the level of several errors and tests the result of a query using the IF...THEN... command.

.SET ERRORLEVEL 2168 SEVERITY 4,
   (2173, 3342, 5262) SEVERITY 8
.SET ERRORLEVEL UNKNOWN SEVERITY 16
...
SELECT * FROM SOMEWHERE;
.IF ERRORLEVEL >= 14 THEN .QUIT 17;
...

The highest severity value encountered during BTEQ's execution will by default be used as BTEQ's return code value unless an argument is explicitly supplied for the EXIT or QUIT command. So although a value up to 2147483647 can be supplied as a mapped severity value, the maximum value supported for return code values by the client OS in use should be take into consideration if its possible that that severity value is going to get used as BTEQ's return code. Otherwise, BTEQ's return code value may get corrupted.