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

#Informix

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

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

Original Message:
Sent: Tue November 16, 2021 02:29 PM
From: Art Kagel
Subject: dbschema output

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