Suspending Workflow Instance Logging

Saving workflow instances is helpful for troubleshooting, but can be a drain on system performance. This is espceailly true when migrating a large amount of data into your system. The scripts below can be used prior to a data migration to turn workflow instance logging off for all workflows during a data migration (saving your current settings) and restoring workflow instance logging afterward.

--------------------------------------------------------------------------------------------
-- ****************** BEFORE MIGRATION *********************------
-- Create backup of instance_data_flag values ------
-- Update all templates to set instance flag = false ------
--------------------------------------------------------------------------------------------

create table wf_template_bk as (
select wf_template_id, wf_template_version, instance_data_flag from wf_template where template_flag = 1);

create table wf_lookup_bk as (
select wf_template_id, wf_template_version, instance_data_flag from wf_lookup);

update wf_template t1 set t1.instance_data_flag = 0 where t1.template_flag = 1;

update wf_lookup t1 set t1.instance_data_flag = 0;

------------------------------------------------------------------------------------------
-- ****************** AFTER MIGRATION **********************------
-- Update from backup of instance_data_flag values ------
-- Drop backup tables ------
------------------------------------------------------------------------------------------

update wf_template t1 set t1.instance_data_flag = (select t2.instance_data_flag from wf_template_bk t2 where t1.wf_template_id = t2.wf_template_id and t1.wf_template_version = t2.wf_template_version)
where t1.template_flag = 1;

update wf_lookup t1 set t1.instance_data_flag = (select t2.instance_data_flag from wf_lookup_bk t2 where t1.wf_template_id = t2.wf_template_id and t1.wf_template_version = t2.wf_template_version);

drop table wf_template_bk;

drop table wf_lookup_bk