News
Abstract
Add columns to QSYS2.SYSPROGRAMSTMTSTAT for SQL dependency level and IBM i Service dependency level
Content
DB2 PTF Group dependencies
-
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.
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);
- 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.
SELECT MESSAGE_ID, MESSAGE_TEXT
FROM TABLE(QSYS2.JOBLOG_INFO('*')) X
WHERE MESSAGE_ID IN ('SQL7901', 'SQL795B')
ORDER BY ORDINAL_POSITION;
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';
Was this topic helpful?
Document Information
Modified date:
11 April 2021
UID
ibm11167916