05
Sat, Oct
2 New Articles

Enhance Your SQL Queries with User-Defined Table Functions

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Simplify your SQL SELECT statements, provide an interface to pass parameters to create dynamic tables, automatically log access to tables, and create a single point of maintenance for the SQL code you use repeatedly.

 

In my previous article, "Reuse Your RPG Code with SQL User-Defined Functions," I discussed how you could reuse RPG business logic in SQL by taking an RPG program or service program that returns a single value and then using the SQL CREATE FUNCTION command to make the business logic available to any high-level language (HLL), such as PHP or Java, or any tool that uses SQL to access data on your system. There are times, however, when you might want a function that returns not just a single value but a set of values or even a table. In that case, you could consider using a User-Defined Table Function (UDTF).

 

Like its name implies, the UDTF returns a table (file) consisting of a row or set of rows that can then be referenced in the FROM clause of an SQL Select statement. While a UDF allows a programmer to reuse RPG business logic and make that logic available through SQL to other programming languages, a UDTF is more likely to be useful in simplifying your SQL statements, consolidating your code in a single place, or creating a table out of data that is not stored in a relational database.

 

As with a UDF, a UDTF can be written using either an SQL Procedural Language (SPL) or an HLL such as RPGLE, C, or Java. I'm going to start with some examples using SPL, since I find it simpler and more intuitive because SQL does the interface work for you. I'll save the RPGLE examples for another article. In these examples, I'm going to use the table shown below consisting of an employee number, department, and name.

 

EMPNUM

EMPDPT

EMPNAM

100201

18

Jim   Brown

100202

17

John   Redhead

100203

14

Bob   Johnson

100204

17

Roger   Carr

100205

17

Jim   Thompson

100206

14

Sam   Jones

 

In our first example, let's create a UDTF that takes as input the department number and returns the employee number, department, and name.

 

CREATE FUNCTION DSPEMPLY(dept INTEGER)

   RETURNS TABLE (Number INTEGER, Departmt INTEGER, Name VARCHAR(30))

 

   LANGUAGE SQL

 

   BEGIN

     RETURN

       SELECT EMPNUM, EMPDPT, EMPNAM

       FROM mylib/EMPLOYEE

       WHERE EMPDPT = dept;

   END

 

I like to think of a CREATE FUNCTION command as consisting of four parts. The first part, CREATE FUNCTION, names the function and follows with any input parameters. The second, RETURNS TABLE, specifies the columns that are being returned and their data types. The third part is the set of keywords and parameters describing the function's attributes. In this case, the only one specified is LANGUAGE SQL, which lets the compiler know the function will be written in SPL. This is not to be confused with the UDF parameter PARAMETER STYLE SQL, which refers to the calling interface of the external program. Finally, the fourth section is the BEGIN…END section that contains the SPL, and within the BEGIN…END section is a RETURN statement with the data elements that correspond to the columns specified in the RETURNS TABLE statement.

 

Creating the function can be done by using the SQL statement CREATE FUNCTION in STRSQL, creating a source member with the CREATE FUNCTION statement in it, and executing the RUNSQLSTM command. Alternatively, you could use the wizard in the System i Navigator, which can be very helpful with drop-downs on data types and SQL statements you can use in SPL if you are not familiar with them. Creating the function registers its name, parameters, and return values with the database.

 

Now that the function has been created, let's use it in a SELECT statement to display the employees from Department 17 and order them by Name as follows:

 

SELECT * from TABLE(dspemply(17)) as T order by Name

 

The TABLE keyword lets SQL know that a table function is being invoked, and the name of the function and any parameters are enclosed in parentheses. Since this is a dynamic table, it requires a name, and I've called it "T." The output is shown below.

 

 022013Statonfigure1                      

Figure 1: This is the output from the Select statement.

 

With the simple example above, you probably wouldn't go to the trouble of creating a UDTF, so let's enhance our function to create a new table from our original one in order to separate the employee's first name and last name into their own columns that we'll call FirstName and LastName.

 

CREATE FUNCTION DSPEMPFL(dept INTEGER)

   RETURNS TABLE (Number INTEGER, Departmt INTEGER,

                   FirstName VARCHAR(30), LastName VARCHAR(30))

   LANGUAGE SQL

 

   BEGIN

     RETURN

       SELECT empnum, empdpt,

       (substring(empnam,1,

         (locate(' ',empnam)-1))) as FirstName,

         substring(empnam,(locate(' ',empnam)+1),(length(empnam)

         - length(substring(empnam,1,(locate(' ',empnam)-1))))) as LastName

       FROM mylib/EMPLOYEE

       WHERE empdpt = dept;

   END

    

