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.
SELECT c1 from nickname1_on_server1 UNION ALL SELECT c1 from nickname2_on_server2 UNION ALL SELECT c1 from nickname3_on_server3
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.