DB2 Stored Procedure and Function comparisons

[Robin Lowther]

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]

Could you send an example CREATE statement for both sides to demonstrate this issue?

thanks

marko

[Robin Lowther]

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]

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]

Thanks for the info. I will investigate this issue.

Marko

[DB Solo Admin]

This should be fixed now, you can find the new version here

dbsolo.com/preview

Marko