Easiest way to search job descriptions

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.