This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).
Shape the future of IBM!
We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:
Search existing ideas
Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updateson them if they matter to you. If you can't find what you are looking for,
Post your ideas
Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,
Post an idea
Upvote ideas that matter most to you
Get feedback from the IBM team to refine your idea
Specific links you will want to bookmark for future use
enhancements to sysprocedures table and dbschema utility
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:
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.
Do not place IBM confidential, company confidential, or personal information into any field.