Nested table expressions for error tolerance - example

The following examples illustrate how to use the RETURN DATA UNTIL clause to return query results when one or more federated data sources are not available.

Example: The following SQL statement selects data from three different servers: SQLServer, Oracle, and Sybase.

SELECT c1 FROM 
TABLE RETURN DATA UNTIL 
FEDERATED SQLSTATE '08001' SQLCODE -30080, -30082 
WITHIN(SELECT c1 FROM n1_from_SQLServer) etq1 
UNION ALL 
SELECT c1 FROM 
TABLE RETURN DATA UNTIL 
FEDERATED SQLSTATE '08001' SQLCODE -30080, -30082 
WITHIN (SELECT c1 FROM n2_from_Oracle) etq2 
UNION ALL 
SELECT c1 FROM 
TABLE RETURN DATA UNTIL 
FEDERATED SQLSTATE '08001' SQLCODE -30080, -30082 
WITHIN(SELECT c1 FROM n3_from_Sybase) etq3;

Scenario 1: One server is not available.

In this scenario, the Oracle server is not available. The SQLServer server and Sybase server are available. In this situation, the query in the second branch of the UNION ALL operation returns an empty result set with the 30080 error, which is specified to be tolerated. The query returns the results from n1_from_SQLServer and from n3_from_Sybase. Warning sqlwarn5='E' is issued.

The result set is equivalent to running the following query:

SELECT c1 FROM n1_from_SQLServer 
UNION ALL 
SELECT c1 FROM n3_from_Sybase;

Scenario 2: All servers are not available.

In this scenario, all servers (SQLServer, Oracle, and Sybase) are unavailable. In this situation, the UNION ALL operation returns an empty result set. Warning sqlwarn5='E' is issued.

Scenario 3: All servers are available.

If all of the servers are available, the result set of the query is equivalent to running the same query without specifying the RETURN DATA UNTIL clause.