Data Ageing - What to do, if Time Selection Partitioning goes wrong

[Former Member]

dear all

after creating a wrong partitioning spec in Data Ageing - Tx DAGPTC.

i found it impossible to immediately revert back to the state i had, prior to the mistake.

this is a test system, so no real damage done, however, in my attempts to find an answer/procedure, i found nothing, so if anyone can point me in a direct, i would be most grateful.

please dont just reference the administration guide.

what i am looking for, would be something like the syntax to merge a time partition, or undo a command, or something.

note: this problem has nothing to do with data aging runs, nor the undo functionality, as it is prior to any runs at all.

any help please

thanks

[former_member246069]

Hello,

Could you please help me out,

I realized that I moved some data to the historical partition because of too short residence time. Is there any way to undo my changes?

How remove the partition?

[BrigitteReinelt]

Hi Margaret,

there's no way via SAP Gui. However, with SAP HANA 2.0, there's an easier way with respect to the statements that you can use, as the functionality to merge data aging partitions (including down to 0 partitions) is included there.

Please refer to help.sap.com (link below). If the link does not work out as expected, search on the help portal for "HANA Platform" and in there for "partitioning". Choose the "SAP HANA Administration Guide for SAP HANA Platform". Chapter "Change a Partitioned Table into a Non-Partitioned Table", esp. sub-chapter "Time Selection Partitioning (Aging)" is what you are looking for.

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.04/en-US/4810afe854254d46b947ef9cd4...

Hope this helps!

Warm regards,

Biggi.

[marge]

Thanks for the inputs Biggi.

[BrigitteReinelt]

Dear Glen,

there is only the possibility to use HANA Studio to overcome this situation. You need to be extra careful here in order not to risk data loss! Asuming, your tables do only contain a single-level partitioning (i.e. no extra hash or range partitioning on top of the aging partitioning) choose one of the following options:

A. if the partitions you want to get rid of do not yet contain data (i.e. no aged data in the cold partitions), you can drop single partitions with ALTER TABLE MY_TABLE DROP PARTITION lower_value <= VALUES < upper_value; lower_value and upper_value being the borders of the partition to drop. Make sure not to create gaps in the partitioning by this.  

B. if you want to undo the complete partitioning, you need to involve a third table (helper table) in order to shift the data around. Use the following procedure:

     1. create table "helper_schema"."my_table" like "SAPSID_schema"."my_table" with data without partition without index;

     --> make sure this really worked, note that the indexes get lost here. Note the extra schema we use here, mainly for safety reasons in order not to mess up with table names.

     2. drop "SAPSID_schema"."my_table";

     --> the original table and data gets lost

     3. create table "SAPSID_schema"."my_table" like "helper_schema"."my_table" with data without partition without index;

     --> in this step you shift the data from your helper table back to a clean version of the original table.

     4. recreate any indices as defined in SE11 (back to ABAP system now) valid for your database. Usually this is done in SE14, the database utility.

Warm regards,

Biggi.

[Former Member]

hi biggi

thanks for your answer

in either case, we can see there is no easy "undo" ability, therefore, performing this task, one must realize that there is no easy way back.

g

[marge]

Hi Biggi,

is the approach and method to "undo" data aging you have mentioned still applied or is there an easier way/method via SAP GUI instead of going into the database/table to do it?

If you could also enlighten how can I undo the data partition done via DAGPTM if I have not run data aging via DAGRUN.

Thanks and regards,

Margaret