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:

1. Create the module.
2. Create the service program.
3. 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?

RELATED STORIES

Surge of Services In DB2 For i, Part 1

Surge of Services in DB2 for i, Part 2

DB2 for i Services

IBM i Services

Use SQL To Read IFS Directories

Squirrel