SAP HANA – SQL Session Management Statements

Hello Everyone,

In this article, we are going to look at different SQL Session Management Statements (SNMS) available in SAP HANA along with their syntax and examples.

 

Different statements available in SAP HANA are,

CONNECT
SET HISTORY SESSION
SET SCHEMA
SET [SESSION] UNSET [SESSION]

Let’s look at details about each statement below.

CONNECT

CONNECT:

  • This statement is used to connect to SAP HANA database instance using either username and password pair or a SAML assertion for identification.

Syntax: CONNECT <user_name> PASSWORD <password> | [WITH SAML SEERTION <xml>]

Examples:

  1. Connect to the SAP HANA system using user name and password.

Ans: CONNECT SAP_STUDENT PASSWORD Welcome01

  1. Connect to the SAP HANA system using user name, password and SAML identity.

Ans: CONNECT SAP_STUDENT PASSWORD Welcome01 WITH IDENTITY ANY FOR SAML PROVIDER company_sap_student

SET HISTORY SESSION

SET HISTORY SESSION:

  • This statement is used to effectively “time travel” the current database session so that it can “see” a previous version of history tables.
  • We can specify the table version required by COMMIT ID or UTCTIMESTAMP format. We can also set the session to see the current state of history tables by using the NOW parameter.

This command only effects history tables, normal tables will remain unchanged.

Syntax: SET HISTORY SESSION TO <when>

We can use different option in <when> to see the history of table at certain point of time. The options we have are,

  • NOW: Specifies that the database session should use the current version of the history table.
  • COMMIT ID: Specifies that the database session should use a version of the database table that was available at a specific commit Id.
  • UTCTIMESTAMP: Specifies that the database session should use a version of the database table that was available at a specific timestamp. The timestamp has to be in format ‘YYYY-MM-DD HH:MM:SS[.FF7]’.

We can see the last commit id’s and timestamp after each commit in session o transaction in the below views under SYS schema.

M_HISTORY_INDEX_LAST_COMMIT_ID: Holds information on last commit id after each commit.
TRANSACTION_HISTORY: Holds information of timestamp of the last commit.

Examples:

Before we test time travel using SET HISTORY SESSION, we need to have HISTORY COLUMN table created in HANA and ‘autocommit’ option should be ‘off’ in SQL console.

  1. Create history column table in SAP HANA using below syntax.

Ans: CREATE HISTORY COLUMN TABLE SAP_STUDENT.HISTOR_TABLE ( COLUMN1 INTEGER, COLUMN2 INTEGER);

Note: Make sure ‘autocommit’ is ‘OFF’ in SQL Console.

  1. Insert two records using below statements and execute commit as we have turned off auto commit option.

Ans: INSERT INTO SAP_STUDENT.HISTOR_TABLE VALUES (1,1);
       COMMIT;
       INSERT INTO SAP_STUDENT.HISTOR_TABLE VALUES (2,2);
       COMMIT;

  1. Get the last commit using the below statement.

Ans: SELECT LAST_COMMIT_ID FROM M_HISTORY_INDEX_LAST_COMMIT_ID WHERE SESSION_ID = CURRENT_CONNECTION;

  1. Delete the data from table using below statement and execute commit.

Ans: DELETE FROM SAP_STUDENT.HISTOR_TABLE;
       COMMIT;

  1. Run the below statements to set history session to the commit id obtained in step3 (at this time we had two records in table) and execute select statement on table to see two records in output.

Ans: SET HISTORY SESSION TO COMMIT ID <last_comit_id> (use commit id from step3);
       SELECT * FROM SAP_STUDENT.HISTOR_TABLE;

  1. Run the below statements to get the timestamp for commit id in step3 and run select statement on table which should give two records in output.

Ans: SELECT COMMIT_TIME FROM SYS.TRANSACTION_HISTORY WHERE COMMIT_ID = <last_comit_id>;
       SET HISTORY SESSION TO UTCTIMESTAMP ‘<commit_time>’;
       SELECT * FROM SAP_STUDENT.HISTOR_TABLE;;

  1. Run the below statements to set history session to current timestamp and execute select statement on table to see no records in output (because we have already deleted data from table and it doesn’t hold any data right now).

Ans: SET HISTORY SESSION TO NOW;
       SELECT * FROM SAP_STUDENT.HISTOR_TABLE;

SET SCHEMA

SET SCHEMA:

  • This statement is used to change the current schema of the session.
  • The current schema is used when SQL statements use database object names, for example table names, that are not prefixed with a schema name.

Syntax: SET SCHEMA <schema_name>

Example:

  1. Run the below sql statement to change the current schema in SQL console to SAP_STUDENT

Ans: SET SCHEMA SAP_STUDENT

We can always see the current schema at top of the SQL console.

SET SESSION

SET [SESSION]:

  • This statement is used set session variables of our own database session by providing key and value pairs.

We can check the list of predefined session variables in view M_SESSION_CONTEXT under SYS schema.
Session variables can be retrieved using SESSION_CONTEXT function or from M_SESSION_CONTEXT view in SYS schema and unset using the UNSET [SESSION] command.

Syntax: SET SESSION <key> = <value>

  • The Key should maximum of 32 characters in length.
  • The value should be maximum of 512 characters in length

Examples:

  1. Set the session variable SS_VAR to ‘ss_student’

Ans: SET ‘SS_VAR’ = ‘sap_student’

  1. Select the variable value using either SESSION_CONTEXT function or from M_SESSION_CONTEXT view in SYS schema.

Ans: SELECT SESSION_CONTEXT(‘SS_VAR’) FROM DUMMY;
                     OR
       SELECT * FROM SYS.M_SESSION_CONTEXT WHERE CONNECTION_ID = CURRENT_CONNECTION

  1. Unset the value for session variable ‘SS_VAR’.

Ans: UNSET ‘SS_VAR’

UNSET SESSION

UNSET [SESSION]:

  • This statement is used to unset session variables of the current session which has been set using SET SESSION statement.

We can check the list of predefined session variables in view M_SESSION_CONTEXT under SYS schema.

Syntax: UNSET SESSION <key>

  • The Key should maximum of 32 characters in length.

Example:

  1. Unset the session variable ‘SS_VAR’

Ans: UNSET ‘SS_VAR’

With this we have seen different SQL statements available in SAP HANA under Session Management statements category along with their syntax and examples.

Thank you for reading and hope this information is helpful. Please do share with your friends if you feel the information is useful.

Happy Learning.

The reference was taken from below link (for more details check the link): 

https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/ff532c751910148657c32fe3431a9f/content.htm?frameset=/en/2e/1ef8b4f4554739959886e55d4c127b/frameset.htm&current_toc=/en/2e/1ef8b4f4554739959886e55d4c127b/plain.htm&node_id=4&show_children=false

Check Also

Capture

SAP HANA – SQL Transaction Management Statements

Hello Everyone, In this article, we are going to look at different SQL Transaction Management …

Leave a Reply

Your email address will not be published. Required fields are marked *

nineteen + 17 =