In a custom code, I have used wm.tn.doc:deleteDocuments to delete ST transactions from TN.
Sometimes, wm.tn.doc:deleteDocuments errors out with following error,

com.wm.app.tn.err.EXMLException:
wm.tn.doc:deleteDocuments

com.wm.app.tn.db.DatastoreException
Couldnt delete documents (0) java.sql.SQLException: [wm-cjdbc31-0001][Oracle JDBC Driver][Oracle]ORA-02292: integrity constraint (FK_ACTLOG_RELATEDDOCID_BIZDOC) violated - child record found

Any way to resolve this issue.

BSR

I wouldn’t recommend writing custom code to delete data from TN DB… use the archive facility instead. A document in its entirety spans over several DB tables which have integrity constraints (for obvious reasons).

~Rohit

I am using wm.tn.doc:deleteDocuments service to delete documents from TN. cannot use archive because I want to delete a particular document only.

Caution webMethods doesn’t recommend deleting the data from SQL tables directly but in several instances they provided sql queries to delete when customers ran into problems with

tn.archive.archiveAfterDays

tn.archive.deleteAfterDays

Below are the queries that we need to execute one after the other to remove the data from TN Tables to clear up the data.

We have to shut down webMethods to execute these commands.

DELETE FROM DeliveryJob WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM ActivityLog WHERE RelatedDocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM ActivityLog WHERE EntryTimestamp < ?;

DELETE FROM BizDocUniqueKeys WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM EDITracking WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM BizDocRelationship WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM BizDocRelationship WHERE RelatedDocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM BizDocAttribute WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM BizDocArrayAttribute WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM BizDocContent WHERE DocID IN (SELECT DocID FROM BizDoc WHERE DocTimestamp < ?);

DELETE FROM BizDoc WHERE DocTimestamp < ?;

After we successfully execute we can include the following parameters in

webMethods Admin → Solutions → Settings → TN Properties → Edit TN Properties Settings

tn.archive.archiveAfterDays=30

tn.archive.deleteAfterDays=90

Based on the error message, you’re violating the integrity constraint FK_ACTLOG_RELATEDDOCID_BIZDOC, which relates the ActivityLog table to the Bizdoc table. In other words, you can’t delete documents that have activity logs associated with them. You’d have to delete the activity logs for the document first.

• Percio