[go: up one dir, main page]
Scribd
Upload
15 views7 pages

HANA SQL ActiveProcedures

The document provides details on the HANA_SQL_ActiveProcedure, which retrieves information about currently running procedures in SAP HANA. It outlines input parameters for filtering results, output parameters for the data returned, and example output format. Additionally, it mentions configuration options for retention of procedure call data and references relevant SAP notes.

Uploaded by

87matteocasanova
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
15 views7 pages

HANA SQL ActiveProcedures

The document provides details on the HANA_SQL_ActiveProcedure, which retrieves information about currently running procedures in SAP HANA. It outlines input parameters for filtering results, output parameters for the data returned, and example output format. Additionally, it mentions configuration options for retention of procedure call data and references relevant SAP notes.

Uploaded by

87matteocasanova
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 7

SELECT

/*

[NAME]

- HANA_SQL_ActiveProcedure

[DESCRIPTION]

- Details for active procedure calls

[SOURCE]

- SAP Note 1969700

[DETAILS AND RESTRICTIONS]

- Per default only populated for currently running procedures


- Increased retention can be configured with the following parameters (SAP Note
2000002):

indexserver.ini -> [sqlscript] -> number_of_calls_to_retain_after_execution


indexserver.ini -> [sqlscript] -> retention_period_for_sqlscript_context

[VALID FOR]

- Revisions: all

[SQL COMMAND VERSION]

- 2017/11/14: 1.0 (initial version)

[INVOLVED TABLES]

- M_ACTIVE_PROCEDURES

[INPUT PARAMETERS]

- BEGIN_TIME

Begin time

TO_TIMESTAMP('2014/06/05 14:05:00', 'YYYY/MM/DD HH24:MI:SS') --> Set begin time


to 5th of June 2014, 14:05
ADD_DAYS(CURRENT_TIMESTAMP, -2) --> Set begin time
to two days before current time

- END_TIME

End time

TO_TIMESTAMP('2014/06/08 14:05:00', 'YYYY/MM/DD HH24:MI:SS') --> Set end time to


8th of June 2014, 14:05
CURRENT_TIMESTAMP --> Set end time to
current time

- TIMEZONE
Used timezone (both for input and output parameters)

'SERVER' --> Display times in SAP HANA server time


'UTC' --> Display times in UTC time

- HOST

Host name

'saphana01' --> Specific host saphana01


'saphana%' --> All hosts starting with saphana
'%' --> All hosts

- PORT

Port number

'30007' --> Port 30007


'%03' --> All ports ending with '03'
'%' --> No restriction to ports

- SCHEMA_NAME

Schema name or pattern

'SAPSR3' --> Specific schema SAPSR3


'SAP%' --> All schemata starting with 'SAP'
'%' --> All schemata

- PROCEDURE_NAME

Procedure name

'STATISTICS_SCHEDULABLEWRAPPER' --> Procedure STATISTICS_SCHEDULABLEWRAPPER


'%' --> No restriction related to procedure

- STATEMENT_STRING

Statement string

'INSERT%' --> Statements starting with "INSERT"


'%' --> No restriction related to statement string

- STATEMENT_STATUS

Statement status

'EXECUTING' --> Statement status EXECUTING


'%' --> No restriction relaed to statement status

- CONN_ID

Connection ID

330655 --> Connection ID 330655


-1 --> No connection ID restriction

- STATEMENT_ID
SQL statement identifier (varies for different executions of same statement hash)

'859110927564988' --> Only display samples with statement ID 859110927564988


'%' --> No restriction related to statement ID

- AGGREGATE_BY

Aggregation criteria (possible values can be found in comment)

'TIME' --> Aggregation by timestamp


'HOST, PORT' --> Aggregation by host and port
'NONE' --> No aggregation

- TIME_AGGREGATE_BY

Aggregation criteria (possible values can be found in comment)

'HOUR' --> Aggregation by hour


'YYYY/WW' --> Aggregation by calendar week
'TS<seconds>' --> Time slice aggregation based on <seconds> seconds
'NONE' --> No aggregation

- ORDER_BY

Sort criteria (available values are provided in comment)

'TIME' --> Sorting by start time


'DURATION' --> Sorting by execution time
'MEMORY' --> Sorting by used memory
'CPU' --> Sorting by CPU time

[OUTPUT PARAMETERS]

- START_TIME: Start time


- HOST: Host name
- PORT: Port
- SCHEMA_NAME: Schema name
- PROCEDURE_NAME: Procedure name
- CONN_ID: Connection ID
- STATEMENT_ID: Statement ID
- EXECUTIONS: Number of executions
- EXECUTE_MS: Total execution time (ms)
- EXE_PER_EXEC_MS: Time per execution (ms)
- COMPILE_MS: Total compile time (ms)
- DEPTH: Call depth
- STATEMENT_STATUS: Statement status
- STATEMENT_STRING: Statement text
- BIND_VALUES: Bind values

[EXAMPLE OUTPUT]

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------
|START_TIME |HOST |PORT |SCHEMA_NAME |PROCEDURE_NAME
|CONN_ID |STATEMENT_ID |EXECUTIONS|EXECUTE_MS|EXE_PER_EXEC_MS|COMPILE_MS|
DEPTH|STATEMENT_STATUS|STATEMENT_STRING
|BIND_VALUES |
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------
|2017/11/14 09:05:45:2889070|saphana5|30003|_SYS_STATISTICS|
STATISTICS_SCHEDULABLEWRAPPER| 400053|1718215682249923| 0| 78.25|
0.00| 0.34| 2|EXECUTING | WITH _SYS_WITH_0 AS (/* procedure:
_SYS_STATISTICS.ALERT_CHECK_INACTIVE_SERVICES variable:...|
|
|2017/11/14 09:05:45:0678180|saphana5|30003|_SYS_STATISTICS|
STATISTICS_SCHEDULABLEWRAPPER| 400050|1718202286830377| 0| 299.26|
0.00| 0.27| 2|EXECUTING | WITH _SYS_WITH_0 AS (/* procedure:
_SYS_STATISTICS.ALERT_BACKUP_LONG_LOG_BACKUP variable: ...|
|
|2017/11/14 09:05:45:0657870|saphana5|30003|_SYS_STATISTICS|
STATISTICS_SCHEDULABLEWRAPPER| 400053|1718214685618559| 1| 219.47|
219.47| 0.27| 2|COMPLETED | INSERT INTO
"_SYS_STATISTICS"."HELPER_ALERT_CHECK_INACTIVE_SERVICES_AGE" SELECT
"SERVICE_ID",...| |
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------

*/

