Routing Queries to Elastic Compute Nodes

To avoid the need to modify SQL queries you can apply hints for routing and asynchronous replication as workload class properties and map queries to the class. The workload class then applies the hint when the query is executed.

Two workload class properties are available to add routing hints to workload classes. Use either or both of these properties as necessary to apply a routing hint and avoid the need to modify SQL statements – the hint is applied to all statements executed by this class. Hints can be removed from the class by using UNSET.

The ROUTING LOCATION HINT property can be set to the value of a table placement location (as defined in the monitoring view M_TABLE_PLACEMENT_LOCATIONS). This has the same effect as applying the ROUTE_TO hint on the SQL command line. The following example routes queries mapped to this class to the group of tables 'ecn_group1'.

Use the RESULT LAG HINT property with the value 'HANA_ATR' to route queries to an asynchronous replica. This has the same effect as applying the RESULT LAG ('hana_atr') hint on the SQL command line:

1
CREATE WORKLOAD CLASS "MyWLC" SET 'ROUTING LOCATION HINT' = 'ecn_group1', 'RESULT LAG HINT' = 'HANA_ATR';

A configuration parameter is available that you can use to control routing in the event that the ECN cannot be reached for some reason. Automatic rerouting back to the coordinator is prevented by default by the force_reroute parameter in the distribution section of the global.ini file. The parameter is set to TRUE by default, which applies the routing defined in the ROUTING LOCATION HINT property, and in the case of failure simply returns an error message rather than routing back to the coordinator (see Options for Handling Rerouting in the previous topic).

You can map queries to this workload class in a number of ways (see topic 'Properties for Workload Class Mappings' for full details).

This first example is based on a named user (ECN_USER) so that the workload class settings of MyWLC are applied to all queries submitted by this user:

1
CREATE WORKLOAD MAPPING "MyWLCMapping1" WORKLOAD CLASS "MyWLC" SET 'USER NAME' = 'ECN_USER';

In this example, a session variable is used so that the workload class settings of MyWLC are applied to all queries with an application component name (session variable value) of 'My_APP':

1
CREATE WORKLOAD MAPPING "MyWLCMapping2" WORKLOAD CLASS "MyWLC" SET 'APPLICATION COMPONENT NAME' = 'My_APP';

You may need to apply different workload class limits to the ECN other than the limits that apply to the coordinator. This is possible using the SET AT LOCATION syntax. The following example applies a statement thread limit at three different targets. The first limit applies generally; the other limits apply to named locations:

1234
CREATE WORKLOAD CLASS MyChildWc PARENT MyParentWc
SET 'STATEMENT THREAD LIMIT' = '11' 
SET AT LOCATION 'coordinator' 'STATEMENT THREAD LIMIT' = '22'
SET AT LOCATION 'ECN_4' 'STATEMENT THREAD LIMIT' = '44';

Refer to the system views WORKLOAD_CLASSES andWORKLOAD_MAPPINGS for details of the properties of workload classes.

The monitoring view M_PREPARED_STATEMENTS includes the WORKLOAD_CLASS_NAME of the workload class that was applied to the execution of a statement.

The monitoring view M_WORKLOAD shows workload values over time expressed as rates where data is collected every minute.

The query execution plan EXPLAIN_PLAN_TABLE System View has schema and table name.

Execution information in the monitoring view M_SQL_PLAN_CACHE is helpful to verify the details of the data being read by each statement string. This view shows statement compile and execution information such as accessed table names, execution count, execution count by routing, execution location, and so on.