NETSTAT_INFO view

The NETSTAT_INFO view returns information about IPv4 and IPv6 network connections.

The values returned for the columns in the view are closely related to the values returned by List Network Connections API and Retrieve Network Connection Data API. Refer to the APIs for more detailed information.

Authorization: None required.

The following table describes the columns in the view. The system name is NS_INFO. The schema is QSYS2.

Table 1. NETSTAT_INFO view
Column Name System Column Name Data Type Description
CONNECTION_TYPE CONN_TYPE CHAR(4) The type of connection.
IPV4
The connection is an IPv4 connection.
IPV6
The connection is an IPv6 connection.
REMOTE_ADDRESS RMT_ADDR VARCHAR(45) The internet address of the remote host.
For IPv4:
  • The address is in IPv4 address format. A value of 0.0.0.0 indicates that either the system is waiting for a connection to open or that a UDP socket is being used. A value of 0 means that the connection is a listening or UDP socket so this field does not apply.
For IPv6:
  • The address is in IPv6 address format. A value of :: means that the connection is a listening socket so this field does not apply.
REMOTE_PORT RMT_PORT INTEGER The remote host port number. A value of 0 means that the connection is a listening or UDP socket, so this field does not apply.
REMOTE_PORT_NAME RMT_NAME VARGRAPHIC(14) CCSID 1200
Nullable
 The remote host well-known port name or the name from the service table entry.

Contains null if there is no well-known port name.
LOCAL_ADDRESS LOCAL_ADDR VARCHAR(45) The local address of this connection on this system.
For IPv4:
  • The address is in IPv4 address format. A value of 0.0.0.0 indicates that either the system is waiting for a connection to open or that a UDP socket is being used.
For IPv6:
  • The address is in IPv6 address format. A value of :: means the local application specified that any local internet address can be used.
LOCAL_PORT LOCAL_PORT INTEGER The local system port number.
LOCAL_PORT_NAME LOCAL_NAME VARGRAPHIC(14) CCSID 1200
Nullable
 The local system well-known port name or the name from the service table entry.

Contains null if there is no well-known port name.
PROTOCOL PROTOCOL VARCHAR(3) Identifies the type of connection protocol.
TCP
A Transmission Control Protocol (TCP) connection or socket.
UDP
A User Datagram Protocol (UDP) socket.
TCP_STATE STATE VARCHAR(12)
Nullable
 The state of the connection.
CLOSED
This connection has ended.
CLOSE-WAIT
Waiting for an end connection request from the local user.
CLOSING
Waiting for an end connection request acknowledgment from the remote host.
ESTABLISHED
The normal state in which data is transferred.
FIN-WAIT-1
Waiting for the remote host to acknowledge the local system request to end the connection.
FIN-WAIT-2
Waiting for the remote host request to end the connection.
LAST-ACK
Waiting for the remote host to acknowledge an end connection request.
LISTEN
Waiting for a connection request from any remote host.
SYN-RECEIVED
Waiting for a confirming connection request acknowledgment.
SYN-SENT
Waiting for a matching connection request after having sent a connection request.
TIME-WAIT
Waiting to allow the remote host enough time to receive the local system's acknowledgment to end the connection.

Contains null if PROTOCOL is UDP.
IDLE_TIME IDLE_TIME DECIMAL(19,3) The length of time, in seconds, since the last activity on this connection.
BIND_USER BIND_USER VARCHAR(10) The user profile of the job on the local system which first performed a sockets API bind() of the socket.
BYTES_SENT_REMOTELY BYTES_OUT BIGINT The number of bytes sent to the remote host.
BYTES_RECEIVED_LOCALLY BYTES_IN BIGINT The number of bytes received from the remote host.
NETWORK_CONNECTION_TYPE NET_TYPE VARCHAR(4) The type of connection or socket.
*TCP
Identifies a transmission control protocol (TCP) connection socket.
*UDP
Identifies a User Datagram Protocol (UDP) socket.

