Error tolerance in nested table expressions

Error tolerance is a mechanism that allows query execution to continue while tolerating certain SQL errors in nested table expressions. With error tolerance, instead of receiving an error for a part of a query and terminating the entire query, you can obtain maximum query results from available data.

When the federated server encounters an allowable error, the server allows the error and continues processing the remainder of the query rather than returning an error for the entire query. The result set that the federated server returns can be a partial or an empty result.

When the federated server tolerates errors, it returns query results even when the data sources that the query accesses are not available. This mechanism is useful when you need to return as much information as is available, despite incomplete query results. For example, consider a doctor who needs data about a particular type of medical condition. A query is run to gather information from remote data sources at several different hospitals. If one or more hospital databases are not available, the results from only the available databases are still very valuable to the doctor.

Queries that contain UNION ALL branches can benefit from error tolerance. Without this mechanism, if processing of one branch of the query encounters an error, the federated server stops processing the query. With this mechanism, when you specify the error to tolerate on that same branch of the query, the federated server tolerates the error and continues to navigate to the rest of the available branches. The UNION ALL operation returns the results from any available data sources.

Example: The following query selects data from three nicknames on three different servers:

SELECT c1 from nickname1_on_server1 
UNION ALL 
SELECT c1 from nickname2_on_server2 
UNION ALL 
SELECT c1 from nickname3_on_server3

When nickname2_on_server2 is not available, or when the remote server server2 is not available during query processing, you can obtain the result set from nickname1_on_server1 and from nickname3_on_server3 by tolerating the errors with nickname2 and server2. A result set from two of the three query branches is equivalent to running the following query:

SELECT c1 from nickname1_on_server1 
UNION ALL 
SELECT c1 from nickname3_on_server3

You can specify the SQL errors that you want to allow in a nested table expression during query processing. The types of errors that the federated server tolerates are errors with remote connections, authorization, and authentication.