Hi all DB2 v 8.1.5 on AIX 5.2

Does anyone know if the optimizer creates a predetermined
access plan for data retrieval at compilation time ?

I have a very fast SQL, after meddling with the indices
but the SP containing it is still very slow, and I can't
thin of any other sources of error.

The SP is:

create procedure db2inst2.s_patoopslag ( in p_cpr char (10) )

specific patoopslag_v3

dynamic result sets 1

reads sql data not deterministic

language sql

a:begin

declare cur_1 cursor with return for

select x.k_serviceyderid, x.k_rekvnr, x.v_cprnr, x.c_rekvydernr, x.c_rekvafd,
x.d_rekvdato, x.d_modtdato, x.d_svardato, y.c_mattypecd, z.k_snomed, z.v_txtll, substr(z.c_fritekst,1,50) as fritekst, z.k_matnr, z.k_sekvensnr
from pat.t_rekv x, pat.t_lprp y, pat.t_diag z
where x.k_serviceyderid=y.k_serviceyderid and x.k_rekvnr=y.k_rekvnr
and x.k_serviceyderid=z.k_serviceyderid and x.k_rekvnr=z.k_rekvnr and x.v_cprnr = p_cpr;

open cur_1;

end a


- not very complicated

Thank You

I had the same problem. Fast SQL in SPUFI.... not so fast in SQL SP. I ended up using OPTHINT and rebinding the stored procedure package with the access path from the SPUFI execution.