Re: Improving SQL Update perfomance

Charles,

Thanks for your answer,

Yes, I'm updating all the table and no, no triggers,
constraints or journals for these files. These are the TEMP
files... Good point about the journals, though, have to
check them when we get to the point of updating the "real"
files.


For Vern:
RMVM can't be used against SQL tables, views, indexes or
any objects that reside in a SQL schema. You get a CPF320A
error message.

Thanks again,

Luis

--- midrange-l-request@xxxxxxxxxxxx wrote:

------------------------------

message: 6
date: Thu, 29 Nov 2007 07:31:06 -0500
from: "Wilt, Charles" <WiltC@xxxxxxxxxx>
subject: RE: Improving SQL Update perfomance

Luis,

There doesn't appear to be a where clause on the
statement.

Thus, you're intending to change every row correct?

Any triggers or constraints on these files? Those would
slow you down considerably.

Are the files journaled? If so, make sure you run the
update under commitment control. Or stop
journaling while you run the update.

I wonder if it'd be any quicker to create a temporary
file with the new values then do a CPYF
MBROPT(*REPLACE) back to the original?

HTH,
Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of

Luis Rodriguez

Sent: Wednesday, November 28, 2007 2:42 PM
To: Midrange-L
Subject: Improving SQL Update perfomance

Hi list,

What would be the best way to improve the performance

of

a SQL Update statement executed with RUNSQLSTM?

I need to run the following SQL for several tables:

UPDATE LIBNAME/TABLE SET
Field1 = round(field1 / 1000, 2),
Field2 = round(field2 / 1000, 2),
...
Fieldn = round(fieldn / 1000, 2);

Each one of these tables can have several thousands of
records. One of them has 15 million of records, and the
process is VERY slow.

Thanks in advance,

Luis

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions


____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs