IBM i Services are Great, but they're NOT Magic

The development team in Rochester has been quite busy over the last several years making it easy to use SQL to access data and services related to the IBM i operating system. These IBM i Services can be used with SQL to perform a variety of requests; these requests range from retrieving data on active jobs & database connections to placing entries on data queues to checking object authorities to writing data to IFS files. I’m using the "IBM i Services" term here generically to refer to services that you call and to queries against system catalog views because many of the newer views utilize service calls to return data.   

The good news is that we’re seeing a lot of IBM i developers leverage these services to solve a number of problems. The bad news is we’re finding many developers using these services without any thought given to performance. Little consideration is given to how much data is being requested and how often the request is being made - it’s almost like they think there’s something magical about the performance of these services just because they are from IBM. While the IBM development team creating these services is really good, performance analysis and testing should be part of the rollout of any new functions using the IBM i Services. The same performance rigor that you would apply to the deployment of new business processes on your production systems needs to be applied to the deployment of code using the IBM i Services.

Our Db2 Lab Services team analyzes the Top 25 Most Time Consuming SQL statements as part of the SQL Performance Assessment service that we perform for clients.  In the last two years, we’re often finding SQL statements using IBM i Services in the list of most time consuming SQL. These time consuming SQL statements often have a negative impact on overall system performance because of their resource usage. A recent conversation with colleagues in IBM Support revealed they are seeing similar trends with clients reporting system performance problems, only to find a suboptimal services call as the root cause.

Here are some performance considerations when using IBM i Services with SQL.

First, not all services are designed to be called repeatedly on the system and/or to be called during daytime hours when system usage is heavy. Some services consume more system resources than others – that’s why performance testing is key to get a feel for how long the service takes to run and how many resources will be consumed by the service. The IBM Support team shared one story of a client complaining about system performance only to determine the client was calling the DISPLAY_JOURNAL service with very few input filters every two minutes on their production system. Repeated calls to other services like DATA_QUEUE_ENTRIES may be perfectly fine and that can be easily determined with performance testing and analysis. Performance analysis may also lead you to determine that the service needs to be called and run overnight when system usage is low.

Second, while the creation of indexes is often a solution to make SQL queries run faster & more efficiently  – this technique does not help with SQL statements using IBM i Services.  Indexes are not applicable because most IBM i Services rely on functions to dynamically generate the result data, so there’s no data to index. The function  logic is accessing & processing IBM i objects with system API calls. The operating system data being processed is not stored in tables and rows which are normally the target of an SQL request.

The lack of help from indexing leads to the last and probably most important performance consideration – ensure your IBM i Service calls are tightly focused.  A tight focus means specifying as many search parameters as possible on your calls to minimize the amount of data that has to be touched in order to return the result. For example, one could use the following query against the SYSPROGRAMSTAT catalog view to retrieve release details about an SQL program (MYSQLPGM)

            SELECT target_release, earliest_possible_release                      FROM qsys2.sysprogramstat

   WHERE program_name='MYSQLPGM'

While this query works, the scope is not focused – the request directs Db2 to search through every library containing an SQL program instead of scoping the search to the library containing the program.  The focus has been tightened on this new version of the SELECT statement resulting in the same result, but with better performance since the scope was narrowed to a single library:

SELECT target_release, earliest_possible_release               FROM qsys2.sysprogramstat

 WHERE program_name='MYSQLPGM' AND program_schema='MYPGMLIB'

Including search predicates on a WHERE clause is one way to narrow the focus of an IBM i Service.  The other method for narrowing focus is passing input parameters on the service call itself. Let’s use the OBJECT_STATISTICS service as an example. An audit requires you to provide a list of all the program & service program objects on your system. The following SELECT statement generates the required listing, but the scope of the service request is too broad requesting all libraries on the system be processed by the table function call.

SELECT objname, objlib, objtype

FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALL', 'PGM SRVPGM'))

WHERE objlib NOT LIKE 'Q%'

While the IBM libraries are filtered out with the WHERE clause – that filtering is performed after the data is returned by the table function.  The amount of data processed by this call would be greatly reduced by changing the first input parameter from *ALL to *ALLUSR so that only user libraries are processed by the service.  Thus, eliminating the need for the WHERE clause.

SELECT objname, objlib, objtype

FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR', 'PGM SRVPGM'))

 

Some IBM i services have options that reduce the amount of detailed data is collected and returned in order to speed up the performance.  The OBJECT_STATISTICS service has such an option, so this request can be made even more efficient by adding a third parameter with a value of *ALLSIMPLE. 

SELECT objname, objlib, objtype

FROM TABLE(QSYS2.OBJECT_STATISTICS('*ALLUSR', 'PGM SRVPGM', '*ALLSIMPLE' ))

 

With these guidelines in tow, you should be able to use IBM i Services in production without negatively impacting system performance.  You can also find a ton of good examples of using IBM i Services on  Scott Forstie’s SQL examples site:  ibm.biz/Db2foriSQLTutor  Let me know if your company needs assistance tuning your usage of IBM i Services.