stored procedure cleanup

IDS 14.10.FC10 on RHEL 8.8 We are trying to identify obsolete stored procedures so that we can remove them from the system. Some of these were developed as part of projects that never went forward, others are procedures that used to be part of our application but have been replaced with newer versions with different signatures. Is there any way to identify which procedures are actively in use? Or the last time a stored procedure was run? Or any other way to identify those procedures that can safely be dropped from the instance? Thanks. ------------------------------ mark collins ------------------------------

Mark: Nothing built-in, no. The best thing to do is to create a table to track proc runs: create table procruns ( procid int not null primary key, runcnt int default 0,lastrun datetime year to second default datetime (1900-01-01 00:00:00) year to second ); Then you can insert a starter row for any procedure that you suspect is unused: insert into procruns( procid ) values (12345); select * from procruns;procid runcnt lastrun 12345 0 1900-01-01 00:00:00 1 row(s) retrieved. Finally instrument those procedures/functions to update their row in the procruns table incrementing the count and updating the lastrun datetime: update procruns set runcnt = runcnt + 1, lastrun = current year to second where procid = (select procid from sysprocedures where procname = 'thisprocedure' ); Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Maybe there is a new sysmaster table where you can look after the stored procedures usage. SQL query select first 100 * from sysmaster:sysprc join sysprocedures on procid = prc_id order by prc_last_access Or in cmd line:onstat -g cac prc ------------------------------Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Hi Art, Our developers talked about creating a tracking table, but we decided it would be too difficult to get the exact procedure identified. If we have test_proc(int, int, varchar) and test_proc(int, char), there is not an easy way to pin down which is being executed. If dbinfo() could return that info, that would be great, but it doesn't. And adding that line at the start of every procedure would likely result in developers copy/pasting from one procedure to another, and somewhere, someone would neglect to update the pasted block with the correct procname (and signature, if we were to add that to the mix) and proc_a would get credited when it was actually proc_b that was run. ------------------------------ mark collins ------------------------------

Mark: As has already been pointed out, there is the sysmaster table sysprc which has the column prc_last_access: Column name Type Nulls prc_hashno integer yes prc_chainno smallint yes prc_id integer yes prc_refcnt integer yes prc_delete integer yes prc_hits bigint yes prc_last_access char(20) yes prc_heapptr int8 yes prc_heapsz integer yes prc_servername char(128) yes prc_dbname char(128) yes prc_ownername char(32) yes prc_name char(257) yes The only problem is that there is no history kept in sysmaster, so you will have to poll it before shutting down or clearing stats. Also it only shows active procedures. Any proc that has not run since stats were cleared will not appear in sysmaster:sysprc, so you will have to outer join its contents with your system catalog for the particular database to determine procs that have not run at all and you will only know whether the proc has been run since stats were zero'd which does not identify the procs that are run once-a-year but are critical to some massive EOY reporting procedure! Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Gábor, This looks promising. I'll have to add prc_dbname to the JOIN clause, and see if there is a way to get the list of paramter types so that we can address things like test_proc(int, int, varchar) vs test_proc(int, char). If you (or anyone knows the answer to that, please share so that I don't have to reinvent the wheel. Thanks. ------------------------------ mark collins ------------------------------

Art, That's a good point, and one I meant to ask when replying to Gábor's post - how long is the info kept in sysprc. I was afraid that it might be flushed periodically as the cache was reused for other procedures. Polling it prior to shutting down or zeroing stats is something we can schedule. If it was being flushed as things moved in and out of cache, it was going to be a bit more cumbersome. And our developer's meeting also included discussion about quarterly, yearly, and ad hoc things that might not show up during the sample period. Still need to pin down the varying argument lists, though. ------------------------------ mark collins ------------------------------

Thinking about this a little bit more, it would be pretty simple to add this as a scheduled monitor task (a sensor?). Just let the sensor run once or twice a day, updating the last run time in a table stored in a "real" database. ------------------------------ mark collins ------------------------------

This is for Art and everyone else - are you seeing prc_ownername being populated? I am not, so I'm curious whether anyone else is. I know it's a NULLable column, but it would be nice to have a value there. ------------------------------ mark collins ------------------------------

Found it - sysprocedures.paramtypes. Since we're joining on procid, it should be simple to get that info. Thanks to all. ------------------------------ mark collins ------------------------------

Maybe this can help for you. paramtypes containing the basic information about the types. The spb.data column containing the first 256 byte from the procedures text select first 100 prc_dbname, procname, paramtypes, spb.data, * from sysmaster:sysprc join sysprocedures sp on sp.procid = prc_id join sysprocbody spb on spb.procid = sp.procid and datakey='T' and seqno = 1 order by prc_last_accessOnly problem with prc_last_access if you run an update statistics for procedure like us at every day 5 am. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

1) sysprc.prcownername containing 32 space. we can call it us a bug select * from sysprc where prc_ownername is not null; 2) Maybe you need to think differently about the problem. You're looking for a none used procedures. Try to reverse the solution. -- You can create a table for all the procnames like ourprocedures -- store every procename from your database. -- check the most recently used procedures and flaged the proc us used in the ourprocedures. -- You can narrow your search for the none used procedures. -- After that, you have to look at the remaining ones one by one to see if someone can use them. ------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------

