Get information about a job, including current SQL statement

I knew of the GET_JOB_INFO table function, but having played with it I did not see a reason why I would use it, I preferred the ACTIVE_JOB_INFO table function.

It was not until someone pointed out to me that I could see the current SQL statement being executed in a job that my interested with GET_JOB_INFO was piqued, especially as this would allow me to see what those data base server jobs are doing.

I decided to compare what GET_JOB_INFO would show me if I executed the following statement via two ways:

1. Using Operations Navigator's "Run SQL Scripts" client
2. Using the STRSQL command

If you do not know how to open the Operations Navigator's "Run SQL Scripts" you...

1. Open Operations Navigator
2. Click on the plus ( + ) next to the IBM i you want to use
3. Click on the plus next to "Databases"
4. Right click on the database name
5. Select "Run SQL Scripts"

I ran the following statement using both SQL clients. The statement I decided to use is the one to retrieve the results using the GET_JOB_INFO table function for the current job, hence the asterisk ( * ) in the table function's parameter:

SELECT * FROM TABLE(QSYS2.GET_JOB_INFO('*')) A

Table column	Run SQL script	STRSQL
V_JOB_STATUS	*ACTIVE	*ACTIVE
V_ACTIVE_JOB_STATUS	RUN	RUN
V_RUN_PRIORITY	20	20
V_SBS_NAME	QUSRWRK	QINTER
V_CPU_USED	342	20
V_TEMP_STORAGE_USED_MB	27	8
V_AUX_IO_REQUESTED	1451	68
V_PAGE_FAULTS	762	29
V_CLIENT_WRKSTNNAME	PC name	Null
V_CLIENT_APPLNAME	System i Navigator - Run SQL Scripts	Null
V_CLIENT_ACCTNG	PC operating system	Null
V_CLIENT_PROGRAMID	cwbunnav.exe	Null
V_CLIENT_USERID	IBM i user name that you started the Operations Navigator with	Null
V_SQL_STATEMENT_TEXT	select * from table(qsys2.get_job_info(?)) a	select * from table(qsys2.get_job_info(?)) a
V_SQL_STMT_STATUS	ACTIVE	COMPLETE
V_SQL_STMT_START_TIMESTAMP	Timestamp value	Null
V_QUERY_OPTIONS_LIB_NAME	QTEMP	QUSRSYS
V_CLIENT_IP_ADDRESS	IP address of PC	IP address of PC
V_PJ_REUSED_COUNT	5	Null
V_PJ_MAXUSE_COUNT	200	Null

By all means that this is a simple SQL statement, but you can see it in the V_SQL_STATEMENT_TEXT variable.

But, what else can I use GET_JOB_INFO for?

ACTIVE_JOB_INFO only shows jobs that are active, i.e. running. Let me compare the information I can get from both of these table functions. I am going to check on a job that I will submit to batch.

The program that will run in the job is a very short CLLE program:

01 PGM 02 DCL VAR(&COUNT) TYPE(*DEC) LEN(15 0) 03 DOUNTIL COND(&COUNT > 100000000) 04 CHGVAR VAR(&COUNT) VALUE(&COUNT + 1) 05 ENDDO 06 DLTF FILE(QTEMP/NOT_A_FILE) 07 ENDPGM

Lines 3 – 5: The program has a Do loop that pauses the program so I can use my SQL statement to retrieve the results from the table functions. When I tested earlier I had used the DLYJOB command, but that just put the job into a DLYW status, which is not what I wanted.

line 6: The delete file will fail, as there is not a file in QTEMP with that name, and give me an error message.

Now to the SQL statement to retrieve the information from both table functions at the same time.

01 SELECT 'GET_JOB_INFO' AS "TABLE FUNCTION", 02 V_JOB_STATUS AS "JOB STATUS", 03 V_ACTIVE_JOB_STATUS AS "ACTIVITY", 04 V_RUN_PRIORITY AS "RUN PRIORITY", 05 V_SBS_NAME AS "SUBSYSTEM" 06 FROM TABLE (QSYS2.GET_JOB_INFO('708692/SIMON/TESTCL')) A 07 UNION 08 SELECT 'ACTIVE_JOB_INFO', 09 'N/A', 10 JOB_STATUS, 11 RUN_PRIORITY, 12 SUBSYSTEM 13 FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) B 14 WHERE JOB_NAME = '708692/SIMON/TESTCL'

As there is not a job name column in the results from GET_JOB_INFO I had to be a bit creative to get the results from both table functions. I used a UNION clause to put the results from both table functions in separate rows.

Lines 1 and 8: The first column in the results row will be the "heading" I have given in these columns.

Line 9: The ACTIVE_JOB_INFO does not have a "Job status" column, therefore, I needed to put something into the equivalent column. I chose to put "N/A", but I could have put any value there.

The rest of the columns that will be returned in the results match.

First thing I do is to submit to batch the call to this test program, and then run my SQL statement:

SBMJOB CMD(CALL PGM(TESTCL)) JOB(TESTCL) JOBQ(QBATCH) TABLE FUNCTION JOB STATUS ACTIVITY RUN PRIORITY SUBSYSTEM GET_JOB_INFO *JOBQ - 0 -

There is no result returned from ACTIVE_JOB_INFO as this job is not active.

When the job comes off the job queue and the Do loop is processed I ran the SQL statement:

TABLE FUNCTION JOB STATUS ACTIVITY RUN PRIORITY SUBSYSTEM GET_JOB_INFO *ACTIVE RUN 50 QBATCH ACTIVE_JOB_INFO N/A RUN 50 QBATCH

Both of the jobs show the same information, except for the "Job status" column which is irrelevant to the ACTIVE_JOB_INFO.

The program tries to delete the file, and errors:

TABLE FUNCTION JOB STATUS ACTIVITY RUN PRIORITY SUBSYSTEM GET_JOB_INFO *ACTIVE MSGW 50 QBATCH ACTIVE_JOB_INFO N/A MSGW 50 QBATCH

I answer the message with an "I", and the program completes. Now when I run the statement there is no information returned from the ACTIVE_JOB_INFO:

TABLE FUNCTION JOB STATUS ACTIVITY RUN PRIORITY SUBSYSTEM GET_JOB_INFO *UNKNOWN - 0 -

I have tried various made up job names, and no matter what is entered GET_JOB_INFO returns what is shown above. So I think it is safe to say if you want to check if a job has completed this is not the table function to use.

Having played with the GET_JOB_INFO I cannot see myself using it to see what is happening with actively running jobs, I am going to stick with using the results from ACTIVE_JOB_INFO.

Performing my tests on IBM i partitions running 7.2 and 7.3 I found that the ACTIVE_JOB_INFO for 7.3 contains all the columns that the GET_JOB_INFO has, including the SQL column. This gives me another reason not to use GET_JOB_INFO, unless my IBM i is 7.2 or less.

 

You can learn more about the GET_JOB_INFO table function from the IBM website here.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.