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?
RELATED STORIES
Surge of Services In DB2 For i, Part 1
Surge of Services in DB2 for i, Part 2
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
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.
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
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.
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.
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
Hi, Jim. Is the service program in the library list?
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.
Thanks, Rusty. LIBLIST is an alternate name for LIBRARY_LIST_INFO. At least one system that I use does not have it. 🙁
I’m glad I wrote this thing. I needed it again today.