You are Here: ://SAP HANA – SQL Workload Management Statements

SAP HANA – SQL Workload Management Statements

Hello Everyone,

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

 

Different statements available in SAP HANA are,

CREATE WORKLOAD MAPPING
ALTER WORKLOAD MAPPING
DROP WORKLOAD MAPPING
CREATE WORKLOAD CLASS
ALTER WORKLOAD CLASS
DROP WORKLOAD CLASS

Let’s look at details about each statement below. These statements are available from SAP HANA SP10.

CREATE WORKLOAD MAPPING

CREATE WORKLOAD MAPPING:

  • This statement is used to define workload mapping in SAP HANA database.

User should have WORKLOAD_ADMIN privilege to run this statement.

Syntax: CREATE WORKLOAD MAPPING <mapping_name> WORKLOAD CLASS <class_name> <properties>

  • Using this statement we can SET or UNSET the workload mapping and workload class for any of the following things.
    • APPLICATION USER NAME
    • USER NAME
    • APPLICATION NAME
    • CLIENT

All the defined workload mappings can be seen in view WORKLOAD_MAPPINGS under SYS schema.

Example:

  1. Create workload mapping with name ‘ss_workload_mapping’ and workload class ‘ss_workload_class’ on user SAP_STUDENT and application user BO_USER

Ans: CREATE WORKLOAD MAPPING “ss_workload_mapping” WORKLOAD CLASS “ss_workload_class” SETUSER NAME’ = ‘SAP_STUDENT’, ‘APPLICATION USER NAME’ = ‘BO_USER’

ALTER WORKLOAD MAPPING

ALTER WORKLOAD MAPPING:

  • This statement is used to make changes to the defined workload mappings in SAP HANA Database.

User should have WORKLOAD_ADMIN privilege to run this statement.

Syntax: ALTER WORKLOAD MAPPING <mapping_name> WORKLOAD CLASS <class_name> <properties>

Example:

  1. Modify the workload mapping ‘ss_workload_mapping’ to change the user to VENKATESH and unset the APPLICATION USER NAME.

Ans: ALTER WORKLOAD MAPPING “ss_workload_mapping” WORKLOAD CLASS “ss_workload_class” SETUSER NAME’ = ‘VENKATESH’, UNSET ‘APPLICATION USER NAME’ = ‘BO_USER’

DROP WORKLOAD MAPPING

DROP WORKLOAD MAPPING:

  • This statement is used to drop (remove) the workload mappings created using CREATE WORKLOAD MAPPING statement in SAP HANA Database.
User should have WORKLOAD_ADMIN privilege to run this statement.

Syntax: DROP WORKLOAD MAPPING <mapping_name>

Example:

  1. Drop the workload mapping ‘ss_workload_mapping’ in SAP HANA.

Ans: DROP WORKLOAD MAPPING “ss_workload_mapping”

CREATE WORKLOAD CLASS

CREATE WORKLOAD CLASS:

  • This statement is used to create workload class in SAP HANA Database.

User should have WORKLOAD_ADMIN privilege to run this statement. Workload class with only NULL values behaves like the default workload class.

Syntax: CREATE WORKLOAD CLASS <workload_class_name> <properties>

  • We have three different properties that can be set or unset using SET and UNSET operators while defining workload class. They are
    • PRIORITY
    • STATEMENT MEMORY LIMIT
    • STATEMENT THREAD LIMIT

All the defined workload classes can be seen in view WORKLOAD_CLASSES under SYS schema.

Example:

  1. Create a workload class ‘ss_workload_class’ with all three properties.

Ans: CREATE WORKLOAD CLASS “ss_workload_class” SETPRIORITY’ = ‘3’, ‘STATEMENT MEMORY LIMIT’ = ‘2’, ‘STATEMENT THREAD LIMIT’ = ‘20’

ALTER WORKLOAD CLASS

ALTER WORKLOAD CLASS:

  • This statement is used to modify the existing workload class in SAP HANA database.

User should have WORKLOAD_ADMIN privilege to run this statement. Workload class with only NULL values behaves like the default workload class.

Syntax: ALTER WORKLOAD CLASS <workload_class_name> <properties>

Example:

  1. Alter the workload class ‘ss_workload_class’ to unset the value for STATEMENT_MEMORY_LIMIT and change the PRIORITY from 3 to 5.

Ans: ALTER WORKLOAD CLASS “ss_workload_class” UNSET ‘STATEMENT MEMORY LIMITSETPRIORITY’ = ‘5’

Once we unset any property of workload class, the view WORKLOAD_CLASSES displays NULL value for that.

DROP WORKLOAD CLASS

DROP WORKLOAD CLASS:

  • This statement is used to drop (remove) the workload classes created using CREATE WORKLOAD CLASS statement in SAP HANA Database.

User should have WORKLOAD_ADMIN privilege to run this statement.

Syntax: DROP WORKLOAD CLASS <workload_class_name>

Example:

  1. Drop the workload class ‘ss_workload_class in SAP HANA.

Ans: DROP WORKLOAD CLASS “ss_workload_class”

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

By | 2017-08-14T22:09:13+00:00 November 12th, 2015|HANA|0 Comments

Leave A Comment

seven + fourteen =

Skip to toolbar