Wednesday, February 17, 2016

Retrieving the job's library list using SQL

library list rtvjoba qusrjobi

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.

11 comments:

  1. I'm wondering why you used CAST on text_description.

    ReplyDelete
  2. Probably because of the data type which is
    VARGRAPHIC(50) CCSID 1200
    Nullable

    Jan

    ReplyDelete
  3. Be sure to install PTF SI58466 (which is now superseded by SI59253). It fixes an issue related to the text description not being converted to CCSID(1200) during the v7.2 upgrade cycle. The CAST isn't required once the PTF is installed. Note: New v7.2 installs/systems wouldn't experience the issue that requires this PTF.

    ReplyDelete
  4. This is Great stuff, if I can just get my system updated to the latest version. I am still on 7.1 mostly...already looking to use active jobs views ....hoping for some MQSeries views too....view Queues for a Queue Manager...anyone?

    ReplyDelete
  5. Thanks, Simon. I know this is just an example but I like these type of subprocedure functions to return an indicator versus a Y/N. I'd code i as an indicator type in the subprocedure and do this:
    exec sql SELECT '1' INTO :i
    FROM LIBRARY_LIST_INFO
    WHERE SYSTEM_SCHEMA_NAME = :Library ;

    Then I could just use:
    if inLibl(mylib);
    do this...
    else;
    do that...
    endif;

    Thank you for all you tips.

    ReplyDelete
  6. Not sure where you looked up library list maximums, but I would sure like to know where. Also, I ran your SQL on a V7R1 machine -- it can return more than one product library, plus I did a DSPLIBL and it showed 2 PRD Libraries, so, if 2 show up, maybe more than 2 can be in your library list - not really sure, again - where did you find the maximums ??

    ReplyDelete
    Replies
    1. The maximums were taken from IBM's documentation for IBM i 7.3.

      Delete
  7. According to IBM Knowledge Center for IBM i 7.3 (https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/rzarl/rzarlseclibl.htm)
    There can be 2 product libraries. This has been true for a long time what I can recall.
    Not sure if most people know about them but you can do some interesting things with product libraries.
    For example, I would copy the command definition from a utility application into QGPL and then change the command to set the product library to the library for the utility. This has the effect of adding that library to the top of the library list when using the command. Then when you exit the command the library list returns back to what it was before.

    ReplyDelete
  8. nice one. works on V7R1M0 :)

    ReplyDelete
  9. Do you need to ORDER BY ordinal_position or is it safe to assume it will always be sorted by ordinal position?

    ReplyDelete
    Replies
    1. You are probably safe not to sort the results by the ordinal position.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.