Wednesday, May 15, 2019

Get information about a job, including current SQL statement

get_job_info to retrieve 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.

4 comments:

  1. To run SQL scripts from Access Client Solutions emulator, click on the button on the tool bar. Hardly ever used the Navigator version because it was so slow to load. The ACS version is snappy! Also if you double-click on a .SQL file, the ACS SQL program loads it.

    ReplyDelete
  2. Interesting you can’t extract the job name, job user, and job number from the table.

    ReplyDelete
  3. I give an example of how to do this in a future post.

    ReplyDelete
  4. Art SaisuphaluckMay 21, 2019 at 10:33 AM

    Thank you for this post. My as400 guys said that there is no way to see SQL Statements. Well now that's gonna change, thank you.

    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.