Trigger Manual Delta Merge on all tables

[Former Member]

Hello,

I am looking for a way to trigger a manual delta merge for all tables in a system or schema. Does such a statement exist?

So far the statements I have found are used to update one specific table: merge delta of 'TABLE';

The reason I ask is SAP note 2105764 which deals with a possible data corruption after upgrading to HANA SPS9 and recommends to clear out the delta store before upgrading.

Sander

[Former Member · Accepted solution]

Hi Sander Bleijenbergh,

  Currently there is no dircet option to merge  all tables at a schema Level.

I hvae writtent a small procedure to do this this will work.

In my example Below the DINESH"."MM_TABLE has the list of tables that has to be merged,replace that table with your own table and run this Procedure.

Note: Merge is a costly operation, please be carefull and keep your system admin informed about your action.

create procedure DB_OPERATIONS2
as
begin
declare v_count        integer;
declare v_index     integer;
declare v_table  char(40);
declare clm_name varchar (50);
declare vc_sql_merge varchar(4096);
declare vc_sql_part varchar(4096);
declare vc_sql_load varchar(4096);
declare vc_merge_part varchar(4096);
--MM_TABLE is the table which has all the tables  belongs to MM module

DECLARE CURSOR c_cursor1 for select table_name from "DINESH"."MM_TABLE";

--Opening the cursor to acess each table
for cur_row as c_cursor1 DO
v_table := cur_row.TABLE_NAME;

vc_merge_part := ('alter table ' ||:v_table || ' merge PARTITIONS');
exec(:vc_merge_part);

--loading the table from memory

end for;
end;

[Former Member · Accepted solution]

Hi Dinesh,

Thx for your reply. Do I understand correctly that I would need to populate a table with the names of all the tables I want to merge?

I used the statement below to generate a list of commands which I could modify to insert into a table:

SELECT 'MERGE DELTA OF ' || SCHEMA_NAME || '.' || TABLE_NAME || ' WITH PARAMETERS (''FORCED_MERGE'' = ''ON'');'

from M_CS_TABLES

ORDER BY MEMORY_SIZE_IN_DELTA DESC;

Does the command "alter table merge partitions" do the same thing as "merge delta of" or is that just an example of a command you can run?

Sander

[Former Member · Accepted solution]

Hi Sander,

I have miss took your question for partition merge,you are looking out for Delta merge right. Ok here goes the answer:

What you do is:

1)create a schema DINESH

2) Create Table MM_TABLE with one column, named as  "table_name "

3) populate the Table MM_TABLE with the list of tables which has to be merged"

4) Then copy paste the below procedure:

create procedure DB_OPERATIONS as

begin

declare v_count        integer;

declare v_index    integer;

declare v_table  char(40);

declare vc_sql_merge varchar(4096);

--MM_TABLE is the table which has all the tables  that has to be merged

DECLARE CURSOR c_cursor1 for select table_name from "DINESH"."MM_TABLE";

--Opening the cursor to acess each table

for cur_row as c_cursor1 DO

v_table := cur_row.TABLE_NAME;

--Merging the Table

vc_sql_merge :=  ( 'MERGE DELTA  OF ' || :v_table || ' WITH PARAMETERS ' || '(''FORCED_MERGE'' = ''ON'')') ;

exec (:vc_sql_merge);

end for;

end;

5) Execute the Procedure

hope this helps... and answers your question.

Thanks & Regards

  A.Dinesh

[Former Member · Accepted solution]

Definitely helpful since it saves the step of having to export results to a text file and then copy-pasting the commands into the sql editor.

[Former Member · Accepted solution]

As a process can you mark it answered