Good alternative. I'll bring that up in our next developers' meeting. ------------------------------ mark collins ------------------------------

I agree, the paramtypes is the best column for our usage. Because of the variations in formatting the CREATE PROCEDURE statement and that fact that some of our procedures may have 20 input parameters, sysprocbody seqno 1 doesn't always provide the full parameter list. That warning about update statistics is a good thing to know. ------------------------------ mark collins ------------------------------

Mark: It looks like prc_ownername is never populated and defaults to spaces (not NULLs). Just ran a test with one of my own procedures and it was not populated. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

It is my own solution for your problem. (tested in 14.10 FC9W1) -- 1) create a table for checking procedures drop table if exists ownprocedures; create table if not exists ownprocedures(ownp_procid integer, ownp_owner varchar(32), ownp_procname nvarchar(128), ownp_paramtypes lvarchar(4096), ownp_lastused char(20) ) lock mode row ; -- 2) run if you want to pick the new procedures that are new in the database insert into ownprocedures (ownp_procid, ownp_owner, ownp_procname, ownp_paramtypes)select procid, owner, procname, paramtypes::lvarchar(4096) from sysprocedures where owner not in ('informix', 'sqlj', 'sysibm', 'sysproc') -- left out the system procedures and not exists(select 1 from ownprocedures where ownp_procname= procname and ownp_paramtypes = paramtypes::lvarchar(4096)); -- 3) run it as frecvently as you want -- it was used in the cache in the last hour, there's a good chance it was used drop table tmpprocusage;select sp.procid as sp_procid, owner, procname, paramtypes::lvarchar(5000) as spparamtype, prc_last_access from sysmaster:sysprc join sysprocedures sp on sp.procid = prc_id join sysprocbody spb on spb.procid = sp.procid and datakey='T' and seqno = 1 where prc_dbname = !!your database name!! and sp.owner not in ('informix', 'sqlj', 'sysibm', 'sysproc') and to_date(prc_last_access, "%Y-%m-%d %H:%M:%S") > current - 1 units hour into temp tmpprocusage; -- updating the the ownprocedures table if the procedure didn't have a lastused value or it was older then a month update ownprocedures set ownp_lastused = (select prc_last_access from tmpprocusage where sp_procid = ownp_procid and ownp_procname = procname and ownp_paramtypes = spparamtype) where ownp_lastused is null or to_date(ownp_lastused, "%Y-%m-%d %H:%M:%S") < current -1 units month; -- 4.1 check it what was used in the past month select count(*) from ownprocedures where ownp_lastused is not null;-- 4.2 check it what wasn't used in the past month select count(*) from ownprocedures where ownp_lastused is null;------------------------------ Gábor Fekete Software Engineer Capital Systems Budapest ------------------------------