Role Suppression to Speed up Regeneration in a Development Environment

Role suppression suppresses views being generated associated with a role (or roles).

A peer of mine suggested that suppressing all roles not having any development in them would be helpful in a development environment.  Specifically, only have the role which has a xu2 or xu5 script which you are developing exposed.  The idea behind this is that the regeneration time should be significantly shortened.

Presently my average generation time is approximately 30 minutes.  When I use this role suppression approach, it goes down to about 10 minutes.

While this is a documented feature of Noetix, it requires some thought and consideration. 

Before initiating a task like this, it is important to “design” this suppression plan.

What do I mean by this? One should design how this will be undertaken.

Here is an example:

****************

Scenario:

I specifically want to develop a custom freight rate view using an xu2 script through cloning the ARX0_Customer_Addresses view.

To do this, I know I need to suppress all roles except ARX0.  Before even jumping into the “tupdprfx.sql” file and making modifications (standard, documented method to suppress roles), it is important to consider any other changes that might be needed before doing any “development”.

Plan:

-Modify “tupdprfx.sql” file by editing the invocation to noetix_prefix_pkg.update_role_status procedure by changing the i_user_enabled_flag from 'Y' to 'N' as follows (for all roles being supressed):

Before

  noetix_prefix_pkg.update_role_status(
   ------------------------------------------------------------------------------
   i_user_enabled_flag      => 'Y',  /* +++ EDIT THIS LINE ONLY (Y or N) +++ */
   ------------------------------------------------------------------------------
   i_application_label      => 'AP',
   i_role_label             => 'PAYABLES',
   i_org_id                 => 84,
   i_instance_type          => 'S');
  --
END;

After

  noetix_prefix_pkg.update_role_status(
   ------------------------------------------------------------------------------
   i_user_enabled_flag      => 'N',  /* +++ EDIT THIS LINE ONLY (Y or N) +++ */
   ------------------------------------------------------------------------------
   i_application_label      => 'AP',
   i_role_label             => 'PAYABLES',
   i_org_id                 => 84,
   i_instance_type          => 'S');
  --
END;

-Through examination (and trial error), I know that most non-template tables will not have entries associated with views associated with suppressed roles.  This means views associated with suppressed roles should not have any xu5 invocations (they will throw foreign key constraint errors).

****************

Execution: 

Edit “tupdprfx.sql” file and perform this modification (using Replace All):

Next, I look for the ARX0 entry, and makes sure this entry actually is “turned on”:

Remove all xu5 invocations to views associated with roles that are suppressed.

This plan worked.

********

Suppose I do not plan my execution of this suppression (or act in ignorance) and have xu5 invocations to views associated with suppressed roles?

Specifically, one can anticipate the ORA-02291 error being thrown.  Here is an example:

ARCM_Transaction_Line_Dtls_xu5.lst:ORA-02291: integrity constraint (NOETIX_SYS_TEST.N_VIEW_COLUMNS_FK1) violated

I check this foreign key constraint:

SELECT

*

FROM

DBA_CONSTRAINTS

WHERE 1=1

AND OWNER = 'NOETIX_SYS'

AND CONSTRAINT_TYPE = 'R' /* foreign key*/

AND CONSTRAINT_NAME = 'N_VIEW_COLUMNS_FK1';

As you can see, this is a foreign key constraint (‘constraint_type = ‘R’).  The dba constraints view actually has a r_constraint_name column (the primary key of the table for whom the constraint is a foreign key) and this is ‘N_VIEW_QUERIES_PK’.

I look this up, with this query:

select

*

from

DBA_CONSTRAINTS

WHERE 1=1

AND OWNER = 'NOETIX_SYS'

AND

CONSTRAINT_NAME = 'N_VIEW_QUERIES_PK'

And

Constraint_type = ‘P’ /*primary key */;

So I know that the corresponding record in the non-template table, ‘N_VIEW_QUERIES’, does not exist.

This tells me that my suppression script worked properly and the xu5 script has an invocation to a view whose role was suppressed.