on 08-07-2012 1:26 PM
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;
Hi John,
Trying to understand your procedure
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
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
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','');
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;
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
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
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
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.