Since the employee's last name is contained in its own column, we can now select the employees from department 17 and easily order them by their last name.

 

SELECT * from table(dspempfl(17)) as T order by LastName

 

By putting the logic for separating the names in a UDTF, the SQL used to separate them is consolidated in one function, the SELECT statement using the new table is simplified, and a convenient way is provided for someone not as well-versed in SQL to access this table. The results are shown in the figure below.

 

022013Statonfigure2

Figure 2: The new results separate first name from last name.

 

So far, I've just used a basic SELECT statement in the body of the SPL to return a set of values. SPL is much more powerful, allowing programming logic to test conditions and to insert, modify, or delete rows and columns in other tables. For example, let's assume that you want to create an audit for every time a program uses the function to access the employees of department 17. To keep things simple, we'll make the modifications to the first function we created. The table LOGFILE will record each access with a row consisting of the timestamp, the user, and department 17. The result is shown below.

 

CREATE FUNCTION DSPEMPLY(dept INTEGER)

   RETURNS TABLE (Number INTEGER, Departmt INTEGER, Name VARCHAR(30))

 

   LANGUAGE SQL

   MODIFIES SQL DATA

 

   BEGIN

   IF dept = 17 THEN

     INSERT INTO mylib/LOGFILE (tstamp, cuser, dep)

         VALUES(CURRENT_TIMESTAMP, USER, '017');

         END IF;

     RETURN

       SELECT EMPNUM, EMPDPT, EMPNAM

       FROM mylib/EMPLOYEE

       WHERE EMPDPT = dept;

   END

  

Notice that since we are performing an INSERT, we are modifying an SQL table, so the keyword MODIFIES SQL DATA has been added to the keyword list. In my previous article, I mentioned several other keywords that can be used with UDFs, and those keywords can be used with UDTFs as well. One keyword that is unique for table functions is the keyword CARDINALITY, which is followed by an integer. This keyword indicates the expected number of rows to be returned by the table function for optimization purposes. For example, in our function above, if it was known that no department contained more than 10 employees, a cardinality of 10 could be specified as shown below.

 

   LANGUAGE SQL

   MODIFIES SQL DATA

   CARDINALITY 10

 

For a complete listing of the keywords and how they are used, see the IBM i Database SQL Programming Guide at the IBM Infocenter Web site.

Summing It Up

If you haven't previously been familiar with SQL UDTFs or haven't had an occasion to use one, I attempted in the above examples to illustrate that they are not that difficult or mysterious. In fact, they are a great way to simplify the complexity of SQL SELECT statements in your coding, to provide a simple interface that allows passing parameters to create dynamic tables, to automatically log access to tables, and to provide a single point of maintenance for the SQL code that you use repeatedly in your programs.

 

For more on SQL User-Defined Functions, consider the IBM Redbook Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries or the DB2 for i SQL Reference at the IBM Infocenter.

 

 

Jim Staton

Jim Staton is Vice President for Information Technology at Mutual Distributing Company, the largest alcohol beverage distributing company in North Carolina.

 

While Jim never worked with IBM midrange systems at IBM, one of his first programming jobs was developing software for an IBM System/3 Model 6 for his family's business. He continued to develop RPG applications for the business in his spare time on a System/32, System/34, System/36, and eventually the AS/400.

 

In 1978, Jim joined IBM at the Research Triangle Park in North Carolina, where he worked with computer communications and protocol standardization. As a manager at IBM, he was responsible for IBM's work with the IEEE 802.x committees to complete the standards for the 802.2-5 protocols for local area networks. In 1985, Jim joined the IBM European Networking Center in Heidelberg, Germany, where he managed the development of IBM's first prototype implementing the CCITT X400 Standard, which were the rules governing the first universal email system. While at IBM, Jim was awarded two patents in communication protocols as well as an Outstanding Technical Achievement Award for his work with X.400 Message Handling System. He also coauthored several articles on computer communications for the IBM Systems Journal.

 

Jim has been a speaker at a number of conferences on a variety of topics, including communications protocols, supply chain management, mobile applications, and business intelligence.

 

Jim graduated from Ohio State University in 1978 with an MS degree in Computer Science.

 

 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: