×
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.
Another idea... create a view. I'm not sure if this view would be updateable, but you might want to give it a shot:
Create view myView AS
select * from contrcp ctr, ADDRINP ad1 where
ctr.cinact = 'CC01' and ctr.ctradr = ad1.akey
and then
UPDATE myView
SET myView.Addr1 = 'this is a test'
And then (optional)
Drop view myView
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Murphy, Mark
Sent: Thursday, February 07, 2008 3:50 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Update Performance
My indexes are good, the UPPER trick and FORCS_JOIN_ORDER made no difference.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis
Budimlic
Sent: Thursday, February 07, 2008 3:23 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Update Performance
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
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 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.