Disabling Constraints and stuff
IDS 10.00.UC8
Solaris 2.8
I am trying to redo a script(s) that we use to disable table objects (indexes,
triggers and constraints). I need to be able to:
1. Disable objects for all tables that are only children - i.e. these tables
are not referenced by any other tables via a foreign key. In the below example
that would be tables C,E,F, and G. This step is easy:
unload to ${CHILD_ONLY_TABLIST}
select a.tabid, c.tabname from sysconstraints a, systables c
where a.tabid = c.tabid
and a.constrtype = "R"
and a.tabid not in (select ptabid from sysreferences)
2. Disable objects for any table that is a parent and a child, but whose
children are all disabled. In the below example only Table D would qualify.
Tab A
| |
Tab B Tab C
|
Tab D - Tab E
|
Tab F - Tab G
3. Disable the rest.
My problem is with step 2. I can get the list of the two tables left that are
parents and children - Tab's B and D. But I cannot figure out how to exclude
Table B from the list. Here is what I have that gives me Tabs B and D:
unload to ${BOTH_CHILDandPARENT_TABLIST}
select unique(a.tabname), b.tabid from systables a, sysconstraints b
where a.tabid = b.tabid
and b.constrtype = "R"
and b.tabid in (select ptabid from sysreferences)
This may be too vague - dunno.
MM
Woh - that diagram did NOT turn out like I expected... I think I have an idea anyways using a temp table - if you caught the drift of my question feel free to fire away with cool ideas. Mike