Wednesday, October 31, 2018

Easiest way to search job descriptions

easy way to get information from job description using sql view

I have written about getting the library list from a job description using the QWDRJOBD API. An API is fine for doing things for one job description, but when I want to search all job descriptions that, for example, contain a library in their library list it is not easy to do with an API. A SQL view would be ideal for this.

IBM has come to rescue and I can now retrieve data from job descriptions using the SQL view JOB_DESCRIPTION_INFO. It is listed as one of the enhancements added with the latest round of IBM i Technical Refreshes, 7.3 TR5 and 7.2 TR9, but I have found it on a instance of IBM i that has not had the latest TR applied.

The view contains every column you need to know about a job description. I am not going to list them in this post, as I want to show examples of the kinds of information I have been asked from get from job descriptions in the past. If you want to see all the columns that are available there is a link at the bottom of this post to the IBM KnowledgeCenter page describing this view.

My first example is what I described in the post using the QWDRJOBD API, how to get the library list from the job description.

01  SELECT JOBDLIB AS LIBRARY,JOBD AS JOBD,TEXT,LIBL_COUNT,LIBL
02    FROM QSYS2.JOB_DESCRIPTION_INFO

What I love about using these views is just how simple it is to get the data. No long lists of parameters or user spaces, just a simple Select statement.

In this case I am selecting:

  1. JOBLIB the library the job description is in. I am calling this column "LIBRARY".
  2. JOBD name of the job description. This column will be "JOBD".
  3. TEXT the text that was entered when the job description was created.
  4. LIBL_COUNT this gives me the number of libraries there are in the job description's library list.
  5. LIBL the job description's library list.

I have chosen to use the short names for the columns due to limitation in the width of this post, and I am being lazy not wanting to key in the long names. You can, of course, use either or both in your statements.

My results look like:

LIBRARY  JOBD      TEXT                     LIBL_COUNT
QGPL     QDFTJOBD  Default job description           1
MYLIB    MYJOBD    Simon Hutchinson                  6

LIBL
*SYSVAL
QTEMP      MYLIB      ANOTHERLIB OTHERLIB1  OTHERLIB2  QGPL

The value *SYSVAL means that the library list used by QDFTJOBD is the default system value. And I can use SQL to get the data from the system values too.

I could refine the above statement and break the library list into separate columns for each library. If you are going to do this each you will find that each library list value is held in 11 characters.

01  SELECT LIBL_COUNT AS COUNT,
02         SUBSTR(LIBL,1,11) AS LIB1,
03         SUBSTR(LIBL,12,11) AS LIB2,
04         SUBSTR(LIBL,23,11) AS LIB3,
05         SUBSTR(LIBL,34,11) AS LIB4,
06         SUBSTR(LIBL,45,11) AS LIB5,
07         SUBSTR(LIBL,56,11) AS LIB6
08    FROM QSYS2.JOB_DESCRIPTION_INFO
09   WHERE JOBD = 'MYJOBD'
10     AND JOBDLIB = 'MYLIB'

As I have selected only my job description, MYJOBD in library MYLIB, the results are:

COUNT   LIB1    LIB2    LIB3        LIB4        LIB5       LIB6
    6   QTEMP   MYLIB   ANOTHERLIB  OTHERLIB1   OTHERLIB2  QGPL

If I needed to produce a list of job descriptions that contain a specific library this is so easy using the view:

01  SELECT JOBDLIB AS LIBRARY,JOBD AS JOBD
02    FROM QSYS2.JOB_DESCRIPTION_INFO
03   WHERE LIBL LIKE '%MYLIB%'

The above statement will give me a list of all the job descriptions that contain the string "MYLIB". The percent sign ( % ) is the wildcard character in Db2 for i, therefore, I want to find any job descriptions with "MYLIB" in it. TMYLIB and MYLIB2 would be valid libraries to return in the results.

In this example only two job descriptions contains the string "MYLIB".

LIBRARY     JOBD
MYLIB       MYJOBD
OTHERLIB2   MYJOBD2

Try doing that as easily using the QWDRJOBD API!

I can search for any information about job descriptions. In this case I want to list the default output queue and job queue in the job descriptions.

01  SELECT JOBDLIB AS LIBRARY,JOBD AS JOBD,
02         OUTQLIB,OUTQ,
03         JOBQLIB,JOBQ
04    FROM QSYS2.JOB_DESCRIPTION_INFO

In this example I want to return:

  1. JOBDLIB job description library.
  2. JOBD job description name.
  3. OUTQLIB library the default output queue is in.
  4. OUTQ default output queue name.
  5. JOBQLIB library the default job queue is in.
  6. JOBQ default job queue name.

The results are:

LIBRARY  JOBD      OUTQLIB  OUTQ     JOBQLIB  JOBQ
QGPL     QDFTJOBD  -        *USRPRF  QGPL     QBATCH
MYLIB    MYJOBD    MYLIB    MYOUTQ   QGPL     QPGMR

The output queue value for the QDFTJOBD means that the output queue is what is set in the user's profile.

I am not going to give more examples as I am sure you can see how useful this view is, and how much easier it is to use than the corresponding API. A big thanks to IBM's Db2 for i team for giving us this view.

 

You can learn more about the JOB_DESCRIPTION_INFO view from the IBM website here.

 

This article was written for IBM i 7.3 TR5 and 7.2 TR9.

6 comments:

  1. I know we are on V7.2, however how do I find out what TRx we're on? Thanks...

    ReplyDelete
  2. This is a list of all the PTFs for IBM i 7.2 TRs here.

    You can discover which PTFs are loaded and applied on your IBM i by using the method described in Quick way to find if PTF present and applied.

    ReplyDelete
  3. Sure. There are a lot of new system tables after which the need for complicated APIs handling is easing out. You continue to do a great service to the community by exploring each one of them. Thanks

    ReplyDelete
  4. Simon, another great teaching moment with examples. Thanks for sharing these great tools using SQL. They will be very useful for quick research related to pgm issues.. thanks again..

    ReplyDelete
  5. This is handy. Thanks.

    ReplyDelete
  6. Thank you for sharing this

    ReplyDelete

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.