### Start Database Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/38f6d1231bf84614853a17206a1e82f8.html Demonstrates how to start a specific tenant database named 'my_database'. Ensure you have the necessary DATABASE ADMIN or DATABASE START privileges. ```sql ALTER SYSTEM START DATABASE my_database; ``` -------------------------------- ### Create Table and Insert Data for CUME_DIST Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/f4351278aade4ac3be7618181acd2ea4.html This snippet demonstrates how to create a table named ProductSales and populate it with sample data. This setup is necessary to illustrate the usage of window functions like CUME_DIST. ```sql CREATE ROW TABLE ProductSales(ProdName VARCHAR(50), Description VARCHAR(20), Sales INT); INSERT INTO ProductSales VALUES('Tee Shirt','Plain',21); INSERT INTO ProductSales VALUES ('Tee Shirt','Lettered',22); INSERT INTO ProductSales VALUES ('Tee Shirt','Team logo',30); INSERT INTO ProductSales VALUES('Hoodie','Plain',60); INSERT INTO ProductSales VALUES ('Hoodie','Lettered',65); INSERT INTO ProductSales VALUES ('Hoodie','Team logo',80); INSERT INTO ProductSales VALUES('Ballcap','Plain',8); INSERT INTO ProductSales VALUES ('Ballcap','Lettered',40); INSERT INTO ProductSales VALUES ('Ballcap','Team logo',27); ``` -------------------------------- ### Create Multistore Table Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/fedf200eab634f2b8c8dc08f52f7efbc.html Example demonstrating the creation of a multistore table with partitions on both default and extended storage. ```sql CREATE COLUMN TABLE ext_a (c1 INT, c2 INT) PARTITION BY RANGE (c1) (USING DEFAULT STORAGE (PARTITION 0 <= values < 10000, PARTITION 10000 <= values < 1000000) USING EXTENDED STORAGE (PARTITION 1000000 <= values < 100000000)); ``` -------------------------------- ### Example: Setting a Configuration Parameter Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/0f9c94eb5c5b4f43abef8e83b0443cc3.html An example demonstrating how to set a specific parameter within a configuration file. This example targets the 'indexserver.ini' file in the SYSTEM layer, setting the 'enable' parameter in the 'trace' section to 'true'. ```sql ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'SYSTEM') SET ('trace', 'enable') = 'true' WITH RECONFIGURE; ``` -------------------------------- ### Move Partitions Example (Online) Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/a4258b865710423b8712b06fb5b8e7c5.html Example of moving multiple partitions to different hosts with the ONLINE option. ```sql ALTER TABLE T1 MOVE PARTITION 1,2 TO 'MyServer1:34240', PARTITION 3,4 TO 'MyServer2' ONLINE ``` -------------------------------- ### Create Table and Insert Data for MIMETYPE Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e485b2751910148316cfd763478894.html Creates a column table with TEXT content and inserts both plain text and HTML examples. This setup is required before using the MIMETYPE function. ```sql CREATE COLUMN TABLE T (CONTENT TEXT FAST PREPROCESS OFF); INSERT INTO T VALUES('This is an example'); INSERT INTO T VALUES('This is an example'); ``` -------------------------------- ### Create Table and Index Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d66989751910148a64a797565ad4dc.html Example demonstrating how to create a table and then add a full text index to one of its columns. ```sql CREATE ROW TABLE A (a INT, b NVARCHAR(10), c NVARCHAR(20)); CREATE INDEX i ON a(b); ``` -------------------------------- ### Create a PSE Store Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4d80bf63fc374a7f99be94d8ce70a07a.html An example demonstrating how to create a PSE store with a specific name. Ensure you have TRUST ADMIN privileges. ```sql CREATE PSE example_pse; ``` -------------------------------- ### Move Partitions Example (Offline) Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/a4258b865710423b8712b06fb5b8e7c5.html Example of moving multiple partitions to a single host without the ONLINE option. ```sql ALTER TABLE T1 MOVE PARTITION 1,2 TO MyServer1:34240; ``` -------------------------------- ### Create Credential Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20cfdad57519101482fcb19af478ec34.html Example of creating a credential for all users used by the component INTERNAL_APP with a specific purpose and password type. ```sql CREATE CREDENTIAL FOR COMPONENT 'INTERNAL_APP' PURPOSE 'COMPANY_MASTER_MACHINE' TYPE 'PASSWORD' USING 'PASSWORD_9876'; ``` -------------------------------- ### Profile Using Root Statement Hash Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/864e9b9851bb467e9c0c4c1f285bef12.html This example demonstrates profiling using a root statement hash. It involves starting the profiler, executing a query, stopping the profiler, and then saving or clearing the data based on sample counts. ```sql ALTER SYSTEM START KERNEL PROFILER ROOT_STATEMENT_HASH 'a68e7882f87a2ec501604ce6feb7dee7'; SELECT TOP 10 * from M_SERVICE_THREADS ORDER BY DURATION; --has this hash (maybe execute several times) ALTER SYSTEM STOP KERNEL PROFILER; SELECT * FROM SYS.M_KERNEL_PROFILER; --check for root_statement_hash column and also if SAMPLE_COUNT > 0 ALTER SYSTEM SAVE KERNEL PROFILER; --if SAMPLE_COUNT > 0 ALTER SYSTEM CLEAR KERNEL PROFILER; ``` -------------------------------- ### Register Secondary Site Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/7ea377f857fc483f844308e9c40a8f25.html Example of registering a secondary site named 'SiteB' with specific replication and operation modes. ```sql ALTER SYSTEM REGISTER SYSTEM REPLICATION SITE 'SiteB' REMOTE HOST '' REPLICATION MODE 'sync' OPERATION MODE 'logreplay'; ``` -------------------------------- ### Create Audit Policy Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/f8575f59c76b4d639287316db152cafd.html This example demonstrates how to create an audit policy named 'ext_store_admin_policy' with specific audit levels. ```sql CREATE AUDIT POLICY ext_store_admin_policy CREATE EXTENDED STORAGE, ALTER EXTENDED STORAGE, DROP EXTENDED STORAGE LEVEL CRITICAL ``` -------------------------------- ### Create Table for Dynamic Range Partitioning Examples Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/f7ae27ca1b954471a4e1a7feab2c24d7.html This CREATE TABLE statement is used as the basis for the dynamic range partitioning examples. ```sql CREATE COLUMN TABLE A1 (A INT, B INT NOT NULL) PARTITION BY RANGE (A) (PARTITION VALUES = 10), RANGE (B) (PARTITION VALUES = 20, PARTITION OTHERS); ``` -------------------------------- ### Save Performance Trace Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d2973275191014bafef616be15326a.html This example demonstrates how to save raw performance trace data into a specific file named 'mytrace.tpt'. ```sql ALTER SYSTEM SAVE PERFTRACE INTO FILE 'mytrace.tpt'; ``` -------------------------------- ### Create Table Type Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d5c1ed75191014a80d897035629def.html An example demonstrating how to create a user-defined table type named 'tt_publishers' with specified columns and data types. ```sql CREATE TYPE tt_publishers AS TABLE ( publisher INTEGER, name VARCHAR(50), price DECIMAL, cnt INTEGER); ``` -------------------------------- ### Example: Remove Trace Files Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d25bff75191014965ad9dcc606d225.html This example demonstrates how to remove specific trace files ('extrace.py', 'extrace.py.old') from the host named 'hananode01'. ```sql ALTER SYSTEM REMOVE TRACES ('hananode01', 'extrace.py', 'extrace.py.old'); ``` -------------------------------- ### Example: Get Suggested Terms with TM_GET_SUGGESTED_TERMS Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/6c6e7f7309b244188dcfe023e2d3605f.html Retrieves the top 5 terms starting with 'john' from a news table, filtering results for a score greater than 0.1 and term frequency greater than 5. This demonstrates type-ahead functionality. ```sql SELECT T.RANK, T.TERM, T.NORMALIZED_TERM, T.TERM_TYPE, T.TERM_FREQUENCY, T.DOCUMENT_FREQUENCY, T.SCORE FROM TM_GET_SUGGESTED_TERMS ( TERM 'john' SEARCH "content" FROM "myschema"."news" RETURN TOP 5 ) AS T WHERE T.SCORE > 0.1 AND T.TERM_FREQUENCY > 5; ``` -------------------------------- ### LOCATE Function Example: Negative Start Position Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e3b6b77519101485e6bd62f7018f75.html Provides examples of using a negative start position to search from the end of the string. ```SQL SELECT LOCATE('AAA', 'A', -1) FROM "DUMMY"; ``` ```SQL SELECT LOCATE('AAA', 'A', -2) FROM "DUMMY"; ``` ```SQL SELECT LOCATE('AAA', 'A', -3) FROM "DUMMY"; ``` ```SQL SELECT LOCATE('AAA', 'A', -4) FROM "DUMMY"; ``` ```SQL SELECT LOCATE('ABABAC', 'A', -2) FROM "DUMMY"; ``` -------------------------------- ### Initialize etsservice Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/eafc3be81c6a48db84941f2cd6d0c8ef.html This example demonstrates initializing the etsserver service on a specific host and port with password credentials. Ensure the port number includes the correct instance number. ```sql ALTER SYSTEM INITIALIZE SERVICE 'etsserver' at 'myasehostname:30021' with CREDENTIAL TYPE 'PASSWORD' USING 'user=sa;password=MySecretPass' ``` -------------------------------- ### LOCATE_REGEXPR Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/cb4866494bd647cd8926763aa940f613.html An example demonstrating how to use the LOCATE_REGEXPR function to find the starting position of the day part in a date string. It returns 7. ```sql SELECT LOCATE_REGEXPR(START '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})' IN '20140401' GROUP 3) "locate_regexpr" FROM DUMMY; ``` -------------------------------- ### Create Table, Library, and Procedure Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/62263ce35ac74488a397553fcb25a7d6.html This example demonstrates creating a table, a SQLScript library with a function and a procedure, and a procedure that calls the library. It shows how to define constants, functions, and procedures within a library and how to use them in another procedure. ```sql CREATE TABLE data_table(col1 INT); DO BEGIN DECLARE idx INT = 0; FOR idx IN 1..200 DO INSERT INTO data_table VALUES (:idx); END FOR; END; CREATE OR REPLACE LIBRARY mylib AS BEGIN PUBLIC VARIABLE maxval CONSTANT INT = 100; PUBLIC FUNCTION bound_with_maxval(i INT) RETURNS x INT AS BEGIN x = CASE WHEN :i > :maxval THEN :maxval ELSE :i END; END; PUBLIC PROCEDURE get_data(IN size INT, OUT result table(col1 INT)) AS BEGIN RESULT = SELECT TOP :size col1 FROM data_table; END; END; CREATE PROCEDURE myproc (IN inval INT) AS BEGIN USING mylib AS mylib; DECLARE var1 INT = mylib:bound_with_maxval(:inval); IF :var1 > mylib:maxval THEN SELECT 'unexpected' FROM dummy; ELSE DECLARE tv table (col1 INT); CALL mylib:get_data(:var1, tv); SELECT COUNT(*) FROM :tv; END IF; END; CALL myproc(10); - returns the value 10 CALL myproc(150); – returns the value 100 ``` -------------------------------- ### NTH_VALUE Example with Spatial Data Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/6522df94eee542d5b1700a7766e6e6a2.html Example showing how to get the 3rd ST_Geometry point value from a table ordered by ID. The result is in binary format. ```sql CREATE TABLE TAB (ID INT, SHAPE ST_Geometry(4326)); INSERT INTO TAB VALUES(1, ST_GeomFromText('POINT(1 1)', 4326)); INSERT INTO TAB VALUES(2, ST_GeomFromText('POINT(2 2)', 4326)); INSERT INTO TAB VALUES(3, ST_GeomFromText('POINT(3 3)', 4326)); INSERT INTO TAB VALUES(4, ST_GeomFromText('POINT(4 4)', 4326)); SELECT NTH_VALUE(SHAPE, 3 ORDER BY ID) FROM TAB; ``` -------------------------------- ### BITSET Function Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d22d0974d29510149373deadadc83fe4.html Demonstrates how to use the BITSET function to set bits in a VARBINARY string. The example shows setting 3 bits starting from the 1st position in '0000'. ```sql SELECT BITSET ('0000', 1, 3) "bitset" FROM DUMMY; ``` -------------------------------- ### Get Result Cache ID Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/9e9da58b772a4df4811e3d0845679d5d.html This example demonstrates how to retrieve the cache ID for a specific result cache entry using the RESULT_CACHE_ID function. Ensure the 'RESULT_CACHE' hint is used. ```sql SELECT DISTINCT RESULT_CACHE_ID () FROM V1 WITH HINT(RESULT_CACHE); ``` -------------------------------- ### Valid Export Path Examples Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/c07f72ed78764bf285e6d77199cae9fa.html Provides examples of valid file system paths for SAP HANA exports, including archive file names. ```sql '/tmp' ``` ```sql '/hana/shared/HDB/HDB00/backup' ``` ```sql '/hana/shared/HDB/HDB00/work/myexport.tgz' ``` -------------------------------- ### LOCATE Function Example: Basic Search Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e3b6b77519101485e6bd62f7018f75.html Illustrates finding the starting position of a substring within a larger string. ```SQL SELECT LOCATE ('length in char', 'length') "locate" FROM DUMMY; ``` -------------------------------- ### Example: Setting a Parameter for a Tenant Database Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/0f9c94eb5c5b4f43abef8e83b0443cc3.html This example shows how to set a parameter in the 'esserver.ini' file for a specific tenant database. The 'layer' is 'DATABASE' and 'layer_name' specifies the tenant database name. ```sql ALTER SYSTEM ALTER CONFIGURATION ('esserver.ini', 'DATABASE', 'dv_tenant1') SET ('storage', 'ess_enabled') = 'true' WITH RECONFIGURE; ``` -------------------------------- ### Get Current User ID Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/75262ff0734c40cdb9fe2f2d15b6e750.html This example demonstrates how to retrieve the current user's ID using the CURRENT_USER_ID function in a SELECT statement. It queries from the DUMMY table, which is a common practice for standalone SQL function calls. ```sql SELECT CURRENT_USER_ID() from DUMMY; ``` -------------------------------- ### Example: Create Runtime Dump Using a Profile Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/6954969bba544da190ca53c1bf367086.html Shows how to create a runtime dump using a predefined profile ('myRTEProfile') and save it to a specified file on the connected service. ```sql ALTER SYSTEM CREATE RUNTIMEDUMP PROFILE 'myRTEProfile' INTO FILE 'my_rte_dump.trc'; ``` -------------------------------- ### Initialize Streaming Analytics Service Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/ec963ad9ab064410ab70114be44ddf88.html Example of initializing the 'streamingserver' service on a specific host and port, using password authentication with a provided cluster password. ```sql ALTER SYSTEM INITIALIZE SERVICE 'streamingserver' at 'mysdshostname:30016' with CREDENTIAL TYPE 'PASSWORD' USING 'MyStreamingClusterPassword' ``` -------------------------------- ### SAP HANA SQL NOW Function Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e5db42751910148cebadec507448b5.html Use this SQL query to get the current timestamp. The result will be aliased as 'now'. ```sql SELECT NOW () "now" FROM DUMMY; ``` -------------------------------- ### Create Table with Partitioning and Insert Rows Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/83ab0209ecae446e90c70e139ad524fc.html Demonstrates creating a column table with range partitioning and inserting initial data. ```sql CREATE COLUMN TABLE T (KEY INT PRIMARY KEY, VAL INT) PARTITION BY RANGE (KEY) ( USING DEFAULT STORAGE (PARTITION '0' <= VALUES < '40') USING EXTENDED STORAGE (PARTITION '40' <= VALUES < '80') ) ; INSERT INTO T VALUES (1, 1); INSERT INTO T VALUES (2, 2); ``` -------------------------------- ### Get Absolute Value with ABS Function Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20da27c175191014b7b3ecbd7dd130b2.html Use the ABS function to retrieve the absolute value of a numeric expression. The example demonstrates returning the absolute value of -1. ```sql SELECT ABS (-1) "absolute" FROM DUMMY; ``` -------------------------------- ### Create and Populate ProductSales Table Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/87f0d4985c4a49bfb64682dcb325173d.html Defines a table to store product sales data and inserts sample records. This setup is required before using the ROW_NUMBER function in the subsequent examples. ```sql CREATE ROW TABLE ProductSales(ProdName VARCHAR(50), Type VARCHAR(20), Sales INT); INSERT INTO ProductSales VALUES('Tee Shirt','Plain',21); INSERT INTO ProductSales VALUES ('Tee Shirt','Lettered',22); INSERT INTO ProductSales VALUES ('Tee Shirt','Team logo',30); INSERT INTO ProductSales VALUES('Hoodie','Plain',60); INSERT INTO ProductSales VALUES ('Hoodie','Lettered',65); INSERT INTO ProductSales VALUES ('Hoodie','Team logo',80); INSERT INTO ProductSales VALUES('Ballcap','Plain',8); INSERT INTO ProductSales VALUES ('Ballcap','Lettered',40); INSERT INTO ProductSales VALUES ('Ballcap','Team logo',27); ``` -------------------------------- ### Example: Check Disk Space for Backup Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/5b1a81a42b8f42209e8800ee3982d420.html Demonstrates how to check if 50GB of space is available in the '/backup/data' folder for a backup. Ensure the system database is online and has the necessary privileges. ```sql BACKUP CHECK USING FILE ('/backup/data') SIZE 53687091200; ``` -------------------------------- ### Extract Substring from String in SAP HANA SQL Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e8341275191014a4cfdcd3c830fc98.html Selects two characters from the string '1234567890' starting at position 4. This example demonstrates basic string manipulation with the SUBSTRING function. ```sql SELECT SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY; ``` -------------------------------- ### Use CS_FILTER_FIRST() Hint Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html Specifies the preferred starting column in a conjunction for column store table queries. This hint helps guide the optimizer in choosing an efficient access path. ```sql SELECT * FROM T1 WITH HINT( CS_FILTER_FIRST(column_name) ); ``` -------------------------------- ### Create and Populate Sample Input Table Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d22d746ed2951014bb7fb0114ffdaf96.html Creates a table named 'sample_input' and populates it with example currency data for testing the CONVERT_CURRENCY function. ```sql CREATE ROW TABLE sample_input (price DECIMAL(15,2), source_unit VARCHAR(4), target_unit VARCHAR(4), ref_date VARCHAR(10) ); INSERT INTO sample_input VALUES (1.0, 'SRC', 'TRG', '2011-01-01'); INSERT INTO sample_input VALUES (1.0, 'SRC', 'TRG', '2011-02-01'); ``` -------------------------------- ### Get Year and Quarter of a Date using QUARTER Function Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e62fe875191014b78bd015a16966fb.html Use the QUARTER function to extract the year and quarter from a specified date. The optional second argument defines the starting month of the first quarter. ```sql SELECT QUARTER (TO_DATE('2012-01-01', 'YYYY-MM-DD'), 2) "quarter" FROM DUMMY; ``` -------------------------------- ### Example: Creating and Using a Schema Synonym Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/1b54e1f64cfc401bb3adbe7c143bc323.html This example demonstrates creating a schema synonym named TEST_SCHEMA_SYN for the SYSTEM schema, then setting it as the current schema. It also shows how to reset the current schema. ```sql CREATE SCHEMA SYNONYM TEST_SCHEMA_SYN FOR SYSTEM; ``` ```sql SET SCHEMA TEST_SCHEMA_SYN; ``` ```sql SELECT CURRENT_SCHEMA FROM DUMMY; ``` ```sql SET SCHEMA SYSTEM; ``` ```sql SELECT CURRENT_SCHEMA FROM DUMMY; ``` -------------------------------- ### Example: Setting a Parameter on a Specific Host Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/0f9c94eb5c5b4f43abef8e83b0443cc3.html This example demonstrates setting a parameter in the 'daemon.ini' file on a specific host. The 'layer' is set to 'HOST' and 'layer_name' specifies the target host. ```sql ALTER SYSTEM ALTER CONFIGURATION ('daemon.ini', 'HOST', 'selxeon12') SET ('log', 'maxfiles') = '10' WITH RECONFIGURE; ``` -------------------------------- ### Asynchronous Table Replication Result Lag Hint (Short) Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html Guides the optimizer to access source or replica tables by evaluating stale data with the parameter. This example uses a short lag name. ```sql SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_short') ); ``` -------------------------------- ### Example: Create Runtime Dump for Specific Section Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/6954969bba544da190ca53c1bf367086.html Demonstrates creating a runtime dump for the 'STACK_SHORT' section at a specified location and saving it to a named file. ```sql ALTER SYSTEM CREATE RUNTIMEDUMP AT LOCATION 'myhost:30003' SECTIONS ('STACK_SHORT') INTO FILE 'my_rte_dump.trc'; ``` -------------------------------- ### Asynchronous Table Replication Result Lag Hint (ATR) Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html Guides the optimizer to access source or replica tables by evaluating stale data with the parameter. This example uses a specific ATR lag name. ```sql SELECT * FROM T1 WITH HINT( RESULT_LAG ('hana_atr') ); ``` -------------------------------- ### Create and Drop Remote Source Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d7332f75191014866fc4b83cfba146.html Demonstrates how to create a remote source named S using the ASEODBC adapter and then subsequently drop it. Requires appropriate credentials and configuration. ```sql CREATE REMOTE SOURCE S ADAPTER ASEODBC CONFIGURATION 'DSN=ProductionASE' WITH CREDENTIAL TYPE 'PASSWORD' USING 'user="aseuser";password="asepassword";'; DROP REMOTE SOURCE S; ``` -------------------------------- ### Calculate Workdays from Table Input Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d22c8391d2951014815a84e76cf66501.html Demonstrates using the ADD_WORKDAYS function with input values sourced from a table. This example calculates shipment dates based on factory calendar ID, start date, and duration for multiple entries. ```sql CREATE SCHEMA SAPABC; SET SCHEMA "SAPABC"; CREATE ROW TABLE MY_DATES (FCID NVARCHAR(2), STARTDATE DATE, DURATION INTEGER); INSERT INTO MY_DATES VALUES ('01', '2014-01-01', 30); INSERT INTO MY_DATES VALUES ('01', '2014-04-01', 28); INSERT INTO MY_DATES VALUES ('01', '2014-07-01', 25); INSERT INTO MY_DATES VALUES ('01', '2014-10-01', 20); SELECT ADD_WORKDAYS(FCID, STARTDATE, DURATION) "shipment date" FROM MY_DATES; ``` -------------------------------- ### Create Table and Insert Data for WEIGHTED_AVG Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d97252bcd22f4d9fa4812715cee5139b.html Sets up a sample 'weather' table and populates it with data to demonstrate the WEIGHTED_AVG function. ```sql CREATE ROW TABLE weather (station INT, ts DATE, temperature FLOAT); INSERT INTO weather VALUES(1, '2014-01-01', 0.0); INSERT INTO weather VALUES(1, '2014-01-02', 3.0); INSERT INTO weather VALUES(1, '2014-01-03', 4.5); INSERT INTO weather VALUES(1, '2014-01-04', 6.0); INSERT INTO weather VALUES(1, '2014-01-05', 6.3); INSERT INTO weather VALUES(1, '2014-01-06', 5.9); INSERT INTO weather VALUES(2, '2014-01-01', 1.0); INSERT INTO weather VALUES(2, '2014-01-02', 3.4); INSERT INTO weather VALUES(2, '2014-01-03', 5.0); INSERT INTO weather VALUES(2, '2014-01-04', 6.7); INSERT INTO weather VALUES(2, '2014-01-05', 4.6); INSERT INTO weather VALUES(2, '2014-01-06', 6.9); ``` -------------------------------- ### Example: Create, Add Version, and Drop Old Versions of CKP Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/e38a58bdf5c446739afa5f239c88a982.html Demonstrates the creation of a client-side encryption key pair, adding a new version, and then dropping all older versions. ```sql CREATE CLIENTSIDE ENCRYPTION KEYPAIR Ckp1 ALGORITHM 'RSA-OAEP-2048'; ALTER CLIENTSIDE ENCRYPTION KEYPAIR Ckp1 ADD NEW VERSION; ALTER CLIENTSIDE ENCRYPTION KEYPAIR Ckp1 DROP OLD VERSIONS; ``` -------------------------------- ### Create and Select from Application-Time Period Table Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20ff268675191014964add3d17700909.html This example demonstrates the creation of a column table with application-time period support and inserting initial values. It then selects all records to show the initial state. ```sql CREATE COLUMN TABLE TAB01 (ValidFrom DATE NOT NULL, ValidTo DATE NOT NULL, PKEY INTEGER, Value INTEGER, PERIOD FOR APPLICATION_TIME (ValidFrom, ValidTo) ); INSERT INTO TAB01 VALUES ('2018-08-01', '2018-08-15', 1, 9494); INSERT INTO TAB01 VALUES ('2018-08-15', '2018-08-31', 1, 20); INSERT INTO TAB01 VALUES ('2018-08-31', '2018-09-10', 1, 30); INSERT INTO TAB01 VALUES ('2018-09-10', '9999-12-31', 1, 40); SELECT * FROM TAB01 ORDER BY ValidFrom; ``` -------------------------------- ### Get Current Local and UTC Dates Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20ddfe5d75191014af50837e2818462d.html This example demonstrates how to retrieve both the current local system date and the current Coordinated Universal Date using CURRENT_DATE and CURRENT_UTCDATE functions. It is recommended to use UTC dates for consistency. ```sql SELECT CURRENT_DATE "Current Date", CURRENT_UTCDATE "Coordinated Universal Date" FROM DUMMY; ``` -------------------------------- ### Create and Populate Tables for RECORD_COMMIT_TIMESTAMP Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/a85896b8b0de48089f76b73ae7388740.html Creates two tables, AA and BB, where AA is configured to record commit timestamps and BB is not. Inserts data and commits the transaction. This setup is necessary to demonstrate the RECORD_COMMIT_TIMESTAMP function's behavior. ```sql CREATE TABLE AA (A int) RECORD COMMIT TIMESTAMP; CREATE TABLE BB (B int); INSERT INTO AA VALUES (1); INSERT INTO BB VALUES (1); COMMIT; ``` -------------------------------- ### Create Virtual Table with Data Provisioning Parameters Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d2a0406155f24a49aa6c549e0c428bd7.html This example shows how to create a virtual table and pass specific parameters to the adapter for metadata requests using the DATAPROVISIONING_PARAMETERS property. ```sql CREATE VIRTUAL TABLE …. REMOTE PROPERTY 'dataprovisioning_parameters'='1,10,1000,300 USA2014-06-01'; ``` -------------------------------- ### Complex Workday Addition with Holidays and Weekends Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d22c8391d2951014815a84e76cf66501.html Adds a significant number of working days to a start date, illustrating the function's ability to navigate multiple weekends and public holidays. This example adds 16 working days to January 1st, 2014, resulting in January 27th, 2014. ```sql SELECT ADD_WORKDAYS('01', '2014-01-01', 16, 'FCTEST') "result date" FROM DUMMY; ``` -------------------------------- ### Configure System Versioning for a Column Table Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d329a6751910149d5fdbc4800f92ff.html This example sets up system versioning for the 'account' column table by creating a history table and altering the main table to include system time period columns. ```sql CREATE COLUMN TABLE account ( account_id INT PRIMARY KEY, account_owner_id NVARCHAR(10), account_balance DOUBLE ); CREATE COLUMN TABLE account_history ( account_id INT, account_owner_id NVARCHAR(10), account_balance DOUBLE, valid_from TIMESTAMP NOT NULL, valid_to TIMESTAMP NOT NULL ); ALTER TABLE account ADD (valid_from TIMESTAMP NOT NULL GENERATED ALWAYS AS ROW START); ALTER TABLE account ADD (valid_to TIMESTAMP NOT NULL GENERATED ALWAYS AS ROW END); ALTER TABLE account ADD PERIOD FOR SYSTEM_TIME(valid_from, valid_to); ALTER TABLE account ADD SYSTEM VERSIONING HISTORY TABLE account_history; ``` -------------------------------- ### Get Current Site ID with TOP 1 Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/9ac7d1b81eb642f98a97c9682097e44f.html Use this example to retrieve the current site ID once, which is recommended for performance. It specifies TOP 1 to return only one row and references the SVERS table for an SAP NetWeaver ABAP environment. The RESULT_LAG hint is specific to SAP HANA System Replication. ```sql SELECT TOP 1 CURRENT_SITE_ID() FROM mySchema.SVERS ( RESULT_LAG('hana_sr')); ``` -------------------------------- ### Create, Partition, and Add Partition to Table Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/f7ae27ca1b954471a4e1a7feab2c24d7.html Create a non-partitioned table, convert it to a range partitioned table, and then add an additional partition. ```sql CREATE COLUMN TABLE T2 (a INT, b INT); ALTER TABLE T2 PARTITION BY RANGE (a) (PARTITION VALUE = 1, PARTITION OTHERS); ALTER TABLE T2 ADD PARTITION (a) 2 <= VALUES < 10; ``` -------------------------------- ### ALTER SYSTEM START DATABASE Statement Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/38f6d1231bf84614853a17206a1e82f8.html Starts the specified database. This statement can optionally start the tenant database using a fallback snapshot. ```APIDOC ## ALTER SYSTEM START DATABASE ### Description Starts the specified database. This statement can optionally start the tenant database using a fallback snapshot, allowing restoration to a previous state. ### Method SQL Statement ### Endpoint N/A (SQL Command) ### Syntax ```sql ALTER SYSTEM START DATABASE [ FROM FALLBACK SNAPSHOT ] ``` ### Parameters #### Path Parameters None #### Query Parameters None #### Request Body None ### Parameters - **``** (identifier) - Required - Specifies the name of the database to start. - **`FROM FALLBACK SNAPSHOT`** - Optional - If present, starts the tenant database using the fallback snapshot, restoring it to the state when the snapshot was created. ### Permissions Requires DATABASE ADMIN or DATABASE START privilege. ### Example ```sql ALTER SYSTEM START DATABASE my_database; ``` ### Response #### Success Response (200) N/A (SQL Statement Execution) #### Response Example N/A ``` -------------------------------- ### Create and Drop Credential Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d64db0751910149535843182bf8d86.html Demonstrates creating a credential for a specific user and component, then dropping it. Requires CREDENTIAL ADMIN privilege. ```sql CREATE CREDENTIAL FOR USER WORKER COMPONENT 'INTERNAL_APP' PURPOSE 'COMPANY_MASTER_MACHINE' TYPE 'PASSWORD' USING 'PASSWORD_9876'; ``` ```sql DROP CREDENTIAL FOR USER WORKER COMPONENT 'INTERNAL_APP' PURPOSE 'COMPANY_MASTER_MACHINE' TYPE 'PASSWORD'; ``` -------------------------------- ### Drop Certificate Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/b7784cfa101942f4ae17a258d060998e.html Example of how to drop a certificate named SAP_CERT. ```sql DROP CERTIFICATE SAP_CERT; ``` -------------------------------- ### Create and Populate MyProducts Table Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/c0c42b56360144e99bd443db90847597.html SQL statements to create a sample table named 'MyProducts' and insert data for demonstration purposes. ```sql DROP TABLE "MyProducts"; CREATE COLUMN TABLE "MyProducts"( "Product_ID" VARCHAR(10), "Product_Name" VARCHAR(100), "Category" VARCHAR(100), "Quantity" INTEGER, "Price" DECIMAL(10,2), PRIMARY KEY ("Product_ID") ); INSERT INTO "MyProducts" VALUES('P1','Shirts', 'Clothes', 32, 20.99); INSERT INTO "MyProducts" VALUES('P2','Jackets', 'Clothes', 16, 99.49); INSERT INTO "MyProducts" VALUES('P3','Trousers', 'Clothes', 30, 32.99); INSERT INTO "MyProducts" VALUES('P4','Coats', 'Clothes', 5, 129.99); INSERT INTO "MyProducts" VALUES('P5','Purse', 'Accessories', 3, 89.49); ``` -------------------------------- ### NORMALIZE Function Examples Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/8fa2d19e3dae4de2a4613a4987858894.html Examples demonstrating the usage of the NORMALIZE function with different normalization forms. ```APIDOC ## Examples ### Canonical Equivalence ```sql SELECT NORMALIZE('Å') FROM DUMMY; -- Result: Å (0x00C5) SELECT NORMALIZE('Å', 'NFC') FROM DUMMY; -- Result: Å (0x00C5) SELECT NORMALIZE('Å', 'NFD') FROM DUMMY; -- Result: Å (0x0041 0x030A) ``` ### Compatibility Equivalence ```sql SELECT NORMALIZE('ﻉ', 'NFKC') FROM DUMMY; -- Result: ﻉ (positional variants) SELECT NORMALIZE ('ﻊ', 'NFKC') FROM DUMMY; -- Result: ﻉ (positional variants) SELECT NORMALIZE('①', 'NFKC') FROM DUMMY; -- Result: 1 (circled variants) SELECT NORMALIZE('ｶ', 'NFKC') FROM DUMMY; -- Result: カ (width variants) SELECT NORMALIZE('¼', 'NFKC') FROM DUMMY; -- Result: 1/4 (fractions) SELECT NORMALIZE('i⁹', 'NFD') FROM DUMMY; -- Result: i9 (superscripts/subscripts) ``` ``` -------------------------------- ### Query Hints - Execution Engine Selection Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fcf24075191014a89e9dc7b8408b26.html Shows examples of using hints to select the execution engine, specifically USE_OLAP_PLAN and NO_USE_OLAP_PLAN. ```sql SELECT * FROM T1 WITH HINT( USE_OLAP_PLAN ); SELECT * FROM T1 WITH HINT( NO_USE_OLAP_PLAN ); ``` -------------------------------- ### SUBSTRING_REGEXPR Start Position Syntax Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/a2f80e8ac8904c13959c69bfc3058f19.html Specifies the starting character position for the search within the subject string. ```sql ::= ``` -------------------------------- ### Example: Drop a PSE Store Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/25d67950e6614770aa98aec23ab117ee.html Demonstrates how to drop a specific PSE store named 'examplepse'. Ensure you have the necessary DROP privilege. ```sql DROP PSE examplepse; ``` -------------------------------- ### OCCURRENCES_REGEXPR Start Position Syntax Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4114b026f750429c8eeef9f54258edfa.html Specifies that the start position for the OCCURRENCES_REGEXPR function must be a positive integer. ```sql ::= ``` -------------------------------- ### Create Table, View, and Add Dynamic Cache Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/3bc89515b93d4844bd700b3492673270.html Initializes a table and view, then enables dynamic caching. This is the setup for managing dynamic cache indexes. ```sql CREATE COLUMN TABLE tab_a ( A INT, B INT, C INT, D INT); CREATE VIEW view_a AS (SELECT A, B, sum(D) D from tab_a group by A, B); ALTER VIEW view_a ADD DYNAMIC CACHE; ``` -------------------------------- ### Query Hints - Scale-out Environment Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fcf24075191014a89e9dc7b8408b26.html Demonstrates hints used in a scale-out environment for controlling data routing, such as ROUTE_TO, NO_ROUTE_TO, ROUTE_BY, and DATA_TRANSFER_COST. ```sql SELECT * FROM T1 WITH HINT( ROUTE_TO(1)); SELECT * FROM T1 WITH HINT( NO_ROUTE_TO(2,3)); SELECT * FROM T1 WITH HINT( ROUTE_BY(T2)); SELECT * FROM T1 WITH HINT( ROUTE_BY_CARDINALITY(T1,T2,T3)); SELECT * FROM T1 WITH HINT( NO_ROUTE_TO(1), ROUTE_TO(1)); -- route to volume id = 1 ( last one is used ) SELECT * FROM T1 WITH HINT( DATA_TRANSFER_COST(0) ); ``` -------------------------------- ### XMLEXTRACTVALUE Example 2 (With Namespaces) Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/85ee0084ac014a63aa2680b384466f39.html An example showing how to use XMLEXTRACTVALUE with XML documents that include namespaces. ```APIDOC ## Example The following example extracts the value from the element in item 2 (and returns Jar): ```sql SELECT XMLEXTRACTVALUE( ' 1Box 2Jar 3Table ', '/doc/ns1:item[2]/ns1:name', 'xmlns:ns1="http://namespace1.sap.com" xmlns:ns2="http://namespace2.sap.com"' ) FROM DUMMY; ``` ``` -------------------------------- ### XML Structure Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/85ee0084ac014a63aa2680b384466f39.html An example of an XML structure illustrating single child nodes suitable for XMLEXTRACTVALUE. ```xml Tom 18 ``` -------------------------------- ### Drop Fulltext Index Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d66989751910148a64a797565ad4dc.html Example showing how to remove a previously created full text index. ```sql DROP FULLTEXT INDEX i ON a(b); ``` -------------------------------- ### Example: Drop an X.509 Provider Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/f7a37e81a9b64a2cb6a6092a38b06c60.html Demonstrates how to drop a specific X.509 provider named 'my_x509_provider1'. ```sql DROP X509 PROVIDER my_x509_provider1; ``` -------------------------------- ### Example: Create Family Tree Graph Workspace Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/e6e1c7e2b9064b05b26572808f941ec4.html Demonstrates creating a schema, defining vertex and edge tables, and then creating a graph workspace named 'FAMILY_TREE.GENEALOGY'. Key columns must be unique and not null. ```sql CREATE SCHEMA "FAMILY_TREE"; CREATE COLUMN TABLE "FAMILY_TREE"."VERTICES" ("KEY" VARCHAR(1024) NOT NULL UNIQUE); CREATE COLUMN TABLE "FAMILY_TREE"."EDGES" ("SOURCE" VARCHAR(1024) NOT NULL, "TARGET" VARCHAR(1024) NOT NULL, "ID" INT NOT NULL UNIQUE); CREATE GRAPH WORKSPACE FAMILY_TREE.GENEALOGY EDGE TABLE FAMILY_TREE.EDGES SOURCE COLUMN "SOURCE" TARGET COLUMN "TARGET" KEY COLUMN ID VERTEX TABLE FAMILY_TREE.VERTICES KEY COLUMN "KEY"; ``` -------------------------------- ### Start Performance Tracing by Statement Hash Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d2d3ed75191014bb9c86ed27c378d8.html Starts performance tracing filtered by a specific root statement hash. ```sql ALTER SYSTEM START PERFTRACE ROOT_STATEMENT_HASH '21db1b17968de7861e6c97645f72963b'; ``` -------------------------------- ### Create and Manage Workload Classes and Mappings Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/dc417c30b1b3460c8860f6c7ea2a7faa.html Demonstrates creating workload classes and mappings, and enabling/disabling them individually or all at once. ```sql CREATE WORKLOAD MAPPING "MyMapping1" WORKLOAD CLASS "MyClass" SET 'CLIENT'='001'; CREATE WORKLOAD MAPPING "MyMapping2" WORKLOAD CLASS "MyClass" SET 'CLIENT'='002'; ALTER WORKLOAD CLASS "MyClass"DISABLE; -- disables "MyClass" and its mappings, "MyMapping1" and "MyMapping2" ALTER WORKLOAD CLASS "MyClass" ENABLE; -- enables "MyClass" and its mappings, "MyMapping1" and "MyMapping2" CREATE WORKLOAD CLASS "MyClass1" SET 'PRIORITY' = '0', 'STATEMENT MEMORY LIMIT' = '3', 'STATEMENT THREAD LIMIT' = '10'; CREATE WORKLOAD MAPPING "MyMapping3" WORKLOAD CLASS "MyClass1" SET 'CLIENT'='003'; ALTER WORKLOAD CLASS ALL DISABLE; -- disables all workload classes, "MyClass" and "MyClass1", and their mappings ("MyMapping1", "MyMapping2" and "MyMapping3") ALTER WORKLOAD CLASS ALL ENABLE; -- enables all workload classes and related mappings again ``` -------------------------------- ### Query Hints - USE_OLAP_PLAN Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fcf24075191014a89e9dc7b8408b26.html Illustrates applying the USE_OLAP_PLAN hint to a query and a subquery, showing how hints can influence query execution. ```sql SELECT T2.* FROM ( SELECT MAX(COL) FROM T1 GROUP BY COL WITH HINT( NO_USE_OLAP_PLAN )) T2 WITH HINT( USE_OLAP_PLAN ); ``` -------------------------------- ### DROP WORKLOAD MAPPING Example Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/8d90e94880924f7d957ba73db7e7db17.html Example of removing a workload mapping named 'MyWorkloadMapping'. Requires WORKLOAD_ADMIN privilege. ```sql DROP WORKLOAD MAPPING "MyWorkloadMapping"; ``` -------------------------------- ### Start Capturing Abstract SQL Plans for All Users Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/dc462710a89d4ebc981eda6908db28dc.html Use this command to initiate the collection of abstract SQL plans for all queries executed against the database. This is the default behavior when no specific users are mentioned. ```sql ALTER SYSTEM START CAPTURE ABSTRACT SQL PLAN; ``` -------------------------------- ### Convert Non-Partitioned Table to Range-Range Partitioned with Dynamic Subpartitions Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d329a6751910149d5fdbc4800f92ff.html This example demonstrates creating a complex heterogeneous partition structure with multiple first-level range partitions and subpartitions, some of which are dynamically enabled. ```sql CREATE COLUMN TABLE T1 (a INT, b INT NOT NULL); ALTER TABLE T1 PARTITION BY RANGE (a) ((PARTITION 10 <= VALUES < 20 INSERT OFF, PARTITION 20 <= VALUES < 30 INSERT OFF) SUBPARTITION BY RANGE (b) (PARTITION 0 <= VALUES < 10, PARTITION 10 <= VALUES < 100, PARTITION OTHERS DYNAMIC THRESHOLD 2), (PARTITION VALUES = 40) SUBPARTITION BY RANGE(B) (PARTITION 0 <= VALUES < 10, PARTITION OTHERS DYNAMIC THRESHOLD 2), (PARTITION OTHERS)); ``` -------------------------------- ### XMLEXTRACTVALUE Example 1 (Basic) Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/85ee0084ac014a63aa2680b384466f39.html An example demonstrating how to extract the value from a simple XML element using XMLEXTRACTVALUE. ```APIDOC ## Example The following example extracts the value from the `` element from item 2 (and returns Jar): ```sql SELECT XMLEXTRACTVALUE( ' 1Box 2Jar ', '/doc/item[2]/name' ) FROM DUMMY; ``` ```