11 Replies Latest reply on May 16, 2019 12:54 PM by Mohit Verma

    Informatica Sqlldr issue

    Mohit Verma Active Member

      Hello Experts,

       

      I getting an issue while trying to load a file from Informatica with Oracle External Loader connection. As per session log, it is reading the file successfully and creating the *.out.ctl(control file) and *.out(data file) but after that failing with below error -

       

       

      Thread

       

      : WRITER_1_*_1

       

      Process ID

       

      : 10667

       

      Message Code

       

      : WRT_8047

       

      Message

       

      : Error: External loader process [10722] exited with error [127]

       

      When I tried to load the same file manually from Unix prompt then it has loaded without any issue. I am using below command by replacing the path, connection and control file details. We are using instantclient_12_2.

       

      <path>/sqlldr <Database connection> control=<Control file>

       

      Can somebody help please to resolve this issue, am I missing any configuration information anywhere in the Informatica setup?

        • 1. Re: Informatica Sqlldr issue
          Nico Heinze Guru

          Do I understand right that you're trying to use the Oracle Instant Client 12R2 for PowerCenter?

          Bad idea. The Instant Client is NOT AT ALL supported with PowerCenter (that's been published hundreds of times here on the forum). Please use the full OCI client.

           

          Don't ask for reasons, I can't tell. All I can tell you is that out of maybe 100 persons trying the Instant Client app. 98 fail completely (they can't even install the software with the Instant Client, or at least the application services won't start) AND that Informatica does NOT support any other client but the full OCI client for PowerCenter. It's that simple.

           

          So: don't ever use the Instant Client with PowerCenter.

           

          Regards,

          Nico

          • 2. Re: Informatica Sqlldr issue
            Mohit Verma Active Member

            Thanks Nico for your reply. I got it however my company is using instantclient and I can just recommend the DBA/Infa Admin to use the Full OCI client and nothing else.

             

            But do you understand the error and can we resolve it without changing the client?

            • 3. Re: Informatica Sqlldr issue
              Nico Heinze Guru

              No and no:

              First to be honest I don't have any experience with the SQL Loader.

              Second using the OCI client instead of the Instant Client is NOT a recommendation; it's a requirement by Informatica, nothing that a customer can decide as they wish. It's really that simple. If they want to use the Instant Client, they cannot have PowerCenter working reliably. Period. That's just a matter of fact. Please tell your customer.

               

              And yes, I have had my share of fun with customers who were not willing to accept that the Instant Client is not supported by Informatica. Nothing to do about it. If they don't swtch to the full OCI client, they will run a combination of products which is not supported, meaning they can NEVER rely on their infrastructure to work reliably.

              Do they want to take that risk? I doubt it. So they will have to accept the fact that the OCI client is a Must-Have for PowerCenter.

               

              That's not my idea, that's just my wording. They do nothing but waste their (and your) time by "discussing" the use of the Instant Client. Period.

               

              Regards (and sorry for the rant, this is not your responsibility, I am just fed up with stubborn customers),

              Nico

              • 4. Re: Informatica Sqlldr issue
                Mohit Verma Active Member

                Hi Nico,

                 

                Thanks a lot !!.

                 

                I had asked my company's informatica Admin to check from Informatica support and they have also replied the same thing - "Informatica do not support Oracle instant client. Please install full Oracle client which is supported".

                 

                Now, the full oracle client should be installed and after that only I would be able to test it again.

                 

                Thanks a lot again for your quick response and guidance.

                 

                 

                • 5. Re: Informatica Sqlldr issue
                  Mohit Verma Active Member

                  Hi Nico/All,

                   

                  Finally oracle full client 12.2.0 has been installed on the host machine BUT I am still getting the same error.

                   

                  Severity Timestamp Node Thread Message Code Message

                  INFO 03/05/2019 12:03:02 PM Infa_Node_nyabc0123 WRITER_1_*_1 WRT_8027 Started [/u01/app/oracle/product/12.2.0/client_1/bin/sqlldr] external loader process [22299] in [Staged] mode for target [XYZ.LAND_TEST]

                  INFO 03/05/2019 12:03:02 PM Infa_Node_nyabc0123 WRITER_1_*_1 WRT_8168 End loading table [LAND_TEST] at: Fri May 03 12:03:01 2019

                  ERROR 03/05/2019 12:03:02 PM Infa_Node_nyabc0123 WRITER_1_*_1 WRT_8047 Error: External loader process [22299] exited with error [3]

                   

                  I am not able to sure why Infomatica or sqlldr is not creating any log file where I can check the exact reason of this error. I have "IS_STAGED" property checked in the Loader connection. Can someone give any pointer to solve it.

                   

                  Note:- sqllder command manually from Unix prompt is running successfully with the new oracle client pkg.

                   

                  Regards,

                  MKV

                  • 6. Re: Informatica Sqlldr issue
                    Nico Heinze Guru

                    Do you happen to use the Hyperion Essbase connector together with SQL Loader on the same Integration Service? That can't work because some libraries of both software packages have the same name.

                     

                    Regards,

                    Nico

                    • 7. Re: Informatica Sqlldr issue
                      Mohit Verma Active Member

                      Hi Nico,

                       

                      No.

                      • 8. Re: Informatica Sqlldr issue
                        Nico Heinze Guru

                        Please try the following:

                        Set up some command task which saves all environment variables to some text file.

                        Run this command task.

                        Verify all environment variables whether they are set as expected.

                         

                        Regards,

                        Nico

                        • 9. Re: Informatica Sqlldr issue
                          Mohit Verma Active Member

                          Hi Nico,

                           

                          Thanks again for your valuable suggestions!!

                           

                          I did the same and found that ORACLE_HOME or ORACLE_SQLLDR are having instant client "/apps/oracle/instantclient_12_2" path.

                           

                          I had changed the .bash_profile and all environment variables are showing correct when I am running the same command on Linux terminal. But I am not able to understand how come it is still pointing to instant client in Informatica.

                           

                          I checked SQLLDR connection properties in the Informatica and the SQLLDR path is correctly pointing to new full oracle pkg and in session log also sqlldr is running from new full oracle client path so not sure environment variables can make any impact.

                           

                          Do I need to make any change anywhere else? If you can guide me I can make the changes right now and test.

                           

                          Regards,

                          MKV

                          • 10. Re: Informatica Sqlldr issue
                            Nico Heinze Guru

                            The whole domain must be restarted for the changed environment variables to take effect. Environment variables are NOT re-evaluated from the current environment when the Integration Service starts or any pmdtm processes are started, they always inherit the environment of the Apache Tomcat installation which hosts the Informatica domain.

                            OK, there's one way to change the environment of the Integration Service which does NOT require restarting the domain: set environment variables for the Integration Service, then restart the Integration Service.

                            Regards,

                            Nico

                            • 11. Re: Informatica Sqlldr issue
                              Mohit Verma Active Member

                              Hi Everyone/Nico,

                               

                              Thanks to all for their help and support.

                               

                              This issue has resolved and the reason was that Informatica V10.1 does not support oracle client 12.2.0. We have updraded to Informatica V10.2 and it worked like a charm. I am not sure if my informatica Admin has made any other change anywhere else but yes he has updated all the configurations and restarted the Integration service again.

                               

                              Regards,

                              Mohit