Database Reconnect/Refresh

[Anonymous]

Hi,

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.

Kind regards,

K

[lojdr]

Hello,

can you please describe what the job should do? I don't understand it. Thanks.

Regards

Lojdr

[Anonymous]

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.

[lojdr]

Does the job still processing queries or waits for some time?

[Anonymous]

It processes one after another whilst the connection is still live.

The connection dies after around 30 minutes which is the problem.

[lojdr]

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.