SQLMD: Cross Node Joins Analysis

[Juwin]

When executing SQLMD for Cross Node Join analysis, the program needs an input file containing Tables and Nodes. 

How to generate this file using the data from HANA DB? Wherever I check in System tables/views, this field with Node information is marked as Internal Use only and always return empty result.

So, how to generate the file with Table & Node information required to execute SQLMD?

Thanks

[dirkhoffmann]

SQLM cross node joins refers to host as node. Node in M_CS_COLUMNS refers to the internal numa node architecture.

You need to check column host in M_CS_TABLES, then you can build a txt file like this:

Table Name Node
%DEFAULT% 1
tableA 2
tableB 2
...
USR21 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

In this example, USR21 is located on all hosts via OSTR.

[Juwin]

Found the answer here:

M_CS_COLUMNS has details of tables, columns and partitions and the following example may be helpful to check the NUMA locations where tables are distributed across NUMA nodes. In this case CPU load is distributed across NUMA nodes by NUMA-local processing, but memory consumption is also better distributed to the memory attached to each NUMA node.

select table_name, column_name, part_id, string_agg(numa_node_index, ',') from m_cs_columns 
group by table_name, column_name, part_id 
order by table_name, column_name, part_id;

[dirkhoffmann]

You misinterpret the term "node". Node for SQLM Cross Node Joins referts to hosts in a scale-out system. It has nothing to do with the internal numa setup of a host. So you need to have a simple text file where you list the table followed by tab or space and then host number.

[dirkhoffmann]

This is an example. USR21 would then be placed in table goup OSTR and is available on all nodes.

Table Name Node
%DEFAULT% 1
TABLE_A 2
TABLE_B 2
TABLE_C 2
USR21 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

[Juwin]

@dirkhoffmann Host and Nodes are different columns in M_NUMA_NODES.

SQLMD asks for Nodes information. Whereas, your comment says the file should actually contain Host number information.

Could you please confirm once more if the file should contain Node or Host information?

My original question was, which System DB View from HANA, contains the information, that must be used to build the text file required to feed into SQLMD?

[dirkhoffmann]

SQLM Cross Node Joins refers to nodes as host. M_NUMA_NODES refers to numa nodes which is the internal architecture of the server. So you must not mix this up. The "node" (aka host) can be queried from M_CS_TABLES.