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 Not under consideration
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Feb 10, 2022

RELATED IDEAS

Eliminate the Propagation of READS SQL DATA in Caller Functions in SQL

When creating User Defined Table functions in SQL, the CONTAINS SQL, READS SQL DATA and MODIFIES SQL DATA statements are currently propagated up the food chain. This means if a simple function calls another function, that called function's attributes are required in the caller.
This should not be the case. In fact, on IBM i, I doubt that this is anything more than a artificial restriction. One that needs to be removed.


Use Case:

I create a user defined function in SQL. In that SQL function I may call QSYS2.QCMDEXC which means my SQL function has to include the MODIFIES SQL DATA clause.
If I call the SPLIT helper function to parse out some data, since I am running a SELECT statement, I have to include the READS SQL DATA in that function, but it also means every other SQL FUNCTION that calls my new function also has to have READS SQL DATA.
If there is no point to this clause, please remove it. If it is necessary figure out a way to make it work globally perhaps by embedded MODIFIES SQL DATA in everything by default assuming there's no real performance difference or authority issue. But this is just very limiting. IBM Support's solution is to always use MODIFIES SQL DATA in case you call QCMDEXC or another function that calls a function with MODIFIES SQL DATA in it. Therefore, make MODIFIES the default, please.


Idea priority Medium
  • Guest
    Reply
    |     Mar 9, 2022

    Thanks I understand and understood the issue with it. Changing the default is probably not a good idea. But what I'm really asking is that the use of things like the QCMDEXC proc do not use MODIFIES SQL DATA since the use of those routines cause that attribute to be bubbled up the function/procedure call stack. Which is why I'm seeing developers resorting to using MODIFIES SQL DATA "just in case" and that then propagates the issue.

    I will submit another for QCMDEXC without MODIFIES SQL DATA as its attribute.
    But since I've already written one that uses QCAPCMD anyway...
    thanks for the info on SQL_DATA_ACCESS

  • Guest
    Reply
    |     Mar 3, 2022

    IBM does not intend to provide a solution to this request at this time, so it is being closed.

    The behavior of READS SQL DATA is intentional and enforced. This is the default behavior for the Db2 family. READS SQL DATA indicates that modification of data by SQL will not happen by the identified function or by any nested functions or procedures invoked by this function. This ensures to the caller of the function that no unexpected data change will occur by invoking the function, even by nested routines that they do not necessarily realize are being used. Using this level of data access as the default provides a level of protection to applications. Changing the default behavior would break this long-established contract with our users.

    If you do not want your functions to be part of this validation, you should define them as MODIFIES SQL DATA. Query the QSYS2.SYSROUTINES catalog and examine the SQL_DATA_ACCESS column to identify routines that you might want to change from READS SQL DATA to MODIFIES SQL DATA.

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.