For IPv4, the following additional value can be returned.

*IPS
Identifies an Internet Protocol (IP) over SNA connection or socket.
CONNECTION_OPEN_TYPE OPN_TYPE VARCHAR(7)
Nullable
 The type of open for the connection.
ACTIVE
The local system opens the connection.
PASSIVE
A remote host opens the connection.

Contains null if PROTOCOL is UDP.
NUMBER_OF_ASSOCIATED_JOBS NUM_JOBS INTEGER The number of jobs associated with this connection.
LINE_DESCRIPTION LINE_DES VARCHAR(10)
Nullable
 The local system line description associated with this connection.

Contains null if this is an IPv4 connection or if the connection is not bound to a link local unicast interface.
VIRTUAL_LAN_ID LAN_ID VARCHAR(4)
Nullable
 The virtual LAN identifier associated with this connection. Can also contain the following special value:
NONE
No virtual LAN identifier is associated with this connection.

Contains null if this is an IPv4 connection or if the connection is not bound to a link local unicast interface.
CONNECTION_TRANSPORT_
LAYER
 CNNTRANSPT VARCHAR(5) The transport that a connection is using. Values are:
  • IPS
  • TCPIP
IP_OPTIONS IP_OPTIONS BINARY(40)
Nullable
 The hex value of IP datagram options that may have been specified for a connection.

Contains null if this is an IPv6 connection or if no IP datagram options have been specified.
ROUND_TRIP_TIME ROUND_TRIP BIGINT
Nullable
 The smoothed round-trip time interval in milliseconds. This is a measure of the time required for a segment on the connection to arrive at its destination, to be processed, and to return an acknowledgment to the client.

Contains null if PROTOCOL is UDP.
ROUND_TRIP_VARIANCE ROUND_VAR BIGINT
Nullable
 The variance in milliseconds from the previous round-trip time.

Contains null if PROTOCOL is UDP.
CURRENT_RETRANSMISSIONS CT_RETRANS BIGINT
Nullable
 The number of times the local system retransmitted the current segment without receiving an acknowledgment.

Contains null if PROTOCOL is UDP.
TOTAL_RETRANSMISSIONS TL_RETRANS BIGINT
Nullable
 The total number of times the local system retransmitted a segment because an acknowledgement was not received. This is a cumulative count of all segments resent during the entire time the connection has been active.

Contains null if PROTOCOL is UDP.
TCP_CONNECTIONS_
CURRENTLY_ESTABLISHED
 TCPCONN BIGINT
Nullable
 The number of TCP connections for which the current state is either ESTABLISHED or CLOSE-WAIT.

Contains null if PROTOCOL is UDP.
TCP_ACTIVE_OPENS TCPACTOPN BIGINT
Nullable
 The number of times TCP connections have made a direct transition to the SYN-SENT state from the CLOSED state. This number is an indication of the number of times this local system opened a connection to a remote system.

Contains null if PROTOCOL is UDP.
TCP_PASSIVE_OPENS TCPPSVOPN BIGINT
Nullable
 The number of times TCP connections have made a direct transition to the SYN-RECEIVED state from the LISTEN state. This number is an indication of the number of times a remote system opened a connection to this system.

Contains null if PROTOCOL is UDP.
TCP_FAILED_OPENS TCPFAILOPN BIGINT
Nullable
 The total number of times TCP connections have made direct transitions to a CLOSED state from either the SYN-SENT state or the SYN-RECEIVED state and/or to LISTEN from SYN-RECEIVED.

Contains null if PROTOCOL is UDP.
TCP_ESTABLISHED_AND_THEN_
RESET
 TCPESTRST BIGINT
Nullable
 The number of times TCP connections have made a direct transition to the CLOSED state from either the ESTABLISHED state or the CLOSE-WAIT state.

Contains null if PROTOCOL is UDP.
TCP_SEGMENTS_SENT TCPSEGSENT BIGINT
Nullable
 The total number of segments sent, including those on current connections but excluding those containing only retransmitted octets.

