RE: SQL with iseries navigator vs sql greenscreen session

Test this first to make sure you get what you want, but this is normally how
one performs a joined update:

UPDATE CLT_FILE C SET CCLTNO =
(SELECT RCLTNO
FROM REF_FIiLE R
WHERE C.CCLTNO = R.RCLTNO AND etc
GROUP BY etc)
WHERE EXISTS
(SELECT RCLTNO
FROM REF_FIiLE R
WHERE C.CCLTNO = R.RCLTNO AND etc
GROUP BY etc)

Typical challenge going from your test SELECT join is that UPDATE does not
support explicit join syntax nor in your case, CTEs (Common Table
Expressions, aka temptable). So when preparing for an UPDATE type join you
should avoid explicit joins and CTEs. You might replace your first
statement with something like this for example:

select CCLTNO
from CLT_FILE, (SELECT RCLTNO
FROM REF_FIiLE, CLT_FILE
WHERE RCLTNO = CCLTNO AND etc
GROUP BY etc) TEMP_FILE
WHERE CCLTNO = RCLTNO

Join is still being done, only implicitly, in the WHERE clause. There is
still a "temptable", only this time as nested table expression instead of a
CTE.
That said, I don't see why you'd need to join the same files twice, both in
an inner and outer select. Once should be enough.

As for the difference in your test results, there are a number of
environmental settings between an interactive SQL session and JDBC
connection (i.e. QZDASOINIT job) your Run SQL Script is using. Any one of
these could affect the decisions query optimizer is making.

BTW, avoid using an IN clause for this type of UPDATE. It is not likely to
use join processing internally but rather build a temporary list for the IN
test, and of course that will not perform as well as keyed join (i.e. read
by key or CHAIN in rpg). Implicit join using WHERE clause criteria is a way
to go here.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL with iseries navigator vs sql greenscreen session

Hi all,

After fixing a program I need to update the client records that have been
wrongly modified.

To display the records in a STRSQL session, I did this :

with TEMP_FILE as (
SELECT RCLTNO FROM REF_FIiLE join CLT_FILE on RCLTNO = CCLTNO
WHERE etc GROUP BY etc)

select CCLTNO
from CLT_FILE join TEMP_FILE on
CCLTNO =RCLTNO

No problems up to there. Now, how should I change the final select to update
the records in CLT_FILE?

Before typing UPDATE, I did this :

SELECT CCLTNO FROM CLT_FILE
WHERE CCLTNO IN (
SELECT RCLTNO FROM TEMP_FILE )

After 8 minutes and no response I'm going to kill the session with
*SYSTEM/ENDRQS.

Now , in iSeries navigator, the same request takes 40 seconds.

Anyone tell me what's happening here ? Thanks