Guru: SQL Table Functions Can Do Non-function Things

May 1, 2017 Ted Holt

We IBM i developers owe a great debt to Scott Forstie. He’s responsible for the wonderful DB2 for i Services and IBM i Services, which give us SQL interfaces for many functions of the operating system. Like IBM, we can write SQL interfaces to help us with non-database tasks. I recently did exactly that.

While I can’t say that I never use the Start SQL Interactive Session (STRSQL) command, I can say that I prefer to use GUI SQL clients, in particular the Run SQL Scripts utility that is part of IBM i Access Client Solutions (ACS). I do get annoyed, however, when something that is easy to do in a green-screen session is harder to do from a GUI program.

Recently I got aggravated because there was no easy way (that I could find) to look at the library list from Run SQL Scripts. You can use the Job Details option of the View menu to see job information, but it takes you into Navigator for i. Non-IBM clients, such as Squirrel, provide no way to view the library list.

This story contains code, which you can download here.

It occurred to me that it would be nice to query something and see the library list as if it were a table. I thought of the services IBM kindly provides us and realized that I could likely write something similar to serve my purpose. That something similar was the DspLibl user-defined table function.

I used the QWCRTVCA API to retrieve the library list.

D qwcRtvCA        pr                   extpgm('QWCRTVCA')
D    ouRcvVariable...
D                             4096a
D    inRcvVarLength...
D                               10i 0  const
D    inFormatName...
D                               10a    const
D    inAttributeCount...
D                               10i 0  const
D    inAttributeKeys...
D                               10i 0  const
D    ioErrorCode                       likeds (ErrorDS)

D ErrorDS         ds                  qualified
D   BytesProv                   10i 0 inz(%size(ErrorDS))
D   BytesAvail                  10i 0

     qwcRtvCA (Receiver: %len(Receiver): 'RTVC0200':
               1: 2702: ErrorDS);

This API loads a data structure with library list information.

D Receiver        ds          4096    qualified
D   BytesReturned...
D                               10i 0
D   BytesAvailable...
D                               10i 0
D   SysLiblCount                10i 0
D   ProdLibCount                10i 0
D   CurLibCount                 10i 0
D   UsrLiblCount                10i 0
D   LiblList                  4072a

Here’s how a user-defined table function works. The system makes an initial call. That’s the time for one-time beginning-of-job processing. In this case, I retrieve the library list.

Next, the system continues to call for a row of data. The program repeatedly answers this call by returning a row. When there is no more data to return, the program sets SQL state to 02000 to signal end of data. For each of those calls, I returned the name and type of the next library in the library list along with a sequence number.

The system makes a final call so that we can clean up if we need to.

The result looks like this:

Voilà!

It’s not the sort of table that I would join to another one, although I could generate a table of library information:

cl:DSPOBJD OBJ(*ALL) OBJTYPE(*LIB) OUTPUT(*OUTFILE) OUTFILE(QTEMP/LIBS);

And join the two tables to include the text description of each library:

select x.sequence, x.library, x.type, L.ODOBTX
  from table(DSPLIBL()) as x
  left outer join qtemp.libs as L
    on x.library = L.ODOBNM;

I have used this function with Run SQL Scripts and Squirrel, and it works well in both places. To modify the library list in Run SQL Scripts, I prefix CL commands with CL: (case doesn’t matter) and terminate them with a semicolon.

cl: addlible mylib *last;

With Squirrel, I call the QCMDEXC service.

call qcmdexc ('chgcurlib yourlib');
call qcmdexc ('addlible hislib');

The DspLibl function is built from one short RPG module. The source code is provided for download at the top of this article.

To create the function requires three steps:

Create the module.
Create the service program.
Create the SQL function.

You will find the necessary creation commands in the RPG source code.

GUI SQL interfaces and access to the system! It looks like we can have our cake and eat it too! What kinds of wonderful non-function things are you going to do with user-defined table functions?

10 thoughts on “Guru: SQL Table Functions Can Do Non-function Things”

Scott Forstie says:

May 1, 2017 at 8:38 am

Ted,
Thanks for the kind words. Building these services into DB2 for i is fun for us, because we get a lot of warm feedback from clients. For the topic of library list, you could also consider using QSYS2.LIBRARY_LIST_INFO. We even return the full SQL Schema name, should it exist.
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzajq/rzajqviewliblinfo.htm
Keep up the good work, I love reading your articles.
Regards, Scott

- Ted Holt says:
  
  May 2, 2017 at 9:00 pm
  
  I looked all thru those services & didn’t see that one listed, Scott. Looks like I’ve re-invented the wheel yet again!
  
  Thanks for the encouragement.
  
Jesse Gorzinski, MBA (@IBMJesseG) says:

May 1, 2017 at 12:06 pm

Great article! It’s great to have documentation on how to write/use UDTF’s.
For awareness, IBM has provided the ability to inspect the library list through the QSYS2.LIBRARY_LIST_INFO view, available in 7.3 GA, SF99702 Level 3 (7.2), or SF99701 Level 32 (7.1)

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.LIBRARY_LIST_INFO%20view

Matt says:

May 1, 2017 at 1:18 pm

Well, too little too late, but…

select * from qsys2.LIBRARY_LIST_INFO

It is actually a view over: select * from table(QSYS2.QSQLIBL()) t

I developed a solution like yours myself. Then I stumbled on this feature.

- Ted Holt says:
  
  May 3, 2017 at 5:45 pm
  
  Thanks, Matt. It was a good thing I wrote that function. Of the various machines I used, I’ve found one so far that doesn’t have LIBRARY_LIST_INFO.
  
mitchejmm says:

May 1, 2017 at 3:28 pm

I created the *srvpgm, then created the DBfunction, but when I ran the suggested script, I received the following error: “Message: [SQL0204] DSPLIBLF in xxx type *N not found. Cause . . . . . : DSPLIBLF in xxx type *N was not found”, even though the function creation seemed to run: “create or replace function xxx.DSPLIBL () returns table… Statement ran successfully (19,784 ms = 19.784 sec)”

(xxx = ‘MyLibrary’)

Any suggestions will be appreciated.

Jim Mitchell
Boise, ID

- Ted Holt says:
  
  May 2, 2017 at 9:05 pm
  
  Hi, Jim. Is the service program in the library list?
  
Rusty Gadberry says:

May 3, 2017 at 7:29 pm

Ted, To see my library list from Run SQL Scripts I do: SELECT * FROM QSYS2.LIBLIST. Don’t know what release this was implemented on but our system is V7R1.

- Ted Holt says:
  
  May 11, 2017 at 11:45 am
  
  Thanks, Rusty. LIBLIST is an alternate name for LIBRARY_LIST_INFO. At least one system that I use does not have it. 🙁
  
Ted Holt says:

November 30, 2017 at 4:28 pm

I’m glad I wrote this thing. I needed it again today.