Overview

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.

New system catalog views

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.

Installing the catalog views

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.

Exploring the GROUP_PTF_INFO view

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:

• COLLECTED_TIME
• PTF_GROUP_NAME
• PTF_GROUP_DESCRIPTION
• PTF_GROUP_LEVEL
• PTF_GROUP_TARGET_RELEASE
• PTF_GROUP_STATUS

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.

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.

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.

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.

Exploring the TCPIP_INFO view

The TCPIP_INFO view pulls together data about the host IBM i system being accessed as well as attributes about the client.

• COLLECTED_TIME
• LOCAL_HOST_NAME
• CLIENT_IP_ADDRESS_TYPE
• CLIENT_IP_ADDRESS
• CLIENT_PORT_NUMBER
• SERVER_IP_ADDRESS_TYPE (Only available on IBM i 7.1)
• SERVER_IP_ADDRESS (Only available on IBM i 7.1)
• SERVER_PORT_NUMBER
• HOST_VERSION

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.

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.

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.

Telnet client

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.

Conclusion

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.

Resources

• 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
  
  

About the author

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 kmill@us.ibm.com

Rate this article

Comments

Back to top

Table of contents

• Overview
• New system catalog views
• Installing the catalog views
• Exploring the GROUP_PTF_INFO view
• Exploring the TCPIP_INFO view
• Telnet client
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into IBM i on developerWorks