Purpose
This control enables users to specify whether the database is to return applicable statement-level errors as independent responses instead of failing an entire multi-statement request.
This command allows BTEQ users to use the Teradata Database's Statement Independence (SI) feature. It may be useful to activate SI for imports where a pack factor greater than 1 is being used. Packing more than one record results in a single-statement request being turned into an iterated multiple-statement request. With SI OFF, all statements are treated as an implicit transaction subject to rollback. All statements must be deemed collectively able to succeed or they will be collectively rejected. If rejected, a single Failure or Error response will be returned from the database. Otherwise, each statement will have its own independent Ok or Success response returned. (Ok responses get returned for Field Mode and Success responses get returned for all other response modes.)
With SI ON, request-level rejection can still occur. However, the database will also be converting some data-related issues, such as a 2801 for a duplicate row, into a statement-level error instead. When the overall request will not result in any request-level failure, the database will let these statement-level errors be returned as independent responses that do not cause rejection of any other statement.
There are two basic effects when SI use is enabled. First, the DBS treats certain data-related request-level failures as statement-level errors which do not trigger rollback. Second, for imports, BTEQ will attempt to automatically resubmit requests that do fail due to a request-level data-related issue when more than one import record was packed for the request's USING data. BTEQ omits the rejected record from the packed USING data prior to the retry. These cleansed retries will repeat until there are no more request-level data-related errors in the pack.
In combination, the effects enable users to submit USING data that may not be completely clean and still take advantage of PACK factor use. All the unacceptable records will get rejected with a corresponding statement or request level failure or error. And when all the failures occurring are only for data-related issues, all acceptable records will get consumed without having to manually redrive their use due to rollbacks. Depending on the overall time needed for the import, the time savings could be significant.
Syntax
Usage Notes
The leading SET keyword is optional. The command takes values ON or OFF. OFF is the default state for the control. When neither ON or OFF is given, ON is assumed. The command cannot be used in an SQL ECHO statement.
The statement error parcel can be reported in Teradata mode for a BT/ET single or multi-request transaction, or an implicit transaction. It can also be reported in ANSI mode for a single or multi-request transaction that ends with a COMMIT statement. The request is considered successful for these cases even though one or more statement errors might have been reported. Reporting of statement error(s) in Teradata transaction mode is not any different than reporting in ANSI transaction mode. The difference comes only when the request is unsuccessful and will report an error (request rollback) in ANSI mode and a failure (transaction rollback) in Teradata mode.
Statement Independence supports the following data error types for INSERT statements:
- Column-level CHECK constraint violations
- Duplicate row errors for SET tables
- Primary index uniqueness violations
- Secondary index uniqueness violations
- Referential integrity violations
Example – SETSTATEMENTINDEPENDENCE
Assume an import is being done and that the first 5 records would have the following outcomes for their associated SQL INSERT statement if a PACK factor of 1 gets used:
- Record #1 will be rejected to prevent a duplicate row with error code 2802.
- Record #2 will be accepted as valid for the insert.
- Record #3 will be rejected as having unmatching data with error code 2673.
- Record #4 will be accepted as valid for the insert.
- Record #5 will be rejected as having unmatching data with error code 2673.
With a PACK factor of 5 and STATEMENTINDEPENDENCE left OFF, all records will be collectively rejected due to the database treating the 2802 and 2673 as request-level failures and BTEQ not intervening with any cleansed retries.
However, with a PACK factor of 5 and STATEMENTINDEPENDENCE turned ON, records #2 and #4 will be inserted with only the other 3 records rejected. This is because BTEQ will attempt cleansed retries to get past the initial 2 request-level 2673 failure responses and then the database will convert the 2802 into a statement-level error.
Assuming MESSAGES-RECORDNUMBERS is also used in the IMPORT command to have record number messages generated, the output could look as follows:
*** Starting record 1 at Fri Oct 2 02:03:11 2015
*** Failure 2673 The source parcel length does not match data that was defined.
Statement# 3, Info =1
*** Rejected Import Record# 3
*** Warning: Resubmitting last request with the rejected record omitted.
*** Failure 2673 The source parcel length does not match data that was defined.
Statement# 4, Info =1
*** Rejected Import Record# 5
*** Warning: Resubmitting last request with the rejected record omitted.
*** Statement Error 2802 Duplicate row error in <user>.<table>.
Statement# 1, Info =0
*** Rejected Import Record# 1
*** Insert completed. One row added.
*** Accepted Import Record# 2
*** Insert completed. One row added.
*** Accepted Import Record# 4
*** Finished at record 5 at Fri Oct 2 02:03:12 2015
*** Total number of statements: 5, Accepted : 2, Rejected : 3
*** Total requests sent to the DBC = 3
*** Pack Maximum: 5, Average : 5, Largest used : 5
*** Successful requests per second = 3.000