quickest way to drop all referential contraints

Is there a super quick way to drop ALL referential constraints and indexes behind them? I would like to put all indexes into a space with a larger page size. I wrote a program one time that consumes dbschema outut and writes sql statements to drop and then add indexes. What I ran into was that when an index is dropped (one that I added before the constraints) the engine renames the index to a name of his choice and then the “add index” fails because there is already and index on that column. The simplest approach appears to be to write a program to grab all the index adds and referential constraint adds and generate sql statements to *drop* all indexes and all constraints. Then, after running that sql just grab the part of the dbschema output that adds them and globally replace “btree” with “btree in idxspace” , save the file and run it through dbaccess. I suppose one of you gurus would hammer the syscontraints table, etc. I’m too chicken. That part of the schema is about 4260 lines, so doing in an editor is not looking like a plan. So is there a better plan? Some magic Kagle unix script that I can convert to run through Windows?

I have code somewhere to read system catalogues and drop all = constraints. I'll dig it up. j. On Feb 2, 2012, at 5:48 PM, Bill Hamilton wrote: > Is there a super quick way to drop ALL referential constraints and = indexes=20 > behind them?=20 >=20 > I would like to put all indexes into a space with a larger page size.=20= > I wrote a program one time that consumes dbschema outut and writes sql=20= > statements to drop and then add indexes.=20 > What I ran into was that when an index is dropped (one that I added = before the=20 > constraints) the engine renames the index to=20 > a name of his choice and then the =93add index=94 fails because there = is=20 > already and index on that column.=20 >=20 > The simplest approach appears to be to write a program to grab all the = index=20 > adds and referential constraint adds and generate sql=20 > statements to *drop* all indexes and all constraints. Then, after = running that=20 > sql just grab the part of the dbschema output that adds them=20 > and globally replace =93btree=94 with =93btree in idxspace=94 , save = the file=20 > and run it through dbaccess.=20 > I suppose one of you gurus would hammer the syscontraints table, etc. = I=92m=20 > too chicken.=20 > That part of the schema is about 4260 lines, so doing in an editor is = not=20 > looking like a plan.=20 >=20 > So is there a better plan? Some magic Kagle unix script that I can = convert to=20 > run through Windows?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

in essence. Muck with it as you like. Essentially join systables with syscontraints and sysindexes. FOREACH SELECT tabname, constrname, e.colname col1, f.colname col2 INTO p_tabname, p_constrname, p_col1, p_col2 FROM systables a, sysconstraints b, mytabs c, sysindexes d,=20 syscolumns e, outer syscolumns f WHERE a.tabname=3Dlower(c.tabname) AND a.tabid=3Db.tabid AND b.constrtype=3D'P' AND b.idxname=3Dd.idxname AND a.tabid=3De.tabid AND d.part1=3De.colno AND a.tabid=3Df.tabid AND d.part2=3Df.colno drop constraint p_constrname END FOREACH On Feb 2, 2012, at 5:48 PM, Bill Hamilton wrote: > Is there a super quick way to drop ALL referential constraints and = indexes=20 > behind them?=20 >=20 > I would like to put all indexes into a space with a larger page size.=20= > I wrote a program one time that consumes dbschema outut and writes sql=20= > statements to drop and then add indexes.=20 > What I ran into was that when an index is dropped (one that I added = before the=20 > constraints) the engine renames the index to=20 > a name of his choice and then the =93add index=94 fails because there = is=20 > already and index on that column.=20 >=20 > The simplest approach appears to be to write a program to grab all the = index=20 > adds and referential constraint adds and generate sql=20 > statements to *drop* all indexes and all constraints. Then, after = running that=20 > sql just grab the part of the dbschema output that adds them=20 > and globally replace =93btree=94 with =93btree in idxspace=94 , save = the file=20 > and run it through dbaccess.=20 > I suppose one of you gurus would hammer the syscontraints table, etc. = I=92m=20 > too chicken.=20 > That part of the schema is about 4260 lines, so doing in an editor is = not=20 > looking like a plan.=20 >=20 > So is there a better plan? Some magic Kagle unix script that I can = convert to=20 > run through Windows?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

