Re: Help with "hanging" embedded SQL

Hi Dan,

the SQL statement alone only helps so much.

My first question is, which kind isolation level is used? *CS, *RS, *RR? If a "simple" SELECT does stall, it has very often to do with concurrent access. It's possible that other jobs are locking records, that have to be fetched - and before the record wait time passes out, the record is released.

If that is the case, it may help, to specify a special isolation level for this statement and/or specifying a concurrent access resolution clause - like this:

Exec SQL
SELECT ....
...
WITH CS USE CURRENTLY COMMITTED;

With these clauses, the locks are more or less ignored, and the last committed data is used.

But I also see that you used a "sub-select" - which in fact is lateral join, and means a lot of additional processing. IMHO a simple join should be more efficient:

Exec SQL
SELECT COUNT(*)
INTO :Messages
FROM dajl030fp l30
JOIN dajl015p l15
ON l15.plsacmcnt > 0
AND l15.plsprpltp IN (' ','H')
AND l15.plscarrier = l30.plbcarrier
AND l15.plsmsgid = l30.plbrecid
WHERE (l30.plbdestid = :@@endptid) AND
(l30.plbcarrier = :@@carrier) AND
(l30.plbaddtime >= :@@begindate) AND
(l30.plbaddtime < :@@enddate);

With the original design, the sub-select on dajl015p (a lateral join) has to be "executed" for every line of dajl030fp - and I don't know, if the optimizer find out, that this lateral join can be resolved into a simple join.

You should in any case try to "explain" the statement - iACS Run SQL Scripts is a real help here.

It would be nice, to read, if those changes help to speed things up.

HTH
Daniel

Am 30.06.2025 um 19:26 schrieb Dan Bale <dan.bale@xxxxxxxxxxxxxxxxxxxxx>:

We have a daily job that occasionally "sits" at this embedded SQL statement for long periods of time. No error, no message waiting. User captured only the call stack, which shows "SQL_Fetch" at the bottom of the call stack.

FWIW, plbaddtime, @@begindate, and @@enddate are all timestamps.

Where should we be looking / what questions should we be asking?

Exec SQL
SELECT COUNT(*)
INTO :Messages
FROM dajl030fp l30
WHERE (l30.plbdestid = :@@endptid) AND
(l30.plbcarrier = :@@carrier) AND
(l30.plbaddtime >= :@@begindate) AND
(l30.plbaddtime < :@@enddate) AND
(EXISTS
(SELECT 1
FROM dajl015p l15
WHERE (l15.plsmsgid = l30.plbrecid) AND
(l15.plscarrier = l30.plbcarrier) AND
(l15.plsacmcnt > 0) AND
(l15.plsprpltp IN (' ','H'))
)
);
*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***