Can we use the attribute TOP in AQL

I would like to shared you some important information about the amWfInstance.

This entry covers four topics:

Section One: Why do we need to do Workflow (WF) Maintenance?
Section Two: An explanation of the Delete completed workflow instances Workflow
Section Three: Is this enough or do we need to do more?
Section Four: How can we delete 1,000,000 completed WF Instances?
Response
Section One: Why do we need to do Workflow Maintenance?

Why do we need to do Workflow (WF) Maintenance? Isn't this something that AssetCenter takes care of that internally? Shouldn't it?

AssetCenter does not generally delete anything unless it is told to and, generally, this works just fine. Unfortunately, for Completed Workflows there is a problem. When a Workflow executes, it creates records in the amWfInstance, amWfOccurEvent, and amWfWorkItem tables. As these tables get large (and unless something is done about it they do get large) performance can degrade significantly. Usually, once a WF has completed it can be deleted, but the requirements vary significantly from WF to WF and from Site to Site based on different business rules. However, without some form of regular maintenance on these tables, they can quickly grow to 1,000,000 records or more.

Section Two: An explanation of the "Delete completed workflow instances" Workflow
The Demo Database that ships with AssetCenter 3.02, 3.5 and above includes a Workflow called "Delete completed workflow instances" that deletes Workflow Instances after they have run to completion. However, since some WF Instances (especially Approval and other financial WFs) need to be kept for a period of time after completion, the Demo Database also has a Cleaning Delay Feature on the amWfScheme table. This Feature is a duration that tells AssetCenter how long you want to keep Instances of that particular WF Scheme in the database before cleaning them out. Only records from amWfInstance are explicitly deleted by the Clean W/F instances Script that the Workflow runs. However, the call to amDeleteRecord() also deletes the amWfOccurEvent and amWfWorkItem records that are tied to the WF Instance being deleted.
This Workflow successfully keeps a database clean, but will fail if it has to do too much at once. Section Four addresses this problem.

Section Three: Is this enough or do we need to do more?
Over time, for one reason or another, bad records can appear within an AssetCenter table. Normally these can be found fairly easily from the AssetCenter Client after they have been identified by the AssetCenter Database Administrator. However, if there are too many and if they are on a large table, days or weeks can be required to delete them by this method. If there is one Golden Rule in AssetCenter Database Administration it is, "Never Touch the Database on the Back End," because the AssetCenter Data Integrity Model is software enforced, not database enforced. This document violates this Rule by running SQL directly against the database.
WARNING: Never run SQL code directly against your backend database unless the code has been reviewed and approved by Hewlett-Packard's AssetCenter Development Group. Erroneous code can be EXTREMELY dangerous to the health of your AssetCenter Database.

Note: The instructions below require access to the AssetCenter Database Administrator for version 3.6 build 1762 or above. Contact Peregrine Customer Support if you need a copy.
1. Backup the database.
2. Connect to your database using the AssetCenter Database Administrator for version 3.6 build 1762 or above. Even if you are running an earlier version of AssetCenter, you MUST use the AssetCenter 3.6 (or later) Database Administrator. It connects to and works properly with older versions of AssetCenter. Earlier versions do not support the required functionality. Contact Hewlett-Packard Software Customer Support if you need a copy.
3. From the Actions menu, select Diagnostics / Repair Database. Ideally, you first run the Analyze on you entire database. However, this can take a long time on a large database. For now you at least need to run the Repair on the amWfInstance, amWfOccurEvent, and amWfWorkItem tables after selecting Searching for broken normal links and Searching for broken typed links.
4. Contact your site Database Administrator (DBA) to run the following SQL statements:
--Delete all WF Instances orphaned from a WF Scheme.
DELETE FROM amWfInstance WHERE lWfSchId = 0 AND lWfInstanceId <> 0
--Delete all WF Instances orphaned from a WF context record.
DELETE FROM amWfInstance WHERE lDocRecordId = 0 AND lWfInstanceId <> 0
--Delete All WF OccurEvents Orphaned from a WF Instance
DELETE FROM amWfOccurEvent WHERE lWfInstanceId = 0 AND lOccurEvtId <> 0
--Delete All WF WorkItems Orphaned from a WF Instance
DELETE FROM amWfWorkItem WHERE lWfInstanceId = 0 AND lWorkItemId <> 0
Hewlett-Packard recommends scheduling this processing during routine monthly database maintenance. Typically, few if any bad records will be identified by the AssetCenter Database Administrator Repair function. If the Repair function repeatedly finds new bad records, contact Hewlett-Packard Software Customer Support for assistance in discovering why.
Section Four: How can we delete 1,000,000 completed WF Instances?
This can be a genuine issue. The Delete WF Workflow discussed in Section Two above works well for maintenance. However, on an existing system with a large accumulation of WF Instances, you should do manual cleanup first. Manual cleanup may also be needed if many WF Instances process in a short time period. For example: you may have a Workflow that triggers based on an update to amAsset:Field1. If you do an Import that updates this field in 250,000 assets, you will generate 250,000 WF Instances at roughly the same time. If their cleaning delay is set to three days, then three days later your AssetCenter Server will either lock up or give a Maximum Number of Instructions Reached error while it tries to delete all of them.
Contact your site Database Administrator (DBA) to run either of the following sets of SQL statements.
SQL to delete ALL completed WF Instances from the database
The following three SQL commands can be issued to an Oracle database to delete all orphaned Workflow Instances in the database. Before running these SQL commands, use the AssetCenter Database Administrator (3.6 or later) to execute a Diagnostics and Repair. This will identify and remove broken links.
--These three can be used to DELETE completed WF Instances if too many have built up to deal with internally.
DELETE FROM amWfOccurEvent WHERE lOccurEvtId <> 0 AND
EXISTS(SELECT lWfInstanceId FROM amWfInstance WHERE seStatus = 1 AND
lWfInstanceId = amWfOccurEvent.lWfInstanceId)
DELETE FROM amWfWorkItem WHERE lWorkItemId <> 0 AND
EXISTS (SELECT lWfInstanceId FROM amWfInstance WHERE seStatus = 1 AND
lWfInstanceId = amWfWorkItem.lWfInstanceId)
DELETE FROM amWfInstance WHERE lWfInstanceId <> 0 AND seStatus = 1
The above code does not take into account the Cleaning Delay feature and would be much slower if it did.
SQL to delete WF Schemes while retaining specific instances
If there are specific WF Schemes you do not want to delete, you can do so but it will take some additional effort. Instead of the three statements above, run the following:
DELETE FROM amWfInstance WHERE lWfInstanceId <> 0 AND seStatus = 1 AND
lWfSchId NOT IN(123, 456, 789)
Replace (123, 456, 789 with a list of lWfSchId values that you do not want deleted. When this code runs, it leaves many broken links on the amWfOccurEvent and amWfWorkItem tables. Follow the steps in Section 3 to clean those up.

About the question of “Can we use the attribute TOP in AQL” doesn’t exist official documentation that confirm or deny that, in my opinion the answer is yes but I haven’t documentation that confirm or not this.