when to do Runstats

frankzhang2000

when to do Runstats
Hi all,
I have a question about when to do runstats?Should I do it before I bind program/plan/package after the change of database structure?I remember an error(Perhaps,I am not sure) that I made after modifying the structure of many tables,I don't do runstats before bind programs,and the result is the same as I have not binded the programs,I am new to DB2,so anyone here pls give me some recommendation.

Thanks
Victor
______________________________________

===================
ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn

ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)



Sanjeev (CTS) S

Re: when to do Runstats
(in response to frankzhang2000)
Victor,
I think there should the installation standard for running the RUNSTAT i.e
weekly or something like this. Other than this there should be some special
occasion i.e after REORG or before Bind/Rebind or Alter object etc. RUNSTATS
are used for giving the optimizer the correct information to determine the
access paths. There are exceptions when RUNSTATS are not run for some
particular set of objects when we want the optimizer the use the one time
best information for access path determination.

I hope it makes some sense.

Regards,
Sanjeev

> -----Original Message-----
> From: frankzhang2000 [SMTP:[login to unmask email]
> Sent: Tuesday, January 02, 2001 10:44 AM
> To: [login to unmask email]
> Subject: when to do Runstats
>
> Hi all,
> I have a question about when to do runstats?Should I do it before I bind
> program/plan/package after the change of database structure?I remember an
> error(Perhaps,I am not sure) that I made after modifying the structure of
> many tables,I don't do runstats before bind programs,and the result is the
> same as I have not binded the programs,I am new to DB2,so anyone here pls
> give me some recommendation.
>
> Thanks
> Victor
> ______________________________________
>
> ===================
> ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn
>
> ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)
>
>
> visit
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



frankzhang2000

Re: when to do Runstats
(in response to Sanjeev (CTS) S)
Hi,
Thanks for your reply,from your answer,that means runstats only has something to do with optimizing application access path to db2 objects,if I don't do runstats after the modification of db2 structure,ie. change some table structure,drop and recreate tablespace,and bind the application program,there will be no harm except that will make application run not at the optimal performance?

Victor
______________________________________

===================
ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn

ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)



Sanjeev (CTS) S

Re: when to do Runstats
(in response to frankzhang2000)
Hi Victor,

Major reason is ofcourse performance but there is one more thing which DBAs
have to look is the proper documentation and the current catalog statistics
give the DBAs good information for analysing the data and the database of
the installation. Other thing is there are many inhouse developed
tools/utilities which use catalog information to give some reports or take
some action like Conditional Reorg, Data Skewing pattern etc.

I don't think there can be any more harm than application not running the
optimal performance. You are correct that there should not be any other harm
than performance. Changing the DB2 objects structure do not always require
RUNSTATS to be run. There are cases when catalog gets updated automatically
and there are cases when you need to run Reorg and then RUNSTATS.

HTH
Regards,
Sanjeev

> -----Original Message-----
> From: frankzhang2000 [SMTP:[login to unmask email]
> Sent: Tuesday, January 02, 2001 3:21 PM
> To: [login to unmask email]
> Subject: Re: when to do Runstats
>
> Hi,
> Thanks for your reply,from your answer,that means runstats only has
> something to do with optimizing application access path to db2 objects,if
> I don't do runstats after the modification of db2 structure,ie. change
> some table structure,drop and recreate tablespace,and bind the application
> program,there will be no harm except that will make application run not at
> the optimal performance?
>
> Victor
> ______________________________________
>
> ===================
> ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn
>
> ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)
>
>
> visit
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



elibs

Re: when to do Runstats
(in response to Sanjeev (CTS) S)
Hi, Sanjeev:

I don't quit understand the meaning of 'There are cases when catalog gets updated automatically and there are cases when you need to run Reorg and then RUNSTATS.'

What I understand is that once the DB2 updates its catalog automatically, we need to REORG ,RESTATS and BIND? Is it right? But how can we know when the DB2 do it except the cases that the DB2 tables are altered?

And by the way ,do you know to recovery the tables by the DB2 log without the help of IMAGE COPIES?

Thanks


----- Original Message -----
From: S, Sanjeev (CTS) <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Tuesday, January 02, 2001 6:34 PM
Subject: Re: when to do Runstats


Hi Victor,

Major reason is ofcourse performance but there is one more thing which DBAs
have to look is the proper documentation and the current catalog statistics
give the DBAs good information for analysing the data and the database of
the installation. Other thing is there are many inhouse developed
tools/utilities which use catalog information to give some reports or take
some action like Conditional Reorg, Data Skewing pattern etc.

