Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: Store Hierarchical Information in DB2 Table

    I want to store hierarchical information in a DB2 table and perform the following operations on the hierarchy

    1. Search
    2. Add (in middle of the tree or as a leaf)
    3. Delete - If a middle node is deleted entire hierarchy below it should be deleted.

    I am using DB2 7.0

    Any ideas on the simplest way to achieve this ?

    I heard about a feature called Common Table expression, however it does not seem to be available in v7.0

  2. #2
    Join Date
    May 2003
    I am not sure that I understand all your requirements, but you can use Referential Integrity to delete rows in dependent tables. You just set up the Foreign Key on the dependent table with delete cascade. That means that if the higher level row on a parent table is deleted, all rows on the dependent table will be deleted if they have a foreign key (if set to delete cascade) with the same value as the primary key on the parent table . Make sure you create an index on all foreign keys.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2002
    I think 'recursion' will solve your problem. Graeme Birchall's DB2 Cookbook has a very good overview of how this works...

Posting Permissions

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