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
Original Message:
Sent: Tue November 16, 2021 01:30 PM
From: David Grove
Subject: dbschema output
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