Using SQL to ping remote servers

Included as part of the recent Technology Refreshes, IBM i TR3 and 7.4 TR9, comes a new Db2 for i, or SQL, table function that allows me to ping a remote server and get the result in one row.

The PING table function has a number of parameters you can use with it:

1. REMOTE_SYSTEM:  This is where I would use the name of the remote server. There is a default value of *INTNETADR, which notifies the table function I am wanting to ping with the IP address.
2. REMOTE_IP_ADDRESS:  The remote server's IP address. If REMOTE_SYSTEM is not *INTNETADR then this parameter is ignored.
3. ADDRESS_VERSION_FORMAT:  Which IP address format to use. Allowed values are *IP4, *IP6, and *CALC which is the default.
4. NUMBER_OF_PACKETS_TO_SEND:  Number of packets to send. Allowed range is 1-999, default is 5.
5. PACKET_LENGTH_TO_SEND:  Length of the packet to send. Allowed range is 8 – 65500, 256 is the default.
6. WAIT_TIME:  Number of seconds to wait for the return packet. Allowed range is 1 – 120, default is 1.
7. LOCAL_IP_ADDRESS:  Local internet address that the outbound packets use. Can be an IP4 or IP6 address, or default which is *ANY.

In all my testing I only used the first two: REMOTE_SYSTEM and REMOTE_IP_ADDRESS.

My first examples are when I first tried the PING table function in ACS's Run SQL Script.

I am using one of RZKH's IBM i partitions, DEV750, and in this example I am trying to ping another, DEV740:

01 SELECT * 02 FROM TABLE(SYSTOOLS.PING( 03 REMOTE_SYSTEM => 'DEV740'))

Line 3: As I am using the name of the server I need to use the REMOTE_SYSTEM parameter.

Nine columns are returned in the results:

PACKAGE_ PACKAGES RESPONSES ROUND_ ROUND_ ROUND_ RESULT SUCCESSFUL _SENT _RETURNED TRIP_AVG TRIP_MIN TRIP_MAX ------- ---------- -------- --------- -------- -------- -------- SUCCESS 100 5 5 0 0 0 REMOTE_ REMOTE_ HOST_NAME IP_ADDRESS ---------------------- ------------ DEV740.POWERBUNKER.COM 89.31.143.90

IMHO the most important columns in the results are the first two:

1. RESULT:  Was the ping successful. Valid values are 'SUCCESS' and 'FAILURE'.
2. PACKAGE_SUCCESSFUL:  This is the percentage of the package response this server received from the remote server.

While the other columns are somewhat interesting, I am really only concerned if my ping was successful.

I can also ping DEV740 using its IP address:

01 SELECT RESULT,PACKAGE_SUCCESFUL 02 FROM TABLE(SYSTOOLS.PING( 03 REMOTE_IP_ADDRESS => '89.31.143.90'))

Line 1: I just want to know if the ping was successful, therefore, I only want the results and percentage of successful packages.

Line 3: I use the REMOTE_IP_ADDRESS parameter for DEV740's IP address.

The results show that my ping was successful.

PACKAGE_ RESULT SUCCESFUL ------- --------- SUCCESS 100

What does the PING table function return if the ping unsuccessful?

01 SELECT * 02 FROM TABLE(SYSTOOLS.PING( 03 REMOTE_IP_ADDRESS => '89.1.1.50'))

Line 1: I want to return all the columns.

Line 3: This is an invalid IP address.

The results were:

PACKAGE_ PACKAGES RESPONSES ROUND_ ROUND_ ROUND_ RESULT SUCCESSFUL _SENT _RETURNED TRIP_AVG TRIP_MIN TRIP_MAX ------- ---------- -------- --------- -------- -------- -------- FAILURE 0 5 0 <NULL> <NULL> <NULL> REMOTE_ REMOTE_ HOST_NAME IP_ADDRESS ---------- ---------- *INTNETADR 89.1.1.50

The result column contains 'FAILURE', and zero of the packages were successful.

I decided to ping something that is not in the RZKH network, Google:

01 SELECT RESULT,PACKAGE_SUCCESFUL 02 FROM TABLE(SYSTOOLS.PING('google.com'))

The first parameter of PING is the remote host name, therefore, I can just pass the remote server's without the parameter's name.

