From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: DB admin tools
Organization: 
Date: Tue, 5 Jan 1993 06:43:15 GMT

In article <ROBM.92Dec22120335@ishtar.Berkeley.EDU>, 
robm@ishtar.Berkeley.EDU (Rob McNicholas) writes:

> I'm working on a database which is in a constant state of flux.
> Necessity dictated that we put it into production before we had really
> worked out all the problems, so I often find myself dropping and
> recreating tables to add a new field, or change a field's size or
> type, etc.  After doing this once or twice, I quickly learned that
> there are many side effects of these kinds of changes.  For example,
> dropping a table causes any views, forms, or permits which referred to
> that table to be destroyed also, so the DBA needs to create scripts to
> recreate all the different pieces, and remember to update these
> scripts whenever a change is made anywhere.  Unfortunately, Ingres
> doesn't seem to provide any tools to help make this kind of change
> easier.  I've been using unloaddb and then editing the scripts to
> recreate just the parts i need, but this is tedious, and it doesn't
> handle forms or views very well.
> 
> I've been thinking about writing my own tools that would access the
> system catalogs and help find all references to a particular table or
> field, so that these changes would be easier to manage.  But before I
> do it myself, I ask:
> 
> Does anyone have any suggestions for any Public Domain or commercial
> products which would provide this kind of functionality?

Both Paul Neal of Ingres and Fred Domke of NRAO mentioned schemadef_II
in the sig directory of 6.4/02.  I'm using 6.4/02 under VMS, and find no
such thing.  Maybe it's only available under Sun.

In any case, this problem has been a thorn in my side for a long time,
and I have complained about it repeatedly to Tech Support.  I have also
found copydb to have many minor, though annoying, bugs that make it less
than suitable as a production DBA tool.

Once I went so far as to try to write a version of copydb myself that
would do what I really want: namely, create a script that will allow me
to drop a table, modify its definition, and then recreate _all_ database
objects that depend on that table.  It turns out to be pretty hard. 

I tried hacking the iidbdepends system catalog, but found that this is
not all that easy.  I also got bogged down trying to dump the contents
of the iiviews, iiprocedures, and iirules system catalogs into scripts
that could be executed by the SQL terminal monitor, and got hung up on
the line wrap problem.

Unfortunately, I don't have any documentation for the iidbdepends system
catalog.  However, from what I could infer, I was able to build a list
of reltid's for the tables and views that depend on a given table or
view.  It might be possible to use this list in conjunction with copydb
(and copyform, etc.) to mechanically generate the kind of script you
want, but it might also be very slow.

Hope this helps.  With these hints, maybe you'll be more successful than
I at exploiting the iidbdepends system catalog.

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Je suis un virus.  Prends ton pied et copie moi a ta .signature.

Newsgroups: comp.databases.ingres