GRID clause

The GRID clause is not valid unless the session is connected to a database within an existing grid. A grid can be created by using appropriate cdr commands and ifx_grid routines of the Enterprise Replication facility.

This syntax is an extension to the ANSI/ISO standard for the SQL language.

This syntax fragment is part of the SELECT statement.

1  GRID? ALL '
2.1 grid
2.1 region
1 '

Usage

Any SELECT statement that explicitly or implicitly includes the GRID clause is called a grid query. The result of a grid query are qualifying rows from a logical UNION or UNION ALL of each table in the FROM clause across tables with the same names and the same schema in every grid server. This union can include tables across all nodes in the grid, or across a subset of those grid nodes, called a region.

The optional ALL keyword

If the optional ALL keyword immediately follows the GRID keyword, the result of the grid query is a logical UNION ALL. meaning that the result set of the grid query can include duplicate rows. Otherwise, if you omit the ALL keyword, only distinct values are returned from the logical UNION of the results from each participating grid server.

SET ENVIRONMENT statement options for grid queries

Two options to the SET ENVIRONMENT statement can define a default GRID clause, so that any subsequent SELECT statements with no GRID clause is interpreted as grid query that includes the default GRID clause:

SET ENVIRONMENT SELECT_GRID

This statement can specify a grid or region as the default scope of subsequent grid queries that return the union of unique qualifying rows. The GRID clause can omit the grid or region name for grid queries that return UNION results for the specified default nodes.

SET ENVIRONMENT SELECT_GRID_ALL

This statement can specify a grid or region as the default scope of subsequent grid queries that return the union of all qualifying rows, including duplicates. The GRID clause can omit the grid or region name for grid queries that return UNION ALL results for the specified default nodes.

While either of these options to the SET ENVIRONMENT statement is enabled, the SQL parser applies the current default GRID clause to every SELECT statement in the session that does not include an explicit GRID clause. No more than one default GRID clause can be in effect during the same session at the same point in time. When either option is in effect, using the SET ENVIRONMENT statement to set the other keyword option, or to reset the same keyword option for a different grid or region disables the previously set default.

SET ENVIRONMENT SELECT_GRID DEFAULT;_
SET ENVIRONMENT SELECT_GRID_ALL DEFAULT; 

SET ENVIRONMENT SELECT_GRID 'region_03'
SELECT * FROM tab1;
SELECT * FROM tab2;

SELECT * FROM tab1 GRID 'region_03';
SELECT * FROM tab2 GRID 'region_03';

No more than one of the SELECT_GRID and SELECT_GRID_ALL session environment options to the SET ENVIRONMENT statement can be enabled for the current user session at the same point in time. When either option is in effect, using the SET ENVIRONMENT statement to set the other keyword option, or to reset the same keyword option for a different grid or region disables the previously set default.

The following SQL statements replace the previous default GRID clause by defining a different default GRID clause that combines the UNION ALL results from participating grid servers in a different region, the region_04 subset of a grid:

SET ENVIRONMENT SELECT_GRID_ALL 'region_04'
SELECT * FROM tab1;
SELECT * FROM tab2;

SELECT * FROM tab1 GRID ALL 'region_04';
SELECT * FROM tab2 GRID ALL 'region_04';

By default, a grid query fails if the database server that issues the grid query cannot connect to one or more of the nodes within the grid or region that the explicit or default GRID clause specifies. Another session environment variable that the SET ENVIRONMENT statement can enable can return partial results from a grid query, even if some grid servers in the specified grid or region are unavailable:

SET ENVIRONMENT GRID_NODE_SKIP

This statement can enable processing of a grid query to continue when one or more of the grid servers is unavailable.

SET ENVIRONMENT GRID_NODE_SKIP ON;

Another function, ifx_gridquery_skipped_node_count(), can be used to detect how many nodes were skipped. For more information about these functions, see the HCL OneDB Enterprise Replication Guide.

Tables in the FROM clause of grid queries

Only permanent database tables are valid in the FROM clause of a grid query. They must be defined as grid tables by running the cdr change gridtable command.

In addition, the projection list of a grid query cannot include any column or expression whose data type is not supported in cross-server queries. The unsupported data types include all complex or large-object types, and some user-defined types (UDTs) and opaque types.

The same restrictions that apply to DISTINCT data types in distributed DML operations across databases of the same HCL OneDB instance also apply to DISTINCT data types in grid queries. For a discussion of the data types that are valid in distributed queries, see the topics Data Types in Cross-Server Transactions and DISTINCT Types in Distributed Operations.

Additional restrictions on grid queries

The user executing the grid query must be a valid user on all nodes within the grid or region.

A grid query cannot be a subquery that contains references to its outer query.

Neither the UNION nor UNION ALL set operators, nor the INTERSECT, MINUS, or EXCEPT set operators, are valid in a grid query block.

The GRID clause should not be included in SELECT statements outside a grid context. For more information about grids, see the SET ENVIRONMENT statement and the HCL OneDB Enterprise Replication Guide.