Art's Utilities Download Page
Latest Gamma Level Releases
Utils2_ak is my primary package of utilities. This is the April 27, 2020 release.
Included:
dbping.ec - Tests connections and reports connection time as well as the actual host and servername connected as well as which alias was used for
the connection. These reports are important for testing DBPATH, connection groups, and Connection Manager behavior as well as verifying
server failover.
dbcopy.ec - Copy data from table to table directly across databases, servers, instances, versions, even between databases at different logging levels
without worrying about long transaction rollbacks.
dbdelete.ec - Delete large amounts of data from a table FAST without risking long transaction rollbacks.
dbmove.ec - Another data copy utility using the methods the dbdelete uses. Can copy some data that dbcopy cannot.
dbsavail.ec - Summary dbspace report. Reports used and free space in pages, KB, and percent. Optional chunk detail.
dbscript.ec - Generate SQL, scripts, or OS commands to run against all or some of the tables in your database.
dbstruct.ec - Generate data structures from your database for C, C++, ESQL/C, Structured FORTRAN, or x4GL.
dostats_ng.ec - The original and still the best way to automate your update statistics runs. The original dostats.ec source is included for servers older than
10.00.
drive_dostats - Divide and conquer. Runs multiple copies of dostats updating a subset list of your tables. If you have the cores to spare, get the job done
faster.
listdb7.ec - Displays details about your databases and tables and indexes.
mydbdiff - Script to compare two schema files or a schema file to a live schema.
myschema - My dbschema replacement utility. Does everything that dbschema does except display data distributions (dbschema -hd) and much more.
Many many options to tune its behavior
printfreeB.ec - Print out a table detail report similar to oncheck -pT
sqlstruct.ec - Generate data structures from SQL statements including input and output structures for SELECT, INSERT, DELETE, and UPDATE statements.
Options to generate C, C++, ESQL/C, 4GL, or SQL DDL (for SELECT).
ul.ec - A binary file unload/load utility. File format is compatible with external table "INFORMIX" format and portable between processor
architectures.
README.1st - Reports what features and fixes are included in each utility.
BUILDING - Describes how to build utils2_ak on each platform and compiler environment. Tells what changes need to be made to the makefiles for
each.
Myexport is my replacement for dbexport and dbimport. These scripts optionally use external tables or the Informix High Performance Loader to unload and load data much faster than dbexport/dbimport without locking the database. Options include parallel export and import, NOVALIDATE data loading, constraint filtering, user selected isolation level, running dostats after the load, compressed unload files, dbexport & dbimport compatibility, dbspace mapping.
The package includes:
myexport - Drop in replacement for dbexport
myimport - Drop in replacement for dbimport
ak_launcher.c - Used by myimport
from_external.awk - Used by myimport to pre-process a dbexport data set for loading via external tables.
Sample dbspace mapping files.
ratios.shr_ak is a package of metric caculations that I use to quickly evaluate the health of a running Informix system. It includes a stored procedure, ratios(), that is installed in sysmaster and a script, newratios.ksh, that retrieves the data calculated by the ratios proc and formats a report.
Included:
dbping.ec - Tests connections and reports connection time as well as the actual host and servername connected as well as which alias was used for
the connection. These reports are important for testing DBPATH, connection groups, and Connection Manager behavior as well as verifying
server failover.
dbcopy.ec - Copy data from table to table directly across databases, servers, instances, versions, even between databases at different logging levels
without worrying about long transaction rollbacks.
dbdelete.ec - Delete large amounts of data from a table FAST without risking long transaction rollbacks.
dbmove.ec - Another data copy utility using the methods the dbdelete uses. Can copy some data that dbcopy cannot.
dbsavail.ec - Summary dbspace report. Reports used and free space in pages, KB, and percent. Optional chunk detail.
dbscript.ec - Generate SQL, scripts, or OS commands to run against all or some of the tables in your database.
dbstruct.ec - Generate data structures from your database for C, C++, ESQL/C, Structured FORTRAN, or x4GL.
dostats_ng.ec - The original and still the best way to automate your update statistics runs. The original dostats.ec source is included for servers older than
10.00.
drive_dostats - Divide and conquer. Runs multiple copies of dostats updating a subset list of your tables. If you have the cores to spare, get the job done
faster.
listdb7.ec - Displays details about your databases and tables and indexes.
mydbdiff - Script to compare two schema files or a schema file to a live schema.
myschema - My dbschema replacement utility. Does everything that dbschema does except display data distributions (dbschema -hd) and much more.
Many many options to tune its behavior
printfreeB.ec - Print out a table detail report similar to oncheck -pT
sqlstruct.ec - Generate data structures from SQL statements including input and output structures for SELECT, INSERT, DELETE, and UPDATE statements.
Options to generate C, C++, ESQL/C, 4GL, or SQL DDL (for SELECT).
ul.ec - A binary file unload/load utility. File format is compatible with external table "INFORMIX" format and portable between processor
architectures.
README.1st - Reports what features and fixes are included in each utility.
BUILDING - Describes how to build utils2_ak on each platform and compiler environment. Tells what changes need to be made to the makefiles for
each.
Myexport is my replacement for dbexport and dbimport. These scripts optionally use external tables or the Informix High Performance Loader to unload and load data much faster than dbexport/dbimport without locking the database. Options include parallel export and import, NOVALIDATE data loading, constraint filtering, user selected isolation level, running dostats after the load, compressed unload files, dbexport & dbimport compatibility, dbspace mapping.
The package includes:
myexport - Drop in replacement for dbexport
myimport - Drop in replacement for dbimport
ak_launcher.c - Used by myimport
from_external.awk - Used by myimport to pre-process a dbexport data set for loading via external tables.
Sample dbspace mapping files.
ratios.shr_ak is a package of metric caculations that I use to quickly evaluate the health of a running Informix system. It includes a stored procedure, ratios(), that is installed in sysmaster and a script, newratios.ksh, that retrieves the data calculated by the ratios proc and formats a report.
News:
April 27, 2021
dbstruct
sqlstruct
New -l option to generate a simple comma separated column name list suitable for inclusion
in SELECT statement projection clauses or in certain coding situations to save typing.
April 19, 2021
dbcopy
GREAT NEWS! The ESQL/C bug that has prevented dbcopy from copying lvarchar type columns
since v3.50.xC5 has finally been fixed as of CSDK v4.50.FC5! Yippee!
Added new features:
-A - lock the source table in exclusive mode. When used with -a (lock target table) this can make
dbcopy run MUCH faster!
-G - Print run time and data copy rate during and following the copy.
-v - Verbose status display that includes processing steps not otherwise displayed.
-c disable active triggers on the source table. Re-enable them after the copy completes.
-C disable active triggers on the target table. Re-enable them after the copy completes.
The -c & -C prevent SELECT and INSERT triggers on the tables from firing during the mass copy.
myschema
Fixed an issue that caused partitioned tables created with AUDIT, ERKEY, and/or REPLCHECK to
generate DML that did not include those WITH clauses.
February 19, 2021
myschema
Fixed an issue affecting PUT clauses for tables with BLOB and CLOB type columns.
Added new features. The --export-scripts and --import-scripts options generate external
table export and import scripts respectively similar to those produced by --myexport-scripts
and --myexport-express. The main difference is only generating either the import or export
scripts and not both and that the unload files are named with the full table name instead
of the dbexport compatible partial table-name-with-tabid. These obey the
--myexport-delimiter and --myexport-escape options. However, for clarity, I have added
synonyms for both that start with --export- and --import- which have the same effect.
February 15, 2021
myexport
Fixed myimport when using -U & -P to pass in user id and password.
myschema
Fixed an issue with some triggers that can produce trigger statement text that will not execute successfully.
January 18, 2021
dbcopy
Removed a trace that should have been displayed only in debug mode (-g). Thanks to Doug Lawry for pointing it out!
myschema
Added new options --no-setsessionauth and --comment-setsessionauth. These are needed because even the database
owner cannot grant SETSESSIONAUTH to itself so some scripts will fail if these auths are included. They should be
processed manually. The -no suppresses and the --comment comments out these privs.
myexport
Minor improvements.
December 23, 2020
The ratios.shr_ak package has been added to this page for easy download.
myschema
This release of utils2_ak contains a fix to the dbsavail utility to correct the free space calculation for smart blob spaces.
December 2, 2020
myschema
Fixed -o to sort systable objects other than tables in name order within type.
Fixed the interaction between -o and --dependency-order to sort tables my name
within dependency level and to sort all non-systables objects by name (was
reverting to id order when --dependency-order is specified with -o. Note the change to
-o may cause new schema to no compare correctly to schema files previously saved by
mydbdiff. Users of mydbdiff should verify the database against save schema files using
an older version of myschema then use this new release to save a new baseline file.
myexport
Added a new option to myimport. The -n option is similar to the -e option in that it assumes
that the database already exists, but it still executes the schema file(s) to create the objects
to be imported. Useful when you want to load into a newly created tenant database or to
add tables to an existing database by importing them from another server.
December 1, 2020
myschema
Extended --no-blade-procs/--no-blade-funcs to also exclude the resources added by the blade
management routines when any blade is registered.
Also fixed the spacing of EXTENT and LOCK MODE clauses if --no-extent specified. Thanks to
Antonin Rozenal for pointing this one out.
November 24, 2020
myexport
New feature: You can now pass options to myschema when it is invoked by myexport by appending
them to the end of the command line preceded by a double dash (--). Example in the usage output.
This release also includes a couple of minor bug fixes. Thanks to David Grove for working with me on
this release.
November 23, 2020
myschema
Added new option --alter-checks to generate check constraints as ALTER TABLE statements rather
than embedded in the CREATE TABLE statement. Similar to --no-check but does not include the
NOVALIDATE clause.
Corrected a usage description.
Fixed sub-version parsing for determining features valid to the source engine.
Fixed --dependency-ordering when it encounters a self-referencing table loop. Thanks to David
Grove for reporting these issues.
November 16, 2020
myschema
Fixed an old initialization bug when processing stored routines.
November 5, 2020
myschema
Fixed an issue in the --infrastructure=sql output for the API command to create the plogdbspace.
September 2, 2020
myschema
Added some missing usage text for a few options that were not properly documented previously.
July 30, 2020
dbcopy.ec
Added -a to lock the source table in exclusive mode.
Added -G to include partial and final progress reporting of data copy rate in rows per second as well as total copy time.
Thanks to Paul Watson for the ideas!
July 20, 2020
myschema.ec
Added a call to se_metadatainit() when the spatial_references table is loaded in the myimport external table load script
generated by --myexport-scripts. This fixes a problem with the spatial datablade cache not updating which can cause the
loads of tables containing spatial columns to fail. Thanks to Daniel Tafili for identifying the problem and hunting up the
solution.
Fixed an empty call to dbaccess in the "cmd" shell style output from --infrastructure where there are no storage pool entries.
dbmove.ec
Added support for ifx_row_id to support copying from partitioned tables created with the WITH ROWID option.
May 15, 2020
dbping.ec:
Added new options: -u user, -p password, -t timeout, -h for usage, and -V for version printout
May 10, 2020
myschema.ec:
Fixed a problem where Access Method support functions were being written to the procedures file which will cause the main
file to fail to execute on the target database because those function definitions are needed to define the Access Method. Thanks
to Kenneth Penza for bringing this to my attention. Support functions are now written to the main file except when only printing
stored procedures.
May 7, 2020
myschema.ec:
Added new option --no-private-synonyms to exclude private synonyms from the output. Thanks to Ognjen Orel for the suggestion.
April 27, 2020
myschema.ec:
Added --help option to report usage with a success return code. Removed stropts.h and the unimplemented system call isastream().
dbscript.ec:
Added options to control what objects are processes:
-e - Include EXTERNAL TABLES in the 'table' names processed.
-v - Include VIEWS in the 'table' names processed.
-s - Include SYNONYMS in the 'table' names processed.
-S - Include SEQUENCES in the 'table' names processed.
-n - Exclude 'normal' tables from processing. Useful only when one or more of -s, -e, -v, or -S are included.
April 12, 2020
dbscript.ec:
myschema.ec:
For both of these utilities - Changed a query to be dynamic because some early CSDK 3.50 releases did not recognize the ORDER SIBLINGS BY
construct and rejected the code even though the corresponding engine releases supported it. The new code will be passed to the engine so
as long as the engine supports the feature it will be OK. This affects the dbscript -D option and the myschema --dependency-order option. The
behavior is unchanged, this change just expands support to some older compilers. Thanks to Mark Collins for identifying the issue.
February 12, 2020
dbdelete:
Fixed a bug when -I was passed. Added new -v (verbose) flag to increase runtime trace.
Added new -P option to specify a pause between delete runs. May improve performance on some servers or reduce impact on other processes.
Fixed a problem with -p which was not obeying "-p 0".
Thanks to David Williams for the ideas for -P & -v and for identifying the -p 0 issue. David also provided the code to implement -P.
dostats_ng.ec:
Fixed a problem acquiring row counts for virtual tables. VTI tables are no longer processed.
January 15, 2020
dbcopy.ec:
Added support for ifx_row_id the undocumented ROWID analogue that works for all tables whether partitioned or not!
myschema:
Fixed extensible chunk output for --infrastructure. Fixed an error message and a usage error.