DB2 Stored Procedure and Function comparisons

16 views
Skip to first unread message

Robin Lowther

unread,
Apr 27, 2021, 4:17:25 PMApr 27
to DB Solo
Creating a separate conversion specifically for this;

When comparing stored procedures in DB2, procschema and procname cannot be used to uniquely identify a procedure.  The comparison needs to be based on procschema, procname and parm_signature.  The same applies to functions as well.

DB Solo Admin

unread,
Apr 27, 2021, 8:56:24 PMApr 27
to DB Solo
Could you send an example CREATE statement for both sides to demonstrate this issue?

thanks
marko

Robin Lowther

unread,
Apr 28, 2021, 12:15:24 PMApr 28
to DB Solo
When you compare databases with the following stored procedures deployed, you get two matches where the source for both matches the first definition.

Source;
create procedure test.sp_test (in in_var varchar(20)) begin end
create procedure test.sp_test (in in_var varchar(20), in in_var2 varchar(20)) begin end

Target;
create procedure test.sp_test (in in_var varchar(20)) begin end
create procedure test.sp_test (in in_var varchar(20), in in_var2 varchar(50)) begin declare i integer; end

Regards,
Robin

Robin Lowther

unread,
Apr 28, 2021, 12:24:26 PMApr 28
to DB Solo

This is what the catalog tables look like where you will see that the parm_signature for the first procedure is based on a single varchar whilst the second procedure is based on two varchars;

select procschema, procname, specificname, parm_signature 
from syscat.procedures 
where procschema='TEST' 
and procname='SP_TEST'

PROCSCHEMA PROCNAME SPECIFICNAME       PARM_SIGNATURE 
---------- -------- ------------------ -------------- 
TEST       SP_TEST  SQL210428165926828 3800           
TEST       SP_TEST  SQL210428165941529 38003800       

DB Solo Admin

unread,
Apr 29, 2021, 3:53:12 PMApr 29
to DB Solo
Thanks for the info. I will investigate this issue.

Marko

DB Solo Admin

unread,
May 9, 2021, 12:11:23 PMMay 9
to DB Solo
This should be fixed now, you can find the new version here

Reply all
Reply to author
Forward
0 new messages