Solved

AS400 - How to test for connectivity

Posted on 2013-12-02
5
563 Views
Last Modified: 2013-12-02
I have a Java program that interacts with an AS400 database.  I am trying to code a work-around for a connectivity problem I am facing.  Sometimes for various reason, after I establish a connection to the database, the connection will be lost (idle timeout, loss of network connection, etc.).  In those cases, I need to be able to check whether the connection is still active, and if not, then I need to re-connect.  In order to test for connectivity, I would like to simply execute some generic SQL statement and check whether it was successful.  Is there any kind of generic "dummy" SQL statement or command that can be run on any AS400 system which will allow me to know whether the connection object is still valid or now?

For example, here is how I do this same operation using both Oracle and MSSQL:

Oracle:   "select 'success' from dual"
MSSQL:  "select 'success'"

Both of those statements work fine for Oracle and MSSQL.  I am looking for something roughly equivalent on the AS400.

Thanks.
0
Comment
Question by:jbaird123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
Dave Ford earned 500 total points
ID: 39690917
Simple enough ...

select 'success'
  from sysibm.sysdummy1

Open in new window


HTH,
DaveSlash
0
 

Author Closing Comment

by:jbaird123
ID: 39690962
Perfect!  Thanks!
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 39691051
How long is the period of time before you experience a connection drop?  Usually this is due to an intermediate firewall, though there is an IBM i TCP connection timer that will eventually drop a connection due to inactivity.  This is a system-wide setting.

Check your firewall log (may need to enable logging of inactive session timeout events), and the QTCP message queue on the IBM i for relevant messages.

Sometimes you can fix this by specifying a longer timeout on the host or firewall.

If you are writing the application, you could just run a small request like the one above just frequently enough to keep the connection "alive" - provided you can determine the idle timeout period.

Also, you can look at the connection in NETSTAT, option 3 on the iSeries and watch the change in status as the connection gets older and older.

This is a common issue, and it can be tricky to resolve unless you are the programmer writing the application - then you can include some sort of (I suggest configurable, since the value could change at the whim of a network admin) keepalive timer.
0
 

Author Comment

by:jbaird123
ID: 39691075
Gary,

Thanks for the info.  I am essentially working on the keep alive timer that you suggest.  I have a situation where a connection is established and then sits idle for a couple of hours while an unrelated process completes.  The issue is that the unrelated process could take anywhere from a couple of seconds to several hours (and there is no way to know in advance how long it will take), and since this connection is used prior to this unrelated process, I cannot wait until after the process is complete to establish the connection.  I basically just need to check whether it is still active after sitting idle for so long and if not, then establish a new connection, otherwise, use the old connection.

Thanks.
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 39691193
Here is my suggestion:

1) Connect
2) Submit job
3) Disconnect
3) Set up some sort of notification mechanism in job - put a message on a data queue when it it done (IBM has PC data queue APIs for Java, legacy Windows (C/VB), and .NET).  Even better, just send a little text file via FTP to the client system
4) Watch for the notification event, and reconnect and move on - no overhead on the host, and easy to implement.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Getting to know the threat landscape in which DDoS has evolved, and making the right choice to get ourselves geared up to defend against  DDoS attacks effectively. Get the necessary preparation works done and focus on Doing the First Things Right.
The recent Petya-like ransomware attack served a big blow to hundreds of banks, corporations and government offices The Acronis blog takes a closer look at this damaging worm to see what’s behind it – and offers up tips on how you can safeguard your…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question