QAQQINI and QRYTIMLMT issues

[JustPassing]

Hello,

We are having problems with some SQL in our RPGLE programs. We are getting erreur SQL0666 :

A database query was about to be started whose estimated run time of 202775 exceeds the specified limit of 200000 or whose estimated temporary storage usage of 4101 exceeds the specified limit of 2147352576. The query time limit and temporary storage limit are specified on the CHGQRYA CL command.

However, I don't understand where the value of 200000 comes from. The QAQQINI file only exists in QSYS and it is not duplicated. If we look at the value of the time limit with

select * from qsys.qaqqini where qqparm = 'QUERY_TIME_LIMIT';

we see that the value is *DEFAULT. Our system value obtained by

DSPSYSVAL SYSVAL(QQRYTIMLMT)

shows a value of 150800.


We have tried using CHGQRYA to increase the value of parameter QRYTIMLMT

CHGQRYA QRYTIMLMT(2000000);

but we get no error messages and the value of QAQQINI remains *DEFAULT.

Stranger still is that the SQL command also ends in error if we run it in SQL Scripts. However, if we run the CHGQRYA command in SQL Scripts, we are then able to run the SQL command with no problem. Looking at the job in SQL Script, it uses the QAQQINI file in QSYS but when we look at the value of QUERY_TIME_LIMIT, it is still *DEFAULT.

We have tried duplicating QAQQINI in QTEMP before applying CHGQRY :

CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) +
TOLIB(QTEMP) DATA(*YES)
CHGQRYA QRYTIMLMT(300000) QRYOPTLIB(QTEMP)

with the same result. No error message, QUERY_TIME_LIMIT remains *DEFAULT, and our program crashes.


Any help would be apreciated.

[EmmanuelW1]

The default location for the query options file on the CHGQRYA command is QUSRSYS - to change the options for an options file located in a different library, that library would need to be specified on the command.

Cheers,

Emmanuel

[B.Hauser]

Have you ever had a look at the OVERRIDE_QAQQINI Stored Procedure located in the QSYS2 Library?

https://www.ibm.com/docs/en/i/7.4?topic=services-override-qaqqini-procedure