Currently, there does not appear to be anything in the sysprocedures table to identify which procedures are provided by Informix (system routines) vs those created by users (user routines). The mode column does not appear to address this particular need.
Additionally, if one runs 'dbschema -d database_name -f all', the utility displays all routines (system routines and user routines), when all that is desired is the user-created routines. For instance, if I want to replicate a database and all of the routines that it contains, I only want those routines that we created in-house. After all, if I take that dbschema output as-is and try to run it in a new database, the server will return errors when it tries to execute the CREATE FUNCTION statements for the system-generated routines that will already exist in the new database.
Proposed solution - add columns to the sysprocedures table, or create a new table using procid as the key. The new columns would include a flag to identify which procedures are user-generated vs system-generated. Additionally, it would be nice to have columns identifying the datetime that the procedure was created, as well as the last_updated_datetime (i.e., for routines updated via the CREATE OR REPLACE) and the last time that statistics for the routine were updated, whether via CREATE PROCEDURE or UPDATE STATISTICS FOR PROCEDURE.
Related to these changes, the dbschema utility needs to be changed to either only output user-created routines, similar to what dbexport does. This could either be made the default behavior, or a new command-line option added to direct dbschema to only include user-created routines.
Examples of the system-generated routines that I want excluded include (but are not limited to) all of the variants of the 'assign' routine, 'bson_*' routines, 'compare' routine, 'equal' routine, the 'notequal' routine, the 'binary18*' routines, etc. These routines have values of 'd', 'r', and 'O' for the mode column. Given that most of the user-generated routines have a value of 'O' (a few having 'R'), mode cannot be used to differentiate between the two types. The procflags column is documented as "internal use only", and it only has a value of 0, so it also does not appear to address this need. The same goes for the column internal, which only has values of 'f' in our database.
The genesis for this request is that we are attempting to save our schema in a source control system. Rather than simply saving a full output from dbschema, we are creating separate files for each table, view, procedure, function, etc., and only for those objects that we created in-house.
This is easy to do for tables, by doing:
SELECT tabname
FROM systables
WHERE tabid > 99
AND tabtype = "T"
AND tabname not like "sys%" -- because there are some sys* tables w/tabid > 99
and then iterating through that output, running 'dbschema -d database_name -t tabname'.
But because there is no way to differentiate between system-generated and user-generated routines, we end up with a lot of unwanted files for routines that we didn't create and frankly do not want to track. After all, if Informix adds new system-generated routines, or alters signatures of existing routines,
If we attempt to compare versions in the source control system, these system-generated routines could be flagged by the 'diff' utility, and we don't want that. We only want to see the differences of our code base, not yours.
[edit to add]
After further digging, it appears that at least some of these routines that I am seeing are related to the datablade that handles binary data types. This datablade is automatically installed the first time that a user attempts to create a column using the binary18 or binaryvar datatype.
Regardless of how they are created, I feel that they should not be included in dbexport/dbschema output. If the schema is being re-created on another database from dbschema output, the same process that auto-installed the routines on the original database should auto-install them on the new database. Only user-generated routines (and other database objects) should be in the dbschema/dbexport output.
