Separating parts of qualified job name with SQL

In a previous post I explained how I could extract the parts of the qualified job name using the LOCATE_IN_STRING scalar function. With the latest Technology Refreshes, IBM i 7.6 TR2 and 7.5 TR8, a number of Db2 for i scalar functions and a table function were added to make this easier. These are:

• JOB_NAME scalar function:  Returns the job name
• JOB_NUMBER scalar function:  Returns the job number
• JOB_USER scalar function:  Returns the job user
• JOB_NAME_DETAILS table function:  Returns all of the above

All of the above are found in the library SYSTOOLS.

I decided to use the SQL view OUTPUT_QUEUE_ENTRIES_BASIC for my examples as it contains a qualified job names, and I think that most people will have the authorization to use the view.

In this first example I am going to use all three of the scalar functions:

01 SELECT JOB_NAME AS "Qualified job name", 02 SYSTOOLS.JOB_NAME(JOB_NAME) AS "Job name", 03 SYSTOOLS.JOB_NUMBER(JOB_NAME) AS "Job No.", 04 SYSTOOLS.JOB_USER(JOB_NAME) AS "Job user" 05 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 06 LIMIT 5

Line 1: This is the qualified job name that exists in the view.

Line 2: I am using the JOB_NAME scalar function to extract the job name. All three of these scalar functions require the qualified job name as their parameter.

Line 3: Using the JOB_NUMBER scalar function to get the job number part of the qualified job name.

Line 4: The JOB_USER extracts the job user.

Line 6: I am limiting the results to five rows as I only need a few to demonstrate this.

The results are:

Qualified job name Job name Job No. Job user -------------------- -------- ------- -------- 865917/SIMON/QPRTJOB QPRTJOB 865917 SIMON 865917/SIMON/QPRTJOB QPRTJOB 865917 SIMON 865917/SIMON/QPRTJOB QPRTJOB 865917 SIMON 524231/ROWENA/DSP01 DSP01 524231 ROWENA 690572/ROWENA/SNE SNE 690572 ROWENA

I can also use these scalar functions in the Where clause. For example, how many spool files does my profile, SIMON, have?

01 SELECT COUNT(*) 02 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 03 WHERE SYSTOOLS.JOB_USER(JOB_NAME) = 'SIMON'

Line 1: I want a count of the number of qualifying results.

Line 3: I am using the JOB_USER scalar function in the Where clause.

I have just three spool files.

00001 ------ 3

The SQL table function will extract the three parts of the qualified job name into their own columns. For example, I can extract the columns from the current job.

01 SELECT * FROM TABLE(SYSTOOLS.JOB_NAME_DETAILS(QSYS2.JOB_NAME))

Line 1: QSYS2.JOB_NAME is a system global variable that contains the qualified job name of the current job.

The results from the JOB_NAME_DETAILS are:

JOB_NUMBER JOB_USER JOB_NAME ---------- -------- ---------- 884755 QUSER_NC QZDASOINIT

I can join the results from the OUTPUT_QUEUE_ENTRIES_BASIC view with JOB_NAME_DETAILS table function using a Lateral statement

01 SELECT A.JOB_NAME,B.JOB_NAME,B.JOB_NUMBER,B.JOB_USER 02 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC A, 03 LATERAL 04 (SELECT JOB_NUMBER,JOB_USER,JOB_NAME FROM TABLE(SYSTOOLS.JOB_NAME_DETAILS(JOB_NAME))) B 05 LIMIT 5

Line 1: The columns prefix with the A come from OUTPUT_QUEUE_ENTRIES_BASIC, the B from JOB_NAME_DETAILS.

Line 2: The part of the statement for the view must end with a comma ( , ).

Line 3: Lateral joins the two.

Line 4: I can use the qualified job name from the view as the parameter for the table function.

Line 5: I am limiting my results to five rows.

And those results are:

JOB_NAME JOB_NAME JOB_NUMBER JOB USER -------------------- -------- ---------- -------- 865917/SIMON/QPRTJOB QPRTJOB 865917 SIMON 865917/SIMON/QPRTJOB QPRTJOB 865917 SIMON 865917/SIMON/QPRTJOB QPRTJOB 865917 SIMON 524231/ROWENA/DSP01 DSP01 524231 ROWENA 690572/ROWENA/SNE SNE 690572 ROWENA

These are great additions to our "tool box", as I do spend a lot of effort extracting the job name or job user from the qualified job name in all kinds of programs I have written.

 

You can learn more about this from the IBM website:

• JOB_NAME scalar function
• JOB_NUMBER scalar function
• JOB_USER scalar function
• JOB_NAME_DETAILS table function

 

This article was written for IBM i 7.6 TR2 and 7.5 TR8.