Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question Unanswered: Slow stored procedure

    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •