Policy Xpress SQL query execution differences between Data and Action tabs

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

 In Policy Xpress (PX), SQL query behavior varies significantly depending on whether the query is executed within the Data tab or the Action tab. Users may find that a query successfully tested in one area fails to execute as expected in the other due to the specific architectural purpose of each tab.

 

 

Environment

Identity Manager

Cause

The failure occurs because the Policy Xpress engine treats the two tabs with distinct functional logic:

• Data Tab: Specifically designed for Data Retrieval. It is optimized for SELECT statements meant to gather information to be used as variables within the policy.
• Action Tab: Specifically designed for Activity-Oriented Operations. It is optimized for Data Manipulation Language (DML) such as INSERT, UPDATE, or DELETE to perform external database updates.

Resolution

To ensure SQL queries execute correctly, match the query type to the appropriate PX tab:

1. For Data Retrieval (SELECT):
   • Place all SELECT queries in the Data tab.
   • Use these to populate PX variables that can then be referenced in subsequent logic.
2. For Database Updates (INSERT/UPDATE/DELETE):
   • Place all DML-oriented queries in the Action tab.
   • Verify that the JBoss/Wildfly datasource has the appropriate write permissions for the target external table.
3. Validation:
   • Ensure the datasource connection is tested successfully in the Application Server before testing within the PX interface.