Writing a pretty heavy query, trying to limit data base on coniditions.
Three table join on multiple columns all part of primary key.
tables are 1 - common table, 2 - line table, 3 - temp table created in previous sql which consits only of all primary keys of items we want from common and line tables

so outline:

select common.values, line.values from
temp table, common.table, line.table
where
temp table.primary key = common table.primary key and
common table.primary key = line table. primary key

the key is made up of 5 columns.

when we join the common to the line the data is back in a flash, however, it returns more data then we will ever need
as soon as we join in the temp table (1/10th the data compared to volume in the common) the query will run forever!!! The main purpose of the temp data is to filter out data we do not need (ie 1/10 of the common table)

right after the temp table is created (previous step) runstats is handled like this
"RUNSTATS ON TABLE DC.TMP_DCS_EXT_2 AND INDEXES ALL SHRLEVEL CHANGE"

tried joining just the common and temp table performance is just as horrible.
any ideas on where to look?

Can you do a

db2 explain all with snapshot for <sql statement>

db2 "select max(explain_time) from explain_instance"

db2exfmt -d sample -g -o explain.out -w -1 -# 0
(If it prompts for other options, hit enter to accept defaults)

If you have not created explain tables before , create explain tables using the definitions in sqllib\misc\EXPLAIN.DDL

If you attach the output of the above command along with the table defintion, someone here may be able to help

yes I can

db2 explain all with snapshot for <sql statement>

db2 "select max(explain_time) from explain_instance"

explain max(explain_time)
results
1
--------------------------
2004-01-26-12.22.27.486488

db2exfmt -d sample -g -o explain.out -w -1 -# 0
see attachments

If you attach the output of the above command along with the table defintion, someone here may be able to help
see attachments

try:
1. check all indexes. if they cover your joins
2. run index advise(see script below).
3. can you get db2 get db cfg and get dbm cfg.
and db2level, oslevel, lsconf (aix)
4. ddls for indexes.
5. check if step 3,7,5 are covered with good indexes.


:
sql_file=$1
set -x
db2advis -d db2prqa -t 5 -o ${sql_file}.out -i $sql_file -a db2inst1/db2inst22
exit;

for your sizes, looks, like you need to tune your parameters too:
all this is too small.
Parallelism: None
CPU Speed: 5.668131e-07
Comm Speed: 100
Buffer Pool size: 1000
Sort Heap size: 256
Database Heap size: 1200
Lock List size: 100
Maximum Lock List: 10
Average Applications: 1
Locks Available: 1130

Also run runstats on tables and indexes involved if you did not yet.