IBM i SQL Services provide a more flexible and efficient way to monitor and analyze network activity compared to traditional NETSTAT commands. The content walks through practical examples for capturing connection data, automating periodic collection, and documenting network configurations to support ongoing system administration.
IBM has been leveraging SQL services to gain access to various system functions for several versions now. Often, these services give you access to system administration functions that do not exist in CL commands. For instance, there are many CL commands to view information, but those often only offer a print option to save data for comparison or review later. But there is often an IBM I service offering that will let you run an SQL statement and get that same data into a file or exportable format.
These SQL services are being added to, or enhanced, all the time with the DB2 PTF groups and the Technology Refresh (TR) updates.
https://www.ibm.com/support/pages/ibm-i-services-sql
This series will give a tech note on utilizing some of these IBM I services as a systems administrator.
Today’s topic is about networking (NETSTAT) information on your system.
When diagnosing a network issue, the NETSTAT command interface gives some great information about current connections and routing information. It’s long been a go-to option to narrow down connection information. It has the options to present all connections, or you can subset to look for specific connection criteria. But, the downside is that once you are viewing it on the screen – there is no output options other than printing it. Also, you might want to get some detailed snapshots of the connections throughout the day if you are tracking certain connections – which NETSTAT on the command line is not as useful for collecting that information.
Although IBM is listing newer releases, the original QSYS2.NETSTAT_INFO service has been around since 7.1 TR11. It has been a useful method to capture and review TCP/IP connection information for close to a decade now. IBM has continued to make improvements to the service over the years – including introducing some off-shoots of the original service as well. In this exercise, we will look at these and how they can be useful in your daily systems administration.
QSYS2.NETSTAT_INFO
This is the original service, and it works similar to issuing the command NETSTAT OPTION(*CNN) on the command line. You will see a listing of all IPV4 connections that are currently active on your system.
Running a simple select statement in ‘Run SQL Scripts’ will give you an output of all current connections that are active on your system.
SELECT *
FROM QSYS2.NETSTAT_INFO;
Much in the same way that you can subset the NETSTAT command results on your screen, the use of some selection criteria within your SQL statement works the same way.
In this example, I am simply prompting (F4) in the ‘Run SQL Scripts’ on my original select statement to select specific fields that I want to see in my results. This service offers all the details that different function keys in the command line would offer – but you can limit those into a smaller set of output quickly by selecting the fields.
SELECT CONNECTION_TYPE,
REMOTE_ADDRESS,
REMOTE_PORT,
IDLE_TIME,
BIND_USER,
CONNECTION_TRANSPORT_LAYER
FROM QSYS2.NETSTAT_INFO;
So, let’s set up a problem that we are trying to capture: You want to see how many connections are coming into your system using a specific service – perhaps telnet for this instance. Along with that, you want to see how many of those are using secure telnet versus non-secure telnet ports. Here is how we can use SQL to gather that information quickly using the NESTAT_INFO service:
SELECT *
FROM QSYS2.NETSTAT_INFO
WHERE LOCAL_PORT_NAME LIKE '%telnet%'
ORDER BY REMOTE_ADDRESS;
As you can see from those results, we have a mixture of ports 23 (telnet) and 992 (secured telnet) in use on the system.
By changing the WHERE clause a bit, you can also use this same statement to review FTP connections as well.
SELECT *
FROM QSYS2.NETSTAT_INFO
WHERE LOCAL_PORT_NAME LIKE '%ftp%'
ORDER BY REMOTE_ADDRESS;
Let’s make another leap – assume you want to track these connections through specific internals during the day. We will borrow some processes from our earlier article on tracking ASP storage and create a file to put our captured data into periodically.
First, we will set up our table (file) to accept the information, and provide our first set of data into the file:
CREATE TABLE library.filename AS
(SELECT Current_DATE AS DATE,
Current_Time AS TIME,
CONNECTION_TYPE,
REMOTE_ADDRESS,
REMOTE_PORT,
REMOTE_PORT_NAME,
LOCAL_ADDRESS,
LOCAL_PORT,
LOCAL_PORT_NAME,
BIND_USER,
NUMBER_OF_ASSOCIATED_JOBS
FROM QSYS2.NETSTAT_INFO
WHERE LOCAL_PORT_NAME LIKE '%telnet%'
ORDER BY REMOTE_ADDRESS)
WITH DATA;
Notice that we are collecting the current date and time as part of our select statement, along with specific fields from the NETSTAT_INFO service. Now we have the start to our table, without the need to define any of the fields.
Now, we just need a statement to insert data into that file. We will reuse our SELECT statement, embedded into an INSERT statement.
Now we have data that spans an interval of time!
Next, let’s transition this SQL to the system command line:
Last step – integrate your RUNSQL into a scheduled job that will run at the intervals you like. This will add data to your file until you choose to stop collecting that information. Be mindful of how often you run this job and check your data file size and record count regularly to make sure you are not taking up too much storage.
QSYS2.NETSTAT_JOB_INFO
This service focuses on the jobs that are tied to specific TCP/IP connections on your system. We will shorten this section to a single example, pivoting on the statements and examples from the NETSTAT_INFO section above.
This service has similar information to the NETSTAT_INFO, but it gathers a bit more related to the jobs that are using the network connections versus the raw connectivity stats.
In this example, we are going to use the TELNET connections type statement again – but focus more on the job and use information instead of just raw connection data.
SELECT Current_DATE AS DATE,
Current_Time AS TIME,
CONNECTION_TYPE,
REMOTE_ADDRESS,
REMOTE_PORT,
REMOTE_PORT_NAME,
LOCAL_ADDRESS,
LOCAL_PORT,
LOCAL_PORT_NAME,
AUTHORIZATION_NAME,
JOB_NAME_SHORT,
JOB_USER,
JOB_NUMBER
FROM QSYS2.NETSTAT_JOB_INFO
WHERE LOCAL_PORT_NAME LIKE '%telnet%'
ORDER BY REMOTE_ADDRESS;
This produces information about the jobs that are using those telnet connections. (I purposely left out some of the fields so I had less to blur on the results.)
Use the examples from the NETSTAT_INFO section to create your own table (file) for tracking these results if you like!
QSYS2.NETSTAT_INTERFACE_INFO and QSYS2.NETSTAT_ROUTE_INFO
These two services work to provide you with information about your currently defined TCP/IP interfaces and routing information. It is not often that these items change (most likely), but using SQL is a good way to keep your interface and route information documented however.
In these examples, we simply want to review (or perhaps document) our existing interface and route information. It might be a handy item to keep in a table (file) as well for future reference.
SELECT *
FROM QSYS2.NETSTAT_INTERFACE_INFO;
There are a lot of fields beyond this screenshot – explore the information on your own system to see what is available.
SELECT *
FROM QSYS2.NETSTAT_ROUTE_INFO;
The routing information is especially handy if you have multiple routes and destinations defined. As with the NETSTAT_INTERFACE_INFO output, there are many more fields to review.
Use the previous examples to utilize these for documentation purposes if you like as well. You can use the SELECT with data option to create a table for each output, and then update them on a scheduled job with an INSERT INTO as well. Don’t forget your current date and time fields so that you know when those entries were recorded.