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:

USE_SHARDING session environment option 

1  SET ENVIRONMENT USE_SHARDING 
2.1 ON
2.1 OFF

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