SERVICES_INFO table

The SERVICES_INFO table returns information about system-supplied services.

Authorization: The caller must have:

*EXECUTE authority to QSYS2, and
*OBJOPR and *READ authority to the QSYS2/SERV_INFO file.

The following table describes the columns in the table. The system name is SERV_INFO. The schema is QSYS2.

Table 1. SERVICES_INFO table
Column Name	System Column Name	Data Type	Description
SERVICE_CATEGORY	CATEGORY	VARCHAR(40)	Classification of the service. APPLICATION BACKUP AND RECOVERY COMMUNICATION CONFIGURATION DATABASE-APPLICATION DATABASE-PERFORMANCE DATABASE-PLAN CACHE DATABASE-UTILITY IFS JAVA JOURNAL LIBRARIAN MESSAGE HANDLING MIGRATE WHILE ACTIVE MIRROR-COMMUNICATION MIRROR-PRODUCT MIRROR-RECLONE MIRROR-REPLICATION MIRROR-RESYNCHRONIZATION MIRROR-SERVICEABILITY PERFORMANCE PRODUCT PTF SECURITY SPOOL STORAGE SYSTEM HEALTH WORK MANAGEMENT
SERVICE_SCHEMA_NAME	SYS_NAME	VARCHAR(128)	Name of the schema containing the service.
SERVICE_NAME	SERVNAME	VARCHAR(128)	Name of the service.
SQL_OBJECT_TYPE	SQLTYPE	VARCHAR(15)	The type of object. GLOBAL VARIABLE PROCEDURE SCALAR FUNCTION TABLE TABLE FUNCTION VIEW
OBJECT_TYPE	OBJTYPE	VARCHAR(7) Nullable	The system object type of the service. *FILE Contains null for procedures, functions, and global variables.
SYSTEM_OBJECT_NAME	SYS_ONAME	VARCHAR(10) Nullable	The system name of the service. Contains null for procedures, functions, and global variables.
LATEST_DB2_GROUP_LEVEL	GROUPLVL	INTEGER Nullable	The Db2® for i PTF Group level which most recently changed this service. Contains null if the service has not been enhanced in a PTF in this release.
INITIAL_DB2_GROUP_LEVEL	INITIALLVL	INTEGER Nullable	The Db2 for i PTF Group level where this service was introduced. Contains null if this service was available in the base for this release.
EARLIEST_POSSIBLE_RELEASE	MINRLS	VARCHAR(6)	The earliest release, in VxRxMx format, where a version of this service is available.
EXAMPLE	EXAMPLE	VARCHAR(5000)	An example SQL script that uses this service.

Example

Show all the available PTF services:

SELECT * FROM QSYS2.SERVICES_INFO
  WHERE SERVICE_CATEGORY = 'PTF'

Db2 PTF Group dependencies

To complement the Db2 PTF Group level information provided by the SERVICES_INFO catalog table, you can determine the Db2 PTF Group dependency level for every static SQL statement within a module, program, or service program. The QSYS2.SYSPROGRAMSTMTSTAT catalog contains one row for every static SQL statement. The Db2 PTF Group dependency information is surfaced in two columns:

SQL_DB2_GROUP_LEVEL

Indicates the use of SQL language features. For example, new SQL statements or query clauses surface as dependencies upon having a certain Db2 PTF Group level (or higher) installed before the statement can be run.

This is an SQL syntax level and is an accurate indication of the dependency level.

SERVICES_DB2_GROUP_LEVEL

Indicates the consumption of IBM® i Services. For example, queries that reference Db2 for i provided views, functions, procedures, or global variables can surface possible dependencies upon having a certain Db2 PTF Group level (or higher) installed before executing the statement. If multiple services are used within a single SQL statement, the highest dependency level is returned.

The services that are instrumented are documented in IBM i Services and Db2 for i Services. SQL built-in functions and built-in global variables are also tracked.

This is not an exact indication of the Db2 PTF Group that is needed. It depends on how the service is being used in your application. The information is provided based solely on the name of the service and the knowledge of when the latest enhancement was added for that service. If the name of an IBM-provided service matches an unqualified name in an SQL statement, it will be tracked as the IBM service. Based on the reported use of these services, you will need to determine whether the reported Db2 PTF Group is actually required.

To check all programs in APPLIB for potential SQL syntax and IBM i Service dependencies, execute the following query. Only programs created after the SERVICES_INFO table was introduced will report this information.

SELECT PROGRAM_NAME, SQL_DB2_GROUP_LEVEL, SERVICES_DB2_GROUP_LEVEL 
       FROM QSYS2.SYSPROGRAMSTMTSTAT 
       WHERE PROGRAM_SCHEMA = 'APPLIB' AND 
             (SQL_DB2_GROUP_LEVEL IS NOT NULL OR 
              SERVICES_DB2_GROUP_LEVEL IS NOT NULL);

To see more detailed information about which services are used in a program, including the name of each service and the Db2 PTF Group level required for the service, perform the following steps:

STRDBG UPDPROD(*YES)
Precompile your program or build your SQL procedure, function, or trigger.
- To have informational messages written to the listing, add SET OPTION OUTPUT=*PRINT to your SQL routine or specify the OUTPUT(*PRINT) parameter on the CRTSQLxxx or RUNSQLSTM CL commands
For each reference to a service, message SQL7901 will be written to the joblog and, optionally, to the precompile listing.
If you precompile with a TGTRLS of 7.1 or later, a message will be issued for each of the earlier releases as well with an indication of the Db2 PTF Group level that is needed on that release. If the service is not supported for a release, message SQL795B will be issued.

This information can be used to determine whether your application contains any content that might require a certain level of Db2 PTF Group. If you need to deploy your application to a different partition or an earlier release, this feedback can alert you to potential dependencies.

After you have created one or more objects using the steps above, you can query your job log to see if any messages were issued that might need to be addressed.

SELECT MESSAGE_ID, MESSAGE_TEXT 
   FROM TABLE(QSYS2.JOBLOG_INFO('*')) X 
   WHERE MESSAGE_ID IN ('SQL7901', 'SQL795B')
   ORDER BY ORDINAL_POSITION;

Here is one more query to help tie this information together. It will tell you the Db2 PTF Group level that is on a partition.

SELECT MAX(PTF_GROUP_LEVEL) AS DB2_PTF_LEVEL FROM QSYS2.GROUP_PTF_INFO
  WHERE PTF_GROUP_NAME LIKE 'SF9970%'  AND PTF_GROUP_STATUS = 'INSTALLED';