BHID id the (only) primary key, and thus index, of Animals, which is the
only real table involved.

Pedigree2 is the view based on Animals.

runstats on table is3.animals ON ALL COLUMNS AND DETAILED INDEXES was
run before the test.

The test was run multiple times to eliminate initial buffering bias.

The execution plan shows lots of relatively expensive nested loop JOINs,
which surprises me because all the JOINs are on the primary key of animals.

The following query takes 3-5 seconds which seems a bit excessive:

select * from is3.pedigree2
 where  bhid in
        ( 322380, 379701, 380901, 394336, 394342, 396039, 400764)

Furthermore, when extended to the desired use, it generates a warning
message for which I can find no explanation for the reason code:

select * from is3.pedigree2
 where  bhid in
        (select bhid from is3.ANIMAL_SETS
           where set_name = '000' and USERID = 'jhoughex')

DB2 SQL error: SQLCODE: 437, SQLSTATE: 01602, SQLERRMC: 1
Performance of this complex query may be sub-optimal.  Reason code: "1".

Both queries depend on the same complex view. I have used much more
complex sql without generating such a message or having such poor
running times. Any suggestions greatly appreciated. The view is below
(longish):

CREATE VIEW IS3.Pedigree2 AS
SELECT  animal.BHID   AS BHID,
        animal.Namex  AS Name,
        animal.assoc  AS Assoc,
        animal.prefix AS Prefix,
        animal.regnum AS Regnum,

        Sire.Namex  AS Name_Sire,
        Sire.assoc  AS Assoc_Sire,
        Sire.prefix AS Prefix_Sire,
        Sire.regnum AS Regnum_Sire,
        Dam.Namex   AS Name_Dam,
        Dam.assoc   AS Assoc_Dam,
        Dam.prefix  AS Prefix_Dam,
        Dam.regnum  AS Regnum_Dam,

        Sire_Sire.Namex       AS Name_Sire_Sire,
        Sire_Sire.assoc       AS Assoc_Sire_Sire,
        Sire_Sire.prefix      AS Prefix_Sire_Sire,
        Sire_Sire.regnum      AS Regnum_Sire_Sire,
        Sire_Dam.Namex        AS Name_Sire_Dam,
        Sire_Dam.assoc        AS Assoc_Sire_Dam,
        Sire_Dam.prefix       AS Prefix_Sire_Dam,
        Sire_Dam.regnum       AS Regnum_Sire_Dam,
        Dam_Sire.Namex        AS Name_Dam_Sire,
        Dam_Sire.assoc        AS Assoc_Dam_Sire,
        Dam_Sire.prefix       AS Prefix_Dam_Sire,
        Dam_Sire.regnum       AS Regnum_Dam_Sire,
        Dam_Dam.Namex         AS Name_Dam_Dam,
        Dam_Dam.assoc         AS Assoc_Dam_Dam,
        Dam_Dam.prefix        AS Prefix_Dam_Dam,
        Dam_Dam.regnum        AS Regnum_Dam_Dam,

        Sire_Sire_Sire.Namex  AS Name_Sire_Sire_Sire,
        Sire_Sire_Sire.assoc  AS Assoc_Sire_Sire_Sire,
        Sire_Sire_Sire.prefix AS Prefix_Sire_Sire_Sire,
        Sire_Sire_Sire.regnum AS Regnum_Sire_Sire_Sire,
        Sire_Sire_Dam.Namex   AS Name_Sire_Sire_Dam,
        Sire_Sire_Dam.assoc   AS Assoc_Sire_Sire_Dam,
        Sire_Sire_Dam.prefix  AS Prefix_Sire_Sire_Dam,
        Sire_Sire_Dam.regnum  AS Regnum_Sire_Sire_Dam,
        Sire_Dam_Sire.Namex   AS Name_Sire_Dam_Sire,
        Sire_Dam_Sire.assoc   AS Assoc_Sire_Dam_Sire,
        Sire_Dam_Sire.prefix  AS Prefix_Sire_Dam_Sire,
        Sire_Dam_Sire.regnum  AS Regnum_Sire_Dam_Sire,
        Sire_Dam_Dam.Namex    AS Name_Sire_Dam_Dam,
        Sire_Dam_Dam.assoc    AS Assoc_Sire_Dam_Dam,
        Sire_Dam_Dam.prefix   AS Prefix_Sire_Dam_Dam,
        Sire_Dam_Dam.regnum   AS Regnum_Sire_Dam_Dam,
        Dam_Sire_Sire.Namex   AS Name_Dam_Sire_Sire,
        Dam_Sire_Sire.assoc   AS Assoc_Dam_Sire_Sire,
        Dam_Sire_Sire.prefix  AS Prefix_Dam_Sire_Sire,
        Dam_Sire_Sire.regnum  AS Regnum_Dam_Sire_Sire,
        Dam_Sire_Dam.Namex    AS Name_Dam_Sire_Dam,
        Dam_Sire_Dam.assoc    AS Assoc_Dam_Sire_Dam,
        Dam_Sire_Dam.prefix   AS Prefix_Dam_Sire_Dam,
        Dam_Sire_Dam.regnum   AS Regnum_Dam_Sire_Dam,
        Dam_Dam_Sire.Namex    AS Name_Dam_Dam_Sire,
        Dam_Dam_Sire.assoc    AS Assoc_Dam_Dam_Sire,
        Dam_Dam_Sire.prefix   AS Prefix_Dam_Dam_Sire,
        Dam_Dam_Sire.regnum   AS Regnum_Dam_Dam_Sire,
        Dam_Dam_Dam.Namex     AS Name_Dam_Dam_Dam,
        Dam_Dam_Dam.assoc     AS Assoc_Dam_Dam_Dam,
        Dam_Dam_Dam.prefix    AS Prefix_Dam_Dam_Dam,
        Dam_Dam_Dam.regnum    AS Regnum_Dam_Dam_Dam

  FROM  is3.animals animal
        /* Sire and Dam of the Animal */
  JOIN  is3.animals Sire ON animal.Sire_bhid = Sire.bhid
  JOIN  is3.animals Dam  ON animal.Dam_bhid  = Dam.bhid
        /* Sire and Dam of the Sire */
  JOIN  is3.animals Sire_Sire ON Sire.Sire_bhid = Sire_Sire.bhid
  JOIN  is3.animals Sire_Dam  ON Sire.Dam_bhid  = Sire_Dam.bhid
        /* Sire and Dam of the Dam */
  JOIN  is3.animals Dam_Sire ON Dam.Sire_bhid = Dam_Sire.bhid
  JOIN  is3.animals Dam_Dam  ON Dam.Dam_bhid  = Dam_Dam.bhid
        /* Sire and Dam of the Sire_Sire */
  JOIN  is3.animals Sire_Sire_Sire ON Sire_Sire.Sire_bhid =
Sire_Sire_Sire.bhid
  JOIN  is3.animals Sire_Sire_Dam  ON Sire_Sire.Dam_bhid  =
Sire_Sire_Dam.bhid
        /* Sire and Dam of the Sire_Dam */
  JOIN  is3.animals Sire_Dam_Sire ON Sire_Dam.Sire_bhid =
Sire_Dam_Sire.bhid
  JOIN  is3.animals Sire_Dam_Dam  ON Sire_Dam.Dam_bhid  = Sire_Dam_Dam.bhid
        /* Sire and Dam of the Dam_Sire */
  JOIN  is3.animals Dam_Sire_Sire ON Dam_Sire.Sire_bhid =
Dam_Sire_Sire.bhid
  JOIN  is3.animals Dam_Sire_Dam  ON Dam_Sire.Dam_bhid  = Dam_Sire_Dam.bhid
        /* Sire and Dam of the Dam_Dam */
  JOIN  is3.animals Dam_Dam_Sire ON Dam_Dam.Sire_bhid = Dam_Dam_Sire.bhid
  JOIN  is3.animals Dam_Dam_Dam  ON Dam_Dam.Dam_bhid  = Dam_Dam_Dam.bhid