Set level acestor report; DB2 UDB 8.1.9 Linux

Bob Stearns

From a table like the one below, is there an easy (ha ha) way to get a
set level of pedigree (3 or 5 generations, say?). I have seen solutions
on this group for all the ancestors, but, unless I misunderstood, no
solution for a fixed number of generations. There is always the 2^n way
join for n generations, but I'm looking for something a little simpler
and not so fast growing in term of n. To make things easier? animal 0 is
its own parents, and the parent of all animals whose parent(s) is(are)
unknown. Right now, I am solving the three generation problem with a
recursive php function, but I am looking for a non-procedural sql solution.
create table t (
animal_id integer,
sire_id integer,
dam_id integer,
namex varchar(40),
<lots of other stuff>,
primary key (animal_id) )
alter table t
add constraint sire_id
foreign key(sire_id)
references t(animal_id)
alter table t
add constraint dam_id
foreign key(dam_id)
references t(animal_id)

Mar 29 '06 #1

Gert van der Kooij

In article <at*************@fe03.lga>, rs**********@charter.net says...

From a table like the one below, is there an easy (ha ha) way to get a
set level of pedigree (3 or 5 generations, say?).

One of my favorite replies :)

Download (and read) the DB2 UDB Cookbook from Graeme Birchall (page 299,
recursive queries) from
http://mysite.verizon.net/Graeme_Birchall/id1.html

Mar 29 '06 #2

Bob Stearns

Gert van der Kooij wrote:

In article <at*************@fe03.lga>, rs**********@charter.net says...

From a table like the one below, is there an easy (ha ha) way to get a
set level of pedigree (3 or 5 generations, say?).

One of my favorite replies :)

Download (and read) the DB2 UDB Cookbook from Graeme Birchall (page 299,
recursive queries) from
http://mysite.verizon.net/Graeme_Birchall/id1.html

Thank you for the reply/pointer.

While I only read the recommended chapter, I would recommend it to any
one using DB2. It is clearly written, with copious examples.

The given solutions, as I read the chapter, contain 1 row for each
individual in the ancestor tree. What if I want 1 row for the scion with
all of the ancestors, perhaps in some given order?

Mar 29 '06 #3