Contains null if PROTOCOL is UDP.
TCP_SEGMENTS_
RETRANSMITTED
 TCPSEGRTRN BIGINT
Nullable
 The number of TCP segments transmitted containing one or more previously transmitted octets.

Contains null if PROTOCOL is UDP.
TCP_SEGMENTS_RESET TCPSEGRST BIGINT
Nullable
 The number of TCP segments sent containing the RST flag.

Contains null if PROTOCOL is UDP.
TCP_SEGMENTS_RECEIVED TCPSEGRCV BIGINT
Nullable
 The total number of segments received, including those received in error. This count includes segments received on currently established connections.

Contains null if PROTOCOL is UDP.
TCP_SEGMENTS_RECEIVED_
ERROR
 TCPSEGRCVE BIGINT
Nullable
 The total number of segments received in error (for example, bad TCP checksums).

Contains null if PROTOCOL is UDP.
OUTGOING_BYTES_BUFFERED BYTES_OUTB BIGINT
Nullable
 The current number of bytes that an application has requested to send, but TCP has not yet sent. If TCP has sent the bytes to the remote system but has not yet received an acknowledgment, the bytes are considered 'not sent'. They are included in this count.

Contains null if PROTOCOL is UDP.
USER_SEND_NEXT USRSNDNXT BIGINT
Nullable
 The sequence number of the next byte of data to be sent by the client application.

Contains null if PROTOCOL is UDP.
SEND_NEXT SEND_NEXT BIGINT
Nullable
 The sequence number of the next byte of data that the local TCP application sends to the remote TCP application.

Contains null if PROTOCOL is UDP.
SEND_UNACKNOWLEDGED SNDUNACK BIGINT
Nullable
 The sequence number of the last segment sent that was not acknowledged. This is the smallest sequence number of the send window.

Contains null if PROTOCOL is UDP.
OUTGOING_PUSH_NUMBER OUTPSHNBR BIGINT
Nullable
 The sequence number of the last byte of push data in the outgoing stream. This value is zero if no push data is in the outgoing data stream.

Contains null if PROTOCOL is UDP.
OUTGOING_URGENCY_NUMBER OUTURGNBR BIGINT
Nullable
 The sequence number of the last byte of urgent data in the outgoing data stream. This value is zero if no urgent data is in the outgoing data stream.

Contains null if PROTOCOL is UDP.
OUTGOING_WINDOW_NUMBER OUTWINNBR BIGINT
Nullable
 The largest sequence number in the send window of the connection. The local TCP application cannot send data bytes with sequence numbers greater than the outgoing window number.

Contains null if PROTOCOL is UDP.
INCOMING_BYTES_BUFFERED BYTES_INB BIGINT
Nullable
 The current number of bytes that are received and buffered by TCP. These bytes are available to be read by an application.

Contains null if PROTOCOL is UDP.
RECEIVE_NEXT RCVNEXT BIGINT
Nullable
 The next sequence number the local TCP is expecting to receive.

Contains null if PROTOCOL is UDP.
USER_RECEIVE_NEXT USRRCVNXT BIGINT
Nullable
 The sequence number of the next byte to be passed to the application by TCP.

Contains null if PROTOCOL is UDP.
INCOMING_PUSH_NUMBER INPSHNBR BIGINT
Nullable
 The sequence number of the last byte of pushed data in the incoming data stream. This value is zero if no push data is in the incoming data stream.

Contains null if PROTOCOL is UDP.
INCOMING_URGENCY_NUMBER INURGNBR BIGINT
Nullable
 The sequence number of the last byte of urgent data in the incoming data stream. This value is zero if no urgent data is in the incoming data stream.

Contains null if PROTOCOL is UDP.
INCOMING_WINDOW_NUMBER INWINNBR BIGINT
Nullable
 The largest sequence number in the incoming window of this connection. Data bytes in the incoming stream having sequence numbers larger than this number are not accepted.

