One of the most popular presentations that I give for conferences and user groups is about how to write User Defined Functions (UDFs) and User Defined Table Functions (UDTFs) in ILE RPG. The handout for that presentation can be found here:
The basic concept is that UDFs and UDTFs are functions that can be called from within an SQL statement. These exist in all databases, of course, but DB2 for i allows you to write them not only in SQL itself, but in other "external" languages, including ILE RPG.
Since RPG is better for certain tasks than SQL, it can often be very useful to write a UDF or UDTF in RPG, and then call it from your SQL statements. More details on how to do this can be found in the following article:
Years ago, I wrote a couple of articles that provided examples of UDTFs. These examples are designed to list the contents of IFS directories on your IBM i using an SQL SELECT statement. This way, you can do things like an ORDER BY clause to sort it differnt ways (date, size, etc) or you can use a WHERE clause to subset the list to only files you want to see. This has proven very popular, so I am reproducing those articles here.
Note:The above articles are the raw manuscripts that were submitted and subsequently published by (now defunct) System iNetwork and iPro Developer. Since they are only manuscripts, the formatting is minimal.