Thanks, Jack. I can do that with the sql below and then drop them in a FOREACH as you suggested. I was hoping there was some powerful utility to drop all referential constraints. SELECT x.constrname[1,64] as CON, i.idxname[1,64] as IDX, t.tabname[1,32] as TAB, c.colname[1,32] as COL, ttt.tabname[1,32] AS REFTAB FROM systables t, sysconstraints x, syscolumns c, sysindexes i, sysreferences r, systables ttt WHERE t.tabid = x.tabid and t.tabid = c.tabid and x.idxname = i.idxname and (part1 = colno or part2 = colno or part3 = colno or part4=colno or part5=colno or part6=colno) and x.constrtype = 'R' and r.ptabid = ttt.tabid and r.constrid = x.constrid ORDER BY 1; -----Original Message----- From: Jack Parker Sent: Thursday, February 02, 2012 5:37 PM To: ids@iiug.org Subject: Re: quickest way to drop all referential contr.... [26156] in essence. Muck with it as you like. Essentially join systables with syscontraints and sysindexes. FOREACH SELECT tabname, constrname, e.colname col1, f.colname col2 INTO p_tabname, p_constrname, p_col1, p_col2 FROM systables a, sysconstraints b, mytabs c, sysindexes d,=20 syscolumns e, outer syscolumns f WHERE a.tabname=3Dlower(c.tabname) AND a.tabid=3Db.tabid AND b.constrtype=3D'P' AND b.idxname=3Dd.idxname AND a.tabid=3De.tabid AND d.part1=3De.colno AND a.tabid=3Df.tabid AND d.part2=3Df.colno drop constraint p_constrname END FOREACH On Feb 2, 2012, at 5:48 PM, Bill Hamilton wrote: > Is there a super quick way to drop ALL referential constraints and = indexes=20 > behind them?=20 >=20 > I would like to put all indexes into a space with a larger page size.=20= > I wrote a program one time that consumes dbschema outut and writes sql=20= > statements to drop and then add indexes.=20 > What I ran into was that when an index is dropped (one that I added = before the=20 > constraints) the engine renames the index to=20 > a name of his choice and then the =93add index=94 fails because there = is=20 > already and index on that column.=20 >=20 > The simplest approach appears to be to write a program to grab all the = index=20 > adds and referential constraint adds and generate sql=20 > statements to *drop* all indexes and all constraints. Then, after = running that=20 > sql just grab the part of the dbschema output that adds them=20 > and globally replace =93btree=94 with =93btree in idxspace=94 , save = the file=20 > and run it through dbaccess.=20 > I suppose one of you gurus would hammer the syscontraints table, etc. = I=92m=20 > too chicken.=20 > That part of the schema is about 4260 lines, so doing in an editor is = not=20 > looking like a plan.=20 >=20 > So is there a better plan? Some magic Kagle unix script that I can = convert to=20 > run through Windows?=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Bill, you want to have a *weapon of mass destruction* ? ? oh, NO... :-) kidding... Thanks, Frank On Fri, Feb 3, 2012 at 12:15 PM, Bill Hamilton <garage_dba@hotmail.com>wrote: > Thanks, Jack. > I can do that with the sql below and then drop them in a FOREACH as you > suggested. > I was hoping there was some powerful utility to drop all referential > constraints. > > SELECT x.constrname[1,64] as CON, > i.idxname[1,64] as IDX, > t.tabname[1,32] as TAB, > c.colname[1,32] as COL, > ttt.tabname[1,32] AS REFTAB > FROM systables t, sysconstraints x, syscolumns c, > > sysindexes i, sysreferences r, systables ttt > WHERE t.tabid = x.tabid and t.tabid = c.tabid > and x.idxname = i.idxname > and (part1 = colno or part2 = colno or part3 = colno or part4=colno or > part5=colno or part6=colno) > and x.constrtype = 'R' > and r.ptabid = ttt.tabid and r.constrid = x.constrid > ORDER BY 1; > > -----Original Message----- > From: Jack Parker > Sent: Thursday, February 02, 2012 5:37 PM > To: ids@iiug.org > Subject: Re: quickest way to drop all referential contr.... [26156] > > in essence. Muck with it as you like. > > Essentially join systables with syscontraints and sysindexes. > > FOREACH > > SELECT tabname, constrname, e.colname col1, f.colname col2> > INTO p_tabname, p_constrname, p_col1, p_col2 > > FROM systables a, sysconstraints b, mytabs c, sysindexes d,=20 > > syscolumns e, outer syscolumns f > > WHERE a.tabname=3Dlower(c.tabname) > > AND a.tabid=3Db.tabid > > AND b.constrtype=3D'P' > > AND b.idxname=3Dd.idxname > > AND a.tabid=3De.tabid > > AND d.part1=3De.colno > > AND a.tabid=3Df.tabid > > AND d.part2=3Df.colno > > drop constraint p_constrname > > END FOREACH > > On Feb 2, 2012, at 5:48 PM, Bill Hamilton wrote: > > > Is there a super quick way to drop ALL referential constraints and = > indexes=20 > > behind them?=20 > >=20 > > I would like to put all indexes into a space with a larger page size.=20= > > > I wrote a program one time that consumes dbschema outut and writes > sql=20= > > > statements to drop and then add indexes.=20 > > What I ran into was that when an index is dropped (one that I added = > before the=20 > > constraints) the engine renames the index to=20 > > a name of his choice and then the =93add index=94 fails because there = > is=20 > > already and index on that column.=20 > >=20 > > The simplest approach appears to be to write a program to grab all the = > index=20 > > adds and referential constraint adds and generate sql=20 > > statements to *drop* all indexes and all constraints. Then, after = > running that=20 > > sql just grab the part of the dbschema output that adds them=20 > > and globally replace =93btree=94 with =93btree in idxspace=94 , save = > the file=20 > > and run it through dbaccess.=20 > > I suppose one of you gurus would hammer the syscontraints table, etc. = > I=92m=20 > > too chicken.=20 > > That part of the schema is about 4260 lines, so doing in an editor is = > not=20 > > looking like a plan.=20 > >=20 > > So is there a better plan? Some magic Kagle unix script that I can = > convert to=20 > > run through Windows?=20 > >=20 > >=20 > > = > **************************************************************************= > *****=20 > > Forum Note: Use "Reply" to post a response in the discussion forum.=20= > > >=20 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --f46d043be25673fe7e04b813b869