Useful queries to display information not provided on AJS interfaces

Troubleshooting

Problem

Useful queries to run over AJS files to provide data that isn't available using standard green screen AJS commands or via the AJS plugin on iNav.

Resolving The Problem

These are some queries that can be ran over the AJS files to pull specific data that you can't get by using basic AJS commands:

1) Determining the user profile for AJS jobs. This could be used when deleting a user profile and trying to determine if that user profile is used to run AJS jobs:

SELECT JMJOB FROM QUSRIJS/QAIJSMST WHERE JMUSER = '<user profile>'

2) Determining which AJS jobs are listed in Management Central:

SELECT * FROM QUSRIJS/QAIJSMST WHERE JMJTYP = '*MC'

3) Query that joins the master file and the command list file listing job name and commands:

SELECT T01.JMJOB, T02.CMCMD, T01.JMGRP, T01.JMSEQ, T01.JMAPPL,
T01.JMSCH, T01.JMTME, T01.JMDTE FROM qusrijs/QAIJSmst T01,
qusrijs/QAIJS1cm T02 WHERE T01.jmjob = T02.cmjob

4) SQL to list job name, run time and date, jobd, jobd library, user, commands, in descending order by run time:

SELECT T01.JMJOB, T01.JMTME, T01.JMDTE, T01.JMJOBD, T01.JMJBDL,
T01.JMUSER, T02.CMCMD FROM qusrijs/QAIJSmst T01, qusrijs/QAIJS1cm
T02 WHERE T01.jmjob = T02.cmjob
ORDER BY T01.JMTME DESC

Same as above except in ascending order by date:

SELECT T01.JMJOB, T01.JMTME, T01.JMDTE, T01.JMJOBD, T01.JMJBDL,
T01.JMUSER, T02.CMCMD FROM qusrijs/QAIJSmst T01, qusrijs/QAIJS1cm
T02 WHERE T01.jmjob = T02.cmjob
ORDER BY T01.JMDTE

5) SQL that lists only jobs that do not have *JOBCTL as the jobd.

SELECT T01.JMJOB, T01.JMJOBD, T01.JMJBDL, T02.CMCMD FROM
qusrijs/QAIJSmst T01, qusrijs/QAIJS1cm T02 WHERE T01.jmjob =
T02.cmjob and T01.jmjobd != '*JOBCTL'

6) SQL to list job name, jobd, jobd library, and commands:

SELECT T01.JMJOB, T01.JMJOBD, T01.JMJBDL, T02.CMCMD FROM
qusrijs/QAIJSmst T01, qusrijs/QAIJS1cm T02 WHERE T01.jmjob =
T02.cmjob

7) SQL to list job name, group, status, jobd, command, and format the last run date to mm/dd/yy:

SELECT T01.JMJOB, T01.JMGRP, T01.JMSTS, T01.JMJOBD, T01.JMJBDL,substr(t02.CMCMD,1,50) as cmcmd,
SUBSTRING(CHAR(JMLRD),3,2) CONCAT '/' CONCAT
SUBSTRING(CHAR(JMLRD),5,2) CONCAT '/' CONCAT
SUBSTRING(CHAR(JMLRD),1,2) AS "Last Run Date", T01.JMLST
FROM QUSRIJS/QAIJSmst T01,
QUSRIJS/QAIJS1cm T02 WHERE T01.jmjob = T02.cmjob

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CNEAA2","label":"Advanced Job Scheduler"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"and future releases;7.3.0;7.4.0;7.5.0;7.6.0"}]

Historical Number

636854749

Was this topic helpful?

Document Information

Modified date:
06 August 2025

UID

nas8N1010922

Tips