How to correct projects in unknown state in BCC/CA ?

Often deployments will fail due to some reason and even if we use cancel button sometimes ATG fails to revert the project to previous state. And because of JVM crash or normal bounce of BCC instance, currently deploying projects will go to unknown state, follow below steps to correct such type of projects.


If BCC instance is bounced while deploying a project to staging
we can get back the project previous state so that we can start from Author state

-- remove asset locks on the project. if any
delete from avm_asset_lock where workspace_id =
(select id from avm_devline where name =
(select workspace from epub_project where project_id = 'prj49001'));

-- change states. Locked to false and editable to true
--   locked to 0 and editable to 1
update epub_project
set locked=0
where project_id = 'prj49001';

update epub_project
set editable=1
where project_id = 'prj49001';

-- change states. Locked to false and editable to true
--   locked to 0 and editable to 1
update avm_workspace
set locked=0
where ws_id =
(select id from avm_devline where name =
(select workspace from epub_project where project_id = 'prj49001'));

update avm_workspace
set editable=1
where ws_id =
(select id from avm_devline where name =
(select workspace from epub_project where project_id = 'prj49001'));

-- finally change state to author which is 3 for all workflows
update epub_ind_workflow
set state=3
where process_id =
(select process_id from epub_process where project='prj49001');
commit;

Invalidate below repository caches
/atg/epub/PublishingRepository/
/atg/epub/version/VersionManagerRepository/

If BCC instance is bounced while deploying a project to production
we can get back the project previous state so that we can redeploy to
production.

--If project with workflow editCommerceAssets.wdl
--stuck while deploying to production

update epub_ind_workflow
set state=11
where process_id =
(select process_id from epub_process where project = 'prj49001');
commit;

-- For projects which are created by auto import workflow

-- If project with /Commerce/autoImportAssets.wdl workflow
-- stuck while production

update epub_ind_workflow
set state=10
where process_id =
(select process_id from epub_process where project = 'prj49001');
commit;

Invalidate only /atg/epub/PublishingRepository/

Completely delete a project

-- Removing locks of the project if any
delete from avm_asset_lock where workspace_id in
(select id from avm_devline where name in
(select workspace from epub_project where project_id = 'prj243002'))

-- delete history of the project
delete from EPUB_PR_HISTORY where project_id in
(select project_id from epub_project where project_id = 'prj243002');

-- delete the project
delete from epub_project where project_id = 'prj243002';

-- delete history of the process
delete from EPUB_PROC_HISTORY where process_id in
(select process_id from epub_process where project = 'prj49001');

-- delete task information of process
delete from EPUB_PROC_TASKINFO where id in
(select process_id from epub_process where project = 'prj49001');

-- delete states of project (if any)
delete  from EPUB_IND_WORKFLOW where process_id in
(select process_id from epub_process where project = 'prj49001');

-- finally delete the process
delete from epub_process where where project = 'prj49001';

commit;

---- Extra Sqls that may give more info --

To give all conflicting projects

select * from epub_project where workspace in
(select name from avm_devline where id in
(select workspace_id from avm_asset_lock))

To give all conflicting projects for a particular asset.

select * from epub_project where workspace in
(select name from avm_devline where id in
(select workspace_id from dcs_category where category_id = 'cat3484800002' and category_id in
(select repository_id from avm_asset_lock where descriptor_name='category')))