I don't think there can be any more harm than application not running the
optimal performance. You are correct that there should not be any other harm
than performance. Changing the DB2 objects structure do not always require
RUNSTATS to be run. There are cases when catalog gets updated automatically
and there are cases when you need to run Reorg and then RUNSTATS.

HTH
Regards,
Sanjeev

> -----Original Message-----
> From: frankzhang2000 [SMTP:[login to unmask email]
> Sent: Tuesday, January 02, 2001 3:21 PM
> To: [login to unmask email]
> Subject: Re: when to do Runstats
>
> Hi,
> Thanks for your reply,from your answer,that means runstats only has
> something to do with optimizing application access path to db2 objects,if
> I don't do runstats after the modification of db2 structure,ie. change
> some table structure,drop and recreate tablespace,and bind the application
> program,there will be no harm except that will make application run not at
> the optimal performance?
>
> Victor
> ______________________________________
>
> ===================
> ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn
>
> ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------

================


Sanjeev (CTS) S

Re: when to do Runstats
(in response to elibs)
Hi Elbis,

I think that the sentence was not quite clear. I meant to say that if you
alter something or drop something, catalog gets affected. Either entry is
done or removed. But at the same time many columns of the catalog tables are
not updated corresponding to that entry and RUNSTATS is used to updated
those.

I wanted to point out not everything in the catalog is updated by
runstats. It is either automatically, with RUNSTATS or with STOSPACE. The
point was raised for the level of documentation which DBAs can have and kind
of confusion the improper documentation can create.Thanks to the STATSTIME
in almost all the table but i do not think it is reported in any of the
reports a DBA uses. May be, not sure !!

For recovery without image copy, you can look at the archive of last two
weeks when one similar case was discussed. Please let us know if you could
find the same. Subject is "What if recovery fails...........'. It is similar
but not exactly same.

HTH
Regards
Sanjeev

> -----Original Message-----
> From: elibs [SMTP:[login to unmask email]
> Sent: Tuesday, January 02, 2001 4:47 PM
> To: [login to unmask email]
> Subject: Re: when to do Runstats
>
> Hi, Sanjeev:
>
> I don't quit understand the meaning of 'There are cases when catalog
> gets updated automatically and there are cases when you need to run Reorg
> and then RUNSTATS.'
>
> What I understand is that once the DB2 updates its catalog
> automatically, we need to REORG ,RESTATS and BIND? Is it right? But how
> can we know when the DB2 do it except the cases that the DB2 tables are
> altered?
>
> And by the way ,do you know to recovery the tables by the DB2 log
> without the help of IMAGE COPIES?
>
> Thanks
>
>
> ----- Original Message -----
> From: S, Sanjeev (CTS) <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Tuesday, January 02, 2001 6:34 PM
> Subject: Re: when to do Runstats
>
>
> Hi Victor,
>
> Major reason is ofcourse performance but there is one more thing which
> DBAs
> have to look is the proper documentation and the current catalog
> statistics
> give the DBAs good information for analysing the data and the database of
> the installation. Other thing is there are many inhouse developed
> tools/utilities which use catalog information to give some reports or take
> some action like Conditional Reorg, Data Skewing pattern etc.
>
> I don't think there can be any more harm than application not running the
> optimal performance. You are correct that there should not be any other
> harm
> than performance. Changing the DB2 objects structure do not always require
> RUNSTATS to be run. There are cases when catalog gets updated
> automatically
> and there are cases when you need to run Reorg and then RUNSTATS.
>
> HTH
> Regards,
> Sanjeev
>
> > -----Original Message-----
> > From: frankzhang2000 [SMTP:[login to unmask email]
> > Sent: Tuesday, January 02, 2001 3:21 PM
> > To: [login to unmask email]
> > Subject: Re: when to do Runstats
> >
> > Hi,
> > Thanks for your reply,from your answer,that means runstats only has
> > something to do with optimizing application access path to db2
> objects,if
> > I don't do runstats after the modification of db2 structure,ie. change
> > some table structure,drop and recreate tablespace,and bind the
> application
> > program,there will be no harm except that will make application run not
> at
> > the optimal performance?
> >
> > Victor
> > ______________________________________
> >
> > ===================
> > ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn
> >
> > ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)
> >
> >
> > visit
> > the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
> >
> --------------------------------------------------------------------------
> ---------------------------------------------------------------
> --------------------------------------------------------------------------
> ---------------------------------------------------------------
> This e-mail and any files transmitted with it are for the sole use
> of the intended recipient(s) and may contain confidential and privileged
> information.
> If you are not the intended recipient, please contact the sender by reply
> e-mail and
> destroy all copies of the original message. Any unauthorised review, use,
> disclosure,
> dissemination, forwarding, printing or copying of this email or any action
> taken in
> reliance on this e-mail is strictly prohibited and may be unlawful.
>
> Visit us at http://www.cognizant.com
> --------------------------------------------------------------------------
> --------------------------------------------------------------
> --------------------------------------------------------------------------
> --------------------------------------------------------------
>
> ================
> visit
>
>
>
> N?!jxÊ<«²æìr¸©¶*'¢>b¢{(®Újw-*.®Ë>±Êâ¦Ø¨zø¬SÛax0v/>¥¨jØm¶YÿÃ+ÉÈoe¢oÝoi
> S...ê0êì¡ûazX¬µÆ§mêÞiÈ^u«CbÑI4XEURZ
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Max Scarpa

Re: when to do Runstats
(in response to Sanjeev (CTS) S)
Hi Victor,

take a look at Gabrielle Wiorkowski's site

http://www.gabrielledb2.com/

There's a PDF document with guidelines for RUNSTATS & REORGs at the URL:

http://www.gabrielledb2.com/rr.pdf


HTH

Regards

Max Scarpa
DB2 sysprog
Happy new year....without any DB2 Odyssey .... :-))))