The result was successful:

PACKAGE_ RESULT SUCCESFUL ------- --------- SUCCESS 100

Playing with this table function in Run SQL Scripts gives you an idea of what it can do. How about a more practical example? Using PING in a RPG program:

01 **free 02 ctl-opt option(*srcstmt) dftactgrp(*no) ; 03 dcl-pr PingIP char(9) ; 04 *n varchar(30) const ; 05 end-pr ; 06 dcl-pr PingHost char(9) ; 07 *n varchar(30) const ; 08 end-pr ; 09 dcl-ds Results qualified ; 10 Result char(7) ; 11 PercentSuccessful packed(3) ; 12 end-ds ; 13 Results = PingIP('89.31.143.90') ; 14 dsply ('1. ' + Results.Result + ' ' + %char(Results.PercentSuccessful) + '%') ; 15 Results = PingHost('DEV740') ; 16 dsply ('2. ' + Results.Result + ' ' + %char(Results.PercentSuccessful) + '%') ; 17 Results = PingIP('89.1.1.50') ; 18 dsply ('3. ' + Results.Result + ' ' + %char(Results.PercentSuccessful) + '%') ;

Line 1: Like all my recent examples, my RPG is always modern RPG.

Line 2: My favorite control option, and I cannot be in the default activation group as I am calling a couple of procedures.

Lines 3 – 5: The first procedure protype for PingIP, it will ping using the IP address. It has one input parameter, line 4, that is a constant. And returns a character nine value, line 3.

Lines 6 – 8: The procedure prototype for the second procedure, PingHost, is identical to the first.

Lines 9 – 12: The character nine value returned by the procedures will be placed in this data structure. The data structure is made up of two subfields: the result and the percentage of successful packets.

Line 13: I call PingIP passing an IP address. The result from the procedure is placed in the Results data structure.

Line 14: I use the Display operation code, DSPLY, to show the two subfields in an easy-to-read string.

Line 15: I call PingHost passing a host name. The result from the procedure is placed in the Results data structure.

Line 16: I display what was returned using the DSPLY operation code.

Line 17: I call PingIP with an invalid IP address.

Line 18: And display the results.

What happens in those procedures? Let me start with PingIP:

20 dcl-proc PingIP ; 21 dcl-pi *n char(9) ; 22 PingThis varchar(30) const ; 23 end-pi ; 24 dcl-ds FromTheSQL ; 25 Result char(7) ; 26 Percent packed(3) ; 27 end-ds ; 28 exec sql SELECT RESULT,PERCENT_SUCCESSFUL 29 INTO :Result, :Percent 30 FROM TABLE(SYSTOOLS.PING( 31 REMOTE_IP_ADDRESS => :PingThis)) ; 32 return FromTheSQL ; 33 end-proc ;

Lines 21 – 23: The procedure interface must match the procedure prototype. Here I have named the passed parameter: PingThis.

Lines 24 – 27: This data structure is for the results of the SQL statement to return back to the calling program. Notice that the data structure is not qualified.

Lines 28 – 31: The SQL statement using the PING table function.

Line 28: I am only interested in these two columns.

Line 29: I move the value of those columns into these data structure subfields.

Line 32: I return the values in the data structure to the calling program.

The other procedure, PingHost looks very similar:

40 dcl-proc PingHost ; 41 dcl-pi *n char(9) ; 42 PingThis varchar(30) const ; 43 end-pi ; 44 dcl-ds FromTheSQL ; 45 Result char(7) ; 46 Percent packed(3) ; 47 end-ds ; 48 exec sql SELECT RESULT,PERCENT_SUCCESSFUL 49 INTO :Result, :Percent 50 FROM TABLE(SYSTOOLS.PING( 51 REMOTE_SYSTEM => :PingThis)) ; 52 return FromTheSQL ; 53 end-proc ;

The only difference between the two procedures is the parameter passed to the PING table function. In this procedure I have used the remote system name.

After compiling the program, I then called it. The following was displayed:

DSPLY 1. SUCCESS 100% DSPLY 2. SUCCESS 100% DSPLY 3. FAILURE 0%

The results mirror the results when I performed the same pings with Run SQL Scripts.

 

You can learn more about the PING SQL table function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.