Contains null if PROTOCOL is UDP.
MAXIMUM_WINDOW_SIZE MAXWINSIZ BIGINT
Nullable
 The largest size of the send window, in bytes, during the entire time the connection has been active.

Contains null if PROTOCOL is UDP.
CURRENT_WINDOW_SIZE CURWINSIZ BIGINT
Nullable
 The current send window size in bytes.

Contains null if PROTOCOL is UDP.
LAST_UPDATE LSTUPD BIGINT
Nullable
 The sequence number of the incoming segment used for the last window update that occurred on the connection.

Contains null if PROTOCOL is UDP.
LAST_UPDATE_ACKNOWLEDGED LSTUPDACK BIGINT
Nullable
 The acknowledgment number of the incoming segment used for the last window update that occurred on the connection.

Contains null if PROTOCOL is UDP.
CONGESTION_WINDOW CONGESTWIN BIGINT
Nullable
 The number of segments that are sent on the next transmission. If an acknowledgment is received, the number is increased. If an acknowledgment is not received, the number is reset to the smallest allowable number.

Contains null if PROTOCOL is UDP.
SLOW_START_THRESHOLD SLWSTRTHR BIGINT
Nullable
 The value of the slow-start threshold.

Contains null if PROTOCOL is UDP.
MAXIMUM_SEGMENT_SIZE MAXSEGSIZ BIGINT
Nullable
 The size in bytes of the largest segment that may be transmitted on this connection.

Contains null if PROTOCOL is UDP.
INITIAL_SEND_SEQUENCE_
NUMBER
 SNDSEQNBR BIGINT
Nullable
 The first sequence number sent on this connection.

Contains null if PROTOCOL is UDP.
INITIAL_RECEIVE_SEQUENCE_
NUMBER
 RCVSEQNBR BIGINT
Nullable
 The first sequence number received on this connection.

Contains null if PROTOCOL is UDP.
UDP_DATAGRAMS_SENT UDPSENT BIGINT
Nullable
 The total number of UDP datagrams sent from all connections since TCP/IP was started.

Contains null if PROTOCOL is TCP.
UDP_DATAGRAMS_RECEIVED UDPRCV BIGINT
Nullable
 The total number of UDP datagrams received, including those received in error. This count includes datagrams received on currently established connections.

Contains null if PROTOCOL is TCP.
UDP_DATAGRAMS_NOT_
DELIVERED_PORT_
NOT_FOUND
 UDPNDPNF BIGINT
Nullable
 The total number of received UDP datagrams for UDP users for which there was no application at the destination port.

Contains null if PROTOCOL is TCP.
UDP_DATAGRAMS_NOT_
DELIVERED_OTHER
 UDPNDOTHER BIGINT
Nullable
 The number of received UDP datagrams that could not be delivered for reasons other than the lack of an application at the destination port.

Contains null if PROTOCOL is TCP.
SOCKET_STATE SOCSTATE VARCHAR(13) The current state of the socket. Values are:
  • BOUND
  • CONNECTED
  • CONNECTING
  • DISCONNECTED
  • ERROR
  • LISTENING
  • UNBOUND
  • UNINITIALIZED
SOCKET_BROADCAST SOCBROAD VARCHAR(3)
Nullable
 Indicates if messages can be sent to the broadcast address.
NO
Messages cannot be sent to the broadcast address.
YES
Messages can be sent to the broadcast address.

Contains null if value is not specified or if socket is not an address family of AF_INET and type SOCK_DGRAM or SOCK_RAW.
SOCKET_BYPASS_ROUTE SOCBYPASS VARCHAR(3)
Nullable
 Indicates if the normal routing mechanism is being bypassed.
NO
The normal routing mechanism is being used.
YES
The normal routing mechanism is being bypassed.

