Mandatory step post moving any Row store table between nodes in HANA DB
OWNERSHIP CONCEPT Vs ROW STORE TABLE MOVE:
In this blog i wish to discuss one of the ownership related issues involved when moving a row store table between nodes in a scale out setup.
As a general recommendation and for better performance in HANA, row store tables should not be sitting on a worker node and Master node is preferred . To correct this issue from TPO, we moved row store tables from worker nodes to master node.
To determine if any row store table is sitting in worker node:
select * from M_RS_TABLES where HOST='<worker node>’;
Command to move the row store table to master node :(same as any other table movement between the nodes)
ALTER TABLE “VYSSSS”.”D_COPQXC_TEMP” MOVE TO ‘Masterhostname:3nn03’ PHYSICAL;
When we accomplished this task we saw that the user id that we used to move a system rowstore table to master node took over the ownership of the moved table .ie,
When a user 1 creates a row(T1) / column store table (T2) on schema XYZ , then the owner of the table XYZ.T1 and XYZ.T2 is user1.
When a user2 moves the row table T1 on schema XYZ to a different node, the actual owner loses his owner ship of this table and the user 2 becomes the owner of this table.
When a user2 moves the column store table T1 on schema XYZ to a different node, the actual owner does not lose his owner ship of this table and the user 1 continues to be the the owner of this table.
This mainly happened because ROW store table is always going to stay inside memory and when we execute a move command , they are basically getting recreated in the target node, and thus leading to ownership change. Any deletion of the personnel user id that we used to move these row store system tables in SQL console mode via cascade , will drop all the system related row store table without even alerting us, putting our system in high risk.
When we checked with SAP , the only way to change the ownership of these tables back is to recreate these tables via reimport or movement between nodes as we were in a version which did not have the facility to change the ownership directly.
Since HANA 2.0, SP02 , we have a SQL to change the ownership of tables from one user to another. Hence when ever you move a row store table exercise caution and ensure that post move the ownership have been put back in case of changes.
alter table XVY.T1 OWNER TO USER1
Thanks for reading!
Follow for more such posts!https://people.sap.com/rajarajeswari_kaliyaperumal
Like and leave a comment or suggestion if any!