× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Make sure the following is in place for your original query:

- index (keyed LF) over ad1.key?
- index (keyed LF) over ctr.CINACT,ctr.CTRADR?

This problem should be solved with judicious use of proper indexing. You
should also try to verify if you get the same behavior with SQE & CQE engine
(AND UPPER('x') = 'X' trick), as that can tell you if you're hitting some
query optimizer bug.

You can try forcing the join order, but that is in no way guaranteed to
work, or if it works it is not guaranteed to work in the future. It's a
workaround and that's never good as long term solution (hint: avoid it if
you can).

Something like this:
1) CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QUSRSYS) OBJTYPE(*FILE) TOLIB(yourLib)
DATA(*YES)
2) UPDATE yourLib/QAQQINI SET QQVAL = '*PRIMARY 2' WHERE QQPARM =
'FORCE_JOIN_ORDER'
3) CHGQRYA JOB(*) QRYOPTLIB(yourLib)

Run your update statement now and see if you get a different join order
(i.e. 2ndary dial should become primary dial now).
Note that you have to tell the job where your update runs to use your
modified QAQQINI before you run the update statement.

Let us know what happens.

HTH, Elvis

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


-----Original Message-----
Subject: SQL Update Performance

I have an update that I am trying to do on V5R2:



UPDATE ADDRINP ad1

SET ad1.Addr1 = 'this is a test'

WHERE exists(

select * from contrcp ctr where

ctr.cinact = 'CC01' and

ctr.ctradr = ad1.akey)



This statement takes forever to run because of the join order the optimizer
is choosing. ADDRINP is
in Join position 1 and CONTRCP is in join position 2. This is most likely
because I am updating
ADDRINP. Only problem is that there are 3,000,000 records in ADDRINP, and
putting it in join position
1 forces the whole file to be read in order to pick out 6 records to be
processed. Can anyone think
of a way to force CONTRCP to join position 1?



Mark Murphy

Star Base Consulting, Inc.

e: murphym@xxxxxxxxxx


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.