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