Re: Improving SQL Update perfomance

[Luis Rodriguez <luis.rodriguez2@xxxxxxxxx>]

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