We have a need for executing Native SQL Procedures under DSM. According to the manual, DSM is supposed to support SQL PL but not debugging. The support for SQL PL is very limited. It does not support any result sets or input variables, which are a must if the executions are going to be of any use. Here is a simple example of a NSP that is doing a select.
CREATE PROCEDURE SP001.DEMOTEST ( )
PACKAGE OWNER D7TOWNR
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT WORKDEPT, GRADE_LEVEL, PAY_RANGE FROM
D7001.SDEPT WHERE WORKDEPT IN ('018','100');
This fails because DSM will not support result sets, even a simple one like this. Input variables such as this are also not supported.
CREATE PROCEDURE D7001.D7NSDEMO
INOUT LASTNAME_IN VARCHAR(15) FOR SBCS DATA CCSID EBCDIC,
OUT TOTEMP INTEGER,
The only type of Stored procedures that work are those that have no input fields and execute inserts or deletes, or some function not requiring output.
Why is it useful?
|Who would benefit from this IDEA?||Anyone developing NSPs, cannot currently use DSM to create or deploy them, but the NSP could be developed offlline in a batch process or some other interactive tool. Once created, the procedure could be executed under DSM to validate the output.|
How should it work?
Set up the RUNSQL option to prompt for input variables in the same manner as Data Studio. Also allow for result sets that can be shown in a similar fashion as Data Studio. The RUNSQL does handle 'call D7001.DEMOMPO1();' so it can handle the execution of NSP's. It need to be more robust to allow execution of more complex procedures.
|Priority Justification||We are limited to using Data Studio which resides on individual desktops and cannot support a server version, therefore support for this function is very difficult to maintain across a large network of users|
|Customer Name||Michael Popalisky|
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "email@example.com" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions