WA Agent for Informatica: Error occurred while fetching the data

Products

• CA Workload Automation DE - Business Agents (dSeries)
• CA Workload Automation DE
• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)
• CA Workload Automation DE - Scheduler (dSeries)

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

Component : CA Workload Automation AE Agent for SAP

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/2019 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#2018:user_dev = 1

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

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

01/01/2019 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.  User password utility in <agent_install_directory> to get the encrypted password. See here for more details.

5. Restart agent for changes to take affect.

Additional Information

See this link for more details.