SAP HANA – SQL Tenant Database Management Statements

Hello Everyone,

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

 

Different statements available in SAP HANA are,

CREATE DATABASE
DROP DATABASE
ALTER DATABASE
ALTER SYSTEM START DATABASE
ALTER SYSTEM STOP DATABASE

This category gives us the list of SQL statements supported by SAP HANA tenant Database Management System.

Let’s look at details about each statement below.

CREATE DATABASE

CREATE DATABASE:

  • This statement is used to create a tenant database. We can create multiple databases.

The use of this statement requires the DATABASE ADMIN privilege.

Syntax: CREATE DATABASE <database_name> [AT [LOCATION] ‘<host_name>:<port_number>’] (ADD ‘<service_type>’ [AT [LOCATION] ‘<host_name>:<port_number>’] SYSTEM USER PASSWORD <password> [OS USER ‘<user_name>’ OS GROUP ‘<group name>’] [NO START]

database_name:

  • This parameter specifies the unique name of the database to be created.

service_type:

  • This parameter specifies the service type name to be used for the database.

user_name:

  • The operating system user under which the tenant is executed. The user must exist already. If left empty, the user sidadm will be created and used.

group_name:

  • The operating system group under which the tenant is executed. The group must exist already. If left empty, the user sapsys will be created and used.

Examples:

  1. Create a tenant database with automatic host placement and port.

Ans: CREATE DATABASE SAP_STUDENT SYSTEM USER PASSWORD Welcome01

  1. Create a tenant database at specific host and port number.

Ans: CREATE DATABASE SAP_STUDENT AT LOCATION ‘SS_01:30147’ SYSTEM USER PASSWORD Welcome01

  1. Create a tenant database at specific location and add additional index server and script server in different host.

Ans: CREATE DATABASE SAP_STUDENT AT LOCATION ‘SS_01’ ADD ‘indexserver’ AT ‘SS_02’ ADD ‘scriptserver’ SYSTEM USER PASSWORD Welcome01

DROP DATABASE

DROP DATABASE:

  • This statement is used to drop tenant database created using CREATE DATABASE statement in SAP HANA.

The use of this statement requires the DATABASE ADMIN privilege.

Syntax: DROP DATABASE <database_name>

Example:

  1. Drop tenant database SAP_STUDENT from SAP HANA.

Ans: DROP DATABASE SAP_STUDENT

ALTER DATABASE

ALTER DATABASE:

  • This statement is used to modify the tenant database created using CREATE DATABASE statement in SAP HANA.
The use of this statement requires the DATABASE ADMIN privilege.

Syntax: ALTER DATABASE <database_name> ((ADD ‘<service_type>’  [AT [LOCATION] ‘<hostname>:<port_number>’ ]) | ( REMOVE ‘<service_type>’ AT [LOCATION] ‘<hostname>:<port_number>’ ))

  • We can either add or remove services using ALTER DATABASE statement in SAP HANA for tenant database.
  • The services we can add are,
    • indexserver
    • xsengine
    • scriptserver
    • dpserver

Example:

  1. Alter tenant database SAP_STUDENT to add xsengine to location ‘SS_01’.

Ans: ALTER DATABASE SAP_STUDENT ADD ‘xsengine’  AT LOCATION ‘SS_01:30303’

ALTER SYSTEM START DATABASE

ALTER SYSTEM START DATABASE:

  • This statement is used to start the specific tenant database in SAP HANA.
The use of this statement requires the DATABASE ADMIN privilege.

Syntax: ALTER DATABASE START DATABASE <database_name>

Example:

  1. Start tenant database SAP_STUDENT

Ans: ALTER DATABASE START DATABASE SAP_STUDENT

ALTER SYSTEM STOP DATABASE

ALTER SYSTEM STOP DATABASE:

  • This statement is used to stop the specific tenant database in SAP HANA.
The use of this statement requires the DATABASE ADMIN privilege.
The statement causes a hard stop. The database is stopped immediately even if users are connected. Open transactions are aborted and rolled back; no savepoint operation is forced. It is not possible to back up a stopped database.

Syntax: ALTER DATABASE STOP DATABASE <database_name>

Example:

  1. Stop tenant database SAP_STUDENT from SAP HANA.

Ans: ALTER DATABASE STOP DATABASE SAP_STUDENT

With this we have seen different SQL statements available in SAP HANA under tenant Database 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 *

3 × 2 =