We've been trying to figure out a way to run a very long running (3-4 hours) stored procedure remotely using either Sybase's isql client or Java. The stored procedure starts fine and even runs and returns some print output. But then hits this very large select * into #tmp .... statement that takes over 3 hours to run. But somewhere after about 2 hours, the network connection is dropped...most likely due to in-activity as no print output comes back from the stored procedure and the isql client is not sending any requests.
Background : When we run this stored procedure remotely from a Linux batch server using the isql command line client, it starts, but then because of the length of time (3-4 hours), something in our network layers simply drops the connection and the Sybase server never responds back to the client. Same issue when we try and run the stored procedure using a GUI client like DBVisualizer on our laptops...remote execution starts, runs, but never responds. Basically, any attempt to run the stored procedure remotely fails.
Non-network : However, when we run the stored procedure ON the actual Sybase Unix server (I.e. the Sybase database and the isql client are on the same Unix server) using the isql client it DOES finish. So, when running the stored procedure in a "local" non-remote situation it finishes fine.
Conclusion : That something in the network layers simply drops the connection because of in-activity.
That said, We have tried the following without success :
- a.) Changed the Linux tcp_keepalive to 4 hours on the batch server.
- b.) Changed the Unix/AIX tcp_keepidle to 4 hours on the Sybase server.
- c.) Wrote a custom Java multi-thread process that created 2 threaded SQL calls, one for the Stored Procedure, one for a simple "keep-alive" select. But once the stored proc starts running it locks/blocks out the other thread. This appears to be a sympthom of the Sybase JDBC Drivers, they simply do NOT allow 2 SQL statements to run in parallel... Concurrent use of same JDBC connection by multiple threads and Is asynchronous jdbc call possible?.
My question is, is there any other options I've over-looked? We've been told by our Database admins that the Sybase engine has no limitations or timeouts set, but is there anyway to confirm this? We are currently checking our firewalls, but i'm skeptical that a firewall maintains a "session"...although I'm not a firewall expert. Again, is there anyway to test a firewall timeout?
tia, adym
UPDATE : 09/11/2017 -
I set the DEFAULT_QUERY_TIMEOUT to 8 hours (I think). From what I read, you provide this in seconds. But this made no difference.
Properties props = new Properties();
props.put ( "user", getJDBCUsername() );
props.put ( "password", getJDBCPassword(isEncrypted()) );
props.put ( "DEFAULT_QUERY_TIMEOUT", 28800 );
this.connection = DriverManager.getConnection ( getJDBCUrl (), props );
