Informix

  • 1.  dbschema output

    Posted 17 days ago
    Informix 12.10.FC14
    Solaris 10  1/13


    Folks,

    The following SPL exists in an Informix database:

    CREATE PROCEDURE informix.is_pattern_arg(str1 varchar(255), options char(4000)) RETURNING INTEGER

    RETURN 1;
    END PROCEDURE;

    -- Permissions for routine "is_pattern_arg"
    grant execute on function 'informix'.is_pattern_arg(varchar,char) to 'public';



    'dbschema -d <database name> -f is_pattern_arg' returns the following:

    informix@ifmx-test-jnu>dbschema -d acoms_dev -f is_pattern_arg

    DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC14


    No procedure is_pattern_arg.
    informix@ifmx-test-jnu>


    Is that correct?  The SPL actually does exist and returns a value, but dbschema reports it as "No procedure".

    Or could that be because the SPL is a function and not a procedure, thus rendering the results reported by dbschema as precisely correct, but in direct opposition to the traditional way Informix has treated user routines?

    Not any kind of showstopper, just something I came across today when a developer complained.

    Thank you.

    DG

    ------------------------------
    David Grove
    ------------------------------


  • 2.  RE: dbschema output

    Posted 17 days ago

    David:

    That is a built-in "protected" procedure. Dbschema won't list it and neither will myschema. 

    Witness:
    > select * from sysprocedures where procname = 'is_pattern_arg';



    procname        is_pattern_arg
    owner           informix
    procid          234
    mode            r
    retsize         108
    symsize         221
    datasize        47
    codesize        44
    numargs         2
    isproc          f
    specificname     
    externalname     
    paramstyle      I
    langid          2
    paramtypes      varchar,char  
    variant         t
    client          f
    handlesnulls    t
    iterator        f
    percallcost     0
    commutator       
    negator          
    selfunc          
    internal        f
    class            
    stack            
    parallelizable  f
    costfunc         
    selconst        0.00
    collation       en_US.819
    procflags       0

    1 row(s) retrieved.

    The proc's "mode" is "r". Modes of "R", & "r" indicate a restricted procedure (upper case is also a DBA procedure). Mode "R" functions behave like mode "O" procedures and execute with their owner's permissions rather than the user's permissions. This much is documented in the Guide to SQL Reference manual. It is documented that "o" indicates a protected mode "Owner" routine ("O" is Owner) while 'd' indicates a protected DBA routine ("D" is DBA), so by extension "r" might mean a protected restricted routine, but that's not explicit in the docs.  I know you can't drop a restricted procedure or function. Beyond that the documentation is thin.

    Anyone from HCL who can fill us in?

    Art



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



  • 3.  RE: dbschema output

    Posted 17 days ago
    Thank you for the explanation, Art.

    It isn't a showstopper, just something that didn't seem to make sense to me.  I mean, I could see it there.  I could execute it.  It returns a value.  But, dbshema claimed it didn't exist.

    But, now I have "the rest of the story".  (Pretty much.)

    "Close enough for government work!" as some would say.  :)

    Thank you.

    DG

    ------------------------------
    David Grove
    ------------------------------