11 Replies Latest reply on Mar 18, 2021 4:07 AM by John Naiderman

    Issue with running dynamic source table in DEI

    John Naiderman Active Member

      Hi,

       

      I have a dynamic source object with the "At run time, get data object columns from the data source" option checked.

      The connection, owner, and resource values are parameterized to run the same mapping for different tables.

      When I run the mapping for one of the Teradata tables I need, I keep getting an error with the following error message:

      "Import of table <owner name>.<table name> failed as table not found"

       

      I created a different source that is not dynamic and points to that table, and it is working fine. so the issue occurs only when I try to access this table through a dynamic source

       

      I can't share the full log due to security reasons.

       

      Did anyone encounter something similar?

       

      Thanks,

      John

        • 1. Re: Issue with running dynamic source table in DEI
          Vlad Ponomarenko Guru

          Hi John,

          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.

           

          Regards, Vlad

          • 2. Re: Issue with running dynamic source table in DEI
            PradeepKumar Active Member

            Hi John,

             

            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).

             

             

            Regards

            Pradeep

            • 3. Re: Issue with running dynamic source table in DEI
              John Naiderman Active Member

              Hi Vlad,

               

              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

               

              thanks,

              John

              • 4. Re: Issue with running dynamic source table in DEI
                John Naiderman Active Member

                Hi Pradeep,

                 

                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 16.20.53.20
                • 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>

                • 5. Re: Issue with running dynamic source table in DEI
                  Vlad Ponomarenko Guru

                  Hi John

                   

                  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?

                  Regards, Vlad


                  • 6. Re: Issue with running dynamic source table in DEI
                    John Naiderman Active Member

                    Hi Vlad,

                     

                    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

                     

                    thanks,

                    John

                    • 7. Re: Issue with running dynamic source table in DEI
                      PradeepKumar Active Member

                      Hi John,

                       

                      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).

                       

                      Regards

                      Pradeep

                      • 8. Re: Issue with running dynamic source table in DEI
                        Vlad Ponomarenko Guru

                        Hi John,

                        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".

                         

                        Regards, Vlad

                        • 9. Re: Issue with running dynamic source table in DEI
                          John Naiderman Active Member

                          Hi Vlad,

                           

                          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?

                          • 10. Re: Issue with running dynamic source table in DEI
                            user165569 Guru

                            Hi John,

                             

                            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?

                             

                            Thanks,
                            Ninju

                            Informatica Support