Art's Utilities Download Page
Latest Gamma Level Releases
Utils2_ak is my primary package of utilities. This is the November 17, 2022 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 binary export and import (not compatible with dbimport).
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 calculations 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.
Scripts_ak: This package contains 17 scripts that I have found to be particularly useful. The package includes a file, scripts_doc.txt, describing each script.
This is a tar file.
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 binary export and import (not compatible with dbimport).
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 calculations 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.
Scripts_ak: This package contains 17 scripts that I have found to be particularly useful. The package includes a file, scripts_doc.txt, describing each script.
This is a tar file.
News:
November 17, 2022
Added new option --include-sysprocs to display system procedures normally ignored.
Fixed an issue with GRANT and REVOKE statement syntax for functions and procedures
with IN, OUT, and INOUT parameters.
September 9, 2022
Added new options export-binary and import-binary to generate only
the export or import script for using an external table in binary format.
Expanded the supported database name size to 149 bytes (including the
server name) and the number of supported databases to 1024.
August 23, 2022
Fixed a problem using the -D <database_to> option. Thanks to Doug Lawry
for pointing this one out.
July 27, 2022
Fixed an issue in myimport processing the dbspace mapping file entries
for BLOB and CLOB type column relocation.
July 3, 2022:
Fixed an issue with myimport hanging waiting for the user to acknowledge
that the ontape archive output target is ready for writing when LTAPEDEV
is set to a file rather than to a directory. The automatic ontape run that
follows an EXPRESS mode load using EXTERNAL TABLES is now a dummy
archive and does not create an archive file. A message was added to the
output to indicate that and another message at the end of the run now
recommends that every myimport run be followed by a full archive of the
server which is Best Practice anyway. Thanks to David Grove for this one.
June 28, 2022:
Myimport has been automatically detecting the presence of multiple
schema DDL files created by myexport with -m, -M, or -A since the
February 8th release. The -m option has now been deprecated and
made no-op. There is a new option, -o, to override the auto-detect
behavior for users who want to manually execute the additional DDL
files that create indexes, constraints, procedures, etc. and create data
distributions (if the myexport option -U dostats option is not selected).
Myexport: Fixed two issues. Myexport was not properly handling target
export directories (-o) that contained a trailing backslash (\). Fixed an
issue affecting command lines that take advantage of "--" to append
options directed at the myschema run if the database name was the last
option before the "--". Now the database name no longer has to be first
(as per dbexport) or last but can be anywhere on the command line as
long as it is not after any "--" or between an option and its argument.
Thanks to David Grove for identifying most of these issues.
June 13, 2022:
Fixed an issue with generated DDL for SEQUENCEs. For a sequence with
a MINVALUE clause specifying a value greater than one, myschema was
including a "START WITH 1" clause which clashes with the "MINVALUE"
clause. Thanks to Paul Watson for pointing out the issue.
May 3, 2022:
dbcopy.ec:
Changed Usage output to write to stdout to simplify piping to less/ more.
Removed old debug trace.
Added new -J option to alter the target table to TYPE(RAW) before the copy
and alter it back to TYPE(STANDARD) when the copy completes. If the target
is already a RAW table -J is ignored and the table's type is preserved either
way. Dbcopy will exit if the alter cannot be processed.
March 15, 2022
dbstruct.ec:
Major code rewrite to simplify adding new output types. New output options:
-R - generate CREATE ROW TYPE output
-P - generate C++ class structure (instead of struct)
March 14, 2022
New package, scripts_ak containing 15 new scripts and a text file describing them. These are
similar to some of Lester Knutsen's sysmaster SQL scripts. All are, however, shell scripts.
February 10, 2022
Technical update. It's been pointed out that the license PDF file is not being properly extracted
due to shar corrupting the PDF. Also the main Makefile was not installing the drive_dostats and
mydbdiff scripts. This release fixes both issues. Thanks to PJ Welsh for reporting these issues.
February 8, 2022
ul.ec:
Added new option -I to perform IO in O_DIRECT mode bypassing the system cache.
myexport:
Removed a deprecated and no-op option (-i).
myimport:
Fixed automated detection of compressed (gzip) data files and of split schema exports.
The -Z and -m flags are now optional. Also changed -U to run dostats in --force-run mode
to replace the distributions created during index building.
February 6, 2022
myimport:
Improvements to EXPRESS mode and INFORMIX mode imports. Now automatically
performs a fake level 0 ontape archive before attempting to create triggers, and
constraints.
January 23, 2022
myschema:
Fixed a major bug causing indexes partitioned by expression to only display the
first partition.
December 21, 2021
dostats_ng.ec
The progress display for LOW stats when writing commands to a file instead
of executing them immediately, has been improved.
New option --display-subsec to display start and finish times including fractions
of a second to 1/1000000th second resolution. If elapsed times are enabled (-e or -E)
the resolution is also 1/1000000 (1/1000) when --display-subsec is invoked. This also
invokes --time-display and is compatible with --display-local.
Also added --display-time as a synonym for --time-display for consistency with the
other time display options.
A bug that sometimes caused the first key column that is different between
indexes that begin with the same key column from being processed as HIGH
has been fixed. Thanks to David Williams for identifying the issue.
myschema:
New option --myexport-binary to generate scripts for myexport's -B option.
November 16, 2021
ratios.shr_ak
The May release contained an improvement to the ratios stored procedure
rendering it incompatible with v11.70. Rather than back out the change for
all releases, I have forked a new ratios.1170.sql version of the procedure
specifically for v11.70.
November 4, 2021
myexport
Added -B option to export using EXTERNAL TABLES in INFORMIX
binary mode. Fixed -B & -E to force -m because EXPRESS mode loads
attempt to alter the tables to type( RAW ) which will fail if constraints
are in already place prior to altering the table's type.
November 3, 2021
myschema
Added new option --myexport-binary to export to the internal INFORMIX
format binary file. Defaults to EXPRESS mode but you can force DELUXE
mode by passing --myexport-scripts after --myexport-binary. Faster for
tables that are not mostly text.
October 28, 2021
myschema
Updated the --myexport-express output (used by myexport) to improve the
myimport external table scripts to alter the target table to RAW mode before
the import and back to STANDARD mode after.
myexport
Fixed a problem with the code in myimport that converts a dbexport directory
to be able to process the import using external tables.
July 5, 2021
dbcopy
Fixed the calculation and display of data copy rate reporting (-G).
June 28, 2021
myexport
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.