CA Clarity Tuesday Tip - How to query DB for process and steps

[Aurora_Gaimon]

When analyzing bg-niku.log / bg-ca.log, we can find errors related to process and steps. We just see the internal ID and not their names which makes dificult to locate process in Clarity UI.

Error:
[...]
The error "com.niku.bpm.BpmException: Cannot get step definition by step ID xxxxxxx"
[...]

You can use following query to get more details from DataBase:

select
 bdp.id  process_id,
 ccn.name process_name,
 bdse.id step_id,
 ccn.name step_name
from bpm_def_processes  bdp
 inner join bpm_def_process_versions bdpv on bdp.id =  bdpv.process_id
 inner join cmn_captions_nls ccn on  bdp.id = ccn.pk_id
   and ccn.table_name = 'BPM_DEF_PROCESSES'
   and ccn.language_code = 'en'
 inner join bpm_def_stages bdsa on bdpv.id = bdsa.process_version_id
 inner join bpm_def_steps bdse on bdsa.id = bdse.stage_id
 inner join cmn_captions_nls bdsecap on bdse.id = bdsecap.pk_id
   and bdsecap.table_name = 'BPM_DEF_STEPS'
   and bdsecap.language_code = 'en'

 

 

[Anon Anon]

Hi ,

Thanks for the query. But i tried it on database and i didn't found the process referenced by the ID (Cannot get step definition by step ID 5010040).

Indeed on the development environnement, the process was deleted.

Please , how can i resolve this log's problem ?

 

 

[Aurora_Gaimon]

Aplogoies in advance if I dont understand it correctly 

so, in DEV, the process was deleted and you still have the error message?

[Anon Anon]

Yes, in DEV, the process was deleted.

 

[Aurora_Gaimon]

You can try this query if it shows any results:

 

select *  from bpm_run_processes where process_version_id not in (select id from bpm_def_process_versions)

 

Results are orphan process instances. You can remove them:

 

delete from bpm_run_processes where process_version_id not in (select id from bpm_def_process_versions)

 

 

For further assistance, I'd suggest to log a case in CA Clarity Support.

 

[navzjoshi00]

This is to check the count -

select count(*) from cmn_sec_assgnd_obj_perm
where object_instance_id IN (
select object_instance_id from cmn_sec_assgnd_obj_perm p
where p.object_ID = 50680
minus
select ID from BPM_RUN_PROCESSES);


The following can batch the deletes for these orphans at 100,000 rows at a time if they are significant.

Delete from cmn_sec_assgnd_obj_perm
where object_ID = 50680
and object_instance_id IN (
select object_instance_id from cmn_sec_assgnd_obj_perm p
where p.object_ID = 50680
minus
select ID from BPM_RUN_PROCESSES)
AND ROWNUM <= 100000;

 

As Aurora mentioned, check with Support as well.

NJ

[Anon Anon]

There are no orphan process instances. I'll log a case in CA Clarity Support.

Thanks Aurore .

 

[System]

Aurora_Gaimon:

When analyzing bg-niku.log / bg-ca.log, we can find errors related to process and steps. We just see the internal ID and not their names which makes dificult to locate process in Clarity UI.

Error:
[...]
The error "com.niku.bpm.BpmException: Cannot get step definition by step ID xxxxxxx"
[...]

You can use following query to get more details from DataBase:

select
 bdp.id  process_id,
 ccn.name process_name,
 bdse.id step_id,
 ccn.name step_name
from bpm_def_processes  bdp
 inner join bpm_def_process_versions bdpv on bdp.id =  bdpv.process_id
 inner join cmn_captions_nls ccn on  bdp.id = ccn.pk_id
   and ccn.table_name = 'BPM_DEF_PROCESSES'
   and ccn.language_code = 'en'
 inner join bpm_def_stages bdsa on bdpv.id = bdsa.process_version_id
 inner join bpm_def_steps bdse on bdsa.id = bdse.stage_id
 inner join cmn_captions_nls bdsecap on bdse.id = bdsecap.pk_id
   and bdsecap.table_name = 'BPM_DEF_STEPS'
   and bdsecap.language_code = 'en'

 

 

Thanks for the great info Aurora!