I have set up the job below, but the issue is the database disconnects after a period of time.
Is there a way to reconnect/referesh the connection to ensure that everything is executed.
Error code below:
Starting job CashTransAllnewQuery at 08:46 21/02/2018. [statistics] connecting to socket on port 3417 [statistics] connected Exception in component tOracleInput_1 (CashTransAllnewQuery) java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at terecero.cashtransallnewquery_0_1.CashTransAllnewQuery.tFileInputDelimited_1Process(CashTransAllnewQuery.java:2152) at terecero.cashtransallnewquery_0_1.CashTransAllnewQuery.runJobInTOS(CashTransAllnewQuery.java:3141) at terecero.cashtransallnewquery_0_1.CashTransAllnewQuery.main(CashTransAllnewQuery.java:2990) Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection [statistics] disconnected at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:445) at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:464) at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:594) at oracle.net.ns.NSProtocol.connect(NSProtocol.java:229) at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1360) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:486) ... 9 more Caused by: java.net.ConnectException: Connection timed out: connect at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method) at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source) at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source) at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source) at java.net.AbstractPlainSocketImpl.connect(Unknown Source) at java.net.PlainSocketImpl.connect(Unknown Source) at java.net.SocksSocketImpl.connect(Unknown Source) at java.net.Socket.connect(Unknown Source) at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:162) at oracle.net.nt.ConnOption.connect(ConnOption.java:133) at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:411) ... 14 more Job CashTransAllnewQuery ended at 09:24 21/02/2018. [exit code=1]
Please could anyone help with this.
At the moment the job picks a number from the input file (one at a time/iterates) as a global variable and passes the number to the query, the data is then mapped accordingly to the table schema using tmap and the output is appended to a csv file on every iteration.
The problem is, the database connection is typically only live for 30 minutes, therefore the complete list of numbers from the input file arent processed.
I need to be able to refresh the database connection somehow so it completes the query against all the input rows.
Would be possible that the firewall between the Talend and Oracle database kill the connection after 30 minutes? Do you have any firewall there?
What is the operating system you run the job? Is it Red Hat?
I would also check the database log if is there any reason why the session was interrupted.