I am currently using the following query to select rows for a report. It
takes forever, even though there are < 100 rows in the result set; The
problem is that animals, epd and ent_herdid are all large tables, but
the selected rows of animal_sets are always much fewer. I, not being an
SQL guru by any means tried the second select statement in order to
reduce the size of the join operations early. DB2 must know about my
lack of expertise, I swear I heard chuckling along with my error message
(but that might have the currently playing cut on my sound system)
reproduced even further below.
Any suggestions on how I can improve the performance would be greatly
appreciated.
BTW bhid is the primary key or an index of animals, epd and ent_herdid.
"SELECT tattoo, herd_id tag,namex,prefix,regnum,t2.*
FROM ($schema.animals t1 LEFT OUTER JOIN
$schema.epd t2 on t1.bhid=t2.bhid)
JOIN $schema.ent_herdid t3 on t1.bhid=t3.bhid
WHERE t1.bhid IN
(SELECT bhid FROM $schema.animal_sets
WHERE set_name='$setname' AND userid='$setuser')";
SELECT tattoo, herd_id tag,namex,prefix,regnum,t2.*
FROM (is3.animals t1 WHERE t1.bhid IN
(SELECT bhid FROM is3.animal_sets
WHERE set_name='AN' AND userid='jhough')
LEFT OUTER JOIN
is3.epd t2 on t1.bhid=t2.bhid)
JOIN is3.ent_herdid t3 on t1.bhid=t3.bhid;
[IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "WHERE" was
found following "FROM (is3.animals t1". Expected tokens may include:
"JOIN". SQLSTATE=42601