Q: Can an external user-defined function (UDF) or stored procedure be called using the library list?

Which Thing to Find?

Most database management software provides the ability to call routines in SQL, including both stored procedures and user-defined functions. But DB2 for i provides "external" procedures and functions, which is to say that the routines don't have to be written in SQL; they can call an external program or service program written in another language. On IBM i, we often have business logic written in RPG, and we want to make it available to call from SQL because that exposes that logic to any program that can run an SQL statement.

When an SQL statement calls an external SQL routine, there are two steps involved:

  1. finding the procedure/function definition in the SQL catalogs
  2. finding the external object (*PGM or *SRVPGM) to be called

Start by asking yourself which of these (or both) should be located using the library list.

The Effect of the Naming Convention

DB2 for i supports two different naming conventions.

  1. SQL Naming: objects are referenced as LIBRARY.OBJECT
  2. System Naming: objects are referenced as LIBRARY/OBJECT

The naming convention applies to all objects you use in an SQL statement. This includes conventional file-type objects (tables, views, indexes) as well as routines (stored procedures and UDFs).

When you're calling a routine, the naming affects more than just whether you use a dot or a slash. It also affects whether you use the library list (system naming) or the SQL PATH variable (SQL naming). If you're using SQL naming, you can't use the library list. Therefore, you must use system naming to use the library list.

Finding the SQL Definition by Library List

Imagine a scenario in which you have two libraries, one named LIBPROD for production programs, and one named LIBTEST for testing programs. Users control which library they use by setting their library list.

Now imagine you ran these two SQL statements:

Create Function libprod/MyFunc(ymd Decimal(8,0))
returns char(10)                
language rpgle                  
deterministic                    
no sql                          
external name 'LIBPROD/DEMO1(MYFUNC)'
parameter style general;        

Create Function libtest/MyFunc(ymd Decimal(8,0))
returns char(10)                
language rpgle                  
deterministic                    
no sql                          
external name 'LIBTEST/DEMO1(MYFUNC)'
parameter style general;

The Create Function SQL statement is something you run to tell the database how to call a UDF. Once I've created the function, I can call it from an SQL statement. I run Create Function only once for each function to "install" it.

With the preceding two Create Function commands, I can run the SQL statement as follows:

select LIBPROD/MYFUNC(someField) from SOMEFILE;

I have LIBPROD hard-coded, so it calls the copy of MyFunc that's in LIBPROD. But what would happen if I didn't hard-code the library?

select MYFUNC(someField) from SOMEFILE;

This time, I didn't specify a library for MyFunc in the Select statement. Because my SQL naming convention has been set to "system," the system searches my library list for the SQL function definition. If LIBPROD is ahead of LIBTEST in my library list, the system finds the function definition (the one I created with Create Function) in LIBPROD and calls the corresponding service program, which is also in LIBPROD. If I had LIBTEST ahead of LIBPROD in my library list, the system would call the version in LIBTEST.

Even though I've hard-coded the library name in the External Name clause of my SQL statement, I can still effectively use the library list, since it'll be used to find my definition. You control which definition is used by choosing to specify (or not) the library on the SQL statement. In this example, the Select statement didn't specify a library, so the system used the library list.

Although I used Create Function in this example, the same holds true for the Create Procedure SQL statement.

Finding the External Program by Library List

Perhaps I don't want to hard-code a library, even on the Create Function (or Create Procedure) SQL statement. I want to rely on the library list throughout. This seems like the most intuitive thing to try:

Create Function MyFunc(ymd Decimal(8,0))
returns char(10)                
language rpgle                  
deterministic                    
no sql                          
external name '*LIBL/DEMO1(MYFUNC)'
parameter style general;        
<span style="color:red">SQL0113 - Name *LIBL not allowed.</h3>

It didn't work! I can't specify *LIBL on an external name clause. However, I can still use the library list if I want to. I just have to leave the library off the external name. To my surprise, I have to omit the quotes to make it work.

Create Function LibProd/MyFunc(ymd Decimal(8,0))
returns char(10)                
language rpgle                  
deterministic                    
no sql                          
external name DEMO1(MYFUNC)
parameter style general;        

Create Function LibTest/MyFunc(ymd Decimal(8,0))
returns char(10)                
language rpgle                  
deterministic                    
no sql                          
external name DEMO1(MYFUNC)
parameter style general;

The SQL reference manual states that when using the system naming convention, and the external name is not qualified, it will use *LIBL to search for the program or service program. You can't put quotes around the external name when you use the library list, or you get an error.

Once again, if I have a LIBPROD library for production objects, and a LIBTEST for test copies, I can run the following SQL statement:

select MYFUNC(someField) from SOMEFILE;

If LIBPROD is ahead of LIBTEST in my library list, I will find the definition in LIBPROD, and it will call the DEMO1 service program also in LIBPROD if it exists, because it's using my library list for both the definition and the external object. The library list is used for everything!

But, now consider this:

select LIBPROD/MYFUNC(someField) from SOMEFILE;

I've hard-coded LIBPROD on the Select statement. It will doubtless find the function definition (created with Create Function) in LIBPROD. But which external object will it use? The one in LIBPROD or the one in LIBTEST? It depends on my library list. So even though I hard-coded the library in the Select statement, it's still going to use *LIBL. What if I don't want it to? What if I have neither LIBPROD nor LIBTEST in my library list, and I want to explicitly specify the library on the Select statement so that I don't have to modify the library list? The library specified in the SQL statement doesn't work, because *LIBL is hard-coded in the definition I created with Create Function.

Again, I used Create Function purely as an example. The same logic also applies to creating a procedure definition with the Create Procedure SQL statement.

Conclusion

Most of us in the IBM i community are used to using *LIBL for everything. We don't ever want to hard-code a library into anything. In that case, specifying an external name without a library causes the routine to use *LIBL, which is what we want. But if you ever want to control the library at runtime, it may make more sense to hard-code the library in the External Name clause. After all, this does not preclude you from using the *LIBL to find the definition!