how to install & run do_stats

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 1Near 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

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.@@N