Oracle SQL errors with Informatica plugin (WA Agent)

Products

• CA Workload Automation AE - System Agent (AutoSys)
• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation DE - Business Agents (dSeries)
• CA Workload Automation DE - System Agent (dSeries)

Issue/Introduction

After installing the Informatica plugin, the job fails and following errors are observed in agent logs

Validating the folder RepoMan and workflow wkf_test_oracle_connection
02/02/20XX 00:00:12.345-0600 1 InformaticaPlugin.pool-1-thread-1.CybInformaticaLoggerHandlerWOB.log[:86] - 
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

 

Environment

Release : 12

Component : Workload Automation Agent for Informatica

Database: Oracle

Cause

The agent does not pre-pend schema name to the tables in the SQL query.  The DB user defined in the agent may not be same as the DB user in Informatica.  This DB user used by agent cannot access tables without specifying the schema. It is recommended to use same DB schema that is specified in Informatica (Power Center) with the agent.

 

Resolution

Agent needs access to the database used by Informatica, it needs read access to certain tables listed here.  If the same DB user/schema as defined in Informatica cannot be used, then consult your DBA to create a public synonym or grant for DB user to be used with agent.

 

Note: Make sure the user and password defined in agent (<informatica_repo>.properties file).

Use the DB user to connect to database and run simple select query on any Informatica tables.