IBM Data & AI

Welcome to the idea forum for IBM Data & AI (Formerly Analytics) Clients! 

 

IBM Employees:

The correct URL for entering your ideas is https://hybridcloudunit-internal.ideas.aha.io

 

Clients:

Our team welcomes any feedback  and suggestions you have for improving our offerings / products!  This forum allows us to connect your offering / product improvement ideas with IBM product and engineering teams.

 

If you have not registered on this portal please click on the following link and register.  To complete registration you will need to open the email you will receive from Aha to confirm your identity.  https://ibm.biz/AnalyticsIdeasPortalRegister

 

Allow Result Sets and Host Variables for Native SQL Procedures

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

DYNAMICRULES DEFINEBIND

P1: BEGIN

DECLARE CURSOR1 CURSOR WITH RETURN FOR

SELECT WORKDEPT, GRADE_LEVEL, PAY_RANGE FROM

D7001.SDEPT WHERE WORKDEPT IN ('018','100');

OPEN CURSOR1;

END P1%

 

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.

  • Guest
  • Apr 23 2019
  • Needs review
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. 

Idea Priority Medium
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
Submitting Organization
Submitter Tags
  • Attach files

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 "anonymous@euprivacy.out" 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