• 1.  dbschema output

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


    The following SPL exists in an Informix database:

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

    RETURN 1;

    -- 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.

    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.


    David Grove

  • 2.  RE: dbschema output

    Posted 17 days ago


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

    > 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
    paramstyle      I
    langid          2
    paramtypes      varchar,char  
    variant         t
    client          f
    handlesnulls    t
    iterator        f
    percallcost     0
    internal        f
    parallelizable  f
    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 S. Kagel, President and Principal Consultant
    ASK Database Management Corp.

  • 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.


    David Grove