Running the BPM Clean-up QueryAMXBPMTablesForCleanup.sql Script

The BPM clean-up database query SQL script (QueryAMXBPMTablesForCleanup.sql) is used to find out what rows exist within an AMX BPM database for a particular version. It queries the database using the supplied version information and outputs data about what rows it has found across the PVM, BRM, DE and WP tables.

It queries the database based on the information the BPM clean-up scripts require from a deployed DAA in the staging area when generating SQL to remove a deployed application from the database.

This SQL is to be used when the following criteria are met during an attempted clean-up of an AMX BPM system using nodeutil and the BPM clean-up scripts:

  • There is no folder for the application and version under the AMX BPM staging area. This folder is used by the BPM clean-up scripts to generate the required SQL.
  • The original DAA for the application version is no longer available
    Note: Note that:
    • If the staging folder is not available, 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 won't be picked up, resulting in rows being left behind in the database, causing a new deployment of the same version DAA to possibly fail.

If both the criteria are met then the SQL stored procedure can be used to query the database using a specific major, minor, micro and qualifier version. The stored procedure outputs either informational data about rows it has found for the version or actual delete and drop SQL commands that can then be used within an existing BPM clean-up generated SQL script.

Warning: Because the stored procedure uses a version only, it is possible that it could pick up rows in the database for multiple applications, all of which have the same exact version. This is unlikely since it requires two different application DAAs to be generated within the exact same minute thus receiving the same time stamp.

When the stored procedure is first run, it outputs data about the applications that are deployed. The output should be examined to see what applications it has picked up. For example, the brm_deployed_component table information shows the application names that are deployed to BRM for the specific version: 

brm_deployed_component: WM__uuYUAHYJEeKVjPluDjvLhg com.tibco.amxbpmperf 2.0.0.201302201159

Also check the pvm_module and wp_managed_archive tables to see that there are no other applications for the given version, and that all rows are applicable to the DAA and version that was originally deployed.

Other information, such as brm_work_types, gives further indication about what applications have been picked up by the stored procedure. Each row found for brm_work_types represents a user task in the process flow. Again, checking these against the expected user tasks in the original deployed DAA helps to be sure no other applications have been picked up by the version.

Running the stored procedure with FALSE as the first parameter will perform the same query on the database but it will output SQL that can be put into an existing BPM clean-up generated SQL script, replacing any matching lines and removing extra ones, leaving just the stored procedures.

Next topic: Deploying a BPM Application Fails If the Referenced Organization Model Has Been Upgraded
Previous topic: Attribute Alias Cleanup