I hope that connection in both mappings is the same .
I met often similar issue due to configuration of connector especially mix-chars support and additional quotes.
Please check the both logs and compare how table name and owner are displayed.
You can check "At run time, get data object columns from the data source" for second mapping, then change owner to invalid to get error message in mapping log.
We have seen the similar issues with Oracle and it was intermittent. When we rerun the job, it goes fine without any issue.
Please help with the below queries
What are the versions for BDM, Teradata and Teradata JDBC Jar?
Are you able to reproduce the issue in every single run?
Are there concurrent jobs(dynamic mapping) that overlap with the mapping run?
Is it possible to share the JDBC driver class name & URL defined in the connection (dummy out the dns/ip/dbName).
Thanks for your response.
The connections are the same for both mappings. I played around with all the options of the mix-chars support and additional quotes and nothing worked.
I did manage to get it to work, but I'm not sure why it is an issue - just for the example, let's assume that the owner name in the DB is OWENER_name. when I run the dynamic mapping with "OWENER_name" it is working but when I run it with "OWENER_NAME" (All caps) it is failing. but when I run this source in the data viewer both options worked (all caps and "OWENER_name"). I even tried with and without the support mixes chars option but it is still not working. It appears that the owner's name is case sensitive which looked odd to me. Is there a way to avoid it from being case sensitive because it is working but it is very prone to errors if we will have to pass the parameter with case sensitivity
You mentioned that you have this issue with ORACLE, which I'm also facing (Same problem as with Teradata),
Here are the details you asked for
- BDM Version: 10.2.2 HotFix1
- TERADATA DB Version: VERSION 22.214.171.124
- TERADATA JDBC Jar version: I'm checking with our Administrator because I'm not sure
Are you able to reproduce the issue in every single run? YES
Are there concurrent jobs(dynamic mapping) that overlap with the mapping run? No
JDBC Driver: com.teradata.jdbc.TeraDriver
Connection string: jdbc:teradata://<host>/database=<db_name>,tmode=ANSI,CLIENT_CHARSET=ISO-8859-8
For Oracle which I also have this issue the details are:
JDBC Driver: com.informatica.jdbc.oracle.OracleDriver
JDBC String: jdbc:informatica:oracle://<host>:<port>;ServiceName=<db_name>
Do you use JDBC connection for Oracle and Teradata (not Teradata PT)? If yes. try to set SQL Identifier Character to None instead of default quotes.
Next, compare Select statement in DataViewer log (icon "Show Log" in right upper corner of DataViewer window) and mapping logs. How are the owner and table names generated?
I'm using JDBC connections for both Teradata and Oracle. The SQL identifier chars are set to none and I still see the issue.
The mapping is not getting to the step where it is running the select query. the last message I see before the error message is:
"Getting metadata for schema (<scheme_name>), table (<table_name>) by calling getTables method"
Then I see the error message:
"Import of table <owner name>.<table name> failed as table not found"
the table name and schema name are correct in this log message
In my case, i was not able to replicate the issue in 10.4.1, so we didn't raise it with Informatica GCS. Also mapping runs fine after the restart without any changes. If you are able to replicate the issue consistently, please raise a case with Informatica to find the root cause (it may be a bug specific to Dynamic mapping).
I'm confused. If you can share DataViewer and mapping log when it worked fine it will be better.
You wrote that DataViewer worked correctly for "OWENER_NAME" and "OWENER_name". How owner and table name are displayed in dataviewer logs?
Next, you wrote that mapping work for "OWENER_name" only.
This can be if Teradata DB is configured in case sensitive mode.
In this case You must exactly set case of name and owner table and use quotes
because of you can have two different tables - "OWENER_NAME" and "OWENER_name".
If you omit quotes the both names are equivalent to "OWENER_NAME".
Sorry I can't share the logs but it will take me a lot of time to mask them from the sensitive info.
I will share part of it here.
When I ran the data viewer i see the following query to the DB:
2021-03-18 11:42:59.774 <TASK_140054504658688-READER_1_1_1> INFO: [RR_4010] SQ instance [<TABLE_NAME>] SQL Query [SELECT <OWNER_NAME>.<TABLE_NAME>.<Column1>,..., FROM <OWNER_NAME>.<TABLE_NAME>]
Even if I set the SQL Identifier char to quotes in the connection property, the data viewer log shwos the query without quotes.
You wrote that:"This can be if Teradata DB is configured in case sensitive mode." but if this was the case then how come it is working fine in data viewer?
Can you please open a case with Informatica GCS so that we can verify if there are any known issues with the Teradata JDBC driver version being used?