Get a picture of your IBM i environment with DB2 catalog views

New catalog views return system information

Traditionally, DB2 for i catalog views have only provided metadata about existing database objects. A new set of catalog views also provide information about your IBM i system environment. Specifically, these view make it easy to access information about the Group PTFs and TCP/IP network connections related to your DB2 for i database.

Kent Milligan, Senior Certified IT Specialist, IBM China

kent 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



10 May 2012

Also available in Chinese Russian

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.

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
PTF_GROUP_NAMEPTF_GROUP_DESCRIPTIONPTF_GROUP_LEVEL
SF99145PERFORMANCE TOOLS2
SF99359WEBSPHERE MQ FOR I5/OS - V7.0.15
SF99362710 BACKUP RECOVERY SOLUTIONS5
SF99363WEBSPHERE APP SERVER V7.06
SF99368IBM HTTP SERVER FOR I10
SF99572JAVA6
SF99701DB2 FOR IBM I11
SF99637DB2 WEB QUERY FOR IBM I V1.1.23
SF99708GROUP SECURITY9
SF99707TECHNOLOGY REFRESH2
SF99707TECHNOLOGY REFRESH1

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.

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.

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
Client_IP_AddressClient_Port_NumberLocal_Host_NameServer_IP_AddressServer_IP_Address_TypeServer_Port_Number
9.10.84.1442134DB271.RCHLAN.COM9.5.168.119IPV48471

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 considerations

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Java technology on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Java technology, Open source
ArticleID=813680
ArticleTitle=Get a picture of your IBM i environment with DB2 catalog views
publish-date=05102012