Liste des Sous-systèmes actifs

SELECT (SELECT CHAR(SUBSTR(CURCHARVAL, 1, 8))
FROM SYSTEM_VALUE_INFO WHERE
SYSVALNAME = 'QSRLNBR') AS SERIAL,
( SELECT SUBSTR(HOST_NAME, 1, 8)
FROM QSYS2.SYSTEM_STATUS_INFO ) AS MACHINE,
SUBSYSTEM AS SBS, SUBSY00001 AS SBSLIB, COUNT(*) AS NBRJOB,
CURDATE() AS RTVDAT, CURTIME() AS RTVTIM, CURRENT USER AS RTVUSR
FROM TABLE(QSYS2.ACTIVE_JOB_INFO()) WHERE JOB_TYPE <> 'SBS' AND JOB_TYPE <> 'SYS' GROUP BY SUBSYSTEM, SUBSY00001 ORDER BY SUBSYSTEM, SUBSY00001

Avec le fichier Temporaire :

CHGVAR VAR(&CMDSQL) VALUE('CREATE TABLE ' *BCAT +
                        &FSQL *BCAT '(AJSN  CHAR(8), AJSYS +
                        CHAR(8), AJSBS CHAR(10), AJNBR CHAR(6), +
                        AJUSR CHAR(10), AJJOB CHAR(10), AJTYP +
                        CHAR(3), AJSBMN CHAR(6), AJSBMU CHAR(10), +
                        AJSBMJ CHAR(10), AJSTRD CHAR(6), AJSTRT +
                        CHAR(6), AJINIT CHAR(6), AJINID CHAR(6), +
                        AJSRVI CHAR(30), AJJBDL CHAR(10), AJJBD +
                        CHAR(10), AJACG  CHAR(15), RTVDAT DATE +
                        with default current_date,   RTVTIM TIME +
                        with default current_time,   RTVUSR +
                        char(10))')

Liste des travaux actifs

SELECT (SELECT CHAR(SUBSTR(CURCHARVAL, 1, 8)) FROM SYSTEM_VALUE_INFO WHERE
SYSVALNAME = 'QSRLNBR') AS SERIAL,
( SELECT SUBSTR(HOST_NAME, 1, 8)
FROM QSYS2.SYSTEM_STATUS_INFO ) AS MACHINE,
JOB_S00001 AS SBS,
SUBSTR(JOB_NAME, 1, 6) AS JNBR,
SUBSTR(JOB_NAME, 8, POSSTR(SUBSTR(JOB_NAME, 8), '/')-1) AS JUSER,
SUBSTR(SUBSTR(JOB_NAME, 8), POSSTR(SUBSTR(JOB_NAME, 8), '/')+1) AS JOB,
JOB_TYPE AS TYPE,
SUBSTR(SUBMI00001, 1, 6) AS SBNBR,
SUBSTR(SUBMI00001, 8, POSSTR(SUBSTR(SUBMI00001, 8), '/')-1) AS SBUSER,
SUBSTR(SUBSTR(SUBMI00001, 8), POSSTR(SUBSTR(SUBMI00001, 8), '/')+1) AS SBJOB,
SUBSTR(VARCHAR_FORMAT(JOB_A00002,'YYMMDD'), 1, 6) AS DEBDAT,
SUBSTR(VARCHAR_FORMAT(JOB_A00002,'HHMMSS'), 1, 6) AS DEBTIM,
SUBSTR(VARCHAR_FORMAT(JOB_E00001,'HHMMSS'), 1, 6) AS INITIM,
SUBSTR(VARCHAR_FORMAT(JOB_E00001,'YYMMDD'), 1, 6) AS INIDAT,
SERVE00001 AS SRVTYP,
JOB_D00001 AS JOBDL, JOB_D00002 AS JOBD, JOB_A00001 AS ACGCDE,
CURDATE() AS RTVDAT, CURTIME() AS RTVTIM, CURRENT USER AS RTVUSR
FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER⇒'*ALL'))
WHERE JOB_STATUS = 'ACTIVE'
ORDER BY JOB_A00002

Avec le fichier temporaire :

CHGVAR     VAR(&CMDSQL) VALUE('CREATE TABLE ' *BCAT +
                        &FSQL *BCAT '(ASSN CHAR(8), ASSYS CHAR(8), ASSBS +
                        CHAR(10), ASSBSL CHAR(10), ASBJOB DEC(7, +
                        0), RTVDAT DATE with default +
                        current_date, RTVTIM TIME with default +
                        current_time, RTVUSR char(10))')