RETRY - 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 whether requests that fail under certain error conditions should be resubmitted.

If the RETRY command has not been used, its value is ON by default. If the RETRY command is used but does not specify ON or OFF, BTEQ sets RETRY to ON.

Syntax



Usage Notes

When set to ON, the RETRY command option takes effect when BTEQ detects any of the following failure codes:

  • 2631 – Transaction aborted due to %VSTR.
  • 2639 – Too many simultaneous transactions.
  • 2641 – %DBID.%TVMID was restructured. Resubmit.
  • 2825 – No record of the last request was found after DBC restart.
  • 2828 – Request was rolled back during system recovery.
  • 3111 – The Dispatcher has timed out the transaction.
  • 3120 – The request is aborted because of a DBS recovery.
  • 3127 – Transaction aborted due to lock conflict with Queue Table Manager.
  • 3128 – Transaction aborted due to exceeding the maximum consume statement limit.
  • 3178 – Queue Table Cache is full.
  • 3319 – TDWM control command timed out.
  • 3329 – Query Band is in transition state – try again.
  • 3598 – Concurrent change conflict on database – try again.
  • 3603 – Concurrent change conflict on table – try again.
  • 3897 – Request aborted due to Teradata system restart. Resubmit.
  • 5880 – Concurrent change conflict on stored procedure – try again.
  • 5991 – Error during plan generation – try again.
  • 7592 – Space accounting cache is full of non-purgeable entries.
  • 7618 – Stored procedure execution request for %DBID.%TVMID has timed out.
  • 7676 – Insufficient memory in stored procedure cache.
  • 8024 – All virtual circuits are currently in use.
  • 8086 – All the virtual circuits on the node are in use.
  • 9728 – Retry later as currently a load operation is in progress on the table %DBID.%TVMID.

If any of these error conditions occur, or if an AP Reset condition occurs while a BTEQ job is running, BTEQ does the following:

  • Determines which session connections have been lost.
  • Issues the appropriate error messages.
  • Reconnects the lost sessions.
  • Resubmits any in-progress requests.

Except for the error messages and delayed responses, BTEQ processes the affected requests in a normal fashion, without additional user intervention.

When RETRY is OFF, BTEQ does not attempt to reconnect sessions.

Error 2631 – Transaction aborted due to %VSTR, may need to be remapped if the RETRY command option is set to OFF. BTEQ by default assigns Error 2631 an error level of zero. If a request is unsuccessfully retried after a deadlock, the final BTEQ error level will be raised.

The ERRORLEVEL command can be used to remap the default severity (for example, zero for Error 2631). The ERRORMAP command can be used to verify the error level currently assigned to Error 2631.

The RETRY command has no effect on other types of errors that might occur repeatedly while processing a REPEAT or = command, such as error 3807—Database does not exist.

BTEQ does not resubmit a request if Teradata Database restarts when the RETRY command option is set to OFF and the CLOSE option of the EXPORT command is specified. In this case, the query results are lost because the output file is either empty or it contains only some of the returned rows.

If RETRY is set to ON and a retryable error occurs during an export containing deferred or non-deferred LDOs, BTEQ will not resubmit the current SQL request, due to recoverability issues for the LDO files. Instead, BTEQ will automatically terminate.

On workstation-attached systems, if the RETRY command option is set to ON during multiple sessions and the IMPORT command is used to provide data for USING modifiers, the data is saved in a buffer. This impacts system memory requirements, especially on a PC. The memory requirements can be calculated by multiplying the number of sessions by the amount of data. For example, if the maximum row size is 4,000 bytes and 10 sessions are in use, then BTEQ uses an additional 40,000 bytes. In this case, to avoid an out-of-memory condition for multiple sessions on the PC, set the RETRY command option to OFF.

When setting the RETRY command option to OFF, it remains off until either exiting BTEQ or using the RETRY command with the ON specification.

The RETRY command is valid in a Teradata SQL macro.

Example 1 – RETRY

To disable the RETRY command, type the following:

.SET RETRY OFF

Example 2 – RETRY instigated by a macro

The Example 1 RETRY command in a Teradata SQL macro appears as follows:

ECHO '.SET RETRY OFF';