SAP HANA Platform

https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/
Admin
SAP HANA Platform is an in-memory database and application platform that provides SQL query...
Tokens:1,486,949
Snippets:12,899
Trust Score:9.8
Update:1 day ago
Show doc for...
Tokens:
Raw
### Start my_database example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/38f6d1231bf84614853a17206a1e82f8.html

Example of starting the my_database database using the ALTER SYSTEM START DATABASE statement.

```sql
ALTER SYSTEM START DATABASE my_database;
```

--------------------------------

### Start SQLScript plan profiler example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/f518753a450942a48fecc02c926e3847.html

Starts the SQLScript plan profiler for the entire system. Requires the TRACE ADMIN or OPTIMIZER ADMIN system privilege.

```sql
ALTER SYSTEM START SQLSCRIPT PLAN PROFILER;
```

--------------------------------

### FOR JSON Example Setup

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fcf24075191014a89e9dc7b8408b26.html

Creates a table and inserts sample data for demonstrating FOR JSON options.

```sql
CREATE TABLE JTable (id int, name nvarchar(20));INSERT INTO JTable VALUES (1, NULL);INSERT INTO JTable VALUES (2, ‘ko’);
```

--------------------------------

### UNSET SYSTEM LICENSE ALL example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fef5c175191014b9daf2f2a9157a0f.html

Example that deletes all currently installed license keys. After execution, the database is locked down until a new valid license key is installed.

```sql
UNSET SYSTEM LICENSE ALL;
```

--------------------------------

### Create table and view example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d9c05f75191014b24fca55a8534531.html

Creates a row table 't' and a view 'v' that selects all records from table 't'. This is the setup for the DROP VIEW example.

```sql
CREATE ROW TABLE t (a INT);
 CREATE VIEW v AS SELECT * FROM t;
```

--------------------------------

### Create table and index for RENAME INDEX example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fb6e10751910148b45cefcb6294342.html

Creates a row table B with a primary key and an index idx on column B. This is the setup for the RENAME INDEX example.

```sql
CREATE ROW TABLE B (A INT PRIMARY KEY, B INT);
 CREATE INDEX idx on B(B);
```

--------------------------------

### Start Performance Trace with ALTER SYSTEM START PERFTRACE

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/beae0b80bb5710149a28e6891a42d641.html

Executes the SQL statement to start the performance trace. This is the example provided in the documentation.

```sql
ALTER SYSTEM START PERFTRACE
```

--------------------------------

### Example: Initialize etsserver service

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/eafc3be81c6a48db84941f2cd6d0c8ef.html

Example that initializes the etsserver service on host myasehostname, port 30021, with credentials user=sa and password=MySecretPass.

```sql
ALTER SYSTEM INITIALIZE SERVICE 'etsserver' at 'myasehostname:30021' 
with CREDENTIAL TYPE 'PASSWORD' USING 'user=sa;password=MySecretPass' 

```

--------------------------------

### Reset SYS.M_HEAP_MEMORY_RESET monitoring view

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d27aa375191014a9cd8ea8c334741a.html

Example that resets the "SYS"."M_HEAP_MEMORY_RESET" monitoring view. Use this to define a starting point for measurements; after resetting, execute an action and then query the _RESET version of the view to get statistics since the last reset.

```SQL
ALTER SYSTEM RESET MONITORING VIEW "SYS"."M_HEAP_MEMORY_RESET";
```

--------------------------------

### WIDTH_BUCKET example with table creation and query

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/953bce3cc9a84ebfa77c17ab687ff0ee.html

Example that creates a table, inserts values, and selects the bucket numbers using WIDTH_BUCKET. The output shows bucket numbers for each value.

```sql
CREATE TABLE myTable (myValues INT);
INSERT INTO myTable VALUES(1);
INSERT INTO myTable VALUES(2);
INSERT INTO myTable VALUES(3);
INSERT INTO myTable VALUES(4);
INSERT INTO myTable VALUES(5);

SELECT myValues, WIDTH_BUCKET( myValues, 2, 5, 2 ) AS "BucketNo" FROM myTable;
```

--------------------------------

### QUARTER function example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e62fe875191014b78bd015a16966fb.html

Example query that returns the year and quarter for the date '2012-01-01' with a start month of 2, producing '2011-Q4'.

```sql
SELECT QUARTER (TO_DATE('2012-01-01', 'YYYY-MM-DD'), 2) "quarter" FROM DUMMY;
```

--------------------------------

### BITSET example query

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d22d0974d29510149373deadadc83fe4.html

Example query that sets 3 bits starting at position 1 in the string '0000', returning the value E000.

```sql
SELECT BITSET ('0000', 1, 3) "bitset" FROM DUMMY;
```

--------------------------------

### SET SYSTEM LICENSE example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20fdca2375191014bde883db2af2cce6.html

