How to perform manual Load and unload of column store tables in HANA
PLAYING AROUNG WITH COLUMN TABLES IN HANA USING SQLs:
Important tables which hold load status of column store table:M_CS_LOADS,M_CS_UNLOADS
As a continuation to my previous blog below, here are the screen prints as of how to load or unload a table manually inside HANA DB memory.
Tables involved: M_CS_TABLES,M_CS_COLUMNS,M_CS_UNLOADS
1.Status check of SAPERP.BSEG inside HANA memory as on date.
Way 1:select * from m_cs_tables where table_name=’BSEG’ and schema_name = ‘SAPERP’
Below screen we can see that BSEG has 7 partitions and each partitions have 159 million records and all these partitions are partially loaded .
2.In above we see that this table is loaded partially. Now Lets find out which columns are loaded into memory for this table.
Way 1:select * from M_CS_COLUMNS where TABLE_NAME=’BSEG’ and SCHEMA_NAME=’SAPERP’
This shows the status of each column and its status in memory. LOADED = TRUE means that this column is inside memory
Way 2:Using Studio
Studio->Find table-BSEG-Columns Tab
3.Lets fully UNLOAD this table from memory
Now the table M_CS_TABLES and M_CS_COLUMNS would have got updated .
Studio ->Catalog->Find table – BSEG
Memory consumption in memory has become empty and loaded state has turned to NO
4.Lets fully check if our UNLOAD reason EXPLICIT has got recorded in M_CS_UNLOADS
Now we see that the unload reason for this table which we triggered manually has got recoded!1
5.Lets LOAD this table partially
When I say partially we can either load only 1 partition for this table out of 7 in our landscape or 11 column alone.
a.Only 1 column alone from this table
Now M_CS_COLUMNS table status have got updated as below.
1.Now lets load the table fully into memory
LOAD “SAPERP”.”BSEG” ALL;
Hope you understood how to manually load and unload tables in HANA DB!
Please leave a comment and suggestion and like if you find this material useful!