ERRORLEVEL Command | Basic Teradata Query - ERRORLEVEL (EL) - Basic Teradata Query

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, the 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 a common practice to use a BTEQ script to query the status of the last SQL statement and obtain information such as the number of rows involved, and any error code that might have been returned by the database. The ERRORLEVEL command can extend that functionality by assigning a severity level to each 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.

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 shown here:

//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. Hence, 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 must be taken into consideration whether it is possible to use the severity value as BTEQ's return code. Otherwise, BTEQ's return code value may be corrupted.

The DEFAULTS command does not affect ERRORLEVEL setting.

The ERRORLEVEL command is valid in an SQL macro.