Workload class permits to limit also the parallelism.
They represent individual and named sets of resource properties
that are mapped to connection
SQL Syntax:
<CREATE|ALTER|DROP> WORKLOAD CLASS <NAME> [SET|UNSET <PROPERTY LIST>]
Example:
create workload class "DATAMART" set
'PRIORITY' = '6'
, 'STATEMENT MEMORY LIMIT' = '2'
,'STATEMENT THREAD LIMIT' = '8‘;
Metadata:
select * from workload_classes;
The HANA session layer reads the client context information
SQL Syntax:
<CREATE|ALTER|DROP> WORKLOAD MAPPING <NAME> WORKLOAD CLASS <WC NAME> [SET <PROPERTY LIST>]
Example:
create workload mapping "BICLIENT" workload class "DATAMART"
set 'USER NAME' = 'DATA001';
Metadata
select * from workload_mappings;
SELECT * FROM T1 WITH HINT( WORKLOAD_CLASS("MY_WORKLOAD_CLASS") );
From 1969700 - SQL Statement Collection for SAP HANA
select
WC.WORKLOAD_CLASS_NAME WORKLOAD_CLASS,
LPAD(WC.PRIORITY, 4) PRIO,
LPAD(TO_DECIMAL(WC.STATEMENT_MEMORY_LIMIT, 10, 2), 17) STMT_MEM_LIMIT_GB,
LPAD(WC.STATEMENT_THREAD_LIMIT, 17) STMT_THREAD_LIMIT,
IFNULL(WM.WORKLOAD_MAPPING_NAME, '') WORKLOAD_MAPPING,
IFNULL(WM.USER_NAME, '') USER_NAME,
IFNULL(WM.APPLICATION_USER_NAME, '') APP_USER_NAME,
IFNULL(WM.APPLICATION_NAME, '') APP_NAME,
IFNULL(WM.CLIENT, '') CLIENT
FROM
( SELECT /* Modification section */
'%' WORKLOAD_CLASS,
'%' WORKLOAD_MAPPING,
'%' USER_NAME,
'%' APPLICATION_USER_NAME,
'%' APPLICATION_NAME
FROM
DUMMY
) BI INNER JOIN
WORKLOAD_CLASSES WC ON
WC.WORKLOAD_CLASS_NAME LIKE BI.WORKLOAD_CLASS LEFT OUTER JOIN
WORKLOAD_MAPPINGS WM ON
UPPER(WM.WORKLOAD_CLASS_NAME) = UPPER(WC.WORKLOAD_CLASS_NAME) AND
UPPER(IFNULL(WM.WORKLOAD_MAPPING_NAME, '')) LIKE UPPER(BI.WORKLOAD_MAPPING) AND
UPPER(IFNULL(WM.USER_NAME, '')) LIKE UPPER(BI.USER_NAME) AND
UPPER(IFNULL(WM.APPLICATION_USER_NAME, '')) LIKE UPPER(BI.APPLICATION_USER_NAME) AND
UPPER(IFNULL(WM.APPLICATION_NAME, '')) LIKE UPPER(BI.APPLICATION_NAME)
ORDER BY
WC.WORKLOAD_CLASS_NAME,
WM.WORKLOAD_MAPPING_NAME,
WM.USER_NAME,
WM.APPLICATION_USER_NAME,
WM.APPLICATION_NAME,
WM.CLIENT
Managing workload classes requires the WORKLOAD ADMIN privilege
global.ini→ resource_tracking → enable_tracking = on / memory_tracking = on