Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    73

    Unanswered: Foreign Key Constraints

    OS: AIX,
    UDB : 8.1 FP7


    How do i get all the foreign key state ments to and for a table?

    Here is what i am trying to do . I am dropping a table and recreating by modifying the order of the columns . But i see a lot of foreign key statemtns
    fromother table to this table. Do they get effected when i drop the tables.

    How do i take care of it.

    thanks

  2. #2
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Yes, if you drop a table the associated RI drops too. You have to preserve the RI in DDL to be run either before or after you re-load the data in the new table (your choice).

    An easy and cheap way to extract RI DDL is to use the IBM DB2 Command Center and right click on the table in question - you'll see "generate DDL" - do this and you will get the RI DDL you need. You may need to get the ddl from the other associated tables. To get this info - right click on the table - then click "show related" and look at the table tab.

    You can use the db2look command too. Do some research on that one. You can also buy a tool like Quest Central for thousands of dollars and it do 99.9% of the work for you - including generating the entire export, drop table, build, import, and runstats, etc.

    The manual way is to find all associated RI to the table and write your own DDL.

    Hope this helps,

    -- Steve
    Certified DB2 LUW DBA

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Of course you can also find out all dependencies (RI), indexes, views, check constraints, authorizations etc. on your table by using plain SQL instead of using the Control Center.

    E.g., to find all indexes defined on your table:
    Code:
    SELECT 'CREATE ' || CASE uniquerule WHEN 'D' THEN '' ELSE 'UNIQUE' END ||
           'INDEX ' || name || ' ON ' || tbcreator || '.' || tbname
    FROM sysibm.sysindexes WHERE tbname = 'YOURTABLE' AND tbcreator = 'TABLEOWNER'
    (This is not at all complete: you will have to consult SYSIBM.SYSINDEXCOLUSE for the columns involved, you must collect the authorizations in SYSIBM.SYSINDEXAUTH, you must make sure to use the same bufferpool, ...)

    Finding out about referential constraints in other tables is similar: query SYSIBM.SYSRELS (one line per foreign key) and SYSIBM.SYSKEYCOLUSE (one line per column of each FK). Don't forget to add the appropriate REFERENCES authorizations back (see SYSIBM.SYSTABAUTH) ...

    Also make sure to place the table in the same tablespace as the old one.
    Last edited by Peter.Vanroose; 07-07-06 at 20:03.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •