Overview of the IBM I environment through the DB2 catalog view

Overview

Programmers often refer to DB2 for I database directory data to access data related to their DB2 objects. The DB2 database directory makes it easier and better for programmers to understand databases. The database directory provides answers to database-related questions, such as how many columns in a database use a particular data type, how many columns in the database are "Card_number" as column names, or how many unique key values exist in the index.

New System catalog view

IBM recently added two new catalog views Tcpip_info and Group_ptf_info, expanded the functionality of the regular database directory, and provided information about the IBM I system environment. As you could imagine, these new views provide information about the TCP/IP properties of the current host connection and the various Group PTF of the IBM I operating system.

With the increasing popularity of SQL in IBM I, more and more developers need to use SQL to retrieve IBM I system information. Some applications may require a specific level of Group PTF to be installed on the system to ensure that the application works properly. In this case, the new Group_ptf_info catalog view makes it easy for the application to programmatically validate the GROUP PTF level of the current system.

Some IBM I application developers will also find that these new catalog views are valuable even if the system environment is not programmatically validated through SQL. Developers are increasingly using SQL on IBM I, and often need to use a variety of SQL tools, such as the Run SQL Script utility, in IBM System I Navigator. In the process of editing and running SQL statements, few people want to start the 5250 Simulation session window to check the Group PTF or TCP/IP settings. The Tcpip_info and Group_ptf_info catalog views enable developers to access these settings using SQL.

Install catalog view

Now that you know what these catalog views are for, the next step is naturally to learn how to install them into your system. As with many other DB2 for I additions, you can add these new catalog views to your system simply by installing the latest version of database Group PTF for IBM i 6.1 and IBM I 7.1.

If you want to confirm which database group PTF levels contain these two catalog views, refer to the link in the DB2 for I Technology Updates wiki,ibm i developerWorks Web site. IBM updates the wiki every time it publishes important DB2 new features in the database Group PTF.

When you visit the DB2 Technology Update wiki, you can find more information about these two catalog views in the DB2 for I admin enhancements directory. Support for Group_ptf_info and Tcpip_info was originally provided in level 19 of IBM I 6.1 database group PTF and level 6 of IBM I 7.1 database group PTF. The Tcpip_info view is enhanced at level 21st of IBM I 6.1 database group PTF and the 10th level of IBM I 7.1 database group PTF.

Explore the Group_ptf_info view

The data returned by the Group_ptf_info view is similar to the output returned when using the PTF GROUP (wrkptfgrp) system command. The Group PTF View will return the following for each IBM I group PTF:

Collected_time

Ptf_group_name

Ptf_group_description

Ptf_group_level

Ptf_group_target_release

Ptf_group_status

In addition to Collected_time columns, other attributes are relatively intuitive and easy to understand. The Collected_time column contains a timestamp value and dynamically sets the time and date when the catalog view retrieves the Group PTF information from the system.

Here are a few examples of queries about this catalog view to better understand how to take advantage of the data returned by this view. The following query retrieves the names, descriptions, and levels of all IBM I Group PTF installed in the current system.

Listing 1. Returns a query sample of the currently installed Group PTF list

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, there are 11 Group PTF in the test system with installed status.