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:
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?