13 Replies Latest reply on Aug 18, 2020 2:21 AM by Robert Whelan

    Error while importing Informatica Core Accelerator in Developer tool

    inuser526279 Guru

      Hi Team,

       

      I'm getting below error while importing Informatica Core Accelerator in Developer tool. My informatica server and client Version is 10.4.

       

      Unable to create database table for ReferenceTable: [[CMSREFTABLE_10019] Cannot create the following reference table: [usa_company_names_std_inf3].].

       

      While looking at Informatica Administrator -> Logs -> Service -> Choosing service type as "Content Management Service", I found below details on the error:

       

      Cannot create a reference table for database table [[usa_company_names_std_inf3]]. Error message: [[informatica][Oracle JDBC Driver][Oracle]ORA-00904: : invalid identifier].

       

      I have ensured the following:

      1. Choose correct target location in Accelerator import window. I have given same schema name which I have defined in "Reference Data Location" in CMS Service.

       

      2. Ensured that audit table is created before import by selecting Manage -> services and nodes -> CMS Service -> Actions -> Audit Tables.

       

      Kindly help on this error. Please see the attachment for more information. Name of Accelerator content is "Informatica_Core_Accelerator_1040.zip".

       

      Note: I have selected "MS Windows Latin 1 (ANSI), superset of Latin1" in code page in import window.

       

      Thanks

        • 1. Re: Error while importing Informatica Core Accelerator in Developer tool
          Namratha Dhoolipalla Guru

          Hi,

           

          Can you please try setting the SQL identifier character to “”(quotes) for the reference database connection in the Connection properties?

          After making this change, please import the Accelerator Content again.

           

          Thanks,

          Namratha

          • 2. Re: Error while importing Informatica Core Accelerator in Developer tool
            inuser526279 Guru

            Hi Namratha,

             

            Thanks for your suggestion. I think it has solved the error. But below new error is popping up. Kindly advise.

             

            Aug 17, 2020 1:00:28 AM com.informatica.cms.service.reftablemanager.CMSReferenceTableManager populateReferenceTable

            WARNING: Cannot load data into reference table [usa_surname_ind_infa1]. Error message: [java.lang.Exception: [MPSVCCMN_10094] The Mapping Service Module failed to run the job with ID [kV5L7OB

            GEeqYX_aG8fMwpQ] because of the following error: [EdtmExec_00007] CMN_1022 Database driver error...

            CMN_1022 [Database driver event...Error occurred loading library [libclntsh.so.11.1: cannot open shared object file: No such file or directory]Database driver event...Error occurred loading l

            ibrary [libpmora8.so]]

             

             

            We are using Oracle Version 12.2.0.1.

             

            Thanks

            Aug 17, 2020 1:00:28 AM com.informatica.cms.service.reftablemanager.CMSReferenceTableManager populateReferenceTable

            WARNING: Cannot load data into reference table [usa_surname_ind_infa1]. Error message: [java.lang.Exception: [MPSVCCMN_10094] The Mapping Service Module failed to run the job with ID [kV5L7OB

            GEeqYX_aG8fMwpQ] because of the following error: [EdtmExec_00007] CMN_1022 Database driver error...

            CMN_1022 [Database driver event...Error occurred loading library [libclntsh.so.11.1: cannot open shared object file: No such file or directory]Database driver event...Error occurred loading l

            ibrary [libpmora8.so]]

            • 3. Re: Error while importing Informatica Core Accelerator in Developer tool
              Namratha Dhoolipalla Guru

              Hi,

               

              This error usually occurs if the ORACLE_HOME/lib is not set in the LD_LIBRARY_PATH.

              Please ensure that the following variables were set:

              • ORACLE_HOME is set and pointing to the client installed on the server.
              • PATH=${PATH}:${ORACLE_HOME}/bin
              • LD_LIBRARY_PATH=${INFA_HOME}/services/shared/bin:${LD_LIBRARY_PATH}:${ORACLE_HOME}/lib

               

              These variables need to be picked up by the Informatica process when starting up. You can verify the tomcat_envvars.txt file to check which variables were set and picked up by the Informatica processes.

              Please shut down the domain, set these variables and bring up the server.

               

              Thanks,

              Namratha

              • 4. Re: Error while importing Informatica Core Accelerator in Developer tool
                inuser526279 Guru

                Hi Namratha,

                 

                I have ensured that three environment variables mentioned are added in .profile at server side.  That error is resolved. Now import fails because of other issue (Although error code remains the same):

                 

                Aug 17, 2020 5:22:26 AM com.informatica.cms.service.reftablemanager.CMSReferenceTableManager populateReferenceTable

                WARNING: Cannot load data into reference table [DQ_AV_GeocodingStatus_infa]. Error message: [java.lang.Exception: [MPSVCCMN_10094] The Mapping Service Module failed to run the job with ID [J6

                o1mOBrEeqCmgYGu6KTEA] because of the following error: [EdtmExec_00007] CMN_1022 Database driver error...

                CMN_1022 [Database driver event...Error occurred loading library [libclntsh.so.11.1: cannot open shared object file: No such file or directory]Database driver event...Error occurred loading l

                ibrary [libpmora8.so]]

                 

                Please help to resolve this too.

                 

                While importing, Accelerator import window shows source location as "staging" by default. Should we need to change it? I do not have any schema by name "staging" created. But I have ensured correction Target location is chosen.

                • 5. Re: Error while importing Informatica Core Accelerator in Developer tool
                  Robert Whelan Guru

                  Can you check the $ORACLE_HOME/lib directory to confirm libclntsh.so.11.1 exists.

                   

                  You mentioned you use Oracle 12 so you may need to create a link.

                   

                  Also, please try running ldd $INFA_HOME/server/bin/libpmora8.so to confirm it can resolve the Oracle dependency.

                  • 6. Re: Error while importing Informatica Core Accelerator in Developer tool
                    Nico Heinze Guru

                    Changing the .profile file on the server is not enough. In order for the changed environment variables to be loaded and used, you have to shut down the Informatica domain, check that all environment variables are set correctly, and restart it.

                    Did you do that?

                     

                    Sorry for the seemingly stupid question, but there have been enough cases in the past where restarting the domain had not been performed.

                     

                    Regards,

                    Nico

                    • 7. Re: Error while importing Informatica Core Accelerator in Developer tool
                      Akanksha Rani Seasoned Veteran

                      Also adding ( as Namratha said earlier ), you can check envvars.txt & tomcat_envvars.txt located at $INFA_HOME/tomcat/temp on the Server machine to check what environment variables have been picked up after the Domain restart.

                      • 8. Re: Error while importing Informatica Core Accelerator in Developer tool
                        inuser526279 Guru

                        Yes, tried all the options suggested. Getting new issues.

                         

                        I'm getting below error while profiling.

                         

                        During profiling

                         

                        My masked tomcat_env file is BELOW:

                         

                         

                        $ cat  /apps/informatica/tomcat/temp/tomcat_envvars.txt

                        XDG_SESSION_ID=2627

                        HOSTNAME=################MASKED######################

                        SELINUX_ROLE_REQUESTED=

                        INFA_NODE_NAME=################MASKED######################

                        OSSINFO=/apps/informatica/DataTransformation

                        TERM=vt100

                        SHELL=/bin/bash

                        HISTSIZE=1000

                        IFCONTENTMASTER_HOME=/apps/informatica/DataTransformation

                        SSH_CLIENT=################MASKED######################

                        SELINUX_USE_CURRENT_RANGE=

                        OLDPWD=/apps/informatica/tomcat

                        SSH_TTY=/dev/pts/2

                        INFA_HOME=/apps/informatica

                        JRE_HOME=/apps/informatica/java/jre

                        USER=################MASKED######################

                        LD_LIBRARY_PATH=/apps/informatica/server/bin:/apps/informatica/java/jre/lib/amd64/server:/apps/informatica/services/shared/bin:/opt/oraClient/12.2.0.1_64/lib:/apps/informatica/DataTransformation/bin:/apps/informatica/java/jre/lib/amd64/server:/apps/informatica/java/jre/lib/amd64

                        LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:

                        PMBUILD_PLATFORM=linux64

                        A__z="*SHLVL

                        INFA_GC_OPTS=-XX:GCTimeRatio=9 -XX:+DisableExplicitGC -XX:+UseG1GC -XX:MaxGCPauseMillis=500 -XX:MaxGCPauseMillis=500

                        SYS_LOG_DIR=/apps/informatica/logs/Node01_UCH_POC

                        PATH=/apps/informatica/DataTransformation/bin:/apps/informatica/server/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/oraClient/12.2.0.1_64/bin:/opt/oraClient/12.2.0.1_64/bin:/opt/jdk/1.8.0_251l64

                        MAIL=/var/spool/mail/################MASKED######################

                        INFA_JAVA_OPTS=-Xmx1024m  -XX:MaxMetaspaceSize=256m -XX:+HeapDumpOnOutOfMemoryError -d64 -XX:+HeapDumpOnOutOfMemoryError

                        PWD=/apps/informatica/tomcat/bin

                        JAVA_HOME=/apps/informatica/java

                        LANG=en_US.UTF-8

                        SELINUX_LEVEL_REQUESTED=

                        KRB5CCNAME=KEYRING:persistent:1010491117

                        HISTCONTROL=ignoredups

                        SHLVL=6

                        HOME=/export/home/################MASKED######################

                        LOGNAME=################MASKED######################

                        CLASSPATH=:/apps/informatica/tomcat/bin/bootstrap.jar:/apps/informatica/tomcat/bin/tomcat-juli.jar:/apps/informatica/services/shared/jars/platform/infatomcatbootstrap.jar

                        SSH_CONNECTION=################MASKED######################

                        LESSOPEN=||/usr/bin/lesspipe.sh %s

                        XDG_RUNTIME_DIR=/run/user/1010491117

                        INFA_JRE_HOME=/apps/informatica/java/jre

                        ORACLE_HOME=/opt/oraClient/12.2.0.1_64

                        INFA_VERBOSE_OPTS=-verbose:gc -XX:+PrintGCDetails -XX:+PrintGCDateStamps -XX:+PrintGCTimeStamps -Xloggc:/apps/informatica/logs/Node01_UCH_POC_gc.log

                        _=/usr/bin/env

                        • 9. Re: Error while importing Informatica Core Accelerator in Developer tool
                          Akanksha Rani Seasoned Veteran

                          You can try setting the TNS_ADMIN environment variable pointing to the tnsnames.ora file.

                           

                          Usually, this file resides in the ORACLE_HOME\NETWORK\ADMIN directory.

                           

                          Also, check the entry in this tnsnames.ora file and ensure that the 'Connection String' specified in the Data Access Properties is the same as the respective entry in the tnsnames.ora file.

                           

                          Check the username and password as well.

                          • 10. Re: Error while importing Informatica Core Accelerator in Developer tool
                            inuser526279 Guru

                            Hi Akanksha,

                             

                            I have added TNS_ENTRY, and I have made domain to come down and started. I could see it in set in TOMCAT_ENV file also.But No luck. When I check the connection in Admin console, I get a message that "Connection successful".

                             

                            When I execute below command (as suggested by@Robert Whelan), still libclntsh.so.11.1 is not found.

                             

                             

                            bash-4.2$ ldd $INFA_HOME/server/bin/libpmora8.so

                                    linux-vdso.so.1 =>  (0x00007ffdeb1fd000)

                                    libdl.so.2 => /lib64/libdl.so.2 (0x00007f45cb69a000)

                                    libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f45cb47e000)

                                    libclntsh.so.11.1 => not found

                                    libpmi18n.so => not found

                                    libpmdec.so => not found

                                    libpmasrt.so => not found

                                    libpmcuti.so => not found

                                    libpmser.so => not found

                                    libpmuti.so => not found

                                    libpmodl.so => not found

                                    libpmlicense.so => not found

                                    libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f45cb177000)

                                    libm.so.6 => /lib64/libm.so.6 (0x00007f45cae75000)

                                    libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f45cac5f000)

                                    libc.so.6 => /lib64/libc.so.6 (0x00007f45ca891000)

                                    /lib64/ld-linux-x86-64.so.2 (0x00007f45cbae1000)

                            bash-4.2$ 

                             

                            I have ensured that softlink is available.

                             

                            • 11. Re: Error while importing Informatica Core Accelerator in Developer tool
                              Robert Whelan Guru

                              I've had to cheat slightly in the past regarding using a softlink as it didn't resolve correctly when running ldd.

                              To workaround it, instead of using a softlink I created a copy of the .so file i.e. if I had a softlink like libclntsh.so.11.1 -> libclntsh.so, I created a copy by running cp libclntsh.so libclntsh.so.11.1

                              • 12. Re: Error while importing Informatica Core Accelerator in Developer tool
                                inuser526279 Guru

                                Hi @Robert Whelan,

                                Please let me know what I must do further

                                • 13. Re: Error while importing Informatica Core Accelerator in Developer tool
                                  Robert Whelan Guru

                                  Hi,

                                  You can try running cp libclntsh.so.12.1 libclntsh.so.11.1

                                  This will replace the link with a copy of the Oracle v12 library.