Database Reconnect/Refresh

Five Stars

Database Reconnect/Refresh

Hi,

 

I have set up the job below, but the issue is the database disconnects after a period of time.

 

Capture.PNG

 

 

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

 

 

 

 

Eight Stars

Re: Database Reconnect/Refresh

Hello,

 

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

 

Regards

Lojdr

Five Stars

Re: Database Reconnect/Refresh

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.

 

Eight Stars

Re: Database Reconnect/Refresh

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

Five Stars

Re: Database Reconnect/Refresh

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

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

Re: Database Reconnect/Refresh

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.