7 Replies Latest reply: Jan 23, 2013 9:15 AM by 986695 RSS

    Database Connection Error in Informatica Workflow Task

    909384
      Hi,

      I am new in Informatica. I have created a mapping that has source of EBS instance and target of DWH. I have created a workflow and configure task properties with source and target connection. When I start task it gives error on source connection while this connection is working fine in mapping SQ. It gives following error.

      everity: ERROR
      Timestamp: 1/16/2013 11:01:37 AM
      Node: node01_enecora02.enec.gov.ae
      Thread: READER_1_1_1
      Process ID: 18126
      Message Code: RR_4036
      Message: Error connecting to database [
      Database driver error...
      Function Name : Logon
      ORA-12154: TNS:could not resolve the connect identifier specified

      Database driver error...
      Function Name : Connect
      Database Error: Failed to connect to database using user [xxapps] and connection string [source_conn].].

      source_conn is ODBC of source instance.
        • 1. Re: Database Connection Error in Informatica Workflow Task
          Voltaire
          Hi,

          Two ways to fix.

          1. Create a new ODBC Connection with name "source_conn" pointing to eBS.
          2. Modify the DAC Parameters in the Execution Plan to a valid ODBC Connection. Once the dac change is done Create a Relational Connection in Informatica Workflow Manager matching DAC Connection Name set earlier.

          Let me know if this worked.

          Regards,
          Jay
          • 2. Re: Database Connection Error in Informatica Workflow Task
            909384
            Hi,

            Thanks for reply.

            1. I have created some other odbc connections pointing to ebs source but same error.

            2. I am running this task from Informatica workflow Manager not from DAC. This task extacat data from ebs source and my target is a staging table in DWH.

            regards
            • 3. Re: Database Connection Error in Informatica Workflow Task
              Voltaire
              Hi,

              In such a case In Informatica Workflow Manager the Target/Source connections needs be configured in the Workflow Manager Relational Database Connections. The way Informatica Connects with the Database is by using the ODBC connection.

              So Create ODBC Connections to match Informatica Workflow Configuration.

              Let me know if this helped.

              Regards,
              Jay
              • 4. Re: Database Connection Error in Informatica Workflow Task
                909384
                Hi,

                I have created source and target Relational Connections in workflow manager and configure task Mapping in Task Developer. This sorce odbc connection is working finr in Source Qualifier in Mapping Designer but not working in workflow task. Below is the session log.

                Severity     Timestamp     Node     Thread     Message Code     Message
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6014     Initializing session [S_AP_Trans] at [Wed Jan 16 14:44:45 2013].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6683     Repository Name: [REPOS_SERVICE]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6684     Server Name: [INT_SERVICE]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6686     Folder: [CUSTOM_AP_SDE_ORA1212_Adaptor]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6685     Workflow: [wf_AP_Trans] Run Instance Name: [] Run Id: [34901]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6101     Mapping name: XX_AP_Mapping [version CheckedOut].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6963     Pre 85 Timestamp Compatibility is Enabled
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6964     Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6827     [d04/orabi/informatica/server/infa_shared/Storage] will be used as storage directory for session [S_AP_Trans].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1805     Recovery cache will be deleted when running in normal mode.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1802     Session recovery cache initialization is complete.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6708     Using configuration property [overrideMpltVarWithMapVar,Yes]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6703     Session [S_AP_Trans] is run by 64-bit Integration Service [node01_enecora02.enec.gov.ae], version [9.0.1 HotFix2], build [1111].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24058     Running Partition Group [1].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24000     Parallel Pipeline Engine initializing.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24001     Parallel Pipeline Engine running.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24003     Initializing session run.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     CMN_1569     Server Mode: [UNICODE]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     CMN_1570     Server Code page: [UTF-8 encoding of Unicode]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6151     The session sort order is [Binary].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6185     Warning. Code page validation is disabled in this session.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6156     Using low precision processing.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6180     Deadlock retry logic will not be implemented.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6187     Session target-based commit interval is [10000].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6307     DTM error log disabled.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TE_7022     TShmWriter: Initialized
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6007     DTM initialized successfully for session [S_AP_Trans]
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     PETL_24033     All DTM Connection Info: [<NONE>].
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24004     PETL_24004 Starting pre-session tasks. : (Wed Jan 16 14:44:45 2013)
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24027     PETL_24027 Pre-session task completed successfully. : (Wed Jan 16 14:44:45 2013)
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     DIRECTOR     PETL_24006     Starting data movement.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6660     Total Buffer Pool size is 4809512 bytes and Block size is 532168 bytes.
                INFO     1/16/2013 2:44:45 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     DBG_21438     Reader: Source is [source_conn], user [xxapps]
                INFO     1/16/2013 2:44:46 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     BLKR_16051     Source database connection [XXSource] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
                ERROR     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     RR_4036     Error connecting to database [
                Database driver error...
                Function Name : Logon
                ORA-12154: TNS:could not resolve the connect identifier specified

                Database driver error...
                Function Name : Connect
                Database Error: Failed to connect to database using user [xxapps] and connection string [source_conn].].
                ERROR     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     READER_1_1_1     BLKR_16001     Error connecting to database...
                INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24031     
                ***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****
                Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_AP_TRANS] has completed. The total run time was insufficient for any meaningful statistics.

                INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24005     PETL_24005 Starting post-session tasks. : (Wed Jan 16 14:49:21 2013)
                INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24029     PETL_24029 Post-session task completed successfully. : (Wed Jan 16 14:49:21 2013)
                INFO     1/16/2013 2:49:21 PM     node01_enecora02.enec.gov.ae     MAPPING     TM_6018     The session completed with [0] row transformation errors.
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     MANAGER     PETL_24002     Parallel Pipeline Engine finished.
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     PETL_24013     Session run completed with failure.
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6022     

                SESSION LOAD SUMMARY
                ================================================

                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6252     Source Load Summary.
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1740     Table: [SQ_AP_TRANS] (Instance Name: [SQ_AP_TRANS])
                     Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6253     Target Load Summary.
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     CMN_1740     Table: [W_XX_AP_XACT_FS] (Instance Name: [W_XX_AP_XACT_FS])
                     Output Rows [0], Affected Rows [0], Applied Rows [0], Rejected Rows [0]
                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6023     
                ===================================================

                INFO     1/16/2013 2:49:22 PM     node01_enecora02.enec.gov.ae     DIRECTOR     TM_6020     Session [S_AP_Trans] completed at [Wed Jan 16 14:49:22 2013].
                • 5. Re: Database Connection Error in Informatica Workflow Task
                  Voltaire
                  Hi,

                  Just to test in the Informatica Workflow Manager Manually set the Connection for Source and Target and try. You need to look out for lookup connections as well.

                  Let me know if this worked.

                  Regards,
                  Jay
                  • 6. Re: Database Connection Error in Informatica Workflow Task
                    909384
                    Hi,

                    How can I set db connections in source and target mannualy? I am using Relational Connection.

                    What exactly I have to put in Connecting String in Relational Connection Editor? ie: ODBC Name, SID, Host etc.

                    I have tried all about three but same error.

                    Regards

                    Farooq
                    • 7. Re: Database Connection Error in Informatica Workflow Task
                      986695
                      Connection string should be your TNS connection name like ORCL... and you should have that tns entry should present in tnsnames.ora file of informatica server machine.