All disclaimers in HAL9000's 64-furlongbit Central Storage apply



Bruno CEI

Re: when to do Runstats
(in response to Max Scarpa)
Hi Victor,


I agree with Sanjeev that the worst problem is performance. Anyway, I'd like
to add that "not optimal" in some cases can mean that performance is
absolutely unacceptable. Ona last thing : after you have run a RUNSTATS
utility, you need to REBIND the affected plan/packages using the new
information available in the catalog. Only after doing this you will be
using the newly optimised access to data.

Bruno

-----Original Message-----
From: S, Sanjeev (CTS) [mailto:[login to unmask email]
Sent: 02 January 2001 10:34
To: [login to unmask email]
Subject: Re: when to do Runstats


Hi Victor,

Major reason is ofcourse performance but there is one more thing which DBAs
have to look is the proper documentation and the current catalog statistics
give the DBAs good information for analysing the data and the database of
the installation. Other thing is there are many inhouse developed
tools/utilities which use catalog information to give some reports or take
some action like Conditional Reorg, Data Skewing pattern etc.

I don't think there can be any more harm than application not running the
optimal performance. You are correct that there should not be any other harm
than performance. Changing the DB2 objects structure do not always require
RUNSTATS to be run. There are cases when catalog gets updated automatically
and there are cases when you need to run Reorg and then RUNSTATS.

HTH
Regards,
Sanjeev

> -----Original Message-----
> From: frankzhang2000 [SMTP:[login to unmask email]
> Sent: Tuesday, January 02, 2001 3:21 PM
> To: [login to unmask email]
> Subject: Re: when to do Runstats
>
> Hi,
> Thanks for your reply,from your answer,that means runstats only has
> something to do with optimizing application access path to db2 objects,if
> I don't do runstats after the modification of db2 structure,ie. change
> some table structure,drop and recreate tablespace,and bind the application
> program,there will be no harm except that will make application run not at
> the optimal performance?
>
> Victor
> ______________________________________
>
> ===================
> ÐÂÀËÃâ·Ñµç×ÓÓÊÏä http://mail.sina.com.cn
>
> ÄãÑ¡ÊÖ»úÎÒÂòµ¥£¡(http://mall.sina.com.cn/yesmobile/)
>
>
>
>
>
----------------------------------------------------------------------------
-------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and
destroy all copies of the original message. Any unauthorised review, use,
disclosure,
dissemination, forwarding, printing or copying of this email or any action
taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------
------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------



http://www.ryci.com/db2-l. The owners of the list can be reached at
[login to unmask email]


___________________________________________________________________________
This email is confidential and intended solely for the use of the
individual to whom it is addressed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of
Sema Group.
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or
copying of this email is strictly prohibited.

If you have received this email in error please notify the Sema Group
Helpdesk by telephone on +44 (0) 121 627 5600.
___________________________________________________________________________