Description:
bpm-db-cleanup scripts for a version are generated using the application template from the Administrator staging directory. The Administrator staging directory is configured under the element <admin.repo> in build.xml. If the application template is deleted, it is deleted from the staging directory and bpm-db-cleanup scripts cannot be generated. 

If the application template is not available in the staging area but the original DAA is still available, then the contents of the original DAA can be extracted to a temporary location and referenced in the BPM clean-up script ant task in the same way as the staging folder.

The DAA must be for the exact same name and version as the application that is being cleaned. Using a later or earlier version might mean that some changes to the process flow will not be picked up, resulting in rows being left behind in the database. This causes a new deployment of the same version DAA to possibly fail.

This solution provides a workaround to cleanup the BPM database when neither the application template nor the DAA are available. This solution should not be used as a standard practice.

This solution uses only the application version to detect the database rows. This solution is not applicable when there are multiple applications using the same version.

Prior to deleting any rows in the database, it is highly recommended to,

- Shutdown the AMX BPM system (tibcohosts/BPMNodes/SystemNodes)
- Take a system backup

Refer to TIBCO ActiveMatrix BPM Troubleshooting Guide > BPM Application Deployment Undeployment Issues > The bpm-db-cleanup Utility > Running the BPM Clean-up QueryAMXBPMTablesForCleanup.sql script for more details.

The following are steps to generate the database cleanup scripts when the bpm-db-cleanup utility cannot be used. 

1). Create the stored procedure - query_bpm_tables_for_cleanup .

- The stored procedure is available at TIBCO_HOME/bpm/3.x/SupportTools/bpm-db-cleanup/<dbType>/QueryAMXBPMTablesForCleanup.sql .

- Install the stored procedure by running the "CREATE OR REPLACE" command.

This SQL file also has a sample execute command at the end.

2). Execute the stored procedure with the first parameter set to true.

Example (for Microsoft SQL Server) -

===run QueryAMXBPMTablesForCleanUp.sql =============
Use bpm31
EXECUTE amxbpm.query_bpm_tables_for_cleanup 1, 1, 0, 0, '20151207152355010'
GO

3). Validate the output from Step 2 corresponding only to a single application. Proceed if this validation is successful.

4). Execute the stored procedure again with 1st parameter set to false.

Example for Microsoft SQL Server.

===run QueryAMXBPMTablesForCleanUp.sql =============
Use bpm31
EXECUTE amxbpm.query_bpm_tables_for_cleanup 0, 1, 0, 0, '20151207152355010'
GO

===result===
EXECUTE amxbpm.pvm_drop_rows '/WelcomeUsersImplementSolution/Process Packages/ProcessPackage.xpdl','11.10.10.20151207152355010', 1000;

5). Run the bpm-db-cleanup utility command "generate-skeleton-sql" from TIBCO_HOME/bin/3.x/scripts/SupportTools/bpm-db-cleanup by providing appropriate values in build.xml. This will generate the SQL script delete_<dbType>_skeleton.sql .

6). Paste the result from Step 4 into the file generated in Step 5. 

Example for Microsoft SQL Server.

======= delete_mssql_skeleton.sql

.................

..............
--
-- Insert generated SQL here
--
EXECUTE amxbpm.pvm_drop_rows '/WelcomeUsersImplementSolution/Process Packages/ProcessPackage.xpdl','11.10.10.20151207152355010', 1000;

DROP PROCEDURE amxbpm.pvm_drop_rows
GO

DROP PROCEDURE amxbpm.bpm_delete_work_items
GO

How to generate the bpm-db-cleanup scripts when the application template does not exist in the software repository.

v3.1 documentation for QueryAMXBPMTablesForCleanup - https://docs.tibco.com/pub/amx-bpm/3.1.0/doc/html/bpmhelp/GUID-A211037C-EC7A-4571-9A12-627E81AF7BF6.html
v3.1 documenation for bpm-db-cleanup utility - https://docs.tibco.com/pub/amx-bpm/3.1.0/doc/html/bpmhelp/GUID-6C1966FF-78B7-4813-94C9-110B3B4C918B.html