Question

Forms SQL query for all instances at a gateway step

Forms Version 10

Updated April 18, 2022

asked on April 15, 2022

We have found quite a few instances that have been stuck at gateways. The ones we have found we have used Skip to move them forward and it works, but now we are afraid of how many more might be sitting at a gateway without us knowing. This is a rather large process and has a lot of gateways. The Workload report does not report on anything other than User Tasks, so we aren't able to use it in this case and creating a custom report and adding each gateway in a filter would be very time consuming. Does anyone know of a SQL query that could return all instances that would be sitting at a step type of gateway?

Using Forms 10.4.4.444

0 0

Answer

SELECTED ANSWER

replied on April 18, 2022

There should be no design issue for exclusive gateway that will cause it stuck, we've seen support cases that instances were stuck at exclusive gateways for network or SQL issues. You can run following query to get a list of instances that are in progress but not at user task or workflow service task:

select wi.bp_instance_id,wi.current_step_id,steps.name, steps.is_deleted, steps.step_type,steps.step_sub_type, wi.update_date,wi.status,mi.lastacted_date from [cf_bp_worker_instances] as wi, cf_bp_steps as steps, cf_bp_main_instances as mi where (wi.status=1 or wi.status=3) and wi.current_process_id=steps.process_id and wi.current_step_id=steps.step_id and steps.step_type!='userTask' and (steps.step_sub_type is NULL or steps.step_sub_type !='workflow') and (wi.update_date<(DATEADD(MI,-1,GETUTCDate())) or wi.update_date is NULL) and mi.bp_instance_id=wi.bp_instance_id and (mi.lastacted_date<(DATEADD(MI,-1,GETUTCDate()))) order by bp_instance_id

0 0

Replies

replied on April 17, 2022

For the instances you have used skip action to move them forward, what types of gateway are in stuck state? Have you checked why these instance are stuck at the gateways？ We know some of the instances will be stuck at parallel gateways or inclusive gateways for design issue. For example, for splitting gateways and merging parallel gateways in pair, if there 3 outflows from splitting gateways but only two goes to the merging parallel gateway, then the merging parallel gateways will be stuck as it is expecting to have 3 inflows reach to move to the next step.

0 0

replied on April 18, 2022

They were exclusive gateways. We have been having some fun SQL issues the last few months that our database team is working on, but unsure if that is what's causing the issues we are seeing.

0 0

SELECTED ANSWER

replied on April 18, 2022

select wi.bp_instance_id,wi.current_step_id,steps.name, steps.is_deleted, steps.step_type,steps.step_sub_type, wi.update_date,wi.status,mi.lastacted_date from [cf_bp_worker_instances] as wi, cf_bp_steps as steps, cf_bp_main_instances as mi where (wi.status=1 or wi.status=3) and wi.current_process_id=steps.process_id and wi.current_step_id=steps.step_id and steps.step_type!='userTask' and (steps.step_sub_type is NULL or steps.step_sub_type !='workflow') and (wi.update_date<(DATEADD(MI,-1,GETUTCDate())) or wi.update_date is NULL) and mi.bp_instance_id=wi.bp_instance_id and (mi.lastacted_date<(DATEADD(MI,-1,GETUTCDate()))) order by bp_instance_id

0 0

You are not allowed to follow up in this post.

Question

Question

Forms SQL query for all instances at a gateway step

Answer

Replies

Sign in to reply to this post.