Dostats
Utility: dostats.ec Synopsis: Dostats automatically generates optimal UPDATE STATISTICS statements for a table(s) or database(s) per the 7.2 release notes, the latest Performance Guide manual, and John Miller III's paper on recent improvements to way that UPDATE STATISTICS works internally and how to take advantage of those improvements. Options control what databases and tables are affected, whether commands are executed or output to a script, whether and how stored procedures are handled, the level of verbosity, tweaking the granularity of the statistical distributions captured, specifying criteria for selecting tables to update, and much more. Author(s): Art S. Kagel Revision: 1.142 Features Version: 5.10 Version(s) supported: Engine versions 5.xx, 6.0x, 7.xx+, 9.xx, 10.xx ESQL 7.2x+, iSDK 2.10+, C4GL 7.20+ (see Makefile for changes needed to compile w/ C4GL or ESQL/C 7.xx) File(s): dostats.ec Comments: Requires compilation by ESQL/C 7.xx or later but can be executed against databases on IDS or SE servers from 5.0x and up from a 7.xx runtime environment. Can execute commands on the fly or output a script for execution later. Options control level of stats created and what objects are processed. Implements the recommendations from the Performance Guide for 7.3x and 9.xx. Release Notes: Revision 1.140 through 1.142 added -w option to allow the user to specify lock mode wait behavior. Revision 1.139 added code to eliminate another upgrade procedure ' sysdistold'. Thanks to John Carlson for pointing out the problem. Release 1.138 added code submitted by Douglas Palmer to eliminate some IDS 11 procs from update stats. Thanks Douglas. Release 1.137 fixed a typo in the DBUPSPACE warning. Thanks to Alinda Watson for pointing it out. Fixed compilation problem in getopt.c on Linux. Thanks to Jim Kenedy for pressing the issue until I found it. Release 1.136 Continued the code cleanup. Fixing *printf format complaints and removing unused vars (except one left in for possible future use). Also updated the getopt.[hc] from the later version in the myschema subdirectory to fix compilation problems on HPUX & Linux. Release 1.132-1.135 Fixed a bug in the optimized server version detection code which would fail to correctly detect 2 digit patch levels for 7.31 and 9.30. Thanks to Richard Harnden for finding this one and providing the fix. Release 1.131 Adds support for the IDS v11 SAMPLING SIZE feature as -Z<minsamp>. Release 1.130 Removes requirement to specify -t (at lease -tnone) with -i. The code does not actually need this restriction to work correctly so it's gone. Release 1.129 Fixed problem affecting portability to some older broken ESQL/C versions. Code cleanup. Release 1.128 fixes a bug detecting the server version if -S was specified. This could cause dostats to fail to correctly support IDS 9 & 10 server features. Also added verbose level -v2 which prints the server version strings. Thanks to Doug Conrey for pointing out the problem and performing the initial investigation. Release 1.126 - 1.127 Expand the verbose (-v) option to provide additional modes of verbosity including suppression of all progress output. Also added an error message if -S is not specified and the argument to -d does not resolve to a list of one or more databases (with -S a -329 error was already being produced if the named database did not exist). Thanks to Ravi Krishna for the suggestion. Release 1.125 Fixed SEGV processing some parameter lists. Added hidden diagnostic argument, -z, to output raw and cooked parameter lists for debugging. Still don't know why this was SEGVing for Colin as none of his parameter lists were longer than the word buffer I expanded, but it seems to be working for him now. Release 1.122-1.124 Seek to eliminate SEG Faults by replacing calls to sprintf with calls to snprintf and expanded several temp strings to better handle long user and host names. Also found a problem handling functions marked in the catalog to have an argument list but really have an empty one and specific routines with a bogus specific name. Thanks to Gary Quiring and Colin Dawson for their persistence in getting me to track this one down, especially Gary. Release 1.121 Changes all error messages to include the string 'FAILED' to simplify scripting. Also fixed a bug in the new stored routine handling code added in 1.120 that failed on 7.xx servers. Release 1.118 - 1.120 fix the problem correctly processing stored procedures in 9.xx+. If -N is not specified on 9.xx+ functions routines will be processed either using their specific name or if none using the complete argument type list. If -N is specified then overloaded routine names will only be mentioned once with no argument list to force compilation of all overloaded routines with that name. Release 1.117 fixed a line of code commented out for testing and not restored. This was causing some garbage in the progress output. Release 1.116 fixed an array overrun and an unterminated comment. Release 1.115 fixes a problem handling tables with very large numbers of columns or very long column names when optimized server handling is enabled. Thanks to Roy Mercer for reporting the error. Release 1.113 & 1.114 Improved 'working on...' progress reports with optimized server handling enabled. Original output was confusing. Release 1.112 Fixed a problem with outputting garbage ownername for first table under some odd conditions. Release 1.111 removes some debug trace and fixes a left over test mode that prevented -G from operating properly. Many thanks to Hannes Visagie for identifying the sort direction problem and for pressing the issues he faces using dostats on servers with huge numbers of tables which effort resulted in the -G and enhanced -i/-x features. Release 1.110 expands -i! & -x! to allow either a WHERE clause or a full sub-query. Either way, multiple -i! or -x! options are now allowed as is mixing the ! versions with the other -x & -i flavors. Multiple -i! option filter results are OR'd and multiple -x! options filter results are AND'd. Now the user has complete flexibility to use a list of tablenames, a file of names, a table of names, and combinations of these. Users can encode run codes in the name list table and reference those codes in the -i/-x sub-query to select tables for multiple parallel runs. A stored procedure that returns just a list of tablenames can also be used in the select list of the sub-query for more complex filters. Upped the Feature version to 5.10 Release 1.109 fixes a problem with processing keys multiple times if multiple indexes have the same key columns but with different sort direction (ASC/DESC). Improved include and exclude sub-query process entry. Removed some restrictions on mixing includes and excludes. Release 1.108 adds a new option, -G, to always get actual row counts for browse (-b) mode operations using COUNT(*). This is needed for databases with thousands of tables where the select from the unindexed sysptnhdr pseudo table is taking too long. Also added the option to -i & -x to specify a WHERE clause to a subquery on systables for the IN clause instead of listing tables singly or by a file. Release 1.107 adds a new option, -N, to suppress the SPECIFIC keyword in 9.xx when updating stats for procedures/functions. Apparently there is a bug in IDS 9.xx that causes massive logical log usage if SPECIFIC is included. This is only needed if you have overloaded function names so one can suppress if otherwise. Known problems: Future: Rewrite old method code to use new code path which is simpler. ?? Other suggestions ??