Myschema
Utility: myschema.source.ar
Synopsis:
My version of dbschema which has a many advantages over the original:
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 optionally calculates new values for EXTENT SIZE and NEXT SIZE based on the
actual pages used by the table and optional adjustment factors to aid
duplicating and migrating databases.
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 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 table create and indexing commands can be written to separate output schema
files.
o can optionally output 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 optionally remove owner names so database can be ported to single owner.
o optionally single table output includes referencing FOREIGN KEY constraints.
o adjust EXTENT SIZE as a percentage of current size or current usage 0->10000%.
o adjust NEXT SIZE as a percentage of EXTENT SIZE 0->10000%.
o generate IN <dbspace> 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.
o supports WITH CRCOLS clauses for replicated databases which dbschema refuses
to do.
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 correctly reports descending functional key indexes (dbschema does not as of
R9.40UC2).
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
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 dbaccess like -u all and -seq.
Author(s): Art S. Kagel
Revision: 2.170
Features Version: 6.32
Version(s) supported: IDS 10.xx, 9.xx, IDS 6/7.xx, OL 5.xx, SE 7.xx
: Requires ESQL/C 7.2x or 9.xx (SDK 2.xx) (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 support is not complete or
otherwise broken. In particular I have no way to test the access
method support I added as thoroughly as I'd like.
Release Notes:
Revisions 2.170 & 2.169 add support for IDS 10.00+ fragmentation using
named partitions which allows more than one fragment of a single
object to reside in a single dbspace. Added new option
--simple-fragments which mimics dbschema in not naming partitions
which have been given implicit names. By default myschema outputs the
PARTITION partname clause for all partitions.
Fixed compilation problem in getopt.c on Linux. Thanks to Jim Kenedy
for pressing the issue until I found it.
Revision 2.168 fixes a bug causing quoted strings in triggers to
sometimes be broken across source lines causing a syntax error on
running the schema. Thanks again to Robert Sosnowski for pointing this
one out also.
Revision 2.167 fixes a bug introduced in 2.161 causing the DBA flag to
be dropped from CREATE DBA PROCEDURE/FUNCTION statements. Thanks to
Robert Sosnowski for pointing this one out to me.
Revision 2.166 fixes a bug in SPL UDR output for IDS 9.xx+. Previous
releases modified the CREATE statement to use FUNCTION for UDRs that
return something and PROCEDURE for those that do not. However, the END
PROCEDURE/FUNCTION statement remained as originally entered into the
server which could cause a mismatch between the create and end
statements which is a syntax error. This release discontinues the
practice of modifying the create command. Thanks to Murray Wood for
pointing this one out to me. Procedures upgraded from 7.xx releases
that return data will no longer be converted into functions by
myschema.
Revision 2.165 fixed compilation problems affecting getopt.h and
getopt.c on HPUX.
Revision 2.164 adds new --filtering option to generate start violations
and set constraints, indexes to filtering for use with myimport.
Revision 2.163 fixed a problem losing the constraint name of the last
check constraint on a table.
Revision 2.162 adds secure connections by supporting username and
password passing using longopts --user and --passwd along with the
environment variables USERNAME and PASSWD. Also update to
myschema.mk.norcs: Added references to getopt.[och] which were
missing.
Revision 2.161 extends owner stripping to SPL. Thanks to Stephan Boyce
for suggesting that this is not as hard as I always thought and for
providing most of the code change to implement this.
Revision 2.160 fixed a problem causing to myschema to crash if
stripping owner names from massive triggers.
Revision 2.157-2.159 added filtering out of ER cdr_deltab_* tables.
Revision 2.158 fixed a problem causing myschema to crash for tables
with huge numbers of columns.
Revision 2.154-2.157 Added long options since I'm running out of 1 char
mnemonics for new options and -N <command> is not elegant. Fixed a
problem where myschema sometimes got confused about what columns
belonged to which tables. Increased the features version to 6.32 to
acknowledge these significant new features.
Revision 2.153 adds 3 new features forcing a Features version upgrade
to 6.31: New -D option to print only UDTs (ala dbschema -ui), new
-Nnotypes option to suppress UDTs from full schema, table, and routine
reports, new -Nnocasts option to suppress Type Casts from full schema,
table, and routine reports. Fixed missing privileges for certain
UDTs. The new multi-functional -N flag is introduced to implement most
new features as I'm running out or sensible single letter mnemonics.
My thanks to Mike Dunham-Wilkie for extensive bug detection and
Revisions 2.146-2.151 add the keyword PROCEDURE and argument lists to
GRANT statements for stored procedures when run against an IDS 9
instance to support overloaded procedures as well as functions.
Revision 2.147 fixed the lower version limit for configurable length
LVARCHAR columns which are only supported by IDS 9.40 and later.
Revisions 2.150-2.152 fix a problem mishandling strings containing
quote characters in SELECT lists and WHERE clauses in VIEW definitions.
debugging help.
Revision 2.145 fixes two bug and completes support for Smart Blob
options. Fixed problem with Smart Blobs created with default EXTENT
SIZE. Fixed problem with illegal key column order option for indexes
using access methods other than BTREE.
Revision 2.143 & 2.144 add two new options: -I to toggle off printing
of the serial type initial value and -i to print a specific initial
value. Both affect SERIAL and SERIAL8 column output. Features Version
has been increased to 6.30 to acknowledge these new features. Usage
documentation of the interaction of -a with -c, -i & -I was improved.
Thanks to Rick Fahey and Yves Dieltiens for convincing me these options
are needed.
Revision 2.142 adds minor revision protection to the generation of
CREATE SEQUENCE statements which are first supported in IDS 9.40. This
was missed when the runtime version protection was added in R2.134.
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 has the same problem.
Futures:
- Break large tables into a small CREATE with subsequent ALTERs?
- Add configurable isolation levels (users DIRTY READ now)
- Add configurable wait mode period (current: 60 seconds)
- Need to work on IDS 10.00 new features support
What else is still missing?
