Reworking Primary and Foreign Keys to be Unattache

IDS 11.5.UC5 AIX 5.3 I am in the process in removing all attached indices (most of them primary and foreign keys) and relocating them to dbspace separate from their tables. Has anyone wrote a routine that would, for all primary keys, generate SQL to do the following: 1. drop the current constraint, 2. create the unique indices, 3. recreate the primary key constraint. Has anyone wrote a routine that will, for every foreign key, generate SQL to: 1. drop the current constraint, 2. create the index, 3. recreate the foreign key constraint. I looked within the IIUG Software area and didn't notice anything there for this ... Just looking for a little help .... or guidance in writing such routines if anyone has any ideas :) Thanks in advance. Clifton _________________________________________________________________ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/171222985/direct/01/

Cliff, Myschema will generate the DDL for the indexes and constraints to a separate file from the rest of the DDL. Just pass it two filenames on the command line instead of only one. The first file will contain all of the CREATE TABLE statements and supporting statements and the second file will contains views, synonyms, indexes, constraints, procedures, and grants. My utils4_ak package contains sample awk scripts to parse the DDL files and generate other statements. It would be simple to modify one of the samples to generate the DROP INDEX and ALTER TABLE DROP CONSTRAINT statements. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Oct 21, 2009 at 2:44 PM, Clifton Bean <clifton_bean@hotmail.com>wrote: > IDS 11.5.UC5 AIX 5.3 > > I am in the process in removing all attached indices (most of them primary > and > foreign keys) and relocating them to dbspace separate from their tables. > > Has anyone wrote a routine that would, for all primary keys, generate SQL > to > do the following: > > 1. drop the current constraint, > > 2. create the unique indices, > > 3. recreate the primary key constraint. > > Has anyone wrote a routine that will, for every foreign key, generate SQL > to: > > 1. drop the current constraint, > > 2. create the index, > > 3. recreate the foreign key constraint. > > I looked within the IIUG Software area and didn't notice anything there for > this ... > > Just looking for a little help .... or guidance in writing such routines if > anyone has any ideas :) > > Thanks in advance. > > Clifton > > _________________________________________________________________ > Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. > http://clk.atdmt.com/GBL/go/171222985/direct/01/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bd49425e29c0476776078