IDS Forum

Re: how to install & run do_stats

Posted By: Art Kagel
Date: Wednesday, 10 June 2009, at 9:36 a.m.

In Response To: how to install & run do_stats (KAMRAN HAQ)

OK, first problem, you unpacked the utils2_ak package, but you did not
compile/build the executables. The package contains only one script that
being the drive_dostats script which is used to run mulitple copies of
dostats for a database. All other utilities in the package are ESQL/C
source code and have to be compiled to a native executable before you can
use them. Indeed, since drive_dostats runs copies of dostats you must
compile the package before even drive_dostats will work.

I will try to help you. If you continue to have problems, please, email me
directly to avoid taking up forum bandwidth and I'll do what I can. What
platform are you running on? What version of IDS? PLEASE read the BUILDING
text file, it will tell you how you may have to modify the makefiles
(Makefile and myschema.d/myschema.mk.norcs) to correctly compile the package
for your platform. The defaults in the makefiles are set up for a Linux
platform and will also usually work on Solaris and AIX if you are using
GCC. If you are using the native compilers on anything other than Linux you
will have to modify the compiler options in the makefile. In addition, if
you do not have GNU Make, you will have to edit the makefiles to remove some
conditional code the makes it easier for users who do not have ESQL/C or the
CSDK to compile the package using C4GL. All of this is documented in the
BUILDING file.

On your other, sort-of-related comments, you said:

*I tested the results for update statistics on different machines and found
no performance gain whether I "set pdqpriority" or not.*

Please read John Miller III's White Paper on running UPDATE STATISTICS
efficiently (
http://www.ibm.com/developerworks/db2/zones/informix/library/techarticle/miller/0203miller.html)
for details, but the quick and dirty is you have to configure several
related things to get best runtimes for UPDATE STATISTICS. PDQPRIORITY is
just one of these. Others are the amount of memory available for in-memory
sorting, how many independent disk structures are available for pushing
sort-work files to disk if the entire sort will not fit in memory
(DBSPACETEMP and/or PSORT_DBTEMP) and what kind they are, whether and the
degree to which you enable parallel sorting using PSORT_NPROCS.

You also ask:

*So would we do "update statistics for procedures" on every "update
statistics medium/high for tableName(columnName)" ?*

Yes, you should recompile all stored procedures that reference any tables
that have had their data distributions updated because that effort will have
invalidated the query plans stored for the procedures at compile/creation
time. This recompile will happen automatically the next time the procedure
is executed but that compile will lock the procedure's query plan and
executable pcode causing other sessions trying to run the procedure to get
the sysprocplan lock you mentioned. This is best done, as you inferred, at
the time that the stats were updated for the table when it is presumed noone
is trying to execute the procedures. My dostats utility will do that for
you automatically unless you tell it not to with commandline options.

Oh, if you are running an IDS version earlier than 11.50 or a CSDK version
earlier than 3.50 you will need to add the following to the EFLAGS variable
in the makefiles: -EDbigint=int This is not yet documented in the BUILDING
file.

Again, if you have any trouble building the package, do not hesitate to
email me. I likely will know what the problem is and how to fix it.

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Wed, Jun 10, 2009 at 7:55 AM, KAMRAN HAQ <khaq@i2cinc.com> wrote:

> I tried to run do_stats but failed below are the results (commands used and
> their output). Morevoer I tested the results for update statistics on
> different machines and found no performance gain whether I "set
> pdqpriority"
> or not. Also when we update the stats for certain columns the corresponding
> procedures (which are using these columns) get problems because of
> sysprocplan. So would we do "update statistics for procedures" on every
> "update statistics medium/high for tableName(columnName)" ?
>
> ---------------------------------------------------------------------
> -- do_stats results
> ---------------------------------------------------------------------
>
> -- installation
> # sh utils2_ak
>
> -- output
> utils2_ak: md5sum: not found
> Note: not verifying md5sums. Consider installing GNU coreutils.
> x - created lock directory `_sh07999'.
> x - extracting BUILDING (text)
> x - extracting README.1st (text)
> x - extracting myschema.README (text)
> x - extracting Makefile (text)
> x - extracting drive_dostats (text)
> x - extracting mydbdiff (text)
> x - extracting getopt.h (text)
> x - extracting getopt.c (text)
> x - extracting dbcopy.ec (text)
> x - extracting dbdelete.ec (text)
> x - extracting dbstruct.ec (text)
> x - extracting dostats.ec (text)
> x - extracting listdb5.ec (text)
> x - extracting listdb7.ec (text)
> x - extracting printfreeB.ec (text)
> x - extracting sqlstruct.ec (text)
> x - extracting ul.ec (text)
> x - extracting myschema.source.ar (text)
> x - removed lock directory `_sh07999'.
>
> -- trying to run dostats
> # ./drive_dostats 1 db_monitoring -x table_io
>
> -- output
> excl= /tmp/drive_dostats
> excludes=
> file=/tmp/drive_dostats
>
> Database selected.
>
> create temp table dr_dostat_excl (tabname char(128)) with no log;
> Temporary table created.
>
> load from "/tmp/drive_dostats" insert into dr_dostat_excl;
> 846: Number of values in load file is not equal to number of columns.
>
> 847: Error in load file line 1.
> Error in line 1
> Near character position 58
>
> select count(*) from dr_dostat_excl;
>
> (count(*))
>
> 0
>
> 1 row(s) retrieved.
>
> unload to "/tmp/drive_dostats.18519.unl"
> select tabname
> from systables
> where tabid > 99
> and tabname matches "*"
> and tabname NOT IN (SELECT tabname from dr_dostat_excl);
> 16 row(s) unloaded.
>
> Database closed.
>
> Database selected.
>
> create temp table dostats_tables (tabname char(128)) with no log;
> Temporary table created.
>
> load from "/tmp/drive_dostats.18519.unl"
> insert into dostats_tables;
> 16 row(s) loaded.
>
> unload to "/tmp/drive_dostats.18519.unl"
> select stn.tabname, sum(nrows)
> from systabnames stn, sysptnhdr sp, dostats_tables dt
> where stn.dbsname = "db_monitoring"
> and stn.partnum = sp.partnum
> and dt.tabname = stn.tabname
> group by 1
> order by 2 desc;
> 16 row(s) unloaded.
>
> Database closed.
>
> ARGS are: table_io
> dostats -d db_monitoring -t none -i@/tmp/drive_dostats.18519.0 table_io
> Waiting for copies of dostats to complete
> Dostats complete logfiles are:
>
> /tmp/drive_dostats.18519.0.log
>
> -- contents of file created
> # cat drive_dostats.18519.0.log
>
> -- output
> ../drive_dostats[164]: dostats: not found
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636c5b2f039de4c046bfe910b

Messages In This Thread

• how to install & run do_stats
  KAMRAN HAQ -- Wednesday, 10 June 2009, at 7:55 a.m.
  • Re: how to install & run do_stats
    Art Kagel -- Wednesday, 10 June 2009, at 9:36 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.