Identifying System UDR's

Is it possible to identify UDR's which are created by the system? In IDS 9.40, when you create a database, you get some 228 user-defined routines. Most are owned by informix, some by sqlj, with mode d or r. A suggested method to identify the system-created procedures is to create a dummy database.... then find the max(procid) and then any procid <= max(procid) would be a system proc. This does not work with datablades or if you have upgraded your database from 7.31. The doc seems to imply that the sysprocedures.mode for system UDR's is 'P' or 'p'. However, we have proven that is not true. See version 11 doc (matches 9.4): http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqlr.doc/sqlr68.htm I know there must be a method, and I cannot believe that the various schema tools are hard-coding a list. Does anyone have any suggestions? Here is the query which ought to have worked, if the doc was correct: SELECT TRIM(sp.procname) AS procname, TRIM(sp.owner) AS owner, sp.mode, sp.retsize, sp.symsize, sp.datasize, sp.codesize, sp.numargs, sp.isproc, sp.specificname, sp.externalname, sp.paramstyle, sp.langid, sp.handlesnulls, sp.variant, sp.paramtypes::lvarchar as paramtypes, spb.seqno, spb.data FROM sysprocedures sp, sysprocbody spb WHERE sp.procid = spb.procid AND sp.mode IN ('P', 'p') ORDER BY sp.procname ; Sincerely, Christopher Coleman

To help you out I exported everything from my sysprocedures table and put it in Excel to compare the records. The following is what jumps out at me. Note: this is IDS 10.00.FC8 with no data blades. p,o,r,t,d = System created P,O,R,T,D= User Created Also the following would be suggested if you are looking for the create syntax only; "AND spb.datakey = "T"" Type of information in the *datakey* column (IDS 11 documentation): A = Routine alter SQL (will not change this value after update statistics) D = Routine user documentation text E = Time of creation information L = Literal value (that is, literal number or quoted string) P = Interpreter instruction code (p-code) R = Routine return value type list S = Routine symbol table T = Routine text creation SQL Here are some queries to start from; System Defined: SELECT TRIM(sp.procname) AS procname, TRIM(sp.owner) AS owner, sp.mode, sp.retsize, sp.symsize, sp.datasize, sp.codesize, sp.numargs, sp.isproc, sp.specificname, sp.externalname, sp.paramstyle, sp.langid, sp.handlesnulls, sp.variant, sp.paramtypes::lvarchar as paramtypes, spb.seqno, spb.data FROM sysprocedures sp, sysprocbody spb WHERE sp.procid = spb.procid AND sp.mode IN ("p","o","r","t","d") AND spb.datakey = "T" ORDER BY sp.procname User Defined: SELECT TRIM(sp.procname) AS procname, TRIM(sp.owner) AS owner, sp.mode, sp.retsize, sp.symsize, sp.datasize, sp.codesize, sp.numargs, sp.isproc, sp.specificname, sp.externalname, sp.paramstyle, sp.langid, sp.handlesnulls, sp.variant, sp.paramtypes::lvarchar as paramtypes, spb.seqno, spb.data FROM sysprocedures sp, sysprocbody spb WHERE sp.procid = spb.procid AND sp.mode IN ("P","O","R","T","D") AND spb.datakey = "T" ORDER BY sp.procname On Thu, Jun 12, 2008 at 6:22 PM, Christopher <christopherc@gmail.com> wrote: > Is it possible to identify UDR's which are created by the system? > > In IDS 9.40, when you create a database, you get some 228 user-defined > routines. Most are owned by informix, some by sqlj, with mode d or r. > > A suggested method to identify the system-created procedures is to > create a dummy database.... then find the max(procid) and then any > procid <= max(procid) would be a system proc. > > This does not work with datablades or if you have upgraded your > database from 7.31. > > The doc seems to imply that the sysprocedures.mode for system UDR's is > 'P' or 'p'. However, we have proven that is not true. > See version 11 doc (matches 9.4): > > http://publib.boulder.ibm.com/infocenter/idshelp/v111/topic/com.ibm.sqlr.doc/sqlr68.htm > > I know there must be a method, and I cannot believe that the various > schema tools are hard-coding a list. > > Does anyone have any suggestions? > > Here is the query which ought to have worked, if the doc was > correct: > SELECT TRIM(sp.procname) AS procname, TRIM(sp.owner) AS owner, > sp.mode, sp.retsize, sp.symsize, sp.datasize, sp.codesize, > sp.numargs, sp.isproc, sp.specificname, sp.externalname, > sp.paramstyle, sp.langid, sp.handlesnulls, sp.variant, > sp.paramtypes::lvarchar as paramtypes, spb.seqno, spb.data > FROM sysprocedures sp, sysprocbody spb > WHERE sp.procid = spb.procid > AND sp.mode IN ('P', 'p') > ORDER BY sp.procname > ; > > Sincerely, > Christopher Coleman > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Eric, I appreciate your reply. As a matter of fact, I am only looking for the create syntax. On Jun 13, 8:38 am, "Eric Rowell" <erow...@gmail.com> wrote: [SNIP] > p,o,r,t,d = System created > P,O,R,T,D= User Created [SNIP] I am going to try this out. Sincerely, Christopher Coleman