Contains null if value is not specified or if socket is not an address family of AF_INET or AF_INET6.
SOCKET_DEBUG SOCDEBUG VARCHAR(3)
Nullable
 Indicates if low-level debugging is active.
NO
Low-level debugging is not active.
YES
Low-level debugging is active.

Contains null if value is not specified.
SOCKET_ERROR SOCERROR INTEGER
Nullable
 Indicates if there any pending errors in the socket. A value of zero indicates no pending errors. Otherwise, the value indicates the error number.

Contains null if value is not specified.
SOCKET_KEEP_ALIVE SOCALIVE VARCHAR(3)
Nullable
 Indicates if the connection is being kept up by periodic transmissions.
NO
The connection is not being kept up by periodic transmissions.
YES
The connection is being kept up by periodic transmissions.

Contains null if value is not specified or if socket is not an address family of AF_INET or AF_INET6 and type SOCK_STREAM.
SOCKET_LINGER SOCLINGER VARCHAR(3)
Nullable
 Indicates whether the system attempts to deliver any buffered data or if the system discards it when a close() is issued.
NO
The system attempts to send buffered data with an infinite wait time.
YES
The system attempts to send buffered data for SOCKET_LINGER_TIME seconds. If the data is not deliverable within that period of time, it is discarded.

Contains null if value is not specified.
SOCKET_LINGER_TIME SOCLTIME BIGINT
Nullable
 The time, in seconds, the system will wait to send buffered data.

Contains null if value is not specified or if SOCKET_LINGER is NO.
SOCKET_OUT_OF_BAND_DATA SOCOUTBAND VARCHAR(3)
Nullable
 Indicates if out-of-band data is received inline with normal data.
NO
Out-of-band data is not received inline with normal data.
YES
Out-of-band data is received inline with normal data.

Contains null if value is not specified or if socket is not an address family of AF_INET or AF_INET6.
SOCKET_RECEIVE_BUFFER_SIZE SOCRCVBUF BIGINT
Nullable
 The size of the receive buffer.

Contains null if value is not specified.
SOCKET_RECEIVE_LOW_WATER_
MARK_SIZE
 SOCRCVSZ BIGINT
Nullable
 The size of the receive low-water mark. The default size is 1.

Contains null if value is not specified or if socket is not type SOCK_STREAM.
SOCKET_REUSE_ADDRESS SOCREUSE VARCHAR(3)
Nullable
 Indicates if the local socket address can be reused.
NO
The local socket address cannot be reused.
YES
The local socket address can be reused.

Contains null if value is not specified or if socket is not an address family of AF_INET or AF_INET6 and type SOCK_STREAM or SOCK_DGRAM.
SOCKET_SEND_BUFFER_SIZE SOCSENDBUF BIGINT
Nullable
 The size of the send buffer.

Contains null if value is not specified.
SOCKET_TYPE SOCTYPE VARCHAR(14)
Nullable
 The socket type. Values are:
SOCK_DGRAM
Datagram type.
SOCK_RAW
Raw type.
SOCK_SEQPACKET
Sequential packet type.
SOCK_STREAM
Stream type.

Contains null if value is not specified.
SOCKET_LOOPBACK SOCLOOPBK VARCHAR(3)
Nullable
 Indicates if the loopback feature is being used.
NO
The loopback feature is not being used.
YES
The loopback feature is being used.

Contains null if value is not specified.
SOCKET_RECEIVE_TIMEOUT SOCRCVTO BIGINT
Nullable
 The receive timeout value.

Contains null if value is not specified.
SOCKET_SEND_LOW_WATER_
MARK_SIZE
 SOCSENDSZ BIGINT
Nullable
 The size of the send low-water mark.

Contains null if value is not specified.
SOCKET_SEND_TIMEOUT SOCSENDTO BIGINT
Nullable
 The send timeout value.

Contains null if value is not specified.

Example

Return information about all network connections for user QLWISVR.

SELECT * FROM QSYS2.NETSTAT_INFO
  WHERE BIND_USER = 'QLWISVR'