Skip to Main Content
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.

Status Needs more information
Workspace IBM i
Categories Db2 for i
Created by Guest
Created on Oct 17, 2023

Allow a SQL stored procedure to use 3 part naming without having to create a custom *SQLPKG on each target.

I created a sql stored procedure which looks like the following:

CREATE PROCEDURE rob.PROPAGATE_QLNKOMT()
         LANGUAGE SQL MODIFIES SQL DATA
        SET OPTION DATFMT = *ISO

P1 : BEGIN
DECLARE OMIT_LIST CHARACTER VARYING ( 32000 ) ;

SELECT FSXCMD INTO OMIT_LIST
FROM QUSRBRM . QA1AFS
WHERE FSNAME = 'QLNKOMT'
;
UPDATE GDI . QUSRBRM . QA1AFS
SET FSXCMD = OMIT_LIST
WHERE FSNAME = 'QLNKOMT' ;
UPDATE DOMTEST . QUSRBRM . QA1AFS
SET FSXCMD = OMIT_LIST
WHERE FSNAME = 'QLNKOMT' ;

...

END P1 
;

The first time I run it, each system used in the 3 part naming ends up with a *SQLPKG in the library rob.

Now, I modify the stored procedure, drop it and recreate it.  When I call it the run fails with 

Cause . . . . . : Package PROPA00001 in ROB on application server GDIHQ cannot be run with the current application program because either the application program has been recompiled without rebuilding the package or the package has been restored from a back level version. Recovery . . . : Rebuild the package by using the CRTSQLPKG command or by using a CRTSQLxxx command specifying the correct relational database. Otherwise, restore the package from a version which matches the application program being run.

If I just run a 3 part statement outside of a stored procedure it doesn't create a sql package on the remote system does it?  Why should it need to just because I ran it from within a stored procedure?

It makes it tough to have to go to each target and blast the sql package.

Idea priority Low
  • Guest
    Reply
    |
    Nov 15, 2023
    SQL packages used as described should get rebuilt without manual intervention. The SQL0818 message is used to communicate the need for a rebuild, not an error, in the scenario described. Can you check whether your packages actually got rebuilt? We believe this is working as you are requesting.

    Db2 for i development team
    IBM Power Systems Development