4 Replies Latest reply: Jan 17, 2013 1:16 AM by luker RSS

    Closed Connection Error

    MaheshGx
      Hi In ODI i am getting following Error frequently.

      com.sunopsis.dwg.SQLWorkReposException: Closed Connection
           at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
           at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
           at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3127)
           at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:3042)
           at sun.reflect.GeneratedMethodAccessor64.invoke(Unknown Source)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:597)
           at oracle.ucp.jdbc.proxy.JDBCConnectionProxyFactory.invoke(JDBCConnectionProxyFactory.java:244)
           at $Proxy1.prepareStatement(Unknown Source)
           at sun.reflect.GeneratedMethodAccessor64.invoke(Unknown Source)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:597)
           at oracle.odi.core.datasource.support.RuntimeClassLoaderDataSourceCreator$ConnectionInvocationHandler.invoke(RuntimeClassLoaderDataSourceCreator.java:104)
           at $Proxy2.prepareStatement(Unknown Source)
           at com.sunopsis.sql.SnpsQuery.getPreparedStatement(SnpsQuery.java:977)
           at com.sunopsis.sql.SnpsQuery.getPreparedStatement(SnpsQuery.java:1018)
           at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java:1872)
           at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:554)
           at com.sunopsis.dwg.dbobj.generated.GeneratedSnpLockedObject.getObjectLst(GeneratedSnpLockedObject.java:368)
           at oracle.odi.ui.OdiDisconnectController.performDisconnect(OdiDisconnectController.java:163)
           at oracle.odi.ui.OdiAddin$8.canShutdown(OdiAddin.java:505)
           at oracle.ide.cmd.ExitCommand.canShutdown(ExitCommand.java:181)
           at oracle.ide.cmd.ExitCommand.doit(ExitCommand.java:357)
           at oracle.ide.controller.CommandProcessor.invoke(CommandProcessor.java:317)
           at oracle.ide.IdeCore.quit(IdeCore.java:785)
           at oracle.ide.Ide.quit(Ide.java:604)
           at oracle.ideimpl.MainWindowImpl$5.windowClosing(MainWindowImpl.java:380)
           at java.awt.AWTEventMulticaster.windowClosing(AWTEventMulticaster.java:332)
           at java.awt.AWTEventMulticaster.windowClosing(AWTEventMulticaster.java:332)
           at java.awt.AWTEventMulticaster.windowClosing(AWTEventMulticaster.java:332)
           at java.awt.Window.processWindowEvent(Window.java:1865)
           at javax.swing.JFrame.processWindowEvent(JFrame.java:274)
           at java.awt.Window.processEvent(Window.java:1823)
           at java.awt.Component.dispatchEventImpl(Component.java:4630)
           at java.awt.Container.dispatchEventImpl(Container.java:2099)
           at java.awt.Window.dispatchEventImpl(Window.java:2478)
           at java.awt.Component.dispatchEvent(Component.java:4460)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
           at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
           at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
           at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

      What would be reason of this?
      And how it can be prevented ?
        • 1. Re: Closed Connection Error
          actdi
          Looks like your network connection is not reliable. Does this close happen with Designer or while running the jobs.
          • 2. Re: Closed Connection Error
            MaheshGx
            Yes.

            It happens sometimes while ruuning jobs also sometimes just while working in ODI.
            • 3. Re: Closed Connection Error
              actdi
              As I said earlier, its due to your network. There are bursts in the network which cause ODI to lose packets and hence the connection is closed.

              Get your network team involved.
              • 4. Re: Closed Connection Error
                luker
                I've experienced this issue with running scenarios in Asynchronous mode where the source and target are the same database.

                I have 4 layers - landing (source table as-is), staging (only the columns I need from landing), integration (transformations based on staging), dimensional (creating dimensions and fact tables).

                I've come across this issue particularly from landing to staging, where it seems the landing packages aren't closing the connection in time for the staging packages to pick up the resource.

                There are a couple of things you can try:
                Set the initial and maximum connections in the weblogic connection pool for the ODI Master Repository. I've found this helps a little but still causes issue when loading large tables. The change can be made via ODI console in weblogic (port 7003 by default) -> navigate to Services -> Data Sources, select the ODI Master Repository, then select the Connection Pool tab - update the Initial Capacity and Maximum Capacity -> Lock & Edit then Activate/Save changes.

                Or - what I've found works without issue is:
                Make all scenarios Synchronous in the package or simply put interfaces in the package rather than scenarios (as they always run in serial).

                Cheers
                coolhand_luke