Art's Utilities Download Page
Latest Gamma Level Releases
Utils2_ak is my primary package of utilities. This is the July 5, 2021 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:
July 5, 2021
dbcopy
Fixed the calculation and display of data copy rate reporting (-G).
June 28, 2021
New option to myimport. -S suppresses the default AUTOLOCATE setting in the
ONCONFIG file to prevent AUTOLOCATE from partitioning tables without an IN
or FRAGMENT BY clause ROUND ROBIN or moving them out of the default
dbspace. This mainly affects using myimport to import a data set created with
dbexport or by explicitly setting --no-storage-options to myexport or myschema.
This addresses a reported bug/issue using dbimport to import in the presence of
a non-zero AUTOLOCATE setting (APAR # IT37426 - https://www.ibm.com/support/pages/apar/IT37426?myns=swgimgmt&mynp=OCSSGU8G&mync=E&cm_sp=swgimgmt-_-OCSSGU8G-_-E)
June 9, 2021
dbping
New feature. Use -l <limit_secs> & -c <command> together to execute a script
if the connection takes longer than -l seconds (set -t <timeout> to some larger
value). The command passed to -c cannot take any arguments, so pack up
more complex command lines into a simple script in the path. Useful for
diagnosing long connection time issues. The command is executed in the
background. If the connection takes 2x<limit_secs> the command may execute
more than once.
June 1, 2021
myschema
Fixed a bug causing a -229 error when processing a database with many tables
when specifying --dependency-order or --myexport-order. Fixed the sizeing of
lvarchar type columns in databases using a multi-byte character set. Fixed a bug
when using --myexport-scripts, --import-scripts, or when running myexport with
the -E option against a database with many tables. Was returning errno 24 (too
many open files).
May 20, 2021
myschema
Fixed a bug that was mistakenly suppressing the optional REPLCHECK index
if it includes the ERKEY columns. Fixed a bug that was failing to print the
WITH ERKEY, REPLCHECK, CRCOLS, and AUDIT options when a table is
partitioned.
May 13, 2021
myschema
Added new option --myexport-order - like --dependency-order but sorts the
dependent tables before their referenced table. For use by myexport to
eliminate the possibility of exporting inconsistent data.
myexport
Changed the -O option to use --myexport-order instead of --dependency-order
The original code line is still there for use with myschema versions earlier than
2.417 which introduces --myexport-order. Also -O enables -m to split constraints,
indexes, etc. into separate scripts so that the foreign keys are created after the
data load. Alternatively one could just add the --no-check option to the myschema
command line and remove the "split=1" assignment from the -O switch event.
May 6, 2021
myschema
New options affecting export scripts:
--no-export-triggers Disables select triggers in the external table export scripts.
For both of the following options, DDL for the tables are generated and they are
created on the target but no data is exported. They behave the same as the
corresponding dbexport options:
--no-data-tables - Do not produce export scripts for listed tables. If the option
is passed but no tables are listed the tsinstancetable table is auto-included. The
optional argument is a comma separated list of table names. Use with export scripts
options: --myexport-scripts, --myexport-express, & --export-scripts.
--no-data-access-method - Do not produce export scripts for tables created with the
listed access method names. If the option is passed but no access methods are
listed the ts_rtl_vtam and ts_vtam access methods are auto-included. The optional
argument is a comma separated list of access method names. Use with export scripts
options: --myexport-scripts, --myexport-express, & --export-scripts.
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.