Contents of shell archive utils2_ak, submitted by Art S. Kagel: Version dated: January 25, 2012 Author's contact address: art@advancedatatools.com The shell archive has been corrected to use the user's preferred pager depending on the PAGER environment variable and defaulting to 'more'. NOTE: I think the entire package will now correctly compile/link with any of: C4GL 7.xx, ESQL/C 7.xx, or cSDK 2.xx or later, though you may have to edit the makefiles and make the changes noted therein in comments. Unfortunately making this happen for less make-aware 4GL programmers requires using some GNU Make features so the makefiles now require GNU Make to run correctly. GNU Make is available from the Free Software Foundation at www.fsf.org. If you do not have GNU Make and are too impatient to download or build a copy of it, you can edit the makefiles (the main one and the myschema makefile as well) and the ifeq/ifneq blocks commenting out the flow control and whichever block you do not need. ---------------------------------------------------------------------------- File: BUILDING Notes, instructions, and suggestions to aid one in building the package on various platforms. This file now automatically displays when you extract the shell archive. Also the shar now offers to also display the myschema.README which has further instructions for that utility. ---------------------------------------------------------------------------- File: Makefile 2010-12-06: Fixed Makefile.nognumake and myschema.norcs.nognumake to work correctly on AIX. Increased the memory for the xlc optimizer when compiling on AIX. 2009-07-22: Added an 'install' target. INSTALLDIR variable controls the location to which executables and scripts are installed. Also added a new makefile, makefile.nognumake that has the changes for using UNIX make if GNU make is not available. When ul.ec is compiled now the makefile will also link that executable to the names bload and bunload and install those as well as ul. Yeah! I finally did it. Here is a makefile that will make the entire package for you. (No Jonathan, I have not yet configurized it.) Therefore if you are compiling on HP or AIX or on Solaris 9+ (64bit), or are using c4gl instead of esql to compile, you will need to make some changes to this Makefile. All of the appropriate changes of which I am aware are included as comments so you just have to swap commented lines about. If you want to build myschema and are on one of the platforms above or are not using RCS you will have to extract the source archive, myschema.source.ar, manually (see the Makefile) into the myschema.d subdirectory, and modify the appropriate makefile (see the myschema.README file for details), then run make from the parent. Reworked to permit compilation of dostats.ec using c4gl versions that do not support host variable declarations in ANSI style function parameter lists. I've used GNU make syntax so if you only have UNIX make available you'll need to edit the Makefile to either remove the ifeq verb or (if you are compiling with C4GL) change it to shell logic. Added a CFLAGS define option to use if your platform does not define getopt() and its globals optind and optarg in stdio.h or stdlib.h. Especially needed in dostats.ec except on Linux which is handled without this define. Improved option suggestions for AIX. ---------------------------------------------------------------------------- Utility: mydbdiff Synopsis: Compare two database schema's using diff or sdiff Author: Art S. Kagel Revision: 1.3 Comments: Requires myschema Features Version 6.25 or later (included here). Mydbdiff is a ksh script that takes advantage of an feature of myschema (-o) to acquire a schema of each database with objects ordered to ease comparison. While the myschema -o option should not be use to create a schema file for porting (as you may not be able to execute such a schema because dependencies may not be created in the correct order) a saved schema created this way can be compared to the active database schema to determine changes over time using the -f option. Release Notes: Releases 1.2 & 1.3 add new features. The new -d option allows one to specify any diff utility. The -S option allows one to specify that one or both of the comparative schemas be saved in a file in the current directory named .mydbdiff. The -f option allows one to compare a live database's schema to a comparative schema previously saved using the -f option or manually generated using myschema -o. The combination of -S and -f allow for tracking changes to a single database over time. ---------------------------------------------------------------------------- Utility: drive_dostats Synopsis: This is a ksh script that will divide the tables from a single database among N copies of dostats running in parallel. Tables are distributed according to size in round robin fashion. Any dostats options that do not conflict with the -d, -t and -i options passed by the script will be passed along to all copies of dostats. Author: Art S. Kagel Revision: 1.8 Comments: As usual, running drive_dostats with no arguments will display a usage aid. Thanks to Douglas McAllister for the inspiration. Release Notes: Version 1.8 adds -i and -i@ options which properly process included table lists and work correctly with the -x/-x@ options. Fixed a bug checking for runs that do not select any tables on Linux. Version 1.7 fixes a problem arising out of differing output formats from varying versions of wc on different platforms. Fixed missing NL at EOF. Version 1.5 fixes a problem handling really HUGE exclude files. Dostats, and until this release drive_dostats also, handles exclude files by building a large 'NOT IN (...)' clause. This fails if the exclude file contains more than about 30,000 bytes as the maximum size of a SQL statement that IDS will process is 32K. Drive_dostats now loads the exclude list into a temp table and uses that to produce the include files the several copies of dostats process. Drive_dostats can now be used to process very large exclude files. Version 1.2 & 1.3 add new options -x and -x@ so users can specify specific files or lists of files to exclude from processing. This is similar to the underlying -x options to dostats itself except that database names are not handled. Multiple -x and -x@ options are accepted and may be intermingled. Changed the allocation of tables to the dostats children to more evenly distribute the work. Allocation now sweeps back and forth across the children rather than the strict round robin allocation of v1.1. Improved the filtering out of system catalog tables, non-catalog tables beginning with 'sys' were being ignored improperly. Version 1.4 just removes a leftover debug line. Notes in the script suggest alternative sorting criteria to better allocate tables to children, implementation left to user's needs. Added echos of the commandlines passed to the children. ---------------------------------------------------------------------------- Utility: dbdelete.ec Synopsis: This utility, was written to delete large numbers of rows while using very few locks and avoiding long transaction problems. The performance of version 3 is on par with a dbaccess executed DELETE statement while eliminating the problems with locks and transaction duration. Versions 1&2 were too slow to be practical and version 1's source was overly complex, thus was born dbdelete.ec version 3. Note that in testing no throughput was gained from executing multiple copies of dbdelete with different WHERE clauses (unlike its progenitors ul.ec and dbcopy.ec). Author(s): Art S. Kagel Revision: 1.25 Features Version: 3.2 Version(s) supported: 7.xx, 8.xx, 9.xx File(s): dbdelete.ec Comments: Requires compilation by ESQL/C 7.xx or later. Release Notes: Version 1.25 Added new option -R which begins all transactions with the WITHOUT REPLICATION clause which prevents the operation from being replicated via Enterprise Replication. Thanks to Øyvind Gjerstad for the suggestion. Also increased the size of the maximum SQL DELETE command (for rowid deletes) from 32750 to 65500 to reflect the increase in the SQL buffer size starting with IDS v9.40. Version 1.24 updates the copyright statement for 2011 Version 1.23 adds a new option, -o, that instructs dbdelete to not close and reopen the fetch cursor between rounds of fetching keys or rowids to delete. This can be slower for deleting massive numbers of rows. The purpose is to reduce (but not eliminate) the possibility of deleting rows that have been inserted to the table during the dbdelete run that match the selection criteria. Ideally, dbdelete should NEVER be run while new rows are being inserted. Even with -o set there is a window of time - really between when the user determines the key values to use for the WHERE clause filter and when the cursor is finally open in dbdelete - when this can still cause a problem. Version 1.22 adds a new option, -q, which permits the user to specify a full SELECT statement to return ROWIDs or keys to select for deletion. If keys are returned then the -u option is required for -q to work properly (unexpected results may pursue you if you don't). Version 1.20 fixes a bug affecting 64bit compiles. Thanks to Raja Nizad Ks for the feedback that led to this fix and for testing the patch. Version 1.21 is cosmetic. Version 1.17 adds the -b CommitSize option and support for FET_BUF_SIZE environment variable to control transaction size. Version 1.18 increases the Features Versions to 3.2. Version 1.19 fixes a typo. Version 1.16 removed some dead code and fixed the return type of usage(). Version 1.14 makes the code safe to use with IDS 9.xx long object names. Version 1.15 just updates the copyright statement. Version 1.13 adds a copyright message to the -V printout. Not to worry, this is just a formality. Use of this utility is still free. Version 1.12 fixed a missing break statement. OK, I give up. Version 1.11 replaces itoa() for all systems with sprintf(). The user support was no longer sensible for the few cycles I gained on a handful of systems. Version 1.9 add #if defined()'s to better support compilation on AIX without GCC. Version 1.10 uses the predefined _AIX rather than the standard __AIX__. Version 1.7 extends support for engines that do not support array fetch code. Now builds the large IN clause anyway. Version 1.8 is cosmetic only. Version 1.6 just improves portability. The code uses itoa() to convert from integer to string, a function which is not universally available. There is now a #if near the top which uses numtos() on Sun systems instead and if neither is available defines an inline function using sprintf which you can enable by defining '-Dnoitoa' at compile time. Notes: Delete by ROWID works by fetching a large number of matching rowids (8191) and using them to build large delete statements with a WHERE rowid IN (...) clause. The size of each of these delete statements is controlled and limited by the -B option. If the statement is too large the time spent by the engine parsing and optimizing the large statement reduces throughput. On my testbed 2K was the best performer overall and that is the default, YMMV so this is configurable! ---------------------------------------------------------------------------- Utility: dostats_ng.ec Synopsis: Next Generation dostats utility. Dostats automatically generates optimal UPDATE STATISTICS statements for a table(s) or database(s) per 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 immediately, written to a stored procedure and scheduled for later execution, 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.26 Features Version: 7.00 Version(s) supported: Engine versions 10.00, 11.xx & later CSDK v3.10 and later Release Notes: Revision 1.27 Fixed a bug in SET ISOLATION when executing immediately. Fixed a bug in --drop-distributions. Added --clean-distributions for cleaning out old distributions when only specific tables are being processed. Can be used globally, but --drop-distributions will tend to be faster. Restricted --drop-distributions from being used with -t, -x or -i since it would drop distributions from all tables and then only rebuild the distributions for the specified tables. Thanks to Ashok Sharma for pointing out these bugs. Revision 1.26 Fixed to output SET ISOLATION commands to the command file or procedure if not executing directly. Revision 1.25 Fixed a problem with dostats not able to compile old systdist functions left over from an in-place upgrade. Some versions renamed the older function as systdistold, others as oldsystdist. One code path caught and ignored both versions of the renamed function, the other was still trying to compile systdistold. Revision 1.24 Removed extraneous COMMIT WORK statements for ANSI mode databases. Fixed a missing CLOSE when a loop made a shortcut exit. Revision 1.23 Fixes a bug in the manual row counting function that affects sites with tables having more than 2^31 rows in a table. Revisions 1.18-1.22 Improved -i'!SELECT ...' to treat the SELECT statement as a derived tables when communicating with v11.xx. This will allow the user to include SELECT clauses which are restricted from sub-queries which were used previouse (and are still used to communicate with v10.00). Deprecated support for -x'!SELECT...' since now it is always possible to negate the filters in such a statement and pass it as a SELECT to -i instead. Deprecated -v with no arguments. Revision 1.17 Fixed bug when running against an ANSI mode database. Periods missing from some statements between the owner and object names. Revision 1.16 Adds a new feature, --proc-local. This supports earlier Informix releases that did not have sysadmin database by creating the table containing commands for the stored procedure to use in each database rather than in sysadmin. Revision 1.13 updates the copyright statement for 2011 Revision 1.12 Added new options --force-run and --auto-run to enforce FORCE mode or AUTO mode disregarding the AUTO_STAT_MODE setting in the ONCONFIG file and the environment. Revisions 1.9 - 1.11 Fixed procedure so it will not error out recompiling itself. Revision 1.8 Fixed a problem with scheduling the stored procedure. Revision 1.7 fixes an intermittent problem returning SQLCODE = -9971 Revision 1.6 adds a new feature, --isolation allows one to specify the isolation level dostats uses when querying sysmaster and the target database. Revision 1.4 & 1.5 add a new option, --drop-distributions, which will initially delete all data distributions from the sysdistrib catalog table. Revision 1.3 is the first official release of this next generation dostats utility. Only one new feature is included: --time-display prints the start time of each step that is executed to make parsing trace logs easier and to help determine if dostats runs are affecting other tasks. This release DOES NOT support any Informix server version earlier than 10.00 no stand-alone ESQL/C or 4GL compilers or CSDK versions earlier than 3.10. This is to permit significant code simplification (1400 lines removed) which resulted in correcting a long standing bug: The table level trace "Working on ..." now works correctly and consistently. Beta testers reported that this release runs faster than the older version. All new features will be added ONLY to this version of the dostats source. ---------------------------------------------------------------------------- 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.180 Features Version: 6.00 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 plus John Miller III's recommendations in his White Paper on the topic. Release Notes: Revision 1.180 Fixed bug when running against an ANSI mode database. Periods missing from some statements between the owner and object names. Revision 1.179 updates the copyright statement for 2011 Revision 1.176-1.178 Fixed procedure so it will not error out recompiling itself. Revision 1.175 Fixed problem with creating the stored procedure. Revision 1.174 fixed a missing table alias affecting server versions earlier than 9.xx. Thanks to Richard Harnden for pointing out this one. Revision 1.174 & 1.175 Fixed missing table alias. Thanks to Richard Harnden for pointing it out. Fixed problem with scheduling the stored procedure. NOTE: 1.173 is the last feature release of dostats.ec. This source has been supplanted by the new generation source file, dostats_ng.ec. This source will only receive bug fixes in the future. See details below. Known problems: Future: ---------------------------------------------------------------------------- Utility: myschema.source.ar Synopsis: My version of dbschema which has a many advantages over the original: o can generate modified schemas for porting including: o altering or dropping owners o eliminating or modifying EXTENT SIZE and NEXT SIZE settings o can access remote databases requiring authentication o optionally report foreign keys referencing a single table schema o does not try to acquire an exclusive lock on the table(s) being reported. o eliminates those pesky automatically generated constraint names to avoid clashes with existing tables o breaks indexes created by UNIQUE, PRIMARY KEY and FOREIGN KEY constraints out as stand alone indexes, with user accessible names, to make reorganization and defragmentation easier. o table create and indexing commands can be written to separate output schema files. o GRANT statements optionally written to a separate file. o GRANT statements immediately follow each table to make it easier to cut and paste. o GRANT statements generated even for single table schema output. o comments indicate where changes have been made to the current schema when generating the output due to myschema options. o will report on system tables, violations tables, and SMI tables which dbschema refuses to do. o can optionally output only UPDATE STATISTICS commands to duplicate each table's level of statistics. o treats tables, views, and synonyms the same so you don't have to know if a tablename is a real table when you run it. o quiet mode to aid piping output to dbaccess/isql/sqlcmd. o compatible with OL5.xx and SE. Dbschema 5.xx does not support IN clauses. o output optionally compatible with dbimport. o optionally output table reorganization commands including altering NEXT SIZE. o generate IN clauses for all tables to ease editing for porting to another server. o can suppress output of START VIOLATIONS statements. o optionally initialize the next serial value to either MAX(col)+1 or the value stored in sysptnhdr.serialv. Similarly for BIGSERIAL and SERIAL8. o outputs CREATE AGGREGATE statements which dbschema does not do. o outputs ALTER TABLE statements for tables created OF TYPE that have been altered since creation adding NOT NULL or DEFAULT clauses to a column. Dbschema and dbexport do not do this either, careful! o multiple permissions on the same object compressed into a single command. o places UDR language privileging at the top of the schema (dbschema places this near the end) to make it easier to alter the privileging when porting a development schema to production Myschema also has one major dbschema feature missing: no -hd option and a few of the more esoteric features added in v9.40+ of dbschema like -u all. Author(s): Art S. Kagel Revision: 2.266 Features Version: 6.32 Version(s) supported: IDS 11.70, 11.50, 11.10, 10.xx, 9.xx, IDS 6/7.xx, OL 5.xx, SE 7.xx : Requires ESQL/C 7.2x or SDK 2.xx or later (ESQL/C r7.2x and C4GL are now fully supported excepting extended types). File(s): myschema.source.ar, a System V 'ar' archive which contains the many source files (get GNU ar from FSF or my ar2 package from the IIUG if your system's ar is BSD format - to my knowledge this includes only AIX currently) Comments: PLEASE let me know if the IDS 9/10/11 support is not complete or otherwise broken. Release Notes: Revision 2.266 Updated the copyright notice. Revision 2.265 Shifted the implicit/explicit keyword to upper case for consistency. Fixed a bug that was ignoring casts from or to built-in (non-UDT) types. Revision 2.264 Added support for External Spaces to --infrastructure. Handled SQL API version better than dbschema, shelling out to onspaces rather than just putting the suggestion in the output. Revisions 2.262 & 2.263 Fix handling of --drop-proc for functions with SET/LIST/MULTISET arguments. Also added REVOKE EXECUTE FROM PUBLIC after the create. If public should have privileges a grant to that effect will also be output. Revision 2.261 Added new feature --drop-proc to optionally insert a drop procedure statement before each create. Thanks to Bruce Simms for suggesting and funding this one. Revision 2.260 Made changes to print_infrastructure.ec to support versions of IDS earlier than 11.xx that do not have dbschema -c support. Revision 2.259 Fixes a problem preventing use with non-logged databases. Fixes problem compiling with ESQL/C versions earlier than 3.50. Revision 2.258 Implements new feature. --infrastructure is like dbschema -c but fixes some of the problems with that feature. Revision 2.257 Fixed problem causing SEGV processing private synonyms, also reordered the output so private synonyms sort to the end with the public synonyms. Revision 2.256 Fixed a problem outputting STATLEVEL clauses under specific conditions. Revision 2.255 Fixed a typo in the output of OP Class Strategies. Revision 2.254 Added a #if defined() to use of SQLINFXBIGINT in print_defaults.ec to better support IDS 10.00 and 11.10 users. Removed stropt.h inclusion on Linux. Apparently some versions of Linux (RHEL for one) do not include this header (Ubuntu does). At any rate, it is not needed on any Linux (I forget which platform I had to add it for, so only removed for Linux). Thanks to Kenneth Penza for pointing these out. Revision 2.253 Updates the copyright notice. Revision 2.252 Fixed problems handling SERIAL8 type column initialization. Added support for STATLEVEL and STATCHANGE in CREATE TABLE statements. Added support for SECURITY POLICY in CREATE TABLE statements. Output of policy definitions is still not supported. New option --force_autostats to force the output of STATLEVEL AUTO when appropriate. This is the default for all tables and so is not printed normally. Revisions 2.250 & 2.251 Fix bugs. Isolation DIRTY READ was not being set in the database being reported, only in sysmaster. Sequences with "NOCACHE" were incorrectly reported as "CACHE 0". Thanks to Cesar Martins for pursuing these bugs. Revision 2.249 Fixed a bug that prevented printing the column names of the hidden columns for VERCOLS, REPLCHECK, and ERKEY in indexes that include those columns in their keys. This would probably have broken indexes for tables that add columns after defining the hidden columns as well. Thanks to Jeffrey Michell for pointing this one out. Revision 2.248 Adds a new feature: --no-blade-funcs (alias --no-blade-procs) to eliminate stored routines associated with IBM Datablades from the output since they auto-install. Revision 2.247 Fixed a bug that caused some columns to be dropped under specific conditions. Sometimes this caused syntax errors running the schema, other times it was a silent loss of a column. Revision 2.246 Fixed problems with --myexport-scripts, etc. Was including external tables (probably views also) in export scripts for exporting with external tables. Scripts were missing the load file name also. Revision 2.245 Added support for environment variable MYEXPORT_ISOLATION to allow myexport to set the isolation level in the export script generated for exporting with external tables. Revision 2.242-2.244 Fixed several queries so that myschema will work with 10.00. Revision 2.241 Support for BIGINT type defaults was missing. Fixed. Thanks to Dusam Simic for catching this one and reporting it. Revision 2.240 Improved the calculation of default pagesize for servers earlier than 11.50. Revision 2.238 & 2.239 Had to recode the EXTERNAL table definitions to produce column lists instead of using SAMEAS because SAMEAS includes any hidden columns (except CRCOLS) which messes up the unload/load scripts. Personally I don't think SAMEAS should work this way. Revision 2.237 Adds a new option --reorg-api which generates Admin API function calls to reorganize selected tables. Revision 2.236 Adds new option --myexport-express. The --myexport-scripts now produces a load file using EXTERNAL TABLES in DELUXE mode. This new option is used to generate EXPRESS mode import scripts. Individual scripts are now generated for each table to ease scripting load scripts. Revision 2.234 & 2.235 Fixed two compile time warnings and a runtime error in print_indexes.ec. Thanks to Oyvind Gjarstad for pointing these out. Revision 2.233 Fixed -l (dbexport compatibility option) to ignore vercols, replcheck, and erkey columns in the column count. Revision 2.232 Adds support for WITH REPLCHECK, WITH ERKEY, and WITH AUDIT clauses in CREATE TABLE. Revision 2.231 Added options to support advanced myexport and myimport scripts using external tables. Functional for IDS 11.70.xC1 and later. Revision 2.226-2.230 Added support for new 11.70 features. Added new --create-conditionally feature to optionally add " IF NOT EXISTS " clauses to object CREATE statements. Added support for External Directives. New options --suppress-directives and --directives-only to control external directives output. Support added for Forest of Trees indexes, Index Extent Sizing, List Fragmentation, and Range/Interval Fragmentation. Revision 2.225 Fixed compile time warnings in several files and a couple of bit test logic bugs in print_puts.ec. Thanks to John Adamski for pointing these out. Revision 2.224 Fixed a bug processing disabled triggers. Revision 2.223 Added new option --wrap-to to specify the wrap column for trigger output. Default is 80, min is 40, -1 means no wrapping. Fixed a bug that line wraps quoted strings when the opening quote is not preceded by a space. Thanks to Joseph Jurcazak for pursuing this one. Revision 2.222 Forgot to initialize the new sequence name match default. Fixed. Revision 2.221 Fixed a bug that prevented sequence output for IDS 10.00 and 11.10 servers (oops). Implemented new option --sequence, --sequence='matches-spec' to print only sequence definitions. Revision 2.220 Fixed object state for unique and foreign keys. Reverted current value detection to use the nextval function as dbschema does. Using the sysactptnhdr record turns out to be unreliable. It doesn't seem to be updated consistently. Revision 2.219 Modified sequence support to get the last sequence value from sysmaster:sysactptnhdr if the database has logging. This will prevent myschema from incrementing the value for those databases that are logged. Revision 2.218 Adds support for default ROLES. Revisions 2.216 & 2.217 Fix the SPL parsing AGAIN and add a new option to ignore debugging procedures added by AGS Server Studio's SPL Debugger. Also added a warning when such routines are found without --no_ags_procs. These debugging procs and the instrumentation that SS adds to debugged routines should properly be removed in Server Studio instead. The parse change better handles weird routine source that contains comments before the first semi-colon which can only be created by a host language program using CREATE ROUTINE FROM ; Revision 2.215 Fixed problem handling procedures added to the DB by AGS Server Studio's SPL debugger. They have copyright comments between keywords within the CREATE statement itself. Revisions 2.214 Fixes a problem with using --no-extent-clause with fragmented tables. Revisions 2.211 - 2.213 Fix oversights in --set-owner. Revision 2.210 Added new option --set-owner to override all object owners with a specified owner name. Revision 2.209 Adds a new option --no-extent-clause which suppresses EXTENT SIZE and NEXT SIZE clauses. Thanks to Bruce Simms for the prompting to get this rather old request done. Also removed extra spaces following the column name in PUT clauses for SLOBs. Revision 2.208 Fixed problem with printing the CREATE PROCEDURE line. Revision 2.206 & 2.207 Added support for External Tables (IDS 11.50xC6+) Revision 2.205 Fixed collection type handling. Thanks to Bruce Simms for pointing out this one. Fixed summary comment to include table owner if not suppressed ala dbschema. Removed unneeded function prototypes. Revision 2.204 Fixed sequence and synonym printing to conform to the -g option. Revisions 2.202 and 2.203 Add a new option, -g, to place user privileges into a separate, specified, file. This is useful for server audit and for larger installations. Thanks to Bruce Simms for the idea. Improved trigger parser. Made index and constraint processing recognize and display object state (enabled, disabled, filtering, etc.). Improved the --filtering option again. It now works correctly, leaving the violated constraints disabled, but the offending records are recorded in the violations table. The user will have to manually delete the keys noted in the violations table(s) and then enable the violated constraints. Revision 2.201 Fixed problem with VIEWS and SYNONYMS defined on tables that are later ALTERED with a non-inplace ALTER printing before the table(s) that they reference. Thanks to Joseph Jurcazak for pointing the problem out. There still may be problems with VIEWS and SYNONYM ordering since SYNONYMS and VIEWS can reference each other, but I can't figure out a good solution for that one. Modified the warning issued when the CREATE statement is not found in the first record of an SPL routine's text to only print when in verbose mode. Revision 2.200 Fixed problem with zero length defaults for character types. Was generating a single space character as default, should be an empty string as these are different for variable char type columns. Known problems: The following seems to have been fixed finally in some 7.31/9.30 release so it's only an issue if you have an earlier release of IDS: Not really a myschema problem, just FYI. The Informix engine adds several layers of parenthesis to trigger definitions as they are saved in the system catalog. Unfortunately sometimes it adds so many additional layers to an otherwise valid trigger that the generated CREATE TRIGGER statement will not execute. This affects dbschema as well. The problem is that the engine's trigger parser routines cannot properly parse the definition as it was saved after the original CREATE TRIGGER statement was parsed. There is a bug report but it would be a good idea to save the original create script for complex triggers and not rely on dbschema or myschema produced scripts. Similarly, it is possible to create a very large table and to subsequently use ALTER TABLE to add so many columns that the resulting CREATE TABLE statement is longer than the maximum SQL statement (~64K) so that the output schema is not usable to recreate the database. Dbschema continues to have the same problem. It is possible, using CREATE PROCEDURE/FUNCTION FROM FILE... to create a stored procedure with comments in very odd places - like before the CREATE keyword. Sometimes this confuses myschema's parser that strips out owner names and performs some other manipulations of the first line of the procedure. Version 2.217 fixes most of these problems, but there may still be some oddities that I have not allowed for. Let me know if you run into any. Futures: - Break large tables into a small CREATE with subsequent ALTERs? May be needed for table definitions that exceed the 64K statement length limit. If anyone runs into this one, let me know. Low priority otherwise. - Add configurable isolation levels (uses DIRTY READ now) - Support for Label Based Access Control Security Policies. Low priority since few are using LBAC due to its complexity. What else is still missing? ---------------------------------------------------------------------------- Utility: printfreeB.ec Synopsis: Reports unused and partially used space within a table. Uses SMI tables to scan a table's bitmaps. This is SLOW. A version I have written that reads the bitmaps directly from disk is almost 100 times faster. I have reported this to Informix. Author(s): Art S. Kagel Revision 1.8 Version(s) supported: 7.2x File(s): printfreeB.ec Comments: (Probably supports 7.1x as well but I am not sure if I have used any of the newer SMI tables and views that appeared in 7.2). Definitely needs changes to support IDS 9.4 even in small chunk mode (mostly works but reports oddities like table is 439% full!) Release Notes: Revision 1.8 Updated the copyright notice to 2011. Revision 1.7 fixes an old bug. Revision 1.6 removes a deprecated RCS tag. Revision 1.5 fixes an uninitialized variable problem. Revision 1.6 just removes an unsupported rcs keyword. Revision 1.4 fixes a compile-time problem w/ strings.h -vs- string.h. Revision 1.3 adds -V revision and copyright option. Not to worry, this is just a formality. Use of this utility is still free. ---------------------------------------------------------------------------- Utility: ul.ec Synopsis: Unload a select set to a disk file(s) in a portable binary file format or load such a file into an insert statement. CLOB support still needs to be done along with UDT, collections, multisets, etc. Author(s): Art S. Kagel & Art Taylor IDS Version(s) supported: 5.xx, 6.0x, 7.xx, 9.xx, 10.00, 11.xx (except UDTs) Revision: 1.53 File(s): ul.ec Comments: Naming the executable 'bload' or 'bunload' (like the bload.ec ESQL/C code example) defaults to -l or -u respectively. Release Notes: Revision 1.53 Added new option -R which begins all transactions with the WITHOUT REPLICATION clause which prevents the operation from being replicated via Enterprise Replication. The option is ignored in unload mode (-u). Thanks to Øyvind Gjerstad for the suggestion. Revisions 1.51 & 1.52 Update the copywrite statement and document the -N option. Revisions 1.49 & 1.50 Moved status printouts to stderr. Implemented "-f -" for reading or writing from/to stdin/stdout so ul.ec can be used as a filter. Added support for bigint (int8 not quite right yet). Added -N option to export in native byte order rather than network byte order. Note: The default file format is compatible with external tables defined as FORMAT 'INFORMIX'. Revision 1.48 Fixed file size handling to allow files larger than 2GB. Fixed handling of BOOLEAN. Fixed a bug in DATETIME handling as string. Improved help. Fixed variable character default to handling as STRING to eliminate soft trailing spaces. Revision 1.47 Replaced all str*cpy's in the getopt loop with snprintf for added security. Revision 1.46 Tried to fix the strlower macro for non-gnu compilers. Revision 1.45 Made to work for mixed case table name arguments when DELIMIDENT is disabled. Revision 1.44 fixed problems compiling with older SDK and esql/C versions. Revisions 1.41-1.43 make the code safe to compile and use in 64bit environments. Fixed a bug in handling DECIMAL column data. New -F option to process LVARCHAR columns as FIXCHAR the same as CHAR and VARCHAR are already handled. Optional because it causes LVARCHARS to be padded in the database to maximum length. Revision 1.40 add support for LVARCHAR, INT8, BIGINT, SERIAL8 and BIGSERIAL. Also added a new option -F to optionally export LVARCHAR columns as FIXCHAR to support binary data embedded in LVARCHAR. Revision 1.39 removed unsupported RCS ident string. Revision 1.38 fixed the platform detection for HPUX systems. Revisions 1.36 & 1.37 fix large file support on HP platforms. Revision 1.35 contains some minor fixes to allow compilation on new HPUX/ia64 systems. Revision 1.34 restores the source compatibility with ESQL/C 7.xx and C4GL 7.xx! The code now uses a parallel array of qualifiers to hold qualifiers for DATETIME and INTERVAL fields. Revision 1.33 just improves portability. Seems HP places the NBO functions in a different header than every other UNIX. Probably some sad BSD leftover. Ahh well. Revisions 1.24-1.32 FINALLY fix the binary loading and unloading of DATETIME and INTERVAL columns! Also swabbed the three binary fields in the dec_t structure on output/input so the output of DECIMAL and MONEY columns is portable. Also Rev 1.31 should have fixed the problem compiling on AIX with newer SDK versions. This broke ESQL 7.xx and c4gl 7.xx compatibility. Revisions 1.17-1.23 fix a few bugs and implement a portable file format. You can now unload data on LittleEndian platforms like Intel and reload on BigEndian platforms like Sparc, PARisc, & PowerPC. Disabled binary DATETIME unloading, I still cannot get this to work reliably and the text unload only takes one extra byte for YEAR TO FRACTION(5). Also somewhere in here BLOB support was quietly completed. Known problems: The compatibility mode supports an old bug in the original version and is only interesting for Bloomberg users needing to emulate that version's input/output files. There is a UNIX Text Utilities filter, ul, that underlines a text stream so the naming of this utility is unfortunate. Maybe bload is a better name. If typing 'man ...' gets you ul's help text you need to rename it. When linking ul.ec on most System V derived UNIX systems you will need to include libgen.a (-lgen). This is not true on most BSD derived UNIX versions such as AIX, FreeBSD, and Linux as the contents of System V's libgen are in libc instead on these systems. Future: Maybe support for more UDTs and complex types. ---------------------------------------------------------------------------- Utility: dbcopy.ec Synopsis: Select data from one database/table and insert into another. Uses separate connections for FETCHing and INSERTing so the source and target can be local, remote, and even have different logging modes. Author(s): Art S. Kagel Version(s) supported: ESQL 7.2x, Client SDK 2.xx, OL 5.xx, IDS 7.xx Revision: 1.79a File(s): dbcopy.ec Comments: This is fast and low overhead. I have been able to run over 40 copies on a 32 processor box with production programs running concurrently and still gotten incremental increases in throughput from the Nth copy. Release Notes: Revision 1.79 Added new option -R which begins all transactions with the WITHOUT REPLICATION clause which prevents the operation from being replicated via Enterprise Replication. Thanks to Øyvind Gjerstad for the suggestion. Revision 1.78 Fixes a missing #ifdef that caused a compile time problem for ESQL/C versions earlier than 3.50. Revision 1.77 updated the copyright notice again. Revisions 1.73-1.76 Fixed a bug in processing DECIMAL/MONEY type columns and tracing/dumping values for BIGINT/BIGSERIAL and INT8/SERIAL8 columns when reporting errors or debugging. Cleaned up some debugging trace duplication. Revision 1.72 Fixed a variable declaration that causes compile time problems on some platforms. Revision 1.71 Updates the copyright notice. Revisions 1.65-1.70 Fix DATETIME and INTERVAL handling problems with wide data rows. Also fixed a problem with -W and -b causing SEGV if -W is invoked without -b or with -b and a buffer size larger than 65151 (docs say this should work to 2^32-1). Reduced default and max values. Thanks to Jonathan Leffler for pitching in on the old DATETIME problem and for patching the code. Jonathan also cleaned up some compile time warnings. Revision 1.64 Fixed LVARCHAR again. The ifdef test protecting older compilers was backwards. Revision 1.63 Fixed handling of BOOLEAN columns. Also improved version detection for compile time handling of BIGINT, INT8, BOOLEAN, etc. Revision 1.62 Fixed compatibility with older ESQL/C compilers. Revision 1.60 and 1.61 Added support for BigFetBufSize to allow fetching more than 32K of data at a time. Default set to 64K if new -W option is passed. New option -b will allow sizing the expanded fetch buffer between 32767 and 4,194,303, default 64K. Revision 1.56-1.59 make the code now safe to compile and use on 64bit systems. New -B option to save error rows to a binary format that can be reloaded using the ul.ec utility - needed if binary or non-printable data is contained in CHAR/VARCHAR/LVACHAR cols. New -e option to control binary/fixed format copying of CHAR, VARCHAR, and LVARCHAR columns. Added support for INT8/SERIAL8 & BIGINT/BIGSERIAL column types. Option -L is now deprecated in favor of '-e varchar'. Revision 1.55 fixed default wait mode to not wait and fixed entry of zero to no wait. Revision 1.54 fixed a typo. Made -I option backward compatible with CSDK versions prior to 3.00 which do not support the READ COMMITTED isolation mode option. Revision 1.53 made some intermediate data type changes. Revision 1.52 expands the size of supported insert and select queries from 3k to 100k. Permanently made -I compatible with SDK versions prior to v3.00 and still support this IDS 11.10 server feature. Changed code to use snprintf for most copies to avoid memory overruns. Revision 1.50 adds support for the new IDS 11.10 isolation mode COMMITTED READ LAST COMMITTED with the new -I option. Revision 1.48 & 1.49 expands the -p to give the user complete control over PDQPRIORITY (was mapping the supplied argument to values in increments of 10 and did not support zero). Fixed -w to support NOT WAIT when the argument is zero (0). Revisions 1.46 & 1.47 fix some errant type casts in diagnostic routines which are not normally executed, but which prevent compilation on some platforms. Revision 1.45 fixes a bug in using secured connections (ie w/username and password). Revision 1.44 cleaned up the Usage and expanded an error message. Revision 1.43 added -P & -U as alternatives to the environment variables to enter username and passwords. Added -a to cause dbcopy to acquire an exclusive lock on the target table before copying data. Revision 1.42 adds support for the environment variables INFORMIXUSER and INFORMIXPASS to simplify copying between hosts that are not trusted. Revision 1.39 fixes a problem with error code reporting in 64 bit compiles. Revision 1.40 is cosmetic. Revision 1.35 fixed a problem with using INFORMIXSERVER if -h/-H not provided. Revision 1.36 fixed the omission of CVCHARTYPE from dumprec() the error logging function. Thanks to Yvind Gjerstad for picking that up. While in there I decided CVCHARTYPE and CFIXCHAR should not have trailing spaces stripped in the log, but normal CCHARTYPE should. However, if using CHAR columns to pack binary data (ex: as a cheap BLOB) you do not want to strip. So default is to continue to treat CHAR columns as FIXCHAR but the new -L option will change that to use CCHARTYPE instead if the user knows there's no binary in the CHAR cols of the table being copied. Revisions 1.37 & 1.38 update the usage and copyright notice. Version 1.33 makes the code safe to use with IDS 9.xx long object names fixes some cut-and-paste born oddities in the source, and fixes a potential memory overwrite bug. Version 1.34 just updates the copyright statement. Revision 1.32 improved the -? and no args usage messages. Also updated the copyright information. Protected _H_LOCALEDEF from multiple definition (see notes on myschema.ec above). Fixed a compiler dependent warning. Revision 1.31 just cleaned up the RCS revision log. Revision 1.30 adds support for 9.21 data types and international character types and VARCHAR support was fixed.. Support added for INTEGER8, SERIAL8, BOOLEAN, VARCHAR, NCHAR, NVARCHAR, and LVARCHAR. Known problems: BLOB columns are output to the error log file using raw I/O so that the file cannot be used with dbload or dbaccess->LOAD to reload an edited and corrected log file. DATETIME and INTERVAL columns are represented internally as type string due to code to work around an old ESQL/C library bug. This has never been removed and MAY slightly affect throughput. Need to port the fix from ul.ec to dbcopy.ec. There seems to be some problem using dbcopy to copy from 7.3x to 9.xx. Investigating, but any help or insight is appreciated. ---------------------------------------------------------------------------- Utility: listdb5.ec Synopsis: Simple program that uses an undocumented library function to list all of the databases on an instance. Author(s): Art Taylor, updated by Art S. Kagel Version(s) supported: 5.xx, 6.0x, 7.xx Revision: 1.3 File(s): listdb5.ec Comments: Note that the problems the previous version had with R7.xx databases has been resolved. The code is now sufficiently dynamic to handle larger servers which, it turns out, was the real problem. The code will now handle up to 500 databases, controlled by the #define DB_MAX. FYI as of IDS Ver 7.30 and SDK 2.10 the library call which this uses has been documented in the ESQL/C manual and so this version has a future. Release Notes: Revision 1.3 cleans up the runtime interface, adds a usage option (-?) and a Version option (-V) which includes copyright information. ---------------------------------------------------------------------------- Utility: listdb7.ec Synopsis: Enhanced version of listdb5.ec that uses the SMI and system tables. Lists databases and optionally their tables. An option prints additional information such as dbspace, owner, logging mode, create date, and NLS status for databases and similar information for tables/fragments/detached indexes. Author(s): Art S. Kagel Version(s) supported: 7.xx, 9.xx Revision: 1.20 File(s): listdb7.ec Comments: Great for tracking down which databases/tables reside in what dbspaces or for a quick table extent report. By specifying -d'*' and -ttablename you can search all databases for a specific tablename. Linking with GNU getopt() improves usability and is now automatic. Release Notes: Revision 1.20 Fixed compilation problem in getopt.c on Linux. Thanks to Jim Kennedy for pressing the issue until I found it. Also made including my getopt.h file unconditional since it's needed everywhere except Linux and harmless there. Updated the copyright notice to 2011. Revisions 1.18 - 1.19 fix a problem reporting extent information for the last table listed for each database. Revision 1.17 resolved problems compiling on Linux due to missing prototypes in strings.h that are in string.h instead. Revision 1.16 added an error message if the selected database does not exist. Previous versions were silent. Revisions 1.12 - 1.15 add an extent report if -D and -t are specified. Also made -t work correctly with or without GNU getopt. If linking with GNU getopt add -DHAVE_GNU_GETOPT=1 to the compiler commandline. Commandline syntax for -t differs depending on which getopt you are using but the Usage printout is adjusted accordingly. Revision 1.11 adds a -V option to print version and copyright info. Revision 1.10 added long name support. Revision 1.9 extends the portability fix in 1.8 to table level calculations also. Revision 1.8 replaces bit field code for determining dbspace numbers with division by 0x100000. The bit fields do not mapp properly on Intel compilers. ---------------------------------------------------------------------------- File getopt.c/getopt.h: Updated to port better. ---------------------------------------------------------------------------- dbstruct.ec Produce a C or ESQL/C structure to match an IDS database table (or all tables. Revision Notes: Revision 1.16 Updated the copyright notice to 2011. Revision 1.15 Added -G to generate 4GL RECORDs Revision 1.14 Added support for external tables and synonyms. Revision 1.13 Fixed a missing type in a static declaration. Some C Compilers let this pass and assume 'int' others complain. Revision 1.12 Fixed compatibility with older ESQL/C compilers. Revision 1.11 Syntax fix. Revision 1.09 & 1.10 Made to work for mixed case table name arguments when DELIMIDENT is disabled. ---------------------------------------------------------------------------- sqlstruct.ec Produce a C or ESQL/C structure to match the results of an SQL statement. For statements that take replaceable parameters, input and or output structures are produced as appropriate. Revision Notes: Revision 1.34 Added -G to generate 4GL RECORDs Revision 1.33 Updated the copyright notice to 2011. Revision 1.29-1.32 Fixed main loop exit and end of input detection. Fixed compile time warning on some platforms. Revision 1.28 Fixed inclusion of getopt.h to use my version of the file which is portable and consistent with my getopt.c ----------------------------------------------------------------------------