Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
SET ENVIRONMENT statementUse the SET ENVIRONMENT statement to specify settings for session environment variables that can affect how queries are executed in the same routine, or other operations in the current user session. For some options, the session variable overrides default behavior that is set by a configuration parameter or by an environment variable of the client or of the database server.
USE_SHARDING session environment optionThe USE_SHARDING session environment option controls the scope of sharded queries.

USE_SHARDING session environment option

The USE_SHARDING session environment option controls the scope of sharded queries.

The SET ENVIRONMENT USE_SHARDING statement of SQL supports the following syntax:

Usage

When you enable the USE_SHARDING environment option, sharded queries run on all shard servers in the cluster. When the USE_SHARDING environment option is disabled, sharded queries are limited to the local database. For consistent sharded insert, update, and delete operations, sharded transactions are applied with the two phase commit protocol instead of being eventually consistent. Data is moved to the appropriate shard server before the transaction is committed. Consistent shard insert, update and delete operations also require that you set the SHARD_ID configuration parameter on each shard server.

You can automatically run the SET ENVIRONMENT USE_SHARDING statement when the session connects to a database by defining the sysdbopen( ) routine, for example:

CREATE PROCEDURE PUBLIC.SYSDBOPEN()
   SET ENVIRONMENT USE_SHARDING ON;
END PROCEDURE