When working with JCMA, it’s not uncommon to encounter situations where a process or transaction gets stuck, preventing further actions. One such scenario involves a stuck JCMA plan. In this blog post, we will guide you through the steps to identify and delete a stuck JCMA plan using SQL queries.

Identifying the Stuck JCMA Plan

Before we proceed with the deletion, we need to identify the specific JCMA plan that is causing the issue. To do this, we can run SQL queries to check the plans associated with the problematic JCMA. Replace the placeholder 403dbcb6-612f-49b7-97d2-9fa39ce9a23d with the actual ID of the plan you want to delete. You can find this ID in the URL when viewing the plan details in your database management system.

Query to Check the Plan

SELECT *
FROM "AO_6FF49D_PLAN_ENTITY"
WHERE "ID" = '403dbcb6-612f-49b7-97d2-9fa39ce9a23d'

Query to Check Associated Migrations

In some cases, the stuck JCMA plan might be associated with migrations. You should also check these migrations using a similar query:

SELECT *
FROM "AO_6FF49D_MIGRATION_ENTITY"
WHERE "PLAN_ID" = '403dbcb6-612f-49b7-97d2-9fa39ce9a23d'

Deleting the Stuck JCMA Plan

Once you’ve identified the problematic JCMA plan and any associated migrations, you can proceed with deleting them. Again, replace the placeholder 403dbcb6-612f-49b7-97d2-9fa39ce9a23d with the actual plan ID.

Query to Delete the Plan

DELETE FROM "AO_6FF49D_PLAN_ENTITY" 
WHERE "ID" = '403dbcb6-612f-49b7-97d2-9fa39ce9a23d'

Query to Delete Associated Migrations

If there are associated migrations, make sure to delete them as well:

DELETE FROM "AO_6FF49D_MIGRATION_ENTITY" 
WHERE "PLAN_ID" = '403dbcb6-612f-49b7-97d2-9fa39ce9a23d'

Cautionary Notes

Before executing any delete queries, it’s essential to exercise caution:

  1. Isolation: Ensure that the plan you are deleting is genuinely stuck and not part of an ongoing process.
  2. Testing: Test these queries in a non-production environment if possible to avoid unintended consequences.
  3. Permissions: Make sure you have the necessary permissions to perform these operations on the database.