Hello,

We have two production servers running as the WM Cluster. And we scheduled the wm.tn.archive:archive to do the archive the record older than 14 days at the 03:03:00 every Sunday. But when I checked the tables these days, there is over 2,200,000 records in the bizdoc table. And the oldest records in this table are with the LASTMODIFIED 2006-08-08. I started this archive service manually with the archiveAfterDays=200 last night. But when I checked this morning, there are only about 20,000 records moved to the archive_bizdoc table. It seems very slowly when did the archive with this service. Is there any one has the same experience? Thanks a lot.

We are having similar issues when running wm.tn.archive:extendedArchive. We are also clustered and are on version 6.5. Our DBAs have indicated our db is healthy, thus we’re getting maximum performance. For instance, in a 4 hour window (during off-hours) of manually running the above service, we are able to delete only the following from the tables listed:

BizDoc 63,766 records
BizDocRelationship 63,585 records
BizDocContent 126,014 records
BizDocAttribute 1,820,000 records
ActivityLog 2,132 records

Considering our bizDoc table is quite large like yours, I’m concerned we cannot delete enough to keep up with our current daily production intake. Like you, we cannot schedule these services until our tables are to a more manageable size.

Has anyone else had and resolved the slowness experienced with archive/extendedArchive ?

One thought is to not archive. Set the configuration to simply delete the old data.

Actually, we are deleting by using wm.tn.archive:extendedArchive, and setting the operation = delete on this service. We are setting a number of days, and in some cases a sender/receiver id in order to control what is being deleted. Is there another method for deleting from wM tables? Or any suggestions on how to make our current method more efficient?

This is indeed the recommended way to delete data from the TN tables.

The SQL for all TN operations are available in a file named dbops.sql. I don’t recall the directory it is in. Someone can review this file to see how the delete is doing its job. With this information, create an index or two may be helpful. I know creating an index for one of the date fields of the bizdoc table helped tremendously for normal operation. Perhaps the right index or two will help the delete process.

It’s much more efficient to archive/delete directly within the database. There are Oracle stored procedures available in wM Advantage to perform this job.

regards, Alex

Thank you all for your advice. We have rebuilt the Index of these tables, and it seems much faster when access these tables now. Thanks a lot.

Depending on TN version you are using youc an request your WM TS/PS to provide you with alternative SQL Archive scripts that you can run outside of WM on your Oracle DB. The problem isyou have too many records to clean and TN may just be working but tae extremely long time and using more resources than reasonable. We had to set archiving outside TN with external script.

what type of index did you use to speed up the delete sql?

No customized index, we just rebuilt the default indexes for the bizdoc,bizdoccontent,bizdocattributes, etc. as the index in Oracle become inefficient when there are much delect in a table. But the webMethods built-in archive service can’t work well on mass transactions, so currently we are running a archive script with a schedule job in database, it is much faster. In this script, it will archive/delete the tables in the connotative sequence from the table foreign keys.

Thanks for updating back this old thread!

Am I reading this correctly? The solution was to run a custom purging solution directly on the database and not use the canned archive and delete service in TN?

I recently created a wrapper service for the built in archive that simply takes an input from a service call to remove documents older than x number of days (180 by default if no value is used) and by doctype (%% if none specified). We have a lot of data to remove to say the least, and I am doing a gradual step down to our desired maximum retention period. Our most recent query took a little over 24 hours to complete, which puts us in contention with the daily schedule. Any suggestions on index updates that could reduce the time to operate?

Yes also have Indexing on the wM TN related tables and you should see some difference in the archive run time:

HTH,
RMG

Rebuilt the indexe or create some new maybe help a little, but not much. The slow step it to archive or delete the LOB records in the BIZDOCCONTENT or PRT tables with LOB records. If it already affect your production process much, you can try to manually delete the old records with the LOB records in your server free time, and then run the webMethods built-in archive services. And you need to disable the Foreign key constraints on these tables. And for a longterm solution, you can try to use the partition tables on the tables with LOB fields, and then TRUNCATE the old partition before run the built-in archive services. Hope it will help.

At one client they had 4 years of transactions, so the built in archiving was not feasible, so we wrote stored procedures to archive the data (start with oldest data and do a little at a time, slowly increasing batch sizes based on dates) until it was down to a year or so, then turned on the built in archiving.

If you have lots of data you could try archiving a little at a time using built-in archiving, I can’t recall if this would work (do one week, then 2 weeks, increase in bigger chunks by saying ‘anything older than X’ based on the earliest timestamps in TN).We tested this on our test environment first of course

We did the same thing as wkriski mentioned above and I know this eats time but that’s the better steps/option:

HTH,
RMG