EXITONDELAY - 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 the maximum time duration within which to allow a submitted SQL request to be processed before CLI will time out the request. When a timeout occurs, the BTEQ process will terminate. The timeout is triggered when either the database goes down or the duration is exceeded

Use cases include the following:
  • When it is desired to put a maximum duration on the LOGON command which has been waiting indefinitely over a database that is down
  • When it is desired to put a maximum duration on an SQL request that may end up being too long-running
  • When it is desired to exit from BTEQ upon a request submitted to a database that has gone down, rather than waiting for the database to come back up and then attempting to re-drive the request

This command is available on Workstation platforms only.

Syntax



where the following is true:

OFF
BTEQ does not time out the LOGON command or a long running SQL request, and will continue to wait indefinitely in case the DBS goes down.
ON
  • BTEQ will use a timeout factor on a logon and exit with a non-0 return code when the allotted time is exceeded.
  • BTEQ will use a timeout factor on a long-running SQL request and exit with a non-0 return code when the allotted time is exceeded before getting a response from the database.
  • BTEQ will use a timeout factor when DBS crashes upon a submitted SQL request and exit with a non-0 return code either immediately or when the allotted timer is exceeded, depending on CLI's Automatic Redrive setting.
When CLI's Automatic Redrive setting is enabled, BTEQ will wait for the allotted timer to exceed before exiting. This gives an opportunity for the request to complete if DBS comes back up before the EXITONDELAY timeout period expires.

If Automatic Redrive and Recoverable Network Protocol is disabled, there is no way for CLI to retry the connection, and BTEQ will exit immediately upon any DBS crash (hard or temporary) or network outage.

Refer to CLI documentation for configuring Automatic Redrive settings.

MAXREQTIME t
When EXITONDELAY ON is used, the optional keyword MAXREQTIME (maximum request time) can accept a number in seconds which BTEQ will wait for the logon to take place or for a long running SQL request to allow execution.

The range for MAXREQTIME is 10 to 65535.

When MAXREQTIME is not used, default wait time is 10 seconds.

RC

When EXITONDELAY ON is used, the optional keyword RC (return code) can accept either a positive number, or ERRORCODE or ERRORLEVEL.

When RC is not used, the return code used by default is 16 – that of a fatal error.

When RC is used, specifying:
  • n – will make BTEQ return the specified code

    The range for n is 0 to 2147483647.

  • ERRORLEVEL – will make BTEQ return the highest severity level encountered.
  • ERRORCODE – will make BTEQ return the error code generated by the last Teradata SQL request.

Usage Notes

EXITONDELAY settings can be changed before or after logging on, i.e. the command can be used SQL specific.

The EXITONDELAY command cannot be issued using an SQL ECHO statement. This is because it has to affect an entire request, not just one statement of the request.

The SET DEFAULTS command does not affect EXITONDELAY settings.

Because the EXITONDELAY command will time out a long running SQL request besides timing out a LOGON command or SQL request over a down DBS, users must estimate the time for the long running SQL request if they actually want it to finish in the no-crash scenario but still time out on a crash or network issue.

Example - EXITONDELAY

To timeout the LOGON command after five minutes, issue this command before LOGON:

.SET EXITONDELAY ON MAXREQTIME 300

BTEQ will time out the LOGON command and exit if the database remains down for more than five minutes.

The above setting will also time out any SQL that takes more than five minutes to respond, in case the database is up.

If the database goes down while an SQL request is being processed, BTEQ will exit when the allotted timeout exceeds (for cases where CLI's Automatic redrive is enabled) or exit immediately (for cases where CLI's Automatic Redrive is disabled).