Stored procedure for writing back to db2 database

Viewed 46

Im new to db2 stored procedure, im approaching here for some help/guidance. I have users who access cognos for reporting. Recently I got a requirement from one of our clients for writing back to the db2 table based on user provided comment or input through IBM cognos.

I tried below code in db2 and cognos, but it works half way. The catch is whenever a user provides a fresh entry it gets stored quickly but whenever a user tries to update the same entry, it takes almost 15-20 mins to refresh that record at table level. I won't understand what i can improve on my code here.

create procedure ngetl.new_update_comment (
        in @p_job_status_summary_key integer
        ,in @p_comment varchar(4000)
        ,in @p_modified_by varchar(25)
      )
    dynamic result sets 1
begin
  declare e1 cursor with return for
    select 1
      from ngetl.job_status_summary
      where job_status_summary_key = 17076
      with ur;
  if upper(@p_modified_by) like '%IBM%'
    or upper(@p_modified_by) like 'V%' then
    update ngetl.job_status_summary
      set ibm_comment = @p_comment
          ,modified_by_ibm = @p_modified_by
          ,timestamp_ibm = current_timestamp
      where job_status_summary_key = @p_job_status_summary_key;
  else update ngetl.job_status_summary
    set sbi_comment = @p_comment
        ,modified_by_sbi = @p_modified_by
    where job_status_summary_key = @p_job_status_summary_key;
  end if;
  commit;
  open e1;
end
0 Answers
Related