Extracting the job's name from the Job Name

I was asked was there an easy way to extract the name from the Job Name column in the Db2 for i table function ACTIVE_JOB_INFO.

The IBM i Job Name consists of three parts:

1. Number
2. User profile
3. Name given to the job when it was started

For example:

741232/SIMONH/QPADEV0001

The issue when extracting the name from the Job Name is it does not always start in the same position. The number is always six long, but the user profile can be any length from one to ten. This means that the name could start anywhere from the tenth to the nineteenth position in the Job Name.

To extract the name I will have to do two things:

1. Determine where the name starts in the Job Name column
2. Substring it

I could use Db2 for i regular expressions, regexp, to find the start of the name, and then substring the name. But I am going to keep this simple and not.

I have written about using the LOCATE_IN_STRING function to find where a character is in a string. I can use the function to start its search either from the start of the string or from the end. In this scenario I will search from the end to find the slash character ( / ), which separates the user profile from the name parts of the job name. I can then use the SUBSTRING function to get the name.

As my first test I used by favorite SQL client to extract the name from my current job name, found in the JOB_NAME global variable.

After playing around for a while my statement looked like:

01 SELECT QSYS2.JOB_NAME, 02 SUBSTR(QSYS2.JOB_NAME, LOCATE_IN_STRING(QSYS2.JOB_NAME,'/',-1)+1) 03 FROM SYSIBM.SYSDUMMY1

Line 1: Standard Select statement retrieving the value of the JOB_NAME global variable. I always qualify the global variables with the library name so everyone knows it is a global variable rather than a column in the table/file.

Line 2: This is where I extract the name of the job. Let me start with how I calculate where the slash is. The LOCATE_IN_STRING has three parameters:

1. Column, or in this case global variable name
2. Character(s) searching for
3. Start position. As the start position is -1 it will search from the end of the string towards the start

In this example LOCATE_IN_STRING returns the value of 13. I need to add 1 to that value as I want the position of the first character of the name, 14.

Now I use the SUBSTR, substring, function to substring from the 14th position to the end of the string.

Line 3: The table SYSDUMMY1 is used in these kinds of example statements.

The results are exactly what I desired:

JOB_NAME 00002 734241/QUSER/QZDASOINIT QZDASOINIT

Now I have my SQL statement worked out I can modify it to use it with the ACTIVE_JOB_INFO table function.

SELECT JOB_NAME, SUBSTR(JOB_NAME,LOCATE_IN_STRING(JOB_NAME,'/',-1)+1) FROM TABLE(ACTIVE_JOB_INFO()) A

No one wants just to see the results. You want to be able to do something with them. Which is why I wrote the following program, to load the results into a data structure array. The array can be used for anything you want: subfile, XML, JSON, etc.

01 **free 02 ctl-opt option(*srcstmt) ; 03 dcl-ds Data qualified dim(9999) ; 04 JobName char(28) ; 05 Name char(10) ; 06 end-ds ; 07 dcl-s Rows packed(5) inz(%elem(Data)) ; 08 exec sql DECLARE C0 CURSOR FOR 09 SELECT JOB_NAME, 10 SUBSTR(JOB_NAME, LOCATE_IN_STRING(JOB_NAME,'/',-1)+1) 11 FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) A 12 FOR READ ONLY ; 13 exec sql OPEN C0 ; 14 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 15 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 16 exec sql CLOSE C0 ;

Line 1: You all know I only write in totally free RPG.

Line 2: I always use the *SRCSTMT control option as I want the program's internal line number to be the same as the source code's line number.

Line 3 – 6: This is the data structure array I will be copying my results into. It has two subfields:

1. JobName:  For the full Job Name (number/user/name)
2. Name:  The name extracted from the Job Name

I have given this array 9,999 elements which will be more than the number of jobs running in this IBM i partition. And the data structure must be qualified too.

Line 7: Here I am defining a variable that will be used to contain the number of rows of results returned from the SQL statement. I have initialized this variable with the number of elements in the data structure array.

Lines 8 – 12: This is my SQL cursor definition. It is the same as the previous SQL select statement I gave. I always add FOR READ ONLY to my cursor so the compiler knows it will not be used for insert or update.

Line 13: I open the cursor.

Line 14: I fetch my results from the cursor. I am using the Rows variable for the number of rows as it contains the number of rows the data structure has. In the future if I change the number of elements the array has I will not have to change anything else in this program. The results are copied into the data structure array.

Line 15: I am using the Get Diagnostics to retrieve the number of rows that were in the results. I can now use the value in Rows elsewhere in this program to load a subfile, or whatever other structure.

Line 16: Close the open cursor.

If I run this program in debug and look at the contents of the data structure array I can see that I have captured the Job Name's name in the Name subfield:

DATA.JOBNAME(1) = '443666/QSYS/#SYSLOAD ' DATA.NAME(1) = '#SYSLOAD ' DATA.JOBNAME(2) = '443667/#SYSLOAD/SYSLOAD ' DATA.NAME(2) = 'SYSLOAD ' DATA.JOBNAME(3) = '443050/QSYS/FB400 ' DATA.NAME(3) = 'FB400 ' DATA.JOBNAME(4) = '442918/QSECOFR/IFS0000011 ' DATA.NAME(4) = 'IFS0000011' DATA.JOBNAME(5) = '361910/QSYS/QBATCH ' DATA.NAME(5) = 'QBATCH ' DATA.JOBNAME(6) = '361915/QSYS/QCMN ' DATA.NAME(6) = 'QCMN ' DATA.JOBNAME(7) = '361931/QUSER/QACSOTP ' DATA.NAME(7) = 'QACSOTP ' DATA.JOBNAME(8) = '361939/QUSER/QLZPSERV ' DATA.NAME(8) = 'QLZPSERV '

 

This article was written for IBM i 7.3 and 7.2 .