Fwd: Identifying System UDR's

My dbschema replacement utility, myschema, uses the following filter to eliminate system procedures: AND mode NOT IN ('P', 'p', 'd', 'r', 'o', 't') All protected mode procedures (modes 'P' & 'p') are internal and cannot be modified or displayed by dbschema. Art On Fri, Jun 13, 2008 at 9:38 AM, Eric Rowell <erowell@gmail.com> wrote: > 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 >> > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

On Jun 13, 10:25 am, "Art Kagel" <art.ka...@gmail.com> wrote: > My dbschema replacement utility, myschema, uses the following filter to > eliminate system procedures: > > AND mode NOT IN ('P', 'p', 'd', 'r', 'o', 't') > > All protected mode procedures (modes 'P' & 'p') are internal and cannot be > modified or displayed by dbschema. > Thanks, Art. I find it odd that I have no 'P' or 'p' UDR's. Sincerely, Christopher Coleman