Hi,
if I understand you correctly, you want to delete a huge amount of data but you would run in a long transaction
while doing the delete ?
There are a number of options, which would allow you to delete the data.
However it depends if you only want to delete parts of the data or purge the complete table.
-> doing the deletion in non-logging mode, you need to cut the replication (set primary server in standard mode),
set the table or the database to non-logging mode and then delete.
Afterwards take a full backup and reinit the secondary server by restoring the backup (only physically, ontape -p).
Then reestablish the HDR (onmode -d primary <sec_server>, onmode -d secondary <pri_server>).
That way, the deletion will take a very small time (in non-logging mode, everything like that is faster), but this will be
interrupting the HDR and maybe make the application unusable while the deletion is executed because
it might rely on transactions.
-> doing the deletion in logging mode will transfer the deletions to the secondary server. You need to do this
in a transaction. You either do it in steps small enough not to fill your logical logs, which would maybe result in a long transaction
or create enough logical logs which are covering the deletions in one run.
In order not to produce a high number of locks during deletion, lock the table in exclusive mode before the delete
statement is executed (in same transaction). (otherwise one lock per row is created, assuming you have lock mode set to row,
which could result in a lock table overload, depending on the memory limits of your machine).
-> In case you want to delete all data, maybe truncate table is the better choice. (or drop and re-create the table)
-> In case you want to preserve only a small number of records, another approach can be to rename the table,
create a new one with the same table layout (and foreign keys, if present) and copy the data to be preserved
using insert into ... select from. Then drop the original table at the end.
Best,
Marcus Haarmann
Original Message:
Sent: 3/26/2023 10:36:00 PM
From: nasiha zailan
Subject: Drop table with no-logging mode
Hi, i have situation where i try to drop table contains of time-series data in production environment with HDR mode. The data is about 200GB. Is there anyone who knows how to do this?
I try to delete table with no-logging since my logical logs are insufficient to carry the transaction causing the server to hang.
------------------------------
nasiha zailan
------------------------------