IBM Power Ideas Portal


This portal is to open public enhancement requests against IBM Power Systems products, including IBM i. 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 updates on them if they matter to you. If you can't find what you are looking for,

Post your ideas

  1. Post an idea.

  2. Get feedback from the IBM team and other customers to refine your idea.

  3. Follow the idea through the IBM Ideas process.


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

Add a new idea Subscribe
Status Delivered
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Dec 11, 2017

RELATED IDEAS

Moving and renaming programs should not update associated stored procedures

A while ago we had a stored procedure fail. Investigating we found the following had happened:
• Created stored procedure X using program Y
• Moved program Y to a holding library Z
• Created new version of program Y
• Recreated stored procedure X
• … all was well for several days
• A purge program renamed program Y in holding library Z to Y123 – and the system updated the stored procedure to use Z/Y123! - which of course failed.

After discussions with IBM support we found this was "Working as designed" and was introduced in a technology update in 2014 under “Improved catalog management for procedures and functions”. An environment variable can be set to partially disable this change.

We feel renaming and moving programs should not update the stored associated with them. Two reasons:

First is that this is how everything else in the system works. Program to program calls. Programs called from commands. Programs in the job scheduler. Programs named on job descriptions... None of these are modified when the program referenced is moved or renamed. We see no reason for stored procedures to work differently.

Second is that the new system is dangerous - as we found. It seems absurd to us that renaming a program - that is not even being used by a stored procedure - can break the production environment. Many program management programs that move old programs out and new ones in will cause problems with the new system.

We see no good reasons for renaming and moving programs to update stored procedures. Doing so is inconsistent and dangerous. We do not think this should be the system's default behaviour.


Use Case:

The following fails

Create stored procedure X using program Y
• Move program Y to a holding library Z
• Create new version of program Y
• Recreate stored procedure X
• … all is well
• Rename program Y in holding library Z. This should be safe as the program is not in use - but it updates and probably breaks stored procedure X.


Idea priority Medium
  • Guest
    Reply
    |     Dec 8, 2020

    IBM believes that the request described has been solved and is available by using the QIBM_SQL_NO_CATALOG_UPDATE environment variable. Setting this at the system level will avoid the updates on move and rename operations.

    There is more information available about how system interfaces interact with SQL's external procedures and functions in this article:
    https://www.mcpressonline.com/programming/sql/techtip-get-to-the-time-machine-marty

  • Guest
    Reply
    |     Dec 13, 2017

    Nice writeup. A very commonly used release management tool uses the pattern that caused you problems.

    The environment variable you hinted at, QIBM_SQL_NO_CATALOG_UPDATE, has solved the issue for us, but I'm still voting for your issue for the reasons you gave.

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.