We are transacting with a partner via AS2 with XML payloads. Our TN database is growing at an alarming rate and we want to periodically purge XML documents from TN. Can we simply delete the records we want from BIZDOCCONTENT or are there other tables that we would need to look at.
Is there a best practice for purging from TN.
You have an option of archiving of database contents through MWS user interface.
Thx,
Tanveer
We do have the option to archive, but we want to purge the transactions. Disk space is our concern and archiving will only move the data from one table to another and not address the space issue.
Hi There,
What I suggest is write StoredProcedure’s, schedule to run for every 1 month which archives more than 3 months old data to other DB and if you think data is no more useful then you can delete it
Thanks,
The question that I have is will it be sufficient to delete rows from BIZDOCCONTENT only or would we need to remove associated rows from other tables, ie BIZDOC, etc, to maintain data integrity.
Need to write a script that will purge data from the below tables based on docid.
activitylog
bizdocuniquekeys
bizdocrelationship
bizdocrelationship
bizdocattribute
bizdocarrayattribute
bizdoccontent
bizdoc
It is a best practice to first archive the data and then apply the script to purge from archive tables.
There is a Built-In-Service in the WmTN-Package exactly for this purpose.
It can distinguish between Archive (moving data to the archive tables in the same schema) or delete (just delete without archiving) by setting the appropriate input parameter.
There is a second input parameter how many days of data will be retained after the run of the server.
This makes sure, that the database is still in a stable state after the run as this also takes care of foreign keys between the tables etc.
Write a Wrapper for this service which then can be scheduled for repeating execution.
Set the input parameters for the archiving service in this wrapper service as services with input parameters can not be scheduled.
Starting with 9.x it is possible to set the input parameters in the IS-Admin directly (static values), so there is no need for a wrapper service any longer (excpet you want to be able to set the values for the parameters in a more flexible way via a properties file or similar).
Regards,
Holger
You will need to edit the stored procedure TN65_ARCHIVE_PROC in Trading Networks schema.
Comment of the insert statements and it should directly purge from main tables for you.
Thanks,
Saurabh
Pls note …
Delete statements will still leave holes opened and db files might not shrink as expected.
Unfortunately SAG has not designed the Transactions tables as DB Partitions …so we can drop the old partitions.
What i used to follow in one of the customer is…
let us say DB has 2 schema A and B with each 1TB size
I would point TN IS to A for 2 months and then take an outage to Point to B … so you can use 2 monitors/mws for transaction checking etc…
Later once you find B getting filled, Truncate all objects in A or (drop and create objects) and take an outage to point IS back to A
So, to conclude following actions on wM txn tables
Delete - Temp. fix
truncate releases space - better
drop and create is - best
Running it with different schema’s can cause issues especially trying to move the data between the schema’s to make sure data is not lost.
Instead would ask SAG to try keeping date timestamp column in all tables so that partition on them can become easy.