Skip to Main Content
IBM Data and AI Ideas Portal for Customers

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 (

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 updates on 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,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal ( - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal ( - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM. - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Data & AI Roadmaps ( - Use this site to view roadmaps for Data & AI products.

IBM Employees should enter Ideas at

Status Future consideration
Workspace Informix
Components Informix Server
Created by Guest
Created on Jul 14, 2022

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:

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.

Needed By Not sure -- Just thought it was cool