Skip to content

Commit 2c36805

Browse files
danielvadanielva
committed
Updates to the ECN and SQL on Files tutorials
1 parent 6bc10cd commit 2c36805

27 files changed

Lines changed: 100 additions & 27 deletions

‎tutorials/hana-clients-choose-hana-instance/hana-clients-choose-hana-instance.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -44,7 +44,7 @@ There are multiple versions of SAP HANA. The information below is a list of lin
4444

4545
| Version | Notes
4646
| :------------- | :-------------
47-
| [SAP HANA Platform 2.0](https://help.sap.com/docs/SAP_HANA_PLATFORM) | Released in November 2016. Current version is 2.0 SPS 07, which was released on April 4rd, 2023.
47+
| [SAP HANA Platform 2.0](https://help.sap.com/docs/SAP_HANA_PLATFORM) | Released in November 2016. Current version is 2.0 SPS 08, which was released on Nov 20th, 2024.
4848
| **[SAP HANA, express edition](https://help.sap.com/docs/SAP_HANA_EXPRESS_EDITION)** | Released in September 2016. Current version is 2.0 SPS 07.
4949
| **[SAP HANA Cloud](https://help.sap.com/docs/hana-cloud)** | Released in March 2020. New features are released quarterly.
5050

‎tutorials/hana-cloud-ecn/added-ecn-details.png‎

39.7 KB
Loading

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

-40.4 KB
Loading

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

Lines changed: 27 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@ primary_tag: software-product>sap-hana-cloud
1111

1212
## Prerequisites
1313
- An SAP BTP account
14-
- An SAP HANA Cloud instance
14+
- An SAP HANA Cloud instance (2024 QRC 4 or higher)
1515

1616
## You will learn
1717
- How to create and delete an ECN
@@ -35,7 +35,7 @@ The following steps attempt to demonstrate an example of adding an ECN to cover
3535

3636
![instance details](instance-details.png)
3737

38-
Click on the memory, compute, network, or storage cards to open the Usage Monitor app. Below notice that the compute is spiking at a set frequency.
38+
Click on the memory, compute, network, or storage cards to open the Usage Monitor app. Below notice that the compute is spiking at a set frequency which is every morning at 7 am.
3939

4040
![Usage Monitor](usage_monitor.png)
4141

@@ -44,6 +44,7 @@ The following steps attempt to demonstrate an example of adding an ECN to cover
4444
```SQL
4545
CREATE USER USER4 PASSWORD "Password4" NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;
4646
GRANT CATALOG READ TO USER4;
47+
GRANT SELECT ON SCHEMA _SYS_STATISTICS TO USER4; --Used by the Elastic Compute Node tab
4748
GRANT WORKLOAD ADMIN TO USER4;
4849
CONNECT USER4 PASSWORD Password4;
4950
GRANT ALL PRIVILEGES ON SCHEMA USER4 TO DBADMIN;
@@ -76,7 +77,9 @@ The following steps attempt to demonstrate an example of adding an ECN to cover
7677
CALL CPU_SPIKE();
7778
CALL CPU_SPIKE();
7879
CALL CPU_SPIKE();
79-
80+
CALL CPU_SPIKE();
81+
CALL CPU_SPIKE();
82+
CALL CPU_SPIKE();
8083

8184
CREATE OR REPLACE PROCEDURE CPU_AND_MEMORY_SPIKE() LANGUAGE SQLSCRIPT AS
8285
BEGIN
@@ -93,6 +96,9 @@ The following steps attempt to demonstrate an example of adding an ECN to cover
9396
CALL CPU_AND_MEMORY_SPIKE();
9497
CALL CPU_AND_MEMORY_SPIKE();
9598
CALL CPU_AND_MEMORY_SPIKE();
99+
CALL CPU_AND_MEMORY_SPIKE();
100+
CALL CPU_AND_MEMORY_SPIKE();
101+
CALL CPU_AND_MEMORY_SPIKE();
96102
```
97103

98104
After calling the stored procedure CPU_SPIKE and CPU_AND_MEMORY_SPIKE, you can see that the CPU and memory usage has spiked, and you may also see an alert.
@@ -138,7 +144,7 @@ An ECN can be created in multiple ways. Further details on the options and limi
138144
}
139145
```
140146

141-
* Create the ECN using the BTP CLI. Further details on using the BTP CLI can be found at [Executing SAP HANA Cloud Tasks from the Command Line](hana-cloud-automation-cli).
147+
* Create the ECN using the BTP CLI or CF CLI. Further details on using the the CLI's can be found at [Executing SAP HANA Cloud Tasks from the Command Line](hana-cloud-automation-cli). An example using the BTP CLI is shown below.
142148
143149
```Shell
144150
btp login --sso
@@ -158,6 +164,8 @@ An ECN can be created in multiple ways. Further details on the options and limi
158164
159165
![ECN details page](added-ecn-details.png)
160166
167+
Note that the status is also shown.
168+
161169
* Through SQL
162170
163171
```SQL
@@ -183,6 +191,12 @@ Workload classes can be used to direct a specified workload to an ECN. Further
183191
ALTER WORKLOAD CLASS "WLC1" SET 'ROUTING LOCATION HINT' = 'ecn1';
184192
```
185193
194+
* This can also be accomplished in the workload management app as shown below.
195+
196+
![Workload management app](routing-ui-1.png)
197+
198+
![Select the routing location](routing-ui-2.png)
199+
186200
There are various ways that the workload can be mapped to the workload class.
187201
188202
* A mapping to a user can be added
@@ -244,15 +258,20 @@ Workload classes can be used to direct a specified workload to an ECN. Further
244258
245259
Notice that the last time the query was run, it was executed on the ECN. Notice also that the table location is not on the ECN.
246260
261+
It may also be of interest to compare the execution time between this run and after the table is replicated onto the ECN node in a subsequent step.
262+
247263
### Route to ECN with a hint
248264
An alternate approach without using a workload class is to use a hint.
249265
250266
1. Execute the below SQL.
251267
252268
```SQL
253269
ALTER WORKLOAD CLASS "WLC1" DISABLE;
270+
254271
SELECT * FROM M_VOLUMES; --1024 is the VOLUME_ID for the ECN
272+
255273
CALL USER4.CPU_AND_MEMORY_SPIKE() with hint (RESULT_LAG('HANA_ATR'), route_to(1024));
274+
256275
SELECT HOST, STATEMENT_STRING, ACCESSED_TABLE_NAMES, TABLE_LOCATIONS, USER_NAME, LAST_EXECUTION_TIMESTAMP
257276
FROM M_SQL_PLAN_CACHE
258277
WHERE STATEMENT_STRING LIKE 'CALL %'
@@ -262,16 +281,19 @@ An alternate approach without using a workload class is to use a hint.
262281
![using a hint](hint.png)
263282
264283
### Replicate a table to the ECN
265-
A replica table can be stored on an ECN. This may lead to lower resource consumption on the coordinator. For additional details see [Advantages and Disadvantages of Table Replication](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-administration-guide/consider). The following steps demonstrate replicating a table to the ECN and using it in a query.
284+
A replica table can be stored on an ECN. This may lead to lower resource consumption on the coordinator and reduced latency. For additional details see [Advantages and Disadvantages of Table Replication](https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-administration-guide/advantages-and-disadvantages-of-table-replication). The following steps demonstrate replicating a table to the ECN and using it in a query.
266285
267286
1. Run the below in the SQL Console after replacing the host name with the value from the previous query against the M_VOLUMES table view.
268287
269288
```SQL
270289
ALTER WORKLOAD CLASS "WLC1" ENABLE;
290+
271291
ALTER TABLE USER4.MYTABLE ADD ASYNCHRONOUS REPLICA AT '6101b9d8-bcad-44dc-98c4-388e64ce7370-ern-ecn1:30040';
292+
272293
ALTER TABLE USER4.MYTABLE ENABLE ASYNCHRONOUS REPLICA;
273294
--ALTER TABLE USER4.MYTABLE DISABLE ASYNCHRONOUS REPLICA;
274295
--ALTER TABLE USER4.MYTABLE DROP REPLICA AT '6101b9d8-bcad-44dc-98c4-388e64ce7370-ern-ecn1:30040';
296+
275297
SELECT * FROM M_TABLE_LOCATIONS WHERE TABLE_NAME LIKE '%MYTABLE%';
276298
SELECT * FROM M_TABLE_REPLICAS;
277299
```

‎tutorials/hana-cloud-ecn/routing-ui-1.png‎

102 KB
Loading

‎tutorials/hana-cloud-ecn/routing-ui-2.png‎

73.2 KB
Loading

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

86.3 KB
Loading

‎tutorials/hana-dbx-sof/add-database-with-different-user.png‎

-6.52 KB
Loading

‎tutorials/hana-dbx-sof/create-virtual-table-wizard.png‎

96 KB
Loading

‎tutorials/hana-dbx-sof/database-objects2.png‎

82.6 KB
Loading

0 commit comments

Comments
 (0)