EXITONDELAY - Basic Teradata Query

Purpose  

This command can be used to establish the maximum time duration within which to allow a submitted SQL request to be processed before CLI will time out the request. When a time out occurs, the BTEQ process will terminate. The time out is triggered when either the database goes down or the duration is exceeded. Situations where its use may be desired:

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 a request has been submitted to database that has gone down and rather than waiting for the database to come back up and then attempting to re-drive the request, the user wants BTEQ to exit.

When it is desired to put a maximum duration on an SQL request that may end up being too long-running.

This command is available on Workstation platforms only.

Syntax  

where:

Syntax Element	Returns
OFF	BTEQ does not timeout 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 immediately 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 immediately exit with a non-0 return code when the allotted time is exceeded. BTEQ will immediately exit with a non-0 return code on any DBS crash (hard or temporary) or network outage.
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 2147483647. When MAXREQTIME is not used, default wait time is 10 seconds.
RC	When MAXREQTIME is used, it may be followed by an optional keyword RC. The optional keyword RC (return code) can accept either a positive number, or ERRORCODE or ERRORLEVEL. When RC is not used, default return code is 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 only be changed when sessions are not logged on. So the command cannot be issued using an SQL ECHO statement.

Either ON or OFF must be used. There is no default value for EXITONDELAY command. Upon startup, EXITONDELAY is OFF.

The SET DEFAULTS command does not affect EXITONDELAY settings.

Note: Since EXITONDELAY command will timeout a long running SQL request besides timing out a LOGON command over a down DBS or immediately exiting upon a crash, 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 exit out on a crash or network issue.

Note: There is a usage scenario that is currently limited by a CLI restriction.To effect an EXITONDELAY timeout, BTEQ must disable CLI's Automatic Redrive capability. This means that when a database goes down, CLI will not be able to re-establish lost connections for the purpose of re-driving their requests. So it may be the case that an SQL request which could have succeeded within the specified timeout duration will not succeed because CLI must immediately return control to BTEQ for a down database and BTEQ must then immediately exit.

Example  

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

   .SET EXITONDELAY ON MAXREQTIME 300

Note: BTEQ will timeout the LOGON command and exit if the database remains down for more than five minutes.

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

If the database goes down while an SQL is being processed, BTEQ will exit immediately.