I am having a issue with locks being created on the SYSIBM.SYSROUTINES when executing a simple query that has a UDF or calls a system function. For example if I try:
select udf_name(last_name,first_name,middle_name,name_suf fix) from member where last_name like 'SMITH%' and first_name like 'J%' for read only
select last_name,first_name,middle_name,name_suffix from member where ucase(last_name) like 'SMITH%' and ucase(first_name) like 'J%' for read only
I will get an "Intention Share" lock on the sysroutines table and several "Share" locks on some rows within the table. The locks hang around until a Commit or Rollback. Our application only issue Commits and Rollbacks when work (inserts, updates, and deletes) is being performed. This means that these locks are almost always in place by at least one user constantly. Normally, this would not concern me too much, but it is impossible to create a UDF or Stored Procedure while these locks are in effect.
Can someone tell me why the locks are being issued on a "For Read Only" select statement and how to prevent the locks in the first place?