Sysmaster - Update Statistics Info
I am trying to find where in the sysmaster, or anywhere else, I can query to determine how up to date the statistics might be. Is there a date last updated, etc? I have a number of customers where I need to determine if they are regularly updating statistics, and how they are doing it; low, medium or high; with distributions, etc? Thanks in advance for any assistance. Jerry
Hi Jerry, You can use the following query to get it. select a.tabid, b.tabname, c.colname, a.mode, a.constructed from sysdistrib a, systables b, syscolumns c where a.tabid = b.tabid and a.colno = c.colno --order by 1,4,5 desc Hope this helps. Sonia ----- Original Message ----- From: "JERRY SERMAN" <jerry_serman@exe.com> To: <ids@iiug.org> Sent: Friday, July 11, 2003 2:32 PM Subject: Sysmaster - Update Statistics Info [1551] > I am trying to find where in the sysmaster, or anywhere else, I can query to determine how up to date the statistics might be. Is there a date last updated, etc? > > I have a number of customers where I need to determine if they are regularly updating statistics, and how they are doing it; low, medium or high; with distributions, etc? > > Thanks in advance for any assistance. > > Jerry > > > >
Be careful with this though....distributions only get created on MEDIUM or HIGH. Therefore sysdistrib won't get updated on LOW only. Assuming that most clients have tables that will get updated MED or HIGH, then most will turn up. But not tables that just have been run as LOW. HTH - Mark. Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com "Sonia Guerra" <sguerra@merkafon To: ids@iiug.org .com> cc: Sent by: Subject: Re: Sysmaster - Update Statistics Info [1553] forum.subscriber@ iiug.org 07/11/2003 02:30 PM Hi Jerry, You can use the following query to get it. select a.tabid, b.tabname, c.colname, a.mode, a.constructed from sysdistrib a, systables b, syscolumns c where a.tabid = b.tabid and a.colno = c.colno --order by 1,4,5 desc Hope this helps. Sonia ----- Original Message ----- From: "JERRY SERMAN" <jerry_serman@exe.com> To: <ids@iiug.org> Sent: Friday, July 11, 2003 2:32 PM Subject: Sysmaster - Update Statistics Info [1551] > I am trying to find where in the sysmaster, or anywhere else, I can query to determine how up to date the statistics might be. Is there a date last updated, etc? > > I have a number of customers where I need to determine if they are regularly updating statistics, and how they are doing it; low, medium or high; with distributions, etc? > > Thanks in advance for any assistance. > > Jerry > > > >
Sonia Guerra wrote: > Hi Jerry, > > You can use the following query to get it. > > select > a.tabid, > b.tabname, > c.colname, > a.mode, > a.constructed > from sysdistrib a, systables b, syscolumns c > where a.tabid = b.tabid > and a.colno = c.colno > --order by 1,4,5 desc Except that this is looking at generated distributions, which are only generated by UPDATE STATISTICS MEDIUM & HIGH. There is no real way to determine if UPDATE STATISTICS LOW has been run. You can look at nrows in systables to see if the number of records is close to the actual number in the table, but a bit clumsy. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+
For the original SQL, be sure to add to the where clause: and c:tabid = b:tabid Rob Schmitz 913-345-6281 Rob.B.Schmitz@mail.sprint.com -----Original Message----- From: Mark D. Stock [mailto:mdstock@MydasSolutions.com] Sent: Tuesday, July 15, 2003 5:22 PM To: ids@iiug.org Subject: Re: Sysmaster - Update Statistics Info [1558] Sonia Guerra wrote: > Hi Jerry, > > You can use the following query to get it. > > select > a.tabid, > b.tabname, > c.colname, > a.mode, > a.constructed > from sysdistrib a, systables b, syscolumns c > where a.tabid = b.tabid > and a.colno = c.colno > --order by 1,4,5 desc Except that this is looking at generated distributions, which are only generated by UPDATE STATISTICS MEDIUM & HIGH. There is no real way to determine if UPDATE STATISTICS LOW has been run. You can look at nrows in systables to see if the number of records is close to the actual number in the table, but a bit clumsy. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+