Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Unanswered: Stored Procedures DB2/AS400 Performance

    Hi Folks,

    I am pretty to new to AS400. I have good experience writing SPs in MS-SQL Server/Sybase/Oracle.

    Since I do not know RPG I am trying to SQL solution for a problem. I have the listing of the program here below.



    /* Enter one or more SQL statements separated by semicolons */

    /*
    **CL:CHGCURLIB CURLIB(DQ02326);
    **
    */

    drop procedure dq02326.flood_gap;


    create procedure dq02326.flood_gap
    (in i_client_nbr decimal(5,0))
    language sql
    begin


    declare v_loan_nbr char(20);
    declare v_prpty_nbr int;
    declare v_loan_bal decimal(11,2);
    declare v_client_nbr decimal(4,0);
    declare v_client_region numeric(4,0);
    declare v_client_branch numeric(4,0);
    declare v_fg_cvg_amt decimal(11,2);
    declare v_fl_cvg_amt decimal(11,2);
    declare v_haz_cvg_amt decimal(11,2);
    declare at_end smallint;
    declare numrec int;
    declare v_rows_found int;
    declare v_ins_line char(3) default '010';
    declare v_haz_ins_line char(3) default '001';
    declare v_cvg_chg_level decimal(3,0) default 0;
    declare v_cvg_type char(3) default 'D';
    declare v_source_of_note char(3) default 'MRO';
    declare v_hist_type char(4) default 'INFO';
    declare v_hist_note char(50) default 'MR-ORION processing for Flood Gap-X';
    declare v_ins_co decimal(5,0) default 0;

    declare sqlstmt char(512);
    declare sqlstmt1 char(512);


    declare not_found condition for '02000';

    declare c2 dynamic scroll cursor with return for s2;

    declare c3 dynamic scroll cursor with return for s3;


    declare c1 cursor for
    select incli#,inclrg,inclbr,inloan,inseq#,lnlbal,incamt
    from rmrdta.mrloan l, rmrdta.mrprop p, rmrdta.mrpins i
    where l.lncli# = p.prcli#
    and l.lnclrg = p.prclrg
    and l.lnclbr = p.prclbr
    and l.lnloan = p.prloan
    and i.incli# = p.prcli#
    and i.inclrg = p.prclrg
    and i.inclbr = p.prclbr
    and i.inloan = p.prloan
    and i.inseq# = p.prseq#
    and l.lncli# = i_client_nbr
    and incvtp = 'FL'
    and lnstsf = 'T'
    and lnlbal > 0
    and inlpsd = 0
    and date(char(inexpd)) >= current_date
    for read only;


    declare continue handler for not_found set at_end = 1;

    open c1;

    set at_end = 0;
    set numrec = 0;

    while at_end = 0 do

    fetch c1 into v_client_nbr,v_client_region,v_client_branch,v_loa n_nbr,v_prpty_nbr,v_loan_bal,v_fl_cvg_amt;


    set numrec = numrec + 1;
    insert into flood_gap_tran
    (fgcli#
    ,fgclrg
    ,fgclbr
    ,fgloan
    ,fgseq#
    ,fgcvln
    ,fgcvtp
    ,fgtrty
    ,fgcamt
    ,fgadid
    ,fglupd)
    values (v_client_nbr
    ,v_client_region
    ,v_client_branch
    ,v_loan_nbr
    ,v_prpty_nbr
    ,v_ins_line
    ,'FA'
    ,'N'
    ,v_fg_cvg_amt
    ,user
    ,now());

    set v_rows_found = 0;

    set sqlstmt = 'select count(*) from rmrdta.mrpins
    where inlpsd = 0
    and incli# = ? and inclrg = ?
    and inclbr = ? and inloan = translate(cast(? as char(20)))
    and inseq# = ? and incvtp = translate(cast(? as char(3)))';
    prepare s2 from sqlstmt;

    open c2 using v_client_nbr,v_client_region,v_client_branch,v_loa n_nbr,v_prpty_nbr,v_cvg_type;
    fetch c2 into v_rows_found;
    close c2;

    if v_rows_found = 0 then

    set numrec = numrec + 1;
    insert into flood_gap_tran
    (fgcli#
    ,fgclrg
    ,fgclbr
    ,fgloan
    ,fgseq#
    ,fgcvln
    ,fgcvtp
    ,fgtrty
    ,fgcamt
    ,fgadid
    ,fglupd)
    values (v_client_nbr
    ,v_client_region
    ,v_client_branch
    ,v_loan_nbr
    ,v_prpty_nbr
    ,v_ins_line
    ,'FB'
    ,'N'
    ,v_fg_cvg_amt
    ,user
    ,now());

    set v_haz_cvg_amt = 0;
    set sqlstmt1 = 'select max(incamt) from rmrdta.mrpins
    where incli# = ? and inclrg = ?
    and inclbr = ? and inloan = translate(cast(? as char(20)))
    and inseq# = ? and incvln = translate(cast(? as char(3)))
    group by incli#,inclrg,inclbr,inloan,inseq#,incvln
    having max(inexpd) = inexpd';
    prepare s3 from sqlstmt;

    open c3 using v_client_nbr,v_client_region,v_client_branch,v_loa n_nbr,v_prpty_nbr,v_haz_ins_line;
    fetch c3 into v_haz_cvg_amt;
    close c3;


    /*
    ** Calculate the Flood Gap Amount
    */
    set v_fg_cvg_amt = v_haz_cvg_amt*0.8;

    if v_loan_bal > v_fg_cvg_amt then
    set v_fg_cvg_amt = v_loan_bal;
    end if;

    if v_fg_cvg_amt > 250000 then
    set v_fg_cvg_amt = 250000;
    end if;

    set v_fg_cvg_amt = v_fg_cvg_amt - v_fl_cvg_amt;

    --if v_fg_cvg_amt > 2000 then

    /*
    ** If the Flood Gap > the limit; Add a record into the transaction table
    ** Insert a Record into MRHIST Table and send a record to 48R table
    */

    set numrec = numrec + 1;
    insert into flood_gap_tran
    (fgcli#
    ,fgclrg
    ,fgclbr
    ,fgloan
    ,fgseq#
    ,fgcvln
    ,fgcvtp
    ,fgtrty
    ,fgcamt
    ,fgadid
    ,fglupd)
    values (v_client_nbr
    ,v_client_region
    ,v_client_branch
    ,v_loan_nbr
    ,v_prpty_nbr
    ,v_ins_line
    ,'FG'
    ,'N'
    ,v_fg_cvg_amt
    ,user
    ,now());

    /*
    insert into rmrdta.mrhist
    (lhcli#
    ,lhclrg
    ,lhclbr
    ,lhloan
    ,lhpseq
    ,lhcvln
    ,lhcvcl
    ,lhcvtp
    ,lhsort
    ,lhtype
    ,lhnote
    ,lhaddt
    ,lhadtm
    ,lhadid
    ,lhadpg
    ,lhadoc
    ,lhico#)
    values
    (v_client_nbr
    ,v_client_region
    ,v_client_branch
    ,v_loan_nbr
    ,v_prpty_nbr
    ,v_ins_line
    ,v_cvg_chg_level
    ,v_cvg_type
    ,v_source_of_note
    ,v_hist_type
    ,v_hist_note
    ,year(current_date)*1000+dayofyear(current_date)
    ,hour(current_time)*10000+minute(current_time)*100 +second(current_time)
    ,user
    ,'FG - SP'
    ,'48R MR-ORION Interface'
    ,v_ins_co);
    */
    --end if;

    end if;

    end while;

    close c1;

    end




    The performance of this is very bad. The dynamic cursors with in the main loop are the problem. Each one is taking 30 secs. There is an index on the fields in the where clause for both the dynamic cursors.

    Please let me know the best way I can improve the performance.

    Thanks much.

    GP

  2. #2
    Join Date
    Jun 2003
    Posts
    7
    I removed the "translate" from the where clause. The performance has dramatically improved.

    Thanks for your help.

Posting Permissions

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