Help set_retention_date() Procedure

[Former Member]

Note : 1929538

HANA Statistics Server - Out of Memory | Consulting HANA Notes | SAP Techies

suggests making a procedure to delete _sys_stats data.

I have been experiencing OOM on AWS HANA One.

I currently do not have access to SCN notes about moving stats server or the above note.

Can some one explain in detail, about how to create the above procedure or move the stats server?

Cheers,

Thank you for your diligence.

[Former Member · Accepted solution]

Hi Zachary,

Here is the procedure. Create the procedure and then run "call set_retention_days(10);" to maintain 10 days worth of stats server data.

CREATE PROCEDURE set_retention_days (IN rdays int) LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

-- rdays [IN]: retention days

l_retention_days integer := 0;

l_alter_sys string;

l_alter_config_set string := 'alter system alter configuration

(''statisticsserver.ini'',''SYSTEM'') set

(''statisticsserver_general'',''editing'')=''yes'' with reconfigure';

l_alter_config_unset string := 'alter system alter configuration

(''statisticsserver.ini'',''SYSTEM'') unset

(''statisticsserver_general'',''editing'') with reconfigure';

CURSOR c_cursor for select substr_before(substr_after(value,'-'),')')

rtime, lower(trim('"' from substr_before(substr_after(value,'.'),' ')))

table_name from m_inifile_contents where

file_name='statisticsserver.ini' and layer_name='DEFAULT' and section =

'statisticsserver_sqlcommands' and value like 'delete%';

BEGIN

exec l_alter_config_set;

for cur as c_cursor do

   if cur.rtime >= rdays then

      l_retention_days := rdays;

   else

      l_retention_days := cur.rtime;

   end if;

   l_alter_sys := 'alter system alter configuration (''statisticsserver.ini'',''SYSTEM'') set (''statisticsserver_sqlcommands'',''delete_old_data_';

   l_alter_sys := l_alter_sys || cur.table_name || ''')='' delete from _sys_statistics.' || cur.table_name;

   l_alter_sys := l_alter_sys ||  ' where server_timestamp < add_days(CURRENT_TIMESTAMP,-' || :l_retention_days || ')''';

   if rdays >= 1 then

     exec l_alter_sys;

   end if;

end for;

exec l_alter_config_unset;

END;

Thanks

Suresh

[Former Member · Accepted solution]

This works nicely.