SQL for listing top tables in a particular node

[Former Member]

Hi Team,

By mistake I closed the earlier thread, my apologies about that.

Is there a way to list the top tables occupying the highest disk space on a particular node of a multi node system. Suppose for example in the five node system we have to list top tables on the master node and the slave nodes etc....

Which view or table data should I be considering?

[Former Member]

You can use SQL: "HANA_Tables_ColumnStore_DiskSize" (brand-new in SAP Note 1969700) for that purpose. Just adjust HOST in the modification section of the statement based on your needs.

[Former Member]

Hi Martin,

Thanks a ton for the information, there should be one for row store as well. Even though row store resides on the master node.

[Former Member]

I have included row store and renamed the command to SQL: "HANA_Tables_DiskSize" - can you download it and check if it works fine for you?

[Former Member]

Hi martin,

Thanks so much for the help on this, I will test the sql query for the row store as well.

[Former Member]

Martin,

Guten Tag,

Thanks for the sql, I am testing it and seeing if it fits all of my requirements. Thanks again for the quick help.

[Former Member]

Hi p517710

The query "HANA_Tables_LargestTables.txt", attached to SAP Note 1969700 (SQL statement collection for SAP HANA) will give you these details.

Ruth

[Former Member]

Hi Kelly,

I did try that query long back, but you know it will say various nodes.

I wanted node specific information. I was checking if there is some information regarding the same.

[Former Member]

The query will specify the host which each table is on, you can also use the modification section to order by the host or alternatively modify the query to return a particular host only.  "Various" is only returned if the table is partitioned over multiple nodes.

[Former Member]

Hi Kelly,

Thanks a ton for the response.

SELECT                                       /* Modification section */

        '%' SCHEMA_NAME,

        '%' TABLE_NAME,

        '%' STORE,                             /* ROW, COLUMN, % */

        ' ' ONLY_BASIS_TABLES,

        50 RESULT_ROWS,

        'TOTAL_DISK' ORDER_BY                    /* TOTAL_DISK, CURRENT_MEM, MAX_MEM, TABLE_MEM, INDEX_MEM */

I dont see an option to put in the order by right for the host.