SERVICES_INFO table

The SERVICES_INFO table returns information about system-supplied services.

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
  • COMMUNICATION
  • DATABASE-APPLICATION
  • DATABASE-PERFORMANCE
  • DATABASE-PLAN CACHE
  • DATABASE-UTILITY
  • JAVA
  • JOURNAL
  • LIBRARIAN
  • MESSAGE HANDLING
  • 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.
  • PROCEDURE
  • SCALAR FUNCTION
  • TABLE
  • TABLE FUNCTION
  • VIEW
OBJECT_TYPE OBJTYPE VARCHAR(7)
Nullable
 The system object type of the service.
  • *FILE
  • *SRVPGM

Contains null for procedures and functions implemented as external routines.
SYSTEM_OBJECT_NAME SYS_ONAME VARCHAR(10)
Nullable
 The system name of the service.

Contains null for procedures and functions implemented as external routines.
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:
  1. STRDBG UPDPROD(*YES)
  2. 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
  3. 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, a message will be issued for the earlier release 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';