WA Agent for Informatica: Error occurred while fetching the data

Products

• ESP dSeries Workload Automation - Business Agents (dSeries)
• ESP dSeries Workload Automation
• DSERIES- SERVER
• ESP dSeries Workload Automation - System Agent (dSeries)
• ESP dSeries Workload Automation - Scheduler (dSeries)
• CA Workload Automation AE - System Agent (AutoSys)
• Workload Automation Agent

Issue/Introduction

When submitting or checking Workflow in Informatica the WA Agent returns SUBERROR or FAILED status.

SUBERROR Status(ERROR) LStatus("Error occurred while fetching the data - SQL Exception in validateFolderName") Plugin(informatica) User(HOSTUSER) Host(INFORMATICA-HOST)

 

 

Environment

Release : 11.4 or above

Component : Workload Automation Agent for Informatica

Cause

The Informatica plugin for WA Agent requires connections to database and the Informatica instance.  When connecting to the database the following message may be seen in plugin_log_informatica.log.

01/01/20## 00:00:24.212 UTC 5 InformaticaPlugin.pool-1-thread-8.CybInformaticaLoggerHandlerWOB.log[:65] - # of available connections matching: jdbc:oracle:thin:@dbhost.example.com:1956:info_dom#example:user = 1

01/01/20## 00:00:24.212 UTC 5 InformaticaPlugin.pool-1-thread-8.CybInformaticaLoggerHandlerWOB.log[:65] - Got session for user ######

01/01/20## 00:00:24.212 UTC 4 InformaticaPlugin.pool-1-thread-8.CybInformaticaLoggerHandlerWOB.log[:65] - Validating the folder

01/01/20## 00:00:24.215 UTC 1 InformaticaPlugin.pool-1-thread-8.CybInformaticaLoggerHandlerWOB.log[:86] - java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)

The WA Agent must use database user (not Informatica) to connect to database.

Resolution

The following must be done for Informatica plugin to properly connect and retrieve the workflows. 

1.  Install the correct JDBC driver for the database.  The JDBC jar will be placed in <agent_install_directory>/jars/ext .

Note: Copy only one JDBC jar in the jars/ext directory.  Remove any older versions.  Move them to another location.  Do not rename.

2.  The DB username must be configured in the <repo_file>.properties file in <agent_install_directory>/config/informatica/default directory.

3. Change informatica.db.user to DB user that has access to the database.  User must have access to all the tables/views in the database.

4. Change informatica.db.password accordingly.  Use password utility in <agent_install_directory> to encrypted password. See here for more details.

5. Restart agent for changes to take affect.

Additional Information

See this link for more details.