Retrieving the job's library list using SQL

Before IBM i 7.2 if I wanted to retrieve the current job's library list I would have to use either the Retrieve Job Attributes command, RTVJOBA, or the Retrieve Job Information API, QUSRJOBI. As part of "librarian services" a new View was introduced, LIBRARY_LIST_INFO, and as its name suggests it allows me to retrieve information about my job's library list.

Like all the other new Views and Table Functions LIBRARY_LIST_INFO resides in the library QSYS2. It is possibly the smallest View I have written about as it has only six columns (fields):

Long name	Short name	Description
ORDINAL_POSITION	COLNO	Position in library list
SCHEMA_NAME	NAME	Long name of the schema (library), can be up to 128 characters
SYSTEM_SCHEMA_NAME	SYS_NAME	System name of the schema (library), ten characters
TYPE	TYPE	The part of the library list the library is in, I will discuss the values later in this post
IASP_NUMBER	IASP	Number of the auxillary storage pool the library is allocated to
TEXT_DESCRIPTION	TEXT	Text description of library

If I wanted to get a list of my job's library list I could just use the following SQL statement:

SELECT ORDINAL_POSITION AS POS, SYSTEM_SCHEMA_NAME AS LIBRARY, TYPE, CAST(TEXT_DESCRIPTION AS CHAR(50)) AS TEXT FROM QSYS2.LIBRARY_LIST_INFO

Which gives me:

POS LIBRARY TYPE TEXT 1 QSYS SYSTEM System Library 2 QSYS2 SYSTEM System Library for CPI's 3 QHLPSYS SYSTEM - 4 QUSRSYS SYSTEM System Library for Users 5 QSQL PRODUCT - 6 CURLIB CURRENT Can be used for *CURLIB 7 QTEMP USER - 8 MYLIB USER Simon`s library 9 MYLIB2 USER Simon No.2 10 QGPL USER General Purpose Library

Type TYPE column gives which part of the library list the library is in. These are:

• SYSTEM – System part, which always comes before all others parts
• PRODUCT – If an IBM product is used, like SQL, then its library will appear after the SYSTEM
• CURRENT – The current library entry is before the user. Personally I do not use this, and only have it here for the example. If there is not a current library entry then this entry does not appear
• USER – This is the last part, which is what we freely manipulate

I looked up what is the maximum number of libraries you could have in a library list:

Part of library list	Maximum libraries
System	15
Product	1
Current	1
User	250
Total	267

How I could I use this View? Having spent some thinking about it I decided to create a suboprocedure that would validate if a library is in the job's library list. Let me start with the the program that calls my subprocedure:

01 **free 02 ctl-opt dftactgrp(*no) ; 03 dcl-pr InLibl char(1) ; 04 *n char(10) value ; 05 end-pr ; 06 dcl-s Found char(1) ; 07 Found = InLibl('MYLIB') ; 08 dsply ('MYLIB = ' + Found) ; 09 Found = InLibl('NO_LIB') ; 10 dsply ('NO_LIB = ' + Found) ; 11 *inlr = *on ;

Line 1: As I am using an IBM i server with 7.2 TR3 I can code in fully free RPG. When I do I need to give **FREE as my first line. If I was on another server without the relevant PTFs I would delete this line, and start all of my RPG code in the eighth column.

Line 2: As I am using a subprocedure I need to have the DFTACTGRP in my Control Options.

Lines 3 – 5: This is the definition of my subprocedure, I have decided to call InLibl. It returns a one character value when it is called, which is defined on line 3 by the CHAR(1) following the subprocedure's name. I am going to be passing a single variable which is a ten character variable, which should come as no surprise as that is the library name.

Line 6: Is the definition for the returned value.

Lines 7 – 10: Lines 7 and 8 and Lines 9 and 10 do the same thing. On line 7 I am calling the subprocedure, passing it the library name "MYLIB". The flag returned from the subprocedure is placed in the variable FOUND. I display this value using the DSPLY operation code on line 8. On line 9 I pass "NO_LIB", and display the result on line 10.

And now the subprocedure:

12 dcl-proc InLibl ; 13 dcl-pi *n char(1) ; 14 Library char(10) value ; 15 end-pi ; 16 dcl-s i char(1) inz('N') ; 17 exec sql SELECT 'Y' INTO :i FROM LIBRARY_LIST_INFO WHERE SYSTEM_SCHEMA_NAME = :Library ; 18 return i ; 19 end-proc ;

Line 12 and 19: All subprocedures start with a DCL_PROC and end with END-PROC.

Lines 13 – 15: I have not named the procedure interface, hence the *N on line 13. Next to it is what I will be returing from this subprocedure, a single character value. The incoming parameter, line 14, is called Library, which is a very good name for it when you consider what it contains.

Line 16: Defines a variable I will be using in the SQL statement. I have used the INZ keyword to initialize it with "N" everytime this subprocedure is called.

Line 17: This statement means move "Y" to the variable I if the library passed to this subprocedure is found in LIBRARY_LIST_INFO. If the library is not found I retains its original value, "N".

Line 18: The value in I is returned to the calling program.

When I call this program this is what the displayed:

DSPLY MYLIB = Y DSPLY NO_LIB = N

As expected I get a positive response for MYLIB, as it is in my library, and a negative one for NO_LIB as it is not in my list.

 

You can learn more about the LIBRARY_LIST_INFO command from the IBM website here.

 

This article was written for IBM i 7.2.