You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Replacing API Calls With DB2 for i Services

About a year ago, we blogged about an open-source tool that made working with APIs that return lists of items easier by generating much of the RPG code necessary to create your own callable procedures to work with the lists and items in the lists. For some of those APIs, there may now be an even easier way to get the information into your RPG program by using the new DB2 for i Services. We thought we'd take a look at a few of them here to give you ideas of the kinds of things you could use them for.

What are DB2 for i Services?

What are these DB2 for i services all about? Suppose you need to get information about spooled files in an output queue, joblog messages or jobs holding a lock. System APIs will give you this information, but unless you already have a set of subprocedures handy to use those specific LIST APIs, it can be a bit daunting to figure out how to use them. Even using the tool mentioned above, there is some work and perhaps a learning curve involved.

What if you could access the required information as if it were rows (aka records) in a table (aka file)? That's what these new DB2 for i Services aim to do—simplify access to many pieces of system information via a database interface rather than list APIs and user spaces. Instead, all you need is an SQL SELECT statement to retrieve specific information. Given the capabilities of the WHERE clause in SQL, it also means you can be picky about which spooled files, locks or messages you want to retrieve, which means you may be able to eliminate some RPG logic to filter through the list items.

These services are available for V7.1 and V7.2 systems that are up to date on the database group PTF levels. Services are not available to replace all of the list APIs— far from it—but more are being added. You can look at the documentation and examples for many of them on DeveloperWorks. Included for each of the services is the release and DB group PTF level required to use it. If you don't know what group PTF level is installed on your system, there's even a DB2 for i Service to tell you that! But, of course, before you can use that one, you must already be at V7.2 or V7.1 with DB group PTF level 6 installed.

Let's take a look at just how simple using these new services can be. Assuming you know enough to write an SQL statement and embed it into RPG, you're 90 percent of the way there!

A Simple Example

Let’s start out with a simple interactive SQL query to see what DB group PTF level we have on our system. Using your favorite interactive SQL interface (e.g., STRSQL, Run SQL Scripts, etc.), try running this statement:

Select MAX(PTF_Group_Level) as DB_GROUP_PTF     
  From QSYS2/Group_PTF_Info                     
  Where PTF_Group_Name IN ('SF99702','SF99701') 
  And PTF_Group_Status = 'INSTALLED'      

When we run this one on our V7.1 system we get back:


That's the most current level for V7.1 as of this writing, so we can use all of the services DB2 for i currently has to offer.

You'll note that all we had to do was select the maximum value for a column (aka field) named PTF_GROUP_LEVEL from GROUP_PTF_INFO in library (aka schema) QSYS2. We used WHERE to pick out only the database groups that were installed (the two values listed are DB group PTF IDs for V7.2 and V7.1).

GROUP_PTF_INFO is a view provided by DB2 for i Services, so this is one of the more straightforward ones to use. This service, as well as many others, is implemented as a view, so you can probably already figure out how to use them, even if you're not an SQL whiz kid. Of course, you'll need to know the column names available via these views and perhaps other attributes like types and expected values, such as INSTALLED used above. The documentation linked above gives you all of the details, but since the views use long, descriptive SQL names, in many cases you may be able to find what you need by looking at the names of the available columns.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters