Programmers normally reference the DB2 for i catalog objects data to access data about their DB2 objects. The DB2 catalog objects make it easy for a programmer to better understand their database. The catalog objects provide answers to questions about your database, such as how many columns in your database use a particular data type or how many columns in the database have "CARD_NUMBER" as a column name or the number of distinct key values in an index.
IBM recently added two new catalog views, TCPIP_INFO and GROUP_PTF_INFO, that go beyond the normal database catalog function and provide information about the IBM i system environment. As one might expect, these new views provide information about the TCP/IP attributes for the current host connection and the various Group PTFs for the IBM i operating system.
As SQL usage on the IBM i increases, more developers are finding a need to be able to retrieve IBM i system information using SQL. Some applications might require a certain level of group PTF be installed on the system in order for the application to work. In this case, the new GROUP_PTF_INFO catalog view makes it simple for an application to programmatically validate the group PTF level of the system where it is running.
Some IBM i developers will also find these new catalog views useful even outside of the requirement to programmatically verify a system environment with SQL. As developers use more and more SQL on IBM i, they spend more of their time using SQL tools such as the Run SQL Script facility in IBM System i Navigator. When you are in the middle of editing and running SQL statements, no one wants to start up a 5250 emulation session to review group PTF or TCP/IP settings. The TCPIP_INFO and GROUP_PTF_INFO catalog views enable the developer to access these settings using SQL.
Now that you understand how these catalog views can be used, the next logical step is determining how to get them loaded on your system. Like many other DB2 for i enhancements, these new catalog views are added to your system by simply installing a recent version of the database group PTF for the IBM i 6.1 and IBM i 7.1 releases.
If you want to know which database group PTF level includes these two catalog views, then you should reference the DB2 for i Technology Updates Wiki that is linked to the IBM i developerWorks site. IBM updates this wiki each time that it delivers significant new DB2 functionality in a database group PTF.
When you visit the DB2 Technology Update Wiki, you'll find the details on these two catalog views in the DB2 for i Management Enhancements category. Support for the GROUP_PTF_INFO and TCPIP_INFO was delivered initially with level 19 of the IBM i 6.1 database group PTF and level 6 of the IBM i 7.1 database group PTF. The TCPIP_INFO view was enhanced with level 21 of the IBM i 6.1 database group PTF and level 10 of the IBM i 7.1 database group PTF.
The GROUP_PTF_INFO view returns data similar to the output returned by the work with PTF groups (WRKPTFGRP) system command. The group PTF view returns the following columns for each IBM i group PTF:
These attributes are relatively straightforward to understand except the COLLECTED_TIME column. This column contains a timestamp value that is dynamically set to the time and date that the group PTF information is retrieved from the system by the catalog view.
Let's examine a couple of sample queries against this catalog view to better understand how you can use the data returned by this view. The following query retrieves the name, description, and level for all the IBM i group PTFs currently installed on the system.
Listing 1. Sample query to return list of currently installed group PTFs
Select PTF_GROUP_NAME, PTF_GROUP_DESCRIPTION, PTF_GROUP_LEVEL From QSYS2.GROUP_PTF_INFO Where PTF_GROUP_STATUS = 'INSTALLED'
Table 1 contains the output of this query. As you can see, the test system has 11 group PTFs with a status value of INSTALLED.
Table 1. Sample output from the GROUP_PTF_INFO view
|SF99359||WEBSPHERE MQ FOR I5/OS - V7.0.1||5|
|SF99362||710 BACKUP RECOVERY SOLUTIONS||5|
|SF99363||WEBSPHERE APP SERVER V7.0||6|
|SF99368||IBM HTTP SERVER FOR I||10|
|SF99701||DB2 FOR IBM I||11|
|SF99637||DB2 WEB QUERY FOR IBM I V1.1.2||3|
The next example query verifies if the IBM i 7.1 database group PTF that delivered the most recent enhancements to the TCPIP_INFO view is installed on the system.
Listing 2. Sample query to determine if the required database group PTF is installed
Select PTF_GROUP_STATUS From QSYS2.PTF_GROUP_INFO Where PTF_GROUP_NAME='SF99701' And PTF_GROUP_LEVEL >= 6 And PTF_GROUP_DESCRIPTION = 'DB2 FOR IBM I'
This query returns the value of INSTALLED, which validates that the proper level of the IBM i 7.1 database group PTF is loaded on the system.
The TCPIP_INFO view pulls together data about the host IBM i system being accessed as well as attributes about the client.
- SERVER_IP_ADDRESS_TYPE (Only available on IBM i 7.1)
- SERVER_IP_ADDRESS (Only available on IBM i 7.1)
In contrast to the GROUP_PTF_INFO view, which returns multiple rows of information, the TCPIP_INFO catalog view only returns a single row of data. This data is best understood by looking at a sample data set returned by a query running against the view.
The following query provides an examlpe of how to retrieve data from the TCPIP_INFO catalog view.
Listing 3. Sample query to return network information from TCPIP_INFO view
Select Client_IP_Address, Client_Port_Number, LOCAL_HOST_NAME, SERVER_IP_ADDRESS, SERVER_IP_ADDRESS_TYPE, SERVER_PORT_NUMBER From QSYS2.TCPIP_INFO
Table 2 contains the output of this query. The Client IP Address and Port information are the values for the client system that ran the query against the TCPIP_INFO catalog view. The Local_Host_Name column contains the TCP/IP host name of the server where the catalog view resides. Correspondingly, the Server Address and Server Address Type contain the TCP/IP address for the same server. The last column in the result set, Server_Port_Number, supplies the number of the port on the server that received the SQL statement request.
Table 2. Sample Output from the TCPIP_INFO catalog view
The TCPIP_INFO catalog view is able to return the client TCP/IP information even when an SQL connection is not established directly from the client. Consider a scenario where a PC client has started a 5250 emulation session. In that 5250 session, a user uses the
STRSQL command to run a query against the view. In this scenario, the TCPIP_INFO view returns the IP address and port of the PC client that initiated the 5250 session despite the fact that the PC client has no active SQL connection to the IBM i server. This same capability also means that the TCIP_INFO view can be used by exit-point or trigger programs to log additional details about the user accessing the system or database for audit purposes.
When you use the
telnet command to establish a connection, you need to give special consideration to client and server addresses returned by the TCPIP_INFO catalog view. In this situation, the client and server address values retrieved by a thread should be considered as advisory values instead of absolute values. This is due to the fact that the addresses are set based on the socket that the thread last performed an operation against and a thread can have multiple active socket connections at one time with different clients or servers.
Hopefully, you now see how it is possible to explore new realms of the system with these two new DB2 catalog views, TCPIP_INFO and GROUP_PTF_INFO.
- Participate in the discussion forum.
- IBM Information Center Documentation on DB2 for i Catalog Views
"Let the IBM i Technology Updates wiki show you the way" provides additional background information on the IBM i Technology Updates wiki and process
Kent Milligan is a DB2 for i Senior Certified IT specialist in IBM ISV Solutions Enablement for the IBM i platform. He spent the first eight years of his IBM career as a member of the DB2 development team in Rochester, Minnesota, and he speaks and writes regularly on DB2 for i relational database topics. You can reach him at firstname.lastname@example.org