Troubleshooting
Problem
After applying PTFs that affect SQL services, one or more SQL services might fail with any number of errors. Typically the PTF in question are for database components. The errors are frequently MCH3601 errors to and from QSQ*, QQ*, or QDB* components.
Cause
When PTFs are applied they invoke a PTF exit program that performs proccess necessary for the completion of the PTF application. It can take up to 30 minutes for all of the exit programs to complete if many of them need to run. If you apply PTFs as part of a system restart, the IPL process will complete and run your startup program without any delay to ensure that those exit programs have completed. IBM i does not block startup program execution while PTF exit programs run, which can allow early use of SQL services before they are fully ready. If a PTF exit program affecting SQL services has not completed, SQL services may be in an inconsistent state (mix of updated and non-updated components), which can result in runtime failures such as MCH3601.
Resolving The Problem
There is no simple solution that wouldn't cause the IPL of the system to take much longer, ideally, one could be prevented from calling certain services if the exit programs haven't completed. Until development can find and implement such a solution there are changes that can be made by system administrators to prevent the failures.
Perhaps the easiest solution is to add a simple DLYJOB 1800 to the beginning of the startup program so that it is a full 30 minutes every time you IPL. But that is not a very elegant solution because not every IPL is going to include the application of database PTFs with exit programs.
There is a neater solution which ironically involves calling a new SQL service, QSYS2.ANALYZE_CATALOG, in your startup program. QSYS2.ANALYZE_CATALOG(OPTION => 'SQL SERVICES') reports the status of SQL service-related PTF processing, allowing you to programmatically detect when all exit programs have completed. This service is specifically designed to be safe during PTF processing and does not depend on the SQL service infrastructure being fully initialized. In order for it to work correctly though, you have to do a bit of systems analysis to determine what, on your particular system, might be using SQL services within the first 30 minutes of the completion of the IPL. Those might be programs called from the startup program or through the job scheduler, remote clients making database connections through DRDA or the database host server, server programs running on the system such as ADMIN1 (Navigator for i uses SQL services for many if not most of its functionality). IBM i Navigator PTFs SJ06521, SJ06520, and SJ06519 (for 7.4, 7.5, and 7.6 respectively) added this functionality to prevent the ADMIN1 job from starting until after the SQL service indicates that it is safe to do so.
To prevent the startup of the database host server and the DDM/DRDA server, you can use Navigator for i to locate those under Network/Servers/'IBM Host Servers' and Network/Servers/TCP/'IP Servers' respectively. Right-click the server and select Properties, deselect the 'Start when TCP/IP is started' option. Then your modified startup program can run the STRHOSTSVR DATABASE and STRTCPSVR *DDM commands.
The following is an example of the use of the ANALYZE_CATALOG SQL service that will run until all SQL service PTF exit programs have completed:
BEGIN
DECLARE V_PENDING_COUNT INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET V_PENDING_COUNT = 1;
REPEAT
SELECT COUNT(*)
INTO V_PENDING_COUNT
FROM TABLE(
QSYS2.ANALYZE_CATALOG(OPTION => 'SQL SERVICES')
)
WHERE DETAIL = 'PENDING';
IF V_PENDING_COUNT > 0 THEN
CALL QSYS2.QCMDEXC('QSYS/DLYJOB DLY(30)');
END IF;
UNTIL V_PENDING_COUNT = 0
END REPEAT;
END;
This can be run in a startup program too. Divide the startup program into the things that can start without using any SQL services first, then the SQL statement, and finally those functions that might use SQL services. The following is an example of the command to use in the startup program between the first and last parts:
RUNSQL SQL('BEGIN DECLARE V_PENDING_COUNT INTEGER; DECLARE CONTINUE HANDLER FOR +
SQLEXCEPTION SET V_PENDING_COUNT = 1; REPEAT SELECT COUNT(*) INTO +
V_PENDING_COUNT FROM TABLE(QSYS2.ANALYZE_CATALOG(OPTION=> ''SQL SERVICES'')) +
WHERE DETAIL = ''PENDING''; IF V_PENDING_COUNT > 0 THEN +
CALL QSYS2.QCMDEXC(''QSYS/DLYJOB DLY(30)''); END IF; UNTIL V_PENDING_COUNT = 0 +
END REPEAT; END')Related Information
Document Location
Worldwide
Was this topic helpful?
Document Information
Modified date:
14 August 2026
UID
ibm17278647