dbschema -f all versus onstat -g prc?

Hi everyone. If I want to get a definitive list of all of the UDR's (stored procedures) in an IDS 9.4 FXC21 database I would think dbschema -f all -d DB_NAME would show them. However when I execute: dbschema -f all -d DB_NAME it comes back with "No procedure" However when I execute onstat -g prc it shows some UDR's in cache. Question: why the apparent discrepancy? I am trying to determine if this database contains UDR's I may need to be concerned about for an upgrade to IDS 10 FC9. If anyone knows I would appreciate it. Thanks.

I supposed select * from sysprocedures; lists all of the stored procedures. So if there are any NON-informix installed SPL's (that are created by Informix when a database is created) then these are the application/user created SPL's. Can someone confirm if I am right about that? Thanks

dbschema will not list 'system' procedures of which there are many in IDS 9.xx and later. However system procedures will show up in the cache. To see all of the stored routines in the database and their types you can use this query: SELECT procname, mode FROM sysprocedures; Procedures with a mode that is lower case are protected mode or system procedures. Art On Thu, Feb 12, 2009 at 2:38 PM, WILL LANDSTROM <willlandstrom@yahoo.com>wrote: > Hi everyone. If I want to get a definitive list of all of the UDR's (stored > procedures) in an IDS 9.4 FXC21 database I would think dbschema -f all -d > DB_NAME would show them. However when I execute: > dbschema -f all -d DB_NAME> > it comes back with "No procedure" > > However when I execute onstat -g prc it shows some UDR's in cache. > > Question: why the apparent discrepancy? I am trying to determine if this > database contains UDR's I may need to be concerned about for an upgrade to > IDS > 10 FC9. > > If anyone knows I would appreciate it. > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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. --0016e640cee81f02390462becb1f

Could your prc be showing UDR's from a different database in the same instance?. The name listed in cache should have the dbname@instancename:UDR name. Thanks -Manoj "WILL LANDSTROM" <willlandstrom@yahoo.com> Sent by: ids-bounces@iiug.org 02/12/2009 01:38 PM Please respond to ids@iiug.org To ids@iiug.org cc Subject dbschema -f all versus onstat -g prc? [14878] Hi everyone. If I want to get a definitive list of all of the UDR's (stored procedures) in an IDS 9.4 FXC21 database I would think dbschema -f all -d DB_NAME would show them. However when I execute: dbschema -f all -d DB_NAME it comes back with "No procedure" However when I execute onstat -g prc it shows some UDR's in cache. Question: why the apparent discrepancy? I am trying to determine if this database contains UDR's I may need to be concerned about for an upgrade to IDS 10 FC9. If anyone knows I would appreciate it. Thanks. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Art, as usual you have given a very helpful answer. That query did the trick. Thanks!