GRID_NODE_SKIP session environment option

Use the GRID_NODE_SKIP option of the SET ENVIRONMENT statement to define the behavior of the database server when a grid server within the grid or region that the query specifies is not available.

GRID_NODE_SKIP environment option

Usage

The GRID_NODE_SKIP session environment option can be set to any of three values:

ON: If a grid server within the specified grid or region is not available when the grid query attempts to retrieve qualifying rows from that node, an error is returned to the server that issued the grid query. That server continues processing the query by attempting to connect to the next server in the grid or region, unless the server whose results were skipped was last among the participating grid servers. In that case, the results from the available nodes are combined, and the logical UNION or UNION ALL from the results of the available grid servers is calculated by the server that issued the grid query.
DEFAULT: This setting specifies the default behavior. If a grid server within the specified grid or region is not available when the grid query attempts to retrieve qualifying rows from a database of that node, an error is returned to the server that issued the grid query, and the query fails.
OFF: This has the same effect as the explicit DEFAULT setting, as described above.

When the SET ENVIRONMENT GRID_NODE_SKIP ON statement is in effect for the current session, a grid query can return results when multiple grid servers are unavailable. The identity of a skipped grid server can be returned by executing the ifx_gridquery_skipped_nodes() function. Another function, ifx_gridquery_skipped_node_count(), can be called to detect how many nodes of the grid or region were skipped. For more information about these functions, see the HCL OneDB™ Enterprise Replication Guide.

Because a grid query is a dynamic UNION or UNION ALL combined query, the decision to skip a grid server occurs at the start of statement preparation, not when the statement is executed. The reason for this is that if information from the remote data dictionary cannot be obtained, then the SELECT statement cannot be prepared. Therefore, the database server that issues the grid query makes the decision to skip or not skip any unavailable grid servers prior to statement execution, if the GRID_NODE_SKIP session environment option is set to ON.

The GRID_NODE_SKIP setting has no effect outside a grid context.

Examples of setting GRID_NODE_SKIP

From a session in a database of a grid server, the following statement prevents subsequent grid queries from failing with an error if any of the grid servers are unavailable when the grid or the region of the query:

SET ENVIRONMENT GRID_NODE_SKIP ON;

With the GRID_NODE_SKIP option enabled, the database server ignores any node which is not available when it executes a grid query, and returns qualifying rows from the participating grid servers.

The next example has the opposite effect, disabling the GRID_NODE_SKIP option:

SET ENVIRONMENT GRID_NODE_SKIP DEFAULT;

The following statement achieves the same effect as the DEFAULT setting, but with fewer keystrokes:

SET ENVIRONMENT GRID_NODE_SKIP OFF;