Skip to content

Commit 1f1d15a

Browse files
rbraineyrbrainey
authored
Merge pull request #23919 from danielva/master
Update to HANA Cloud ECN and Automation tutorials
2 parents d0ea128 + 9efd732 commit 1f1d15a

7 files changed

Lines changed: 97 additions & 11 deletions

File tree

‎tutorials/hana-cloud-automation-scheduling/download-sql-statements.png‎

42.5 KB
Loading

‎tutorials/hana-cloud-automation-scheduling/hana-cloud-automation-scheduling.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -23,11 +23,11 @@ primary_tag: software-product>sap-hana-cloud
2323
There are different tools that can be used to schedule commands. SAP HANA enables stored procedures to be scheduled. Linux provides a service called cron that can execute a script at a scheduled time. The SAP Automation Pilot is a service in the SAP BTP that has a feature to enable commands to be scheduled. This tutorial will demonstrate each of these methods of scheduling tasks.
2424

2525
### Scheduling SQL using CREATE SCHEDULER JOB
26-
In this step a diagnostic script from the [SQL Statement Collection for SAP HANA](https://me.sap.com/notes/1969700) will be used. If you have not already done so, download the zip. The download section shown below is near the bottom of the SAP Note.
26+
In this step a diagnostic script from the [SQL Statement Collection for SAP HANA](https://me.sap.com/notes/1969700) will be used. If you have not already done so, download SQLStatements_SHC.zip. The download section shown below is near the bottom of the SAP Note.
2727

2828
![download SQL statements](download-sql-statements.png)
2929

30-
These scripts are for SAP HANA databases. The script `HANA_Configuration_MiniChecks_SHC.txt` will be placed in a stored procedure and its output will be written to a table. The procedure can be scheduled to run once a day. It will check for configuration values that are outside of expected values.
30+
The script `HANA_Configuration_MiniChecks_SHC.txt` will be placed in a stored procedure and its output will be written to a table. The procedure can be scheduled to run once a day. It will check for configuration values that are outside of expected values.
3131

3232
1. Create a table to hold the results. Run the below SQL in the SAP HANA database explorer connected to an SAP HANA database.
3333

‎tutorials/hana-cloud-ecn/hana-cloud-ecn.md‎

Lines changed: 95 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
parser: v2
33
auto_validation: true
4-
time: 30
4+
time: 40
55
tags: [tutorial>intermediate, software-product-function>sap-hana-cloud--sap-hana-database,tutorial>license]
66
primary_tag: software-product>sap-hana-cloud
77
---
@@ -187,11 +187,12 @@ An ECN can be created in multiple ways. Further details on the options and limi
187187
### Create a workload class
188188
Workload classes can be used to direct a specified workload to an ECN. Further details on workload classes can be found at [Workload Management](https://help.sap.com/docs/HANA_CLOUD_DATABASE/f9c5015e72e04fffa14d7d4f7267d897/workload-management).
189189
190-
1. There are multiple ways to map a workload to an ECN node. The first step is to create a workload class.
190+
1. Create a workload class.
191191
192192
```SQL
193193
CREATE WORKLOAD CLASS "WLC1";
194194
```
195+
2. Route the an ECN.
195196
196197
The workload class can set a routing hint to the ECN.
197198
@@ -205,7 +206,7 @@ Workload classes can be used to direct a specified workload to an ECN. Further
205206
206207
![Select the routing location](routing-ui-2.png)
207208
208-
There are various ways that the workload can be mapped to the workload class.
209+
3. Map a workload to the workload class.
209210
210211
* A mapping to a user can be added
211212
@@ -233,7 +234,7 @@ Workload classes can be used to direct a specified workload to an ECN. Further
233234
234235
Additional details for these values can be found at [CREATE WORKLOAD MAPPING](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-sql-reference-guide/create-workload-mapping-statement-workload-management).
235236
236-
A workload class can be enabled, disabled, or deleted.
237+
4. Enable the workload class.
237238
238239
```SQL
239240
ALTER WORKLOAD CLASS "WLC1" DISABLE;
@@ -250,7 +251,7 @@ Workload classes can be used to direct a specified workload to an ECN. Further
250251
251252
![workload class details](mappings.png)
252253
253-
2. Run the query again using the ECN and verify that the query was run on the ECN node.
254+
5. Run the query again and verify that the query was run on the ECN node.
254255
255256
```SQL
256257
CALL CPU_AND_MEMORY_SPIKE();
@@ -264,7 +265,7 @@ Workload classes can be used to direct a specified workload to an ECN. Further
264265
265266
![ECN verification](ecn_ex1.png)
266267
267-
Notice that the last time the query was run, it was executed on the ECN as the HOST value ends in ecn1 . Notice also that the table location is not on the ECN.
268+
Notice that the last time the query was run, it was executed on the ECN as the HOST value ends in ecn1. Notice also that the table location is not on the ECN.
268269
269270
It is also possible to direct a query to a workload class through a hint as shown below.
270271
@@ -347,7 +348,12 @@ Before removing the ECN node, disable the workload class so that new queries are
347348
ALTER WORKLOAD CLASS "WLC1" DISABLE;
348349
```
349350
350-
If the ECN is removed and the workload class is still enabled, workloads directed to the ECN will fail with an error message "Client failed to reroute after a server change in workload class routing: Invalid routing location". This behavior can be changed using the [force_reroute](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-administration-guide/wlc) setting.
351+
If the ECN is removed and the workload class is still enabled, workloads directed to the ECN will fail with an error message "Client failed to reroute after a server change in workload class routing: Invalid routing location". This behavior can be changed using the [force_reroute](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-administration-guide/routing-queries-to-replicas) setting.
352+
353+
```SQL
354+
ALTER SYSTEM ALTER CONFIGURATION ('global.ini', 'DATABASE') SET ('distribution', 'force_reroute') = 'FALSE' WITH RECONFIGURE;
355+
SELECT * FROM SYS.M_CONFIGURATION_PARAMETER_VALUES WHERE SECTION = 'distribution';
356+
```
351357
352358
2. Remove the ECN.
353359
@@ -356,6 +362,83 @@ Before removing the ECN node, disable the workload class so that new queries are
356362
btp.exe get services/instance <instance ID>
357363
```
358364
365+
### Node.js app demonstrating prepared statements and the option routeDirectExecute on statement routing
366+
The below Node.js app is used to demonstrate that statements must be prepared first to be routed to an ECN as documented at [Statement Routing](https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/077d30cc847443288d3f0574356de4e7.html).
367+
368+
Some tools such as the SQL Console in SAP HANA Cloud Central, the SAP HANA database explorer, and [hdbsql](https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/c24d054bbb571014b253ac5d6943b5bd.html) by default, always prepare statements.
369+
370+
![prepare before execute](prepare-before-execute.png)
371+
372+
The below code when run, will not be executed on the ECN, unless the variable prepare is set to true or the routeDirectExecute option is set to true.
373+
374+
Further details on creating applications that connect to an SAP HANA Cloud database can be found at [Use Clients to Query an SAP HANA Database](https://developers.sap.com/mission.hana-cloud-clients.html).
375+
376+
1. Try running the below app and adjust the prepare and routeDirectExecute variables.
377+
378+
```JavaScript
379+
'use strict';
380+
var util = require('util');
381+
var hana = require('@sap/hana-client');
382+
383+
//Used to specify if prepare should be called before executing a query
384+
var prepare = false;
385+
386+
var connOptions = {
387+
//default value is false. If a statement is not prepared before executed, it is not routed to the ECN. Setting this value to true ensures a prepare.
388+
//Further details at https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/4fe9978ebac44f35b9369ef5a4a26f4c.html
389+
routeDirectExecute: 'false',
390+
391+
//Specify the connection parameters
392+
serverNode: 'host:port',
393+
UID: 'USER4',
394+
PWD: 'Password4',
395+
};
396+
console.log('routeDirectExecute: ' + connOptions.routeDirectExecute);
397+
398+
var connection = hana.createConnection();
399+
400+
connection.connect(connOptions);
401+
402+
var sql = 'SELECT SUM(VAL1) FROM MYTABLE;';
403+
var result;
404+
if (prepare) {
405+
//Prepare before execute
406+
console.log('prepare: ' + prepare);
407+
const statement = connection.prepare(sql);
408+
var results = statement.execQuery();
409+
if (results.next()) {
410+
result=results.getValues();
411+
} ;
412+
}
413+
else {
414+
//Direct execute,
415+
//Unless routeDirectExecute is set to true, this is not routed to ECN
416+
console.log('prepare: ' + prepare);
417+
result = connection.exec(sql);
418+
}
419+
console.log(util.inspect(result, { colors: false }));
420+
421+
//By looking at the host value, it can be determined if the previous query was run on the ECN node or not.
422+
sql = "SELECT HOST, STATEMENT_STRING, ACCESSED_TABLE_NAMES, TABLE_LOCATIONS, USER_NAME, LAST_EXECUTION_TIMESTAMP FROM M_SQL_PLAN_CACHE WHERE STATEMENT_STRING LIKE 'SELECT SUM(VAL1) FROM MYTABLE;' ORDER BY LAST_EXECUTION_TIMESTAMP DESC;"
423+
result = connection.exec(sql);
424+
console.log(util.inspect(result[0], { colors: false }));
425+
connection.disconnect();
426+
```
427+
428+
Output when the variable prepare is false.
429+
430+
![prepare is false](no-prepare.png)
431+
432+
Output when the variable prepare is true.
433+
434+
![prepare is true](prepare.png)
435+
436+
Output when the variable prepare is false but routeDirectExecute is true.
437+
438+
![routedirectexecute](route-direct-execute.png)
439+
440+
For further details on the methods of the connection class used above consult [prepare](https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/c63d283b3635469bb8afbefbbe7aea7b.html) and [execute](https://help.sap.com/docs/SAP_HANA_CLIENT/f1b440ded6144a54ada97ff95dac7adf/ef5564058b1747ce99fd3d1e03266b39.html).
441+
359442
### Procedure to check if the ECN is started
360443
The following procedure can be used to check if the ECN is available and then when it is to run a workload.
361444
@@ -409,7 +492,7 @@ The following steps demonstrate an approach to automating the creation, running
409492
Instructions on using hdbsql and setting the user key can be found at [Executing SQL Statements from a shell](hana-cloud-automation-cli).
410493
411494
### Use SAP Automation Pilot with an ECN
412-
The SAP Automation Pilot can be used to perform and schedule operations on services running in the SAP BTP. The tutorial [Automating SAP HANA Cloud Tasks with the SAP Automation Pilot Service](https://developers.sap.com/tutorials/hana-cloud-automation-pilot.html) can be used to started with the SAP Automation Pilot.
495+
The SAP Automation Pilot can be used to perform and schedule operations on services running in the SAP BTP. The tutorial [Automating SAP HANA Cloud Tasks with the SAP Automation Pilot Service](https://developers.sap.com/tutorials/hana-cloud-automation-pilot.html) can be used to started with the SAP Automation Pilot. The following step demonstrates how commands can be scheduled which will start and stop an ECN node and also execute SQL to enable or disable a workload class.
413496
414497
1. Import the catalog below into the SAP Automation Pilot.
415498
@@ -707,11 +790,14 @@ The SAP Automation Pilot can be used to perform and schedule operations on servi
707790
708791
![executed](executed.png)
709792
793+
If your SAP HANA Cloud instance has an allow list configured, add the IP from [IPs for requests from SAP Automation Pilot](https://help.sap.com/docs/automation-pilot/automation-pilot/what-is-sap-automation-pilot?version=Cloud).
794+
795+
If an error is shown authenticating with the BTP user, ensure that it is a technical user and does not have two factor authentication enabled. The SAP Note [3085908](https://me.sap.com/notes/3085908) may also help with authentication issues.
796+
710797
8. Schedule the AddECN and DeleteECN commands.
711798
712799
![Schedule](schedule.png)
713800
714-
715801
### ECN advisor
716802
SAP HANA Cloud Central includes an ECN advisor that provides recommendations for workloads that may be applicable to be run on an ECN.
717803

‎tutorials/hana-cloud-ecn/no-prepare.png‎

43 KB
Loading

‎tutorials/hana-cloud-ecn/prepare-before-execute.png‎

48.7 KB
Loading

‎tutorials/hana-cloud-ecn/prepare.png‎

42.7 KB
Loading

‎tutorials/hana-cloud-ecn/route-direct-execute.png‎

42.9 KB
Loading

0 commit comments

Comments
 (0)