Troubleshooting
Problem
This document explains how SQE can create secondary threads for a job.
Symptom
|
CPE3524 |
Message . . . . : Function not allowed. Cause . . . . . : Function is not allowed in a job that is running with multiple threads. |
| CPF180B | Message . . . . : Function &1 not allowed. Cause . . . . . : Function &1 is not allowed in a job which has multiple threads. |
| CPFA0D4 | Message . . . . : File system error occurred. Error number 3524. |
| CPF180E CPF4380 |
Message........: &4 not changed. Message........- Open attributes not valid in a multithreaded job. |
Cause
SQE can create secondary threads for a job that is using:
SMP (DB2 Symmetric Multiprocessing)
UDFs (User-Defined Function) that are fenced
UDTFs (User-Defined Table Function)
FieldProc (Field Procedure)
AQP (Adaptive Query Processing)
When running SQL with SMP or using user-defined functions that are defined as fenced, or when using UDTFs, field procedures or AQP the system will often spawn multiple threads to complete the request.
Starting with IBM i 7.1, these threads are left open longer to improve the performance of future calls to the same function or statement.
The system will generally close these treads after 60 seconds of not being used.
Multiple threads in a job that are left open can cause failures if the job the runs any function or command that can not be ran in a job with multiple threads.
SMP (DB2 Symmetric Multiprocessing)
UDFs (User-Defined Function) that are fenced
UDTFs (User-Defined Table Function)
FieldProc (Field Procedure)
AQP (Adaptive Query Processing)
When running SQL with SMP or using user-defined functions that are defined as fenced, or when using UDTFs, field procedures or AQP the system will often spawn multiple threads to complete the request.
Starting with IBM i 7.1, these threads are left open longer to improve the performance of future calls to the same function or statement.
The system will generally close these treads after 60 seconds of not being used.
Multiple threads in a job that are left open can cause failures if the job the runs any function or command that can not be ran in a job with multiple threads.
Resolving The Problem
To avoid these errors, you should do one of the following:
1. Monitor for the error and delay for 5 minutes to retry. Often, the threads will be closed by the system by then.
2. Submit the command or function that is failing to batch.
3. Issue the following SQL CALL before calling the function or command that needs to be called from a single-threaded job:
CALL QSYS2.END_IDLE_SQE_THREADS();
1. Monitor for the error and delay for 5 minutes to retry. Often, the threads will be closed by the system by then.
2. Submit the command or function that is failing to batch.
3. Issue the following SQL CALL before calling the function or command that needs to be called from a single-threaded job:
CALL QSYS2.END_IDLE_SQE_THREADS();
This function was added with SF99704 Level 13 and SF99703 Level 24
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Historical Number
N1011308
Was this topic helpful?
Document Information
Modified date:
11 November 2021
UID
nas8N1011308