Go Premium for a chance to win a PS4. Enter to Win


DB2 - How to create a function that takes dynamic input

Posted on 2017-08-10
Medium Priority
Last Modified: 2017-08-25
I am trying to write a function in DB2 and the function will parse a certain string based on delimiter and return as seperate fields .
The string I should pass will come from another query ,

example :
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
   RETURN values (tempstring,tempstring1);--

Now can I call this function and pass a set of strings like select somestring from table ? or use the same query select somestring from table in the function and iterate through it ?
Question by:FranklinRaj22
  • 2
LVL 46

Expert Comment

by:Kent Olsen
ID: 42250164
Hi Franklin,

If you want to return a known number items, it's pretty easy.  

  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
      case when locate(';', string) = 0 then string else left (string, locate(';',string)-1) end Leftside,
      case when locate(';', string) = 0 then ' ' else substr (string, locate (';', string)+1) end Rightside
    FROM sysibm.sysdummy1;

Open in new window

The string "AB;CDEF" is returned as "AB" and "CDEF".
The string "ABCDEF" is returned as "ABCDEF" and "".

It's a little redundant, but the left and right half of the string are derived separately and both processes need to ensure that the separator character exists in the string.  Otherwise, locate() will return 0 which is an illegal value when passed to LEFT or SUBSTR.


Author Comment

ID: 42250183

Thanks for the response . I believe I did not pose the right question , the input parameter should be a list or use a query and store it in an array and iterate over .

example : calling the function
select * from table SPLIT_STRING(select stringname from table where condition);

or I can use the same query inside the function and do a
SET ARRAY VARCHAR = select stringname from table where condition ;

Ideally my input should be a collection not scalar .
LVL 46

Expert Comment

by:Kent Olsen
ID: 42250219
I don't believe that a DB2 function is flexible to do what you want.  A function is normally called on row data and processes the items in the row.  You/we should be able to process the array, and return any other array with a Stored Procedure.
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 42253943

The most easiest way for this would be to use the ARRAY and ARRAY_AGG functions eg.
SET MY_ARRAY = ARRAY[select stringname from table where condition]; 

Open in new window

in a stored procedure as shown in the above example (the link ;) ).

Another solution if you are passing a delimited string is to create a Java stored procedure and use the simple String.split function to solve this.

Another solution would be to do something recursive like this

     Tomas Helgi

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Join & Write a Comment

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question