Example of setting the system license with a license code. The license shown does not contain a license key, so the example cannot be run.

```sql
SET SYSTEM LICENSE
 '----- Begin SAP License -----
 SAPSYSTEM=HD1
 HARDWARE-KEY=K4150485960
 INSTNO=0110008649
 BEGIN=20110809
 EXPIRATION=20151231
 LKEY=...
 SWPRODUCTNAME=SAP-HANA
 SWPRODUCTLIMIT=2147483647
 SYSTEM-NR=00000000031047460';
```

--------------------------------

### Example: create table, create library, alter library

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d0b979ccbbd14377983f254e85241ff4.html

This example creates a table, creates a SQLScript user-defined library that operates on the table, and then alters the library body to be BEGIN END.

```sql
CREATE TABLE data_table(col1 INT);
				
CREATE LIBRARY mylib AS BEGIN
   PUBLIC VARIABLE maxval CONSTANT INT = 100;
   PUBLIC PROCEDURE get_data(IN size INT, OUT result table(col1 INT)) AS 
      BEGIN
    	RESULT = SELECT TOP :size col1 FROM data_table;
	  END;
   END;
				
ALTER LIBRARY mylib AS BEGIN END;
```

--------------------------------

### Example: create CKP, add version, drop old versions

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/e38a58bdf5c446739afa5f239c88a982.html

Example that creates a CKP named Ckp1, adds a new version, and then drops all old versions. Requires ownership and ALTER CLIENTSIDE ENCRYPTION KEYPAIR privilege for adding, and the privilege for dropping.

```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;
```

--------------------------------

### MOVE PARTITION with ONLINE example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/a4258b865710423b8712b06fb5b8e7c5.html

Example of moving partitions 1 and 2 to MyServer1 and partitions 3 and 4 to MyServer2 with the ONLINE option, which is allowed when moving to different hosts.

```sql
ALTER TABLE T1 MOVE PARTITION 1,2 TO 'MyServer1:34240', PARTITION 3,4 TO 'MyServer2' ONLINE
```

--------------------------------

### BITUNSET example query

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/d22d21ddd2951014a15dcdbdf3fc5e00.html

Example query that returns the value 1FFF for the alias 'bitunset' by unsetting 3 bits starting at position 1 in the hexadecimal value 'ffff'.

```sql
SELECT BITUNSET ('ffff', 1, 3) "bitunset" FROM DUMMY;
```

--------------------------------

### Example: USING SOURCE for tenant database

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/93637a07e3b544398aa02de1541b903c.html

Example of USING SOURCE for recovering a tenant database from a Backint-based backup created in system PR1 with name Tenant_1.

```sql
<system_source_definition> ::= 
   USING SOURCE 'Tenant_1@PR1'
```

--------------------------------

### Start SQLScript code coverage for a session

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/1a40f07c15c3401083fc8fd8b9786f7b.html

Starts code coverage for the session with the specified connection ID (example uses 203247). Requires EXECUTE, DEBUG, and ATTACH_DEBUGGER privileges.

```sql
ALTER SYSTEM START SQLSCRIPT CODE COVERAGE FOR SESSION '203247';
```

--------------------------------

### SQL Example: REMOTE_JOIN Hint

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html

Guides the optimizer to process the join at the remote server if possible. The example joins RT and HT on RT.C1 = HT.C1 and uses WITH HINT(REMOTE_JOIN).

```sql
SELECT * FROM RT  JOIN HT ON  RT.C1= HT.C1 WITH HINT(REMOTE_JOIN)
```

--------------------------------

### Create PSE example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4d80bf63fc374a7f99be94d8ce70a07a.html

Creates a PSE store named example_pse. Requires the TRUST ADMIN privilege.

```sql
CREATE PSE example_pse;
```

--------------------------------

### Example: Add streamingserver to database

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/cf7f0f00cbfb4d909b5f6398d51627c2.html

Example showing how to add the streamingserver service to the database my_database at hostA for instance 03.

```sql
ALTER DATABASE my_database ADD 'streamingserver'  AT LOCATION 'hostA:30316'
```

--------------------------------

### NO_RESULT_CACHE hint example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html

Guides the optimizer to not utilize the result cache, even if it is available.

```sql
SELECT * FROM T1 WITH HINT( NO_RESULT_CACHE );
```

--------------------------------

### Create user example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d8d33275191014adce9c099eeed35c.html

Creates a new user named new_user with the password Password1.

```sql
CREATE USER new_user PASSWORD Password1;
```

--------------------------------

### PERCENT_RANK example: create table, insert data, and query

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/90f4533f07d34269a47924c963f1d5ec.html

This example demonstrates the PERCENT_RANK function. It creates a ProductSales table, inserts sample data, and runs a query that partitions by ProdName and orders by Sales ASC to calculate the percentage rank of each row within its partition.

