Error SQL0727N when you query view results in Watson Query

You might encounter SQL0727N errors if you work in an environment that is heavily reliant on access control that is based on user groups.

Symptoms

An SQL0727N error message is returned when querying a view.

SQL0727N  An error occurred during implicit system action type "3". 
Information returned for the error includes SQLCODE "-142", SQLSTATE "     " 
and message tokens "".  SQLSTATE=56098

Causes

This issue can occur when access control and authorization based on groups is widely used. This error occurs when you query views that are created on virtual tables if certain other conditions also hold true. Specifically, the view creator’s access to the underlying table is based on group membership. When these views are accessed, the user’s access to the view is checked and the view creator’s access to the underlying table that the view is based on. Since that view creator’s access to the underlying table relies on an external identity provider, the view is marked invalid while it is awaiting revalidation.

For more information, see Restrictions on the use of group privileges when executing DDL statements or binding packages8.

Resolving the problem

  1. If the error is as a result of a view that is marked invalid, a user with the Admin role can resolve the condition that caused the view to be marked invalid. When you work with access based on groups, generally an Admin must grant SELECT privileges to the user rather than or in addition to the group.

    You can also grant public access on your objects for all roles or all data virtualization users and then restrict access by using data protection rules that are defined on groups. For more information, see Governing virtual data with data protection rules in Watson Query.

  2. When the corrective action is taken, running SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS() on that object revalidates the object. Subsequent access to the object then succeeds. This procedure requires a Watson Query user with an Admin role to run the necessary steps. Execution of the procedure must follow this sequence.
CALL SYSHADOOP.BIG_SQL_SERVICE_MODE('ON');
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS(object-type, object_schema, object_name);
CALL SYSHADOOP.BIG_SQL_SERVICE_MODE('OFF');

For more information, see the following resources.

  1. ADMIN_REVALIDATE_DB_OBJECTS procedure - Revalidate invalid database objects
  2. auto_reval - Automatic revalidation and invalidation configuration parameter