Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: Table relationship with view in DB2

    I am using db2 9.1 on redhat linux 5.1

    I know that before dropping a table we must drop all constraints, references,triggers and procedures.

    But my question is do we have to drop all views before dropping a table..

    My actual scenario is that I have to drop a table and move it into a different tablespace... for that I need to drop the table and recreate it in a different table space with the same structure....


    Will I Need to drop all views on that table before dropping the table...

    or is it possible to drop the table and recreate it without having to drop and recreate the views

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes you can drop a table without dropping the view
    the view will be invalid
    when creating again the view it will become valid if all conditions are valid
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You won't have to remove unique/primary keys and/or referential constraints (or any other constraints). They will be removed along with the table. Also, triggers and procedures may automatically be invalidated as well.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by stolze View Post
    You won't have to remove unique/primary keys and/or referential constraints (or any other constraints). They will be removed along with the table. Also, triggers and procedures may automatically be invalidated as well.
    Yeah, awareness is important here:
    At the latest IDUG in Prague I learned (from Scott) about the usefull parameter setting:
    Code:
    pctfree 0 page split high
    if your index has an guaranteed ascending sequence. As all our PK's are composed of generated columns I brewed a script to drop all PK contraints & indexes and rebuilt them acoording to the new rules I'd just learned......

    Not a good idea: I lost all my FK-contraints because they are dropped together with the PK-constraint on the parent-table (I sence a "delete cascade" smell here).
    Luckely I found out in my test systems on time so I dropped the idea instead of the PK's

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Not surprising, I'd say. Each FK has to reference a unique constraint (PK is just a unique constraint). If that referenced constraint vanishes, the FK vanishes.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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