====== dostats ====== Dostats is written and maintained by [[useful:whoswho#art kagel|Art Kagel]] and the latest versions or Art's utilities can always be [[http://www.oninit.com/utils/index.php|here]] ===== Usage or semi-man page ===== Usage: ./dostats [-h host] -d database [-t table] [-p] [-f filename] [-g] [-N] [-i table_1 [-i table_2 [...]]] [-x [table_a|dbase_a:] [-x...]] [-i @[file1] [-i @[file2]]] [-x @[file1] [-x @[file2]]] [-G] [-i '!where clause'|subquery] [-x '!where clause'|subquery] [-D|-6|-5] [-S] [-v[-1|0|1]] [-e|-E] [-s] [-V] [-F] [-T] [-I] [-C] [-L] [-o] [-P procPDQ] [-Q tablePDQ] [-w Nsecs] [-R HighRes] [-r MedRes] [-c MedConf] [-Z MinSamp] [-m] [-b[-B BrowseThreshHold]] [-a [-A AgingLimit]] [-X] -h Host defaults to $INFORMIXSERVER. (The program uses multiple connections to the host. This requires a non-shared memory connection.) -d Database may contain valid "matches" clause wildcards. The string 'ALL' is equivalent to '*' and will cause ./dostats to execute against all databases except sysmaster. -t Table may contain valid "matches" clause wildcards. Table defaults to '*', all user tables (No system catalog tables.) The special name "none" can be used with the -i option to specify a limited list of tables with dissimilar names. -G Get physical row count using COUNT(*). On servers with a very large number of tables this option can improve runtime as sysptnhdr has no indices and must be scanned. -b Browse mode. Select only tables which have had their row count change by a percentage specified by the -B, browse threshhold, option since statistics were last updated. Overrides inclusions. This option requires a non-shared memory connection and so is not compatible with -S. -B Specify browse threshhold. Specify percent change used by the browse mode option (-b) to filter tables on which to operate. Range: 0.0001<->10000.0 (Default: 15.0%) -a Distribution Aging. Select only tables whose distributions are more than AgingLimit days old. If specified with -b adds tables rejected by browsing that satisfy the AgingLimit. Otherwise Aging eliminates tables otherwise included if they do no exceed AgingLimit. -A Specify distribution AgingLimit. Range: >= 0. (Default: 30 days) -p Update statistics for stored procedures. If -t is not also specified only stored procedures are updated. (Default: true if no -t option provided, false if -t, -x, or -i are provided.) -Q Set PDQPRIORITY for updating statistics on tables. (Default: Environment value of PDQPRIORITY or zero.) -w Specify wait setting for lock mode. >0 wait N seconds, <0 wait forever, ==0 set to not wait. -P Set PDQPRIORITY for compiling stored procedures. A PDQPRIORITY value for stored procedure compilation is now always produced. (Default: zero.) -f filename writes statistics commands to the named file and does not execute (default: Execute commands immediately). If filename is '-' output is written to stdout (errout should be redirected). -X Enable SET EXPLAIN ON during the statistics run. You can review the sqexplain.out file do see if other options and environment variables may improve the performance of dostats. See Informix manuals about using PDQPRIORITY, PSORT_NPROCS, PSORT_DBTEMP, DBSPACETEMP, and DBUPSPACE with UPDATE STATISTICS. -D Needed for 6.00 databases. Removes new 7.xx syntax. -5 Needed for 5.xx databases. Removes new ODS syntax. Only permits a single database to be processed. If omitted and SYSMASTER database not found this option is assumed. -6 Synonym for -D for consistency with the -5 option. -v Verbose mode. There are 4 modes: -1 - No progress output. Only print error messages. 0 - Normal progress output. The -v without argument is equivalent to -v0. 1 - Expands all explicit and inferred options in addition to normal progress output. 2 - Adds even more verbosity. -S Shared memory connection handling. Only permits a single database to be processed. -i Specify tables to forceably include in the tables processed. One may enter a single table, the @ sign followed by the name of a file containing a list of tables one per line, or an exclamation point (!) followed by either a WHERE filter to be applied to systables to select tables for inclusion, or a complete sub-query beginning with the keyword 'select' which will return a list of valid table names to include. To aid script use if the argument to -i is simply '@' stdin is read. Multiple -i options are concatenated to form a net list. Note that all -i options can be used together. (NB - Database includes are not implemented.) -x Specify tables or databases to forceably exclude from those processed. One may enter a single table or database, the @ sign followed by the name of a file containing a list of tables and/or databases, one per line, or an exclamation point (!) followed by a WHERE filter to be applied to systables to select tables to be excluded, or a complete sub-query beginning with the keyword 'select' which will return a list of valid table names to exclude. To aid script use if the argument to -x is simply '@' stdin is read. Multiple -x options are concatenated to form a net list. Note that all -x options can be used together. Database names are specified by appending a colon (:) to the name (ex: "sysutils:" to exclude the sysutils database). -s Recognize Standard Engine limitation of 8 index columns. Only permits a single database to be processed. -e Expand reporting to output elapsed time of each stage in seconds and fractions. -E Expand reporting to output elapsed time of each stage in hours, minutes, seconds and fractions. -V Print out dostats version and source code revision and exit. -F Disable single column index optimization. Use HIGH...DISTRIBUTIONS ONLY and LOW which is faster on some systems/databases. -N No specific keyword for updating stored procedures/functions. -o Override detected server optimization level. By default if the target engine contains the optimized UPDATE STATISTICS algorithms introduced in 7.31xD2 & 9.30xC3 all HIGH level distributions are requested with a minimum number of statements. For earlier releases of IDS HIGH level distributions are requested for a single column at a time. This option reverses this behavior so that scripts for use in an advanced engine can be created from a schema loaded on an earlier release and vice-versa. -R HighRes sets resolution for HIGH Level distributions. Calculate distributions by dividing keys into (100 / HighRes) bins each with approximately the same number of keys. Valid range: max(0.005,1/nrows) <= HighRes <= 10.00 Default: 0.5 (200 bins) -r MedRes sets resolution for MEDIUM Level distributions. Calculate distributions by dividing keys into (100 / MedRes) bins each with approximately the same number of keys. Valid range: max(0.005,1/nrows) <= MedRes <= 10.00 Default: 2.5 (40 bins) -c MedConf set confidence level for MEDIUM Level distributions. Valid range: 0.80 <= MedConf <= 0.99, Default: 0.95 -m Enable processing of system catalog metadata tables. (Default: disabled) -Z Set minimum sample size for MEDIUM resolution. (0 < MinSamp < nrows) By default all of -T, -I, & -C flags are set. Manually specifying any of -T, -L, -I, or -C disables any of the others not also explicitly specified. Enabling -T -I emulates dostats version 1. Options -T & -L are mutually exclusive if both are given the last prevails. -T Enable table level statistics update to MEDIUM. -L Set table level statistics update to LOW and drop distributions. -l Set table level statistics update to LOW keeping existing distributions. -I Enable statistics update of first column of each index to HIGH. -C Enable statistics update indexed columns to LOW. -g Enable statistics update fragmentation expression columns to HIGH (ie treat like first index key columns). Setting PDQPRIORITY in the ./dostats environment causes an effective argument of '-P 0' to be added the commandline to protect procedures. The environment variables INFORMIXUSER and INFORMIXPASS can to used to specify a username and password (respectively) other than the current user's id and password. ===== dostats Cookbook =====