Hi Family.

I was asked by a user to locate a trigger that accesses a certain table.  On the way to finding out, I also needed a query that looks for stored procedures that access a certain table.  Here's the query I came up with.  (I'm likely re-inventing a wheel already developed by Lester Knutsen.)  Note the commented AND clause:

select sp.procname, sp.mode,
       sb.datakey, sb.seqno, sb.data
   from sysprocedures sp, sysprocbody sb
 where sp.procid = sb.procid
-- and sp.procname not like " %"    -- Skip built-ins with space in procname
   and sb.datakey in ("A", "T")     -- Really only need "T"
 order by procname, datakey, seqno;

The commented line skips procedures where the first character of the procname is a space.  This renders it pretty unusable to users.  Similar to the automatic index on a PK constraint (if you didn't create it atop an existing index), so there is no syntax you can use to drop it.  I had to add that clause because otherwise it was turning up procs with names like " systdistold" (note the initial space).  I have no idea how these are used internally but their existence certainly raised an eyebrow (Spock style. ;-). If anyone is willing to share that knowledge I'm sure the community of Informix geeks would appreciate it.

The next interesting thing is the clause and sb.datakey in ("A", "T").  This is actually from an error I made looking for the text part of sysprocbody and looking for datakey "A" as I did in systrigbody.  There were a bunch of procs, even in sysmaster, like these 2:

procname  comb_aggrelem
mode      d
datakey   A
seqno     1
data      alter function informix.comb_aggrelem (informix.pointer,informix.poin
          ter)
                  with (add handlesnulls)

procname  comb_aggrelemc
mode      d
datakey   A
seqno     1
data      alter function informix.comb_aggrelemc (informix.pointer,informix.poi
          nter)
                  with (add handlesnulls)

Interesting observation: When I tried to see the code using dbschema in a user database to decode the proc it said:
No procedure comb_aggrelem. These procs seem to be supporting aggregates created with the "CREATE AGGREGATE" statement. But how they are used? Well, since I've never used that command it remains mysterious.

Again, anyone able and willing to share these interesting tidbits and how, if at all, we as DBAs can make use of them?

BTW I told my user I got two new scripts out of his question and thanked HIM for bringing it up.

------------------------------
I am pleased to report that I had no problems today.
I had only issues, opportunities, challenges and
valuable learning experiences.
-- Jacob S

------------------------------

Jacob:

So, procnames that start with a space are deprecated older versions of a built-in proc that was replaced. They are typically kept around for a couple of versions just in case the behavior of the new one breaks someone's code and they have to rename it back into use. It's just a way to make a procedure unusable without dropping it.

The sysprocbody records with datakey = 'A' are, as you discovered, alter statements setting attributes of a function or procedure such as making it 'VARIANT' or 'NOT VARIANT'. You mostly find these modifying a datablade function. Some are redundant. For example most of the timeseries functions have an associated 'A' record that "modifies" the function's external "name", aka it's binary implementation in the datablade's shared library that are really not needed. Witness this timeseries function, ts_vtam_close():

In this case the "Modify External Name" alter is completely redundant as the CREATE FUNCTION statement already defined the function's correct external name. <sigh> Also, the "parallelizable" and "variant" attributes could have been included in the initial CREATE FUNCTION statement.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------