Georgy N Joseph

CA PPM Process Tables (BPM Tables)

Blog Post created by Georgy N Joseph on Dec 29, 2016

Hi Friends,

 

What do you miss the most in CA's online documentation available on CA Clarity PPM? If you ask me, my answer will be database column level details of the CA PPM process tables i.e. the BPM tables. Not sure why CA skipped this section in the Technical Reference Guide when it has almost all other OOTB table columns explained in detail 

 

Need for Database Schema Details of BPM Process Tables

 

The complete database schema details of the BPM process tables for CA PPM would have helped us in many different ways. It can be to drill down to the process and its instance whose error is tagged in the PPM log files where they might just refer the process instance id or the thread id. We could have also used the information to do some general global searches across all Clarity processes, their log messages or a search in all GEL scripts

 

Given below are a set of Oracle SQL queries touching most of the BPM process tables. Even if this is not complete, it can be used as a quick reference point to know the table names and their foreign keys to be used. Before running the below SQL queries, please make sure that you are replacing the text in bold red with appropriate values from your CA PPM environment.

 

Query Connecting BPM_DEF Tables

 

select 

caption.name process_name,

defn.process_code,

step.step_code,

action.action_code
from
BPM_DEF_PROCESSES defn
inner join BPM_DEF_PROCESS_VERSIONS ver on ver.process_id=defn.id
inner join BPM_DEF_STAGES stg on stg.process_version_id=ver.id
inner join BPM_DEF_STEPS step on step.stage_id=stg.id
left join BPM_DEF_STEP_ACTIONS action on action.step_id=step.id
left join BPM_DEF_STEP_ACTION_PARAMS parm on parm.step_action_id=action.id
left join BPM_DEF_STEP_AI_ACTIONS aiaction on aiaction.step_action_id=action.id
left join BPM_DEF_ASSIGNEES assignee on (assignee.table_name='BPM_DEF_STEP_ACTIONS' and assignee.pk_id=action.id)
left join BPM_DEF_STEP_CONDITIONS cnd on cnd.step_id=step.id
left join BPM_DEF_STEP_TRANSITIONS trnstn on trnstn.step_condition_id=cnd.id
left join CMN_CUSTOM_SCRIPTS scripts on scripts.id=action.script_id
left join CMN_CUSTOM_SCRIPT_PARAMS par on par.script_id=scripts.id
left join BPM_DEF_OBJECTS obj on (obj.pk_id=ver.id and obj.table_name='BPM_DEF_PROCESS_VERSIONS')
inner join CMN_CAPTIONS_NLS caption on (caption.table_name='BPM_DEF_PROCESSES' AND caption.language_code ='en' AND caption.pk_id=defn.id)
where

defn.process_code='<process code>'
group by caption.name, defn.process_code, step.step_code, action.action_code
order by Process_Name, step_code, action_code

NOTE: For a couple of tables like BPM_DEF_ASSIGNEES and BPM_DEF_OBJECTS, multiple join conditions are possible as decided by the table name value specified in the TABLE_NAME column of these tables. Based on that the values provided for TABLE_NAME and PK_ID columns in the query join condition can be tweaked.

 

Query Connecting BPM_RUN Tables

 

select
caption.name process_name,
defn.process_code,
runs.id process_instance_id,
runs.status_code,
runs.start_date,
runs.end_date,
csu.first_name||' '||csu.last_name Run_By
from
BPM_RUN_PROCESSES runs
inner join BPM_RUN_PROCESS_ENGINES engine on engine.id=runs.process_engine_id
inner join BPM_RUN_STEPS steps on steps.process_instance_id=runs.id
inner join BPM_RUN_THREADS threads on (threads.process_instance_id=runs.id and steps.thread_id=threads.id)
inner join BPM_RUN_OBJECTS obj on (obj.pk_id=runs.id and obj.table_name='BPM_RUN_PROCESSES')
left join BPM_RUN_STEP_COND_RESULTS condres on condres.step_instance_id=steps.id
left join BPM_RUN_STEP_TRANSITIONS trnstn on trnstn.step_instance_id=steps.id
left join BPM_RUN_STEP_ACTION_RESULTS actres on actres.step_instance_id=steps.id
left join BPM_RUN_ASSIGNEES assignee on (assignee.pk_id=actres.id and assignee.table_name='BPM_RUN_STEP_ACTION_RESULTS')
left join BPM_RUN_ASSIGNEE_NOTES notes on notes.run_assignee_id=assignee.id
inner join BPM_DEF_PROCESS_VERSIONS ver on ver.id=runs.process_version_id
inner join BPM_DEF_PROCESSES defn on defn.id=ver.process_id
inner join CMN_CAPTIONS_NLS caption on (caption.table_name='BPM_DEF_PROCESSES' AND caption.language_code ='en' AND caption.pk_id=defn.id)
left join CMN_SEC_USERS csu on csu.id=runs.created_by
where
defn.process_code='<process code>'
and runs.id=<process instance id>
group by caption.name, defn.process_code, runs.id, runs.status_code, runs.start_date, runs.end_date, csu.first_name||' '||csu.last_name
order by process_name, process_instance_id

NOTE: The above note applies here also, to both the BPM_RUN_ASSIGNEES and BPM_RUN_OBJECTS tables.

 

Search All Process Log Messages

 

We usually print some important information to the process log messages using the gel:log tag within GEL scripts. This will insert this data in the BPM_ERRORS table when we execute the script in a custom step within a process inside Clarity. This happens irrespective of the warning level used for this tag in the GEL script. The only thing to note here is that as this is a process message and not a logger one, DEBUG/INFO becomes INFO, WARN is WARNING, and ERROR/FATAL becomes ERROR messages in the BPM_ERRORS table. 

 

Being the system administrator or the process creator, if we already know the error/warn/info message text that can be expected from different processes, the below search query on all the process messages can quickly give us a list of process instances that need our attention.

 

select
caption.name process_name,
defn.process_code,
runs.id process_instance_id,
runs.status_code,
runs.start_date,
runs.end_date,
csu.first_name||' '||csu.last_name Run_By,
be.type_code warning_level,
be.exception_trace2 Log_Message,
arg.arg_value error_argument_value
from
BPM_ERRORS be
inner join BPM_RUN_PROCESSES runs on runs.id=be.process_instance_id
inner join BPM_DEF_PROCESS_VERSIONS ver on ver.id=runs.process_version_id
inner join BPM_DEF_PROCESSES defn on defn.id=ver.process_id
inner join CMN_CAPTIONS_NLS caption on (caption.table_name='BPM_DEF_PROCESSES' AND caption.language_code ='en' AND caption.pk_id=defn.id)
left join BPM_ERROR_ARGUMENTS arg on arg.error_id=be.id
left join CMN_SEC_USERS csu on csu.id=runs.created_by
where

UPPER(be.exception_trace2) like UPPER('%your search text%')
order by process_name, process_instance_id

 

Modify any of the above queries as needed for your use.

 

Happy BPM-Querying 

 

Regards,

Georgy

Outcomes