```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('Hoodie','Vintage',67);
INSERT INTO ProductSales VALUES('Ballcap','Plain',8);
INSERT INTO ProductSales VALUES('Ballcap','Lettered',40);
INSERT INTO ProductSales VALUES('Ballcap','Team logo',27);

SELECT ProdName, Type, Sales,
  PERCENT_RANK() OVER (PARTITION BY ProdName ORDER BY Sales ASC) AS Percent_Rank
FROM ProductSales
ORDER BY Sales DESC;
```

--------------------------------

### SQL Example: REMOTE_UNION Hint

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html

Guides the optimizer to process union operation at the remote server if possible (default behavior). The example unions RT and HT and uses WITH HINT(REMOTE_UNION).

```sql
SELECT * FROM RT UNION SELECT * FROM HT WITH HINT(REMOTE_UNION)
```

--------------------------------

### Connect with User and Password Example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d3b9ad751910148cdccc8205563a87.html

Connects to the database using the user name new_user and password Password1.

```sql
CONNECT new_user PASSWORD Password1;
```

--------------------------------

### Example: LOCATE_REGEXPR with GROUP

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/cb4866494bd647cd8926763aa940f613.html

Returns the start position of the day part from the date value '20140401'. The example uses GROUP 3 to capture the third group, which is the day part, and returns 7.

```sql
SELECT LOCATE_REGEXPR(START '([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})' IN '20140401' GROUP 3) "locate_regexpr" FROM DUMMY;
```

--------------------------------

### Example: Query with TM_GET_SUGGESTED_TERMS

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/6c6e7f7309b244188dcfe023e2d3605f.html

Returns terms starting with 'john' from a news table, filtering by score and term frequency. The example shows the function call with TERM, SEARCH, and RETURN TOP clauses.

```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;
```

--------------------------------

### Create table and index example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20d6f4e575191014b333904458242bd7.html

Creates a row table A and adds an index i on column b.

```sql
CREATE ROW TABLE A (a INT, b NVARCHAR(10), c NVARCHAR(20));
 CREATE INDEX i ON A(b);
```

--------------------------------

### Example 1: Basic procedure with placeholder parameters

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/f1c17eb3a5b04f8b82d5908218e3fa68.html

Creates a SQLScript procedure that calls a calculation view CALC_VIEW with placeholder parameters 'client' and 'currency', binding them to input parameters.

```sql
CREATE PROCEDURE my_proc_caller (IN in_client INT, IN in_currency INT, OUT outtab mytab_t) LANGUAGE SQLSCRIPT READS SQL DATA AS
BEGIN
    outtab = SELECT * FROM CALC_VIEW (PLACEHOLDER."$$client$$" => :in_client , PLACEHOLDER."$$currency$$" => :in_currency );
END;
```

--------------------------------

### SQL Example: REMOTE_DISTINCT Hint

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html

Guides the optimizer to process the distinct operation at the remote server if possible (default behavior). The example selects DISTINCT(HT.C1) from a join and uses WITH HINT(REMOTE_DISTINCT).

```sql
SELECT DISTINCT(HT.C1) FROM RT  JOIN HT ON  RT.C1 = HT.C1 WITH HINT(REMOTE_DISTINCT)
```

--------------------------------

### SQL Example: REMOTE_AGGR Hint

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/4ba9edce1f2347a0b9fcda99879c17a1.html

Guides the optimizer to process aggregation at remote data source if possible (default behavior). The example joins RT and HT, groups by RT.C1, and uses WITH HINT(REMOTE_AGGR).

```sql
SELECT SUM(HT.C1) FROM RT  JOIN HT ON  RT.C1 = HT.C1 GROUP BY  RT.C1 WITH HINT(REMOTE_AGGR)
```

--------------------------------

### Create table and table function for result cache example

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/1102d570f16a4925a706f20285870350.html

Creates a row table Tab and a table function function_a that selects all records from Tab. This setup is used as the basis for the subsequent ALTER FUNCTION examples.

```sql
CREATE ROW TABLE Tab (COL1 INT PRIMARY KEY, COL2 INT);
CREATE FUNCTION function_a RETURNS TABLE (COL1 INT, COL2 INT) AS 
BEGIN 
   RETURN SELECT * FROM Tab; 
END;
```

--------------------------------

### Example: create table, library, procedure, and call

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/62263ce35ac74488a397553fcb25a7d6.html

Example statements that create a table, a SQLScript user-defined library, a procedure that calls the library, and then call the procedure. The library defines a constant, a function, and a procedure. The procedure uses the library and calls its function and procedure. The calls return 10 and 100 respectively.

```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
```

--------------------------------

### SUBSTRING example on string

Source: https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/20e8341275191014a4cfdcd3c830fc98.html

Selects two characters from the string '1234567890' starting at position 4, returning '45'.

```sql
SELECT SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY;
```
Tokens:
Raw