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.
- 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
Syntax
| Command | Shorthand |
|---|---|
| EXITONDELAY | EOD |
| Option | Shorthand | Option | Shorthand | Option | Option |
|---|---|---|---|---|---|
| MAXREQTIME | MRT | ERRORLEVEL | EL | ERRORCODE | EC |
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 database goes down.
- ON
- BTEQ will use a timeout factor on a logon and exit with a non-zero return code when the allotted time is exceeded.
- BTEQ will use a timeout factor on a long-running SQL request and ereceiving exit with a non-zero return code when the allotted time is exceeded before getting a response from the database.
- BTEQ will use a timeout factor when database crashes upon a submitted SQL request and exit with a non-zero 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 database 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 database crash (hard or temporary) or network outage.
Refer to Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 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 SQL request.
- n will make BTEQ return the specified code
Usage Notes
EXITONDELAY settings can be changed before or after logging on, that is, 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 DEFAULTS command does not affect EXITONDELAY settings.
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 previous setting will also time out any SQL that takes more than five minutes to respond, if the database is up.
If the database goes down while an SQL request is being processed, BTEQ will exit when the allotted timeout exceeds (when CLI's Automatic redrive is enabled) or exit immediately (when CLI's Automatic Redrive is disabled).