START_TIME,
HOST,
LPAD(PORT, 5) PORT,
SCHEMA_NAME,
PROCEDURE_NAME,
LPAD(CONN_ID, 10) CONN_ID,
LPAD(STATEMENT_ID, 16) STATEMENT_ID,
LPAD(EXECUTIONS, 10) EXECUTIONS,
LPAD(TO_DECIMAL(EXECUTE_MS, 10, 2), 10) EXECUTE_MS,
LPAD(TO_DECIMAL(MAP(EXECUTIONS, 0, 0, EXECUTE_MS / EXECUTIONS), 10, 2), 15)
EXE_PER_EXEC_MS,
LPAD(TO_DECIMAL(COMPILE_MS, 10, 2), 10) COMPILE_MS,
LPAD(DEPTH, 5) DEPTH,
STATEMENT_STATUS,
STATEMENT_STRING,
BIND_VALUES
FROM
( SELECT
CASE
WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'TIME') != 0 THEN
CASE
WHEN BI.TIME_AGGREGATE_BY LIKE 'TS%' THEN
TO_VARCHAR(ADD_SECONDS(TO_TIMESTAMP('2014/01/01 00:00:00', 'YYYY/MM/DD
HH24:MI:SS'), FLOOR(SECONDS_BETWEEN(TO_TIMESTAMP('2014/01/01 00:00:00',
'YYYY/MM/DD HH24:MI:SS'), CASE BI.TIMEZONE WHEN 'UTC' THEN
ADD_SECONDS(P.STATEMENT_START_TIME, SECONDS_BETWEEN(CURRENT_TIMESTAMP,
CURRENT_UTCTIMESTAMP)) ELSE P.STATEMENT_START_TIME END) /
SUBSTR(BI.TIME_AGGREGATE_BY, 3)) * SUBSTR(BI.TIME_AGGREGATE_BY, 3)), 'YYYY/MM/DD
HH24:MI:SS')
ELSE TO_VARCHAR(CASE BI.TIMEZONE WHEN 'UTC' THEN
ADD_SECONDS(P.STATEMENT_START_TIME, SECONDS_BETWEEN(CURRENT_TIMESTAMP,
CURRENT_UTCTIMESTAMP)) ELSE P.STATEMENT_START_TIME END, BI.TIME_AGGREGATE_BY)
END
ELSE 'any'
END START_TIME,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'HOST') !=
0 THEN P.PROCEDURE_HOST ELSE MAP(BI.HOST, '%',
'any', BI.HOST) END HOST,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PORT') !=
0 THEN TO_VARCHAR(P.PROCEDURE_PORT) ELSE MAP(BI.PORT, '%',
'any', BI.PORT) END PORT,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'CONN_ID') !=
0 THEN TO_VARCHAR(P.PROCEDURE_CONNECTION_ID) ELSE MAP(BI.CONN_ID, -1,
'any', TO_VARCHAR(BI.CONN_ID)) END CONN_ID,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'STATEMENT_ID') !=
0 THEN TO_VARCHAR(P.STATEMENT_ID) ELSE MAP(BI.STATEMENT_ID, -1,
'any', TO_VARCHAR(BI.STATEMENT_ID)) END STATEMENT_ID,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'SCHEMA') !=
0 THEN P.PROCEDURE_SCHEMA_NAME ELSE MAP(BI.SCHEMA_NAME, '%',
'any', BI.SCHEMA_NAME) END SCHEMA_NAME,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PROCEDURE') !=
0 THEN P.PROCEDURE_NAME ELSE MAP(BI.PROCEDURE_NAME, '%',
'any', BI.PROCEDURE_NAME) END PROCEDURE_NAME,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'STATUS') !=
0 THEN P.STATEMENT_STATUS ELSE MAP(BI.STATEMENT_STATUS, '%',
'any', BI.STATEMENT_STATUS) END STATEMENT_STATUS,
MAP(MIN(TO_VARCHAR(P.STATEMENT_STRING)), MAX(TO_VARCHAR(P.STATEMENT_STRING)),
MIN(TO_VARCHAR(P.STATEMENT_STRING)), 'any') STATEMENT_STRING,
MAP(MIN(TO_VARCHAR(P.STATEMENT_PARAMETERS)),
MAX(TO_VARCHAR(P.STATEMENT_PARAMETERS)), MIN(TO_VARCHAR(P.STATEMENT_PARAMETERS)),
'any') BIND_VALUES,
SUM(P.STATEMENT_EXECUTION_COUNT) EXECUTIONS,
MAP(MIN(P.STATEMENT_DEPTH), MAX(P.STATEMENT_DEPTH),
TO_VARCHAR(MIN(P.STATEMENT_DEPTH)), 'any') DEPTH,
SUM(P.STATEMENT_EXECUTION_TIME) / 1000 EXECUTE_MS,
SUM(P.STATEMENT_COMPILE_TIME) / 1000 COMPILE_MS,
BI.ORDER_BY
FROM
( SELECT
BEGIN_TIME,
END_TIME,
TIMEZONE,
HOST,
PORT,
SCHEMA_NAME,
PROCEDURE_NAME,
STATEMENT_STRING,
STATEMENT_STATUS,
CONN_ID,
STATEMENT_ID,
AGGREGATE_BY,
MAP(TIME_AGGREGATE_BY,
'NONE', 'YYYY/MM/DD HH24:MI:SS:FF7',
'HOUR', 'YYYY/MM/DD HH24',
'DAY', 'YYYY/MM/DD (DY)',
'HOUR_OF_DAY', 'HH24',
TIME_AGGREGATE_BY ) TIME_AGGREGATE_BY,
ORDER_BY
FROM
( SELECT /* Modification section */
TO_TIMESTAMP('1000/01/12 12:00:00', 'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME,
TO_TIMESTAMP('9999/01/13 12:00:00', 'YYYY/MM/DD HH24:MI:SS') END_TIME,
'SERVER' TIMEZONE, /* SERVER, UTC */
'%' HOST,
'%' PORT,
'%' SCHEMA_NAME,
'%' PROCEDURE_NAME,
'%' STATEMENT_STRING,
'%' STATEMENT_STATUS, /* EXECUTING, COMPLETED, ... */
-1 CONN_ID,
-1 STATEMENT_ID,
'NONE' AGGREGATE_BY, /* TIME, HOST, PORT, CONN_ID,
SCHEMA, PROCEDURE, STATUS or comma separated combinations, NONE for no aggregation
*/
'NONE' TIME_AGGREGATE_BY, /* HOUR, DAY, HOUR_OF_DAY or
database time pattern, TS<seconds> for time slice, NONE for no aggregation */
'TIME' ORDER_BY /* TIMESTAMP, PROCEDURE, EXECUTIONS,
EXEC_TIME, COMP_TIME */
FROM
DUMMY
)
) BI,
M_ACTIVE_PROCEDURES P
WHERE
CASE BI.TIMEZONE WHEN 'UTC' THEN ADD_SECONDS(P.STATEMENT_START_TIME,
SECONDS_BETWEEN(CURRENT_TIMESTAMP, CURRENT_UTCTIMESTAMP)) ELSE
P.STATEMENT_START_TIME END BETWEEN BI.BEGIN_TIME AND BI.END_TIME AND
P.PROCEDURE_HOST LIKE BI.HOST AND
TO_VARCHAR(P.PROCEDURE_PORT) LIKE BI.PORT AND
P.PROCEDURE_SCHEMA_NAME LIKE BI.SCHEMA_NAME AND
P.PROCEDURE_NAME LIKE BI.PROCEDURE_NAME AND
( BI.CONN_ID = -1 OR P.PROCEDURE_CONNECTION_ID = BI.CONN_ID ) AND
( BI.STATEMENT_ID = -1 OR P.STATEMENT_ID = BI.STATEMENT_ID ) AND
TO_VARCHAR(P.STATEMENT_STRING) LIKE BI.STATEMENT_STRING AND
P.STATEMENT_STATUS LIKE BI.STATEMENT_STATUS
GROUP BY
CASE
WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'TIME') != 0 THEN
CASE
WHEN BI.TIME_AGGREGATE_BY LIKE 'TS%' THEN
TO_VARCHAR(ADD_SECONDS(TO_TIMESTAMP('2014/01/01 00:00:00', 'YYYY/MM/DD
HH24:MI:SS'), FLOOR(SECONDS_BETWEEN(TO_TIMESTAMP('2014/01/01 00:00:00',
'YYYY/MM/DD HH24:MI:SS'), CASE BI.TIMEZONE WHEN 'UTC' THEN
ADD_SECONDS(P.STATEMENT_START_TIME, SECONDS_BETWEEN(CURRENT_TIMESTAMP,
CURRENT_UTCTIMESTAMP)) ELSE P.STATEMENT_START_TIME END) /
SUBSTR(BI.TIME_AGGREGATE_BY, 3)) * SUBSTR(BI.TIME_AGGREGATE_BY, 3)), 'YYYY/MM/DD
HH24:MI:SS')
ELSE TO_VARCHAR(CASE BI.TIMEZONE WHEN 'UTC' THEN
ADD_SECONDS(P.STATEMENT_START_TIME, SECONDS_BETWEEN(CURRENT_TIMESTAMP,
CURRENT_UTCTIMESTAMP)) ELSE P.STATEMENT_START_TIME END, BI.TIME_AGGREGATE_BY)
END
ELSE 'any'
END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'HOST') !=
0 THEN P.PROCEDURE_HOST ELSE MAP(BI.HOST, '%',
'any', BI.HOST) END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PORT') !=
0 THEN TO_VARCHAR(P.PROCEDURE_PORT) ELSE MAP(BI.PORT, '%',
'any', BI.PORT) END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'CONN_ID') !=
0 THEN TO_VARCHAR(P.PROCEDURE_CONNECTION_ID) ELSE MAP(BI.CONN_ID, -1,
'any', TO_VARCHAR(BI.CONN_ID)) END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'STATEMENT_ID') !=
0 THEN TO_VARCHAR(P.STATEMENT_ID) ELSE MAP(BI.STATEMENT_ID, -1,
'any', TO_VARCHAR(BI.STATEMENT_ID)) END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'SCHEMA') !=
0 THEN P.PROCEDURE_SCHEMA_NAME ELSE MAP(BI.SCHEMA_NAME, '%',
'any', BI.SCHEMA_NAME) END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PROCEDURE') !=
0 THEN P.PROCEDURE_NAME ELSE MAP(BI.PROCEDURE_NAME, '%',
'any', BI.PROCEDURE_NAME) END,
CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'STATUS') !=
0 THEN P.STATEMENT_STATUS ELSE MAP(BI.STATEMENT_STATUS, '%',
'any', BI.STATEMENT_STATUS) END,
BI.ORDER_BY
)
ORDER BY
MAP(ORDER_BY, 'PROCEDURE', SCHEMA_NAME || PROCEDURE_NAME),
MAP(ORDER_BY, 'EXECUTIONS', EXECUTIONS, 'EXEC_TIME', EXECUTE_MS, 'COMP_TIME',
COMPILE_MS) DESC,
START_TIME DESC

You might also like