Re: Help with "hanging" embedded SQL

HiI don't know here with the details given so far if you can straight replace an "assertion of EXISTance" predicate with a straight join... because without knowing cardinality you can inflate the count(*) if the cardinality is 1:N with respect to the relation. An I suspect that EXISTS was used here for that reason.
You can indeed hack around using i.e. COUNT( DISTINCT myuniquekey) to disambiguate but of course it requires a proper key to coalesce on, i.e. " a message id".

Anyway, display locks when it happens... here I'm assuming that all the indexes are in place obviously.



On Monday, June 30, 2025 at 07:57:41 PM GMT+2, Daniel Gross <daniel@xxxxxxxx> wrote:

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. ***