IBM Data and AI Ideas Portal for Clients

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:

Post your ideas

If you have not registered on this portal please register at http://ibm.biz/IBM-Data-and-AI-Portal-Register . To complete registration you will need to open the email you will receive from Aha to confirm your identity.

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea

Help IBM prioritize your ideas and requests

The IBM team may need your help to refine the ideas so they may ask for more information or feedback. The offering manager team will then decide if they can begin working on your idea. If they can start during the next development cycle, they will put the idea on the priority list. Each team at IBM works on a different schedule, where some ideas can be implemented right away, others may be placed on a different schedule.

Receive notification on the decision

Some ideas can be implemented at IBM, while others may not fit within the development plans for the product. In either case, the team will let you know as soon as possible. In some cases, we may be able to find alternatives for ideas which cannot be implemented in a reasonable time.

Additional Information

To view our roadmaps: http://ibm.biz/Data-and-AI-Roadmaps

Reminder: This is not the place to submit defects or support needs, please use normal support channel for these cases

IBM Employees:

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

allow CREATE FUNCTION with RETURNS TABLE to use SQLPL

Today (in V12 <and maybe earlier>) it is possible CREATE FUNCTION using SQLPL and RETURN a value (of any data type).

This is pretty cool.. and powerful.

This is useful to perform complex SQLPL operations inside the UDF (LANGUAGE SQL).

For example, the UDF can CALL procedures, process result sets, loop, etc . All very handy. But this type of SQLPL UDF can only RETURN one value.

I believe the knowledge center refers to this type of UDF as compiled SQL scalar.

The logical extension or next step of this current and existing functionality is to allow CREATE FUNCTION using SQLPL and RETURNS TABLE.

I believe the knowledge center refers to this type of function today as SQL table.

But the syntax diagram is clear that only SQL statement can be in the SQL-routine-body … and I suppose it must be a SELECT.

No real SQLPL is allowed in this type of UDF that RETURNS a TABLE.

I think allowing CREATE FUNCTION using SQLPL and RETURNS TABLE could be useful for sophisticated application development.

Today I can create the complex SQLPL scalar function to return a single value. And it can do something procedurally complex. But if my procedure wants to return multiple pieces of information then I am stuck with the option of creating multiple UDF to return each piece of info… or I make one big UDF that gathers all the required data and then returns it as one big varchar (or XML) value with all the values concatenated together.

This current workaround is not horrible.. but it is not elegant.

Now that IBM/DB2 can CREATE FUNCTION using SQLPL that returns a single value… it is a logical extension to allow it to RETURNS TABLE. It does not sound like too much work…

To top it off… I am certain (not 100%) that this functionality exists in other RDBS such as DB2LUW

  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Oct 1 2020
  • Under Review
Who would benefit from this IDEA? application developers. people who write sophisticated SQL with UDF
How should it work?

Here is a simple generic scenario that can work in DB2z V12.

I created a UDF to call IBM supplied procedure ADMIN_COMMAND_DB2 to issue DISPLAY DDF DETAIL. My UDF then parses the result from the call and finds the SQL Domain hostname and IPV4ADDR. My UDF then concatenates the values together and returns it to the SELECT that invoked the UDF.

This is now a UDF to confirm via SELECT and UDF the basic DDF info about my local Db2.

I can run this SELECT and use this UDF in DSNTEP2 and SPUFI which makes now makes it easy for me to use in TSO and confirm trivia like my local hostname and IP address. Fun!

My example of using this UDF looks like this

SELECT CURRENT TIMESTAMP AS CUR_TS

,DBCDBP1.UDF_DDF_HOSTNAME() AS DDF_HOST

FROM SYSIBM.SYSDUMMY1;

+-----------------------------

| CUR_TS |

+-----------------------------

1_| 2020-09-28-18.46.17.736166 |

+-----------------------------

-----------------------------------------------------------------------------------------------------------------------------

| DDF_HOST |

-----------------------------------------------------------------------------------------------------------------------------

1_| mainframej.manulife.com(10.101.31.21) LOCATION=HCDDB2GW DDF-STATUS=STARTD |

-----------------------------------------------------------------------------------------------------------------------------

0SUCCESSFUL RETRIEVAL OF 1 ROW(S)

I tried to extend my UDF to RETURN a table. In this case, the returned table will only have one row because the RETURN is a SELECT from SYSDUMMY1… but I was trying to separate my parsed variables as distinct columns in the SYSDUMMY1 table… but it wont work. This attempt to CREATE FUNCTION fails with SQLCODE -628… which led me to carefully reread the documentation and realize that it can be done.

Basically… it would have been nice to use this return-table-type UDF in a simple many like

SELECT * FROM TABLE(UDF_RETURNS_TABLE);

You can see my CREATE FUNCITON attached to this idea. The UDF basically works as provided (you need to change the SQLID ). It currently retunrs one value!

When you uncomment/change the two RETURN lines.. it will change the type to RETURNS TABLE but the create will fail with -628

Idea Priority Medium
Priority Justification low
Client Name Brian Laube
  • Attach files

IBM's success depends on gathering feedback from customers like yourself. Aha Ideas Portal is the third party tool through which IBM Offering Managers gather feedback from customers such as yourself.
IBM is a global organization with business processes, management structures, technical systems and service provider networks that cross borders. As such, the information collected through Aha Ideas Portal (Customer Name, Customer Email Address) will be stored by them in the United States, and handled only as per IBM's instructions and policies. Your data (Name and Email Address) will NOT be shared with other IBM customers.
In order to safeguard your information in Aha, do not leave your workstation unattended while using this application, log off after using it, and print only if necessary. If you need to make a hardcopy, remember to pick up the print-out immediately, keep it under lock, and destroy it immediately when no longer needed.
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