SQLScript to drop memory-resident columnar tables

[Former Member]

Hi,

I tried to write SQLScript to unload the tables from my HANA appliance on mass. But whilst it runs, it doesn't do anything. Is anyone able to spot why? Thanks so much!

Regards,

John

DROP PROCEDURE unload_tables;

CREATE PROCEDURE unload_tables() LANGUAGE SQLSCRIPT AS           v_table_name

VARCHAR(20) := '';  v_table VARCHAR(60) := '';

CURSOR c_cursor1 (v_table_name VARCHAR(60) ) FOR

SELECT TABLE_NAME FROM M_CS_TABLES WHERE RECORD_COUNT!=0 AND TABLE_NAME =

:v_table_name ORDER BY MEMORY_SIZE_IN_TOTAL DESC;

BEGIN

FOR cur_row as c_cursor1(v_table_name) DO

v_table := cur_row.TABLE_NAME;

EXEC 'UNLOAD SAPBW1.(''' || :v_table || ''')';

END FOR;

END;

[former_member184768 · Accepted solution]

Hi John,

Trying to understand your procedure

• which does not take any input
• has v_table_name assigned to ' ' (blank)
• v_table_name with blank value is used in the cursor select statement

If v_table_name to the cursor is not assigned, won't the select statement return 0 records and no unload statement will be executed.

Did I miss something here ?

Regards,

Ravi

[Former Member · Accepted solution]

You may be right - I was trying to use the cursor to return a list as per the example in the SQLScript guide:

CREATE PROCEDURE foreach_proc() LANGUAGE SQLSCRIPT AS v_isbn VARCHAR(20) := '';
CURSOR c_cursor1 (v_isbn VARCHAR(20)) FOR

       SELECT isbn, title, price, crcy FROM books ORDER BY isbn; 

BEGIN

FOR cur_row as c_cursor1 DO
CALL ins_msg_proc('book title is: ' || cur_row.title);

END FOR; END;

Are you saying it should be something like:

DROP PROCEDURE unload_tables;

CREATE PROCEDURE unload_tables() LANGUAGE SQLSCRIPT AS           v_table_name

VARCHAR(20) := '';  v_table VARCHAR(60) := '';

CURSOR c_cursor1 (v_table_name VARCHAR(60) ) FOR

SELECT TABLE_NAME FROM M_CS_TABLES WHERE RECORD_COUNT!=0 ORDER BY MEMORY_SIZE_IN_TOTAL DESC;

BEGIN

FOR cur_row as c_cursor1(v_table_name) DO

v_table := cur_row.TABLE_NAME;

EXEC 'UNLOAD SAPBW1.(''' || :v_table || ''')';

END FOR;

END;

[former_member182114 · Accepted solution]

Hi John,

Complementing what Ravi pointed out, the  UNLOAD command will fail (when have data), below syntax is incorrect:

UNLOAD SAPBW1.('TABLE');

Should be:

UNLOAD SAPBW1.TABLE;

or

UNLOAD "SAPBW1"."TABLE";

Regards, Fernando Da Rós

[former_member184768 · Accepted solution]

Hi John,

I took the liberty to change your procedure code and the following code works fine.

CREATE PROCEDURE unload_tables()

  LANGUAGE SQLSCRIPT AS

v_table_name VARCHAR(20) := 'TT_3'; 

v_table VARCHAR(60) := '';

CURSOR c_cursor1 (vc_table_name VARCHAR(60) ) FOR

SELECT TABLE_NAME FROM M_CS_TABLES WHERE RECORD_COUNT!=0 AND TABLE_NAME = :vc_table_name ORDER BY MEMORY_SIZE_IN_TOTAL DESC;

BEGIN

FOR cur_row as c_cursor1(v_table_name) DO

v_table := cur_row.TABLE_NAME;

EXEC 'UNLOAD BIAPPL.' || :v_table;

END FOR;

END;

Regards,

Ravi

[former_member184768 · Accepted solution]

BTW, you can pass the table name or table names to the procedure as INPUT variables and use the table name(s) with IN operator in the cursor SELECT statement.

Regards,

Ravi

[former_member182114 · Accepted solution]

Follow with schema and table as input parameters:

DROP PROCEDURE unload_tables;

CREATE PROCEDURE unload_tables( IN pi_schema VARCHAR(60), IN pi_table VARCHAR(60) ) LANGUAGE SQLSCRIPT AS

CURSOR c_cursor1 FOR

SELECT TABLE_NAME

FROM M_CS_TABLES

WHERE RECORD_COUNT != 0

  AND SCHEMA_NAME = :pi_schema

  AND (TABLE_NAME = :pi_table or :pi_table = '');

BEGIN

  FOR cur_row as c_cursor1() DO

    EXEC 'UNLOAD "' || :pi_schema || '"."' || cur_row.TABLE_NAME || '"';

  END FOR;

END;

-- unload all tables of schema SAPBW1

call unload_tables('SAPBW1','');

[Former Member · Accepted solution]

Perfect thanks so much. And actually my original code was really unoptimal - I added two more exclusions, first to exclude when the table is not loaded into memory (i.e. Memory size = 0) and also when the delta is bigger than the main, because we don't want to unload tables whilst they are undergoing a delta merge. The following code runs 40x faster 🙂

DROP PROCEDURE unload_tables;

CREATE PROCEDURE unload_tables( IN pi_schema VARCHAR(60), IN pi_table VARCHAR(60) ) LANGUAGE SQLSCRIPT AS

CURSOR c_cursor1 FOR

SELECT TABLE_NAME

FROM M_CS_TABLES

WHERE RECORD_COUNT != 0

  AND MEMORY_SIZE_IN_MAIN!=0

  AND MEMORY_SIZE_IN_MAIN>MEMORY_SIZE_IN_DELTA

  AND SCHEMA_NAME = :pi_schema

  AND (TABLE_NAME = :pi_table or :pi_table = '');

BEGIN

  FOR cur_row as c_cursor1() DO

    EXEC 'UNLOAD "' || :pi_schema || '"."' || cur_row.TABLE_NAME || '"';

  END FOR;

END;

[Former Member · Accepted solution]

Thanks for the help Ravi, appreciate it. I'm not the best SQL programmer.

[former_member182114 · Accepted solution]

Hi John,

Just a question for understanding... What is the scenario you need this?

As far as I know these unloads are automatic when HANA understand that need unload it.

It's not working properly for you?

Regards, Fernando Da Rós

[Former Member · Accepted solution]

You're right that it should, but it doesn't in my case. I'm trying to load a BW ABAP export into a system which is too small (Mac Mini).

I think there is a Linux bug on this chipset which is causing a memory leak in the disk cache because the cache keeps growing and not giving the RAM back.

Possibly that is confusing HANA into thinking she has more RAM available than she does. This script lets me load the DB without memory errors 🙂

John

[former_member184768 · Accepted solution]

Hi Fernando,

I am sure John will comment on his need for unloading the tables, but I too unload tables quite often. I am not sure when HANA makes the decision to unload it automatically, but many a times, I have seen the data loads / activation / Query executions failing because of non-availability of the memory.

So I keep checking the tables in the memory which are not frequently used and unload them to free up the memory space.

Regards,

Ravi

[former_member182114 · Accepted solution]

Hi Ravi,

Many thanks for this info. I didn't know this happens so usually.

Here on the project, despite we had 512/1T installation) we just faced memory exausted on beggining of project (time that we had SQL's which huge wrong and unecessary materializations) joins with incorrect links...

Thanks again, I'll keep this in mind.

Kind regards, Fernando Da Rós

[Former Member · Accepted solution]

Makes sense. I thought it was specific to my test box.

I specifically find that HANA doesn't drop tables during the BW import. Wonder if there is a good reason for this. Obviously in the real world you would have enough RAM and this wouldn't matter.

[Former Member]

Hi John -

I have a similar requirement to unload all tables of SAP BW schema, Please advice as the script given above is not working,

Code seems to be working but it is unloading only few tables...as around 5gb is occupied by BW schema and running the script made difference not greater than 0.5 MB, please advice

thanks!

[henrique_pinto]

This was a great thread with awesome inputs from everybody.

Meanwhile, BW 7.3 SP8 supposedly brought news regarding the automatic unloading of non-active data in BW models (DSOs & InfoCubes), reducing the overall memory usage to aound ~20% of the total.

Check pages 10, 11 & 12 of the document attached to this article:

http://www.saphana.com/community/blogs/blog/2012/10/08/whats-new-with-sap-netweaver-bw-73-sp-8

Notice this functionality is based on SPS5 of HANA DB.

Best regards,

Henrique.