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
--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.