Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

Question on sysprocedures

  • 1.  Question on sysprocedures

    Posted 07/12/22 06:25 PM
    Informix 14.10.FC7W1 on RHEL 8.6

    I've got a couple of questions about the sysprocedures table.  First, the isproc column.  It looks like isproc is set to 'f' any time the routine has return values, regardless of whether it was created via CREATE PROCEDURE or CREATE FUNCTION.  I guess that brings up a question of whether there is any difference between a CREATE PROCEDURE that has return values vs a CREATE FUNCTION, which must have return values.  Can you use both interchageably?  Do both work the same way in projection clauses, WHERE clauses, and SPL LET statements (among other places)?

    So it seems that isproc will only be 't' if the routine was created via CREATE PROCEDURE and has no return values.  Is that correct?

    Next, the mode column.  Is it correct that mode would be 'D' only for those routines created via CREATE DBA PROCEDURE or CREATE DBA FUNCTION?  It sounds like mode would be 'R' for routines created via "CREATE PROCEDURE username.my_procedure" if you ran this as user informix or some other user with DBA permission.  Is that correct?  

    Finally, how do you identify routines that were generated by your own staff?  I was thinking it would be a simple case of mode = "O", but that isn't it.  I've got a number of procedures that I know were created by our developers, which have mode = "O" (or possibly "R"), but there are many others, such as the binary18* routines and some of the equal and notequal routines, that also have mode = "O".  Is there some other column that differentiates those routines provided by IBM from those created in-house?  Or another table that I need to join against to get that information?

    Thanks.


    ------------------------------
    Mark Collins
    ------------------------------

    #Informix


  • 2.  RE: Question on sysprocedures

    Posted 07/12/22 07:51 PM
    Mark:

    See my notes below inline with your post:

    I've got a couple of questions about the sysprocedures table.  First, the isproc column.  It looks like isproc is set to 'f' any time the routine has return values, regardless of whether it was created via CREATE PROCEDURE or CREATE FUNCTION.  I guess that brings up a question of whether there is any difference between a CREATE PROCEDURE that has return values vs a CREATE FUNCTION, which must have return values.  Can you use both interchageably?  Do both work the same way in projection clauses, WHERE clauses, and SPL LET statements (among other places)?

    Originally Informix only had CREATE PROCEDURE so procedures were allowed to return data and the isproc flag indicates whether the procedure returns a value (so is really a function and has isproc set to 'f') or not (isproc == 't'). This is just legacy and you can sort-of use FUNCTION and a PROCEDURE that returns data interchangeably, but you still have to use the corresponding key work (FUNCTION or PROCEDURE) when referring to it in nearly all situations. So for example you cannot execute the API launch functions task() or admin() with EXECUTE PROCEDURE, only with EXECUTE FUNCTION.

    So it seems that isproc will only be 't' if the routine was created via CREATE PROCEDURE and has no return values.  Is that correct?

    Correct. Mostly because a FUNCTION must return data. Only a PROCEDURE can not include a RETURN clause in its CREATE line.

    Next, the mode column.  Is it correct that mode would be 'D' only for those routines created via CREATE DBA PROCEDURE or CREATE DBA FUNCTION?  It sounds like mode would be 'R' for routines created via "CREATE PROCEDURE username.my_procedure" if you ran this as user informix or some other user with DBA permission.  Is that correct?  

    From the Guide to SQL Reference PDF manual v14.10, the mode values are:
    D or d = DBA
    O or o = Owner
    P or p = Protected
    R or r = Restricted
    T or t = Trigger

    Finally, how do you identify routines that were generated by your own staff?  I was thinking it would be a simple case of mode = "O", but that isn't it.  I've got a number of procedures that I know were created by our developers, which have mode = "O" (or possibly "R"), but there are many others, such as the binary18* routines and some of the equal and notequal routines, that also have mode = "O".  Is there some other column that differentiates those routines provided by IBM from those created in-house?  Or another table that I need to join against to get that information?

    You really cannot. Believe me I've tried. If you install a datablade or bladelet it's routines are flagged as mode "O". Myschema was coded explicitly to ignore certain known system routines that should not be included in a schema file. But, for example, if you have timeseries columns in your database, hundreds of timeseries datablade routines will be installed  neither dbschema nor myschema can separate these out even by name, let alone with some flag.

    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 3.  RE: Question on sysprocedures

    Posted 07/13/22 06:30 PM
    Hi Art,

    Thanks for the info.  I won't lie, it's disappointing that there is no way to positively identify user routines vs system routines.  That almost sounds worthy of an RFE, but I doubt that there are enough people who would be worried about it.

    I wonder how dbexport does it, since things like the binary18*, equal, notequal, and other such routines don't show up in the dbexport SQL file.  But I see what you're saying about dbschema, as 'dbschema -d database_name -f all' does give all of those system routines.  I had not tried that before.



    Mark

    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 4.  RE: Question on sysprocedures

    Posted 07/14/22 02:38 AM
    Would be nice to have a created attribute in sysprocedures too, see if you can sneak it into the same RFE ;-)

    ------------------------------
    Davorin Kremenjas
    ------------------------------

    Original Message


  • 5.  RE: Question on sysprocedures

    Posted 07/14/22 08:14 AM
    created and also last updated/compiled!

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

    Original Message


  • 6.  RE: Question on sysprocedures

    Posted 07/14/22 09:48 AM
    Since there is no "CREATE OR REPLACE PROCEDURE", we have to drop the procedure and then create it, right?  Or did I miss a new replace/update option? 

    So if we have to drop the procedure before we create it, would there ever be a difference b/t created_dttm and last_updated_dttm?  Or do you mean the last time that UPDATE STATISTICS FOR PROCEDURE was run?

    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 7.  RE: Question on sysprocedures

    Posted 07/14/22 10:25 AM
    Edited by System Admin 01/20/23 04:43 PM
    I see now that they did sneak in a new OR REPLACE clause on the CREATE FUNCTION / CREATE PROCEDURE statements.  So last_updated_dttm or compiled_dttm can differ from created_dttm.

    But do we also want a statistics_last_updated column?  I think so, but maybe others have a different opinion?


    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 8.  RE: Question on sysprocedures

    Posted 07/14/22 11:14 AM
    Mark:

    Actually we do have CREATE OR REPLACE, here:

    > create or replace PROCEDURE  "informix".oni_trace (LVARCHAR, LVARCHAR)
          external name "$INFORMIXDIR/extend/ASKdbm/askdbm.bld(oni_trace)" LANGUAGE C;               
    Routine created.

    It came in with V14.10.

    But, yes I was referring to recompiling a procedure with UPDATE STATISTICS FOR PROCEDURE.

    Art

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

    Original Message


  • 9.  RE: Question on sysprocedures

    Posted 07/14/22 01:07 PM
    Edited by System Admin 01/20/23 04:24 PM
    Art, Davorin,

    I've created the RFE.  It's ready for your votes.  

    I've only ever created one RFE prior to this, so I didn't realize that the information that I put in the "additional comments" field don't display.

    [edit]
    I was able to edit the RFE to copy all of the "additional information" up to the "problem description" so that it all shows in the RFE page now.



    Mark


    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 10.  RE: Question on sysprocedures

    Posted 07/15/22 02:14 AM
    Thanks for the effort Mark, just voted on it.

    ------------------------------
    Davorin Kremenjas
    ------------------------------

    Original Message


  • 11.  RE: Question on sysprocedures

    Posted 07/14/22 11:36 AM
    dbschema/dbexport would only produce mode 'O', 'D' and 'R' ones, so not those 'd' and 'r' internal ones.

    ------------------------------
    Andreas Legner
    ------------------------------



  • 12.  RE: Question on sysprocedures

    Posted 07/14/22 01:03 PM
    Andreas,

    I believe you are correct about it excluding the 'o', 'd', and 'r' routines, but that's only part of the problem I'm seeing.  For example, in sysprocedures, a number of these "system-generated" routines have mode = "O".  Examples include binary18cast, binary18expb, binary18in, binary18out, binary18recv, binaryvarcast, binaryvarexpb, binaryvarexpt, binaryvarimpb, compare, equal, greaterthanorequal, and notequal.  

    Running 'dbschema -d database_name -f all | grep -i "^create " | grep equal', I get:

    create function "informix".equal (SYSBldSQLText,SYSBldSQLText)
    create function "informix".equal (binaryvar,binaryvar)
    create function "informix".notequal (binaryvar,binaryvar)
    create function "informix".lessthanorequal (binaryvar,binaryvar)
    create function "informix".greaterthanorequal (binaryvar,binaryvar)
    create function "informix".equal (binary18,binary18)
    create function "informix".notequal (binary18,binary18)
    create function "informix".lessthanorequal (binary18,binary18)
    create function "informix".greaterthanorequal (binary18,binary18)

    We didn't create any of those in-house.  And none of them show up in the database_name.sql file created by dbexport.  So dbschema appears to be extracting the three 'equal' routines that have mode = "O", but not the two that have mode = "r".

    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 13.  RE: Question on sysprocedures

    Posted 07/14/22 01:10 PM

    Sounds like these are procedures from a datablade. These are not standard, at least not in any of my 14.10 
    databases. 
    MARCUS HAARMANN



    Original Message


  • 14.  RE: Question on sysprocedures

    Posted 07/14/22 01:23 PM
    Marcus,

    You may be correct about it being related to a datablade.  I do not recall installing a datablade, but I can't rule it out either.  But regardless, I would want the datablade-related routines to be identifiable in some way that they do not show up in dbschema output and can be excluded via the WHERE clause when querying sysprocedures.

    I am not an expert on datablades, but it seems like I might encounter problems if I did a dbschema of a database, and the output of that included datablade-related routines, and then I attempted to run that output in another database perhaps on another server.  I would expect that some of these datablade-related routines might call external C programs that might not exist on the new server.  This would cause the application to fail if it tried invoking these routines, wouldn't it?

    Of course, if the application is using the datablade, then it would fail until the datablade is installed/registered on the new server.  But that installation/registration process should be what controls the creation of these routines.  They should not be in the dbschema output, in my opinion.




    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 15.  RE: Question on sysprocedures

    Posted 07/14/22 01:40 PM

    With blades you can also get caught if the path has changed, very common with TS when the blade can be tightly coupled to the engine version.

     




    Original Message


  • 16.  RE: Question on sysprocedures

    Posted 07/14/22 04:31 PM
    Hence my thought that dbexport/dbschema should not include those routines in their output. 

    If the instance creates them, I don't want to have them show up mixed in with my routines.

    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


  • 17.  RE: Question on sysprocedures

    Posted 07/14/22 01:57 PM
    Marcus:

    Yes, those are from an auto-installed Bladelet that is included with Informix, the binaryudt
     package. The point is that dbexport knows enough to not export these tables and procedures from standard datablades, but other blades not so much, and dbschema (and myschema for that matter) do not exclude any of them. In the case of myschema, I agree with Mark the bladelets and datablades change from release to release so I'd be chasing a moving target.

    Art


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

    Original Message


  • 18.  RE: Question on sysprocedures

    Posted 07/14/22 04:38 PM
    Edited by System Admin 01/20/23 04:34 PM
    That explains it.  We were working with binary18 in one database (the one I've been using for this discussion).  And our other databases do not have those routines yet, as this is still in development, using just this one database.

    But it was auto-installed, not anything that I manually did.  So, is there a way to uninstall it?  That's more out of curiosity than anything else, as I do expect this to roll out to additional development databases and eventually into production.


    ------------------------------
    Mark Collins
    ------------------------------

    Original Message


Related Content