1. CRTLIB SCDRPT TEXT('Schedule Report')
2. CRTSRCPF SCDRPT/SOURCES TEXT('Schedule Report Sources')
3. CRTPF FILE(SCDRPT/SCDRPT) RCDLEN(300) TEXT('Schedule Report')
4. Create another Physical File (Work File)
SCDRPTW (PF) Schedule Report Work File
A R REG
A JOB 10A
A USER 10A
A JOBNBR 6A
A DATE 8S EDTWRD(' - - ')
A TIME 6S EDTWRD(' : : ')
A STATUS 1A
A MSGID 7A
A MSG 132A
A K DATE
A K JOB
A K TIME
A K JOBNBR
To create file: CRTPF FILE(SCDRPT/SCDRPTW) SRCFILE(SCDRPT/SOURCES) ADDLIBLE SCDRPT
5. Write CL Programs
5.1. SCDRPTG Schedule Report Generator
PGM
DCL VAR(&LJOB) TYPE(*CHAR) LEN(10)
DCL VAR(&LJOBNBR) TYPE(*CHAR) LEN(6)
DCL VAR(&LUSER) TYPE(*CHAR) LEN(10)
DCL VAR(&SJOB) TYPE(*CHAR) LEN(16)
DCL VAR(&SDATE) TYPE(*CHAR) LEN(8)
DCL VAR(&STIME) TYPE(*CHAR) LEN(6)
DCL VAR(&RPT) TYPE(*CHAR) LEN(300)
DCL VAR(&R1) TYPE(*CHAR) LEN(50)
DCL VAR(&R2) TYPE(*CHAR) LEN(50)
DCL VAR(&R3) TYPE(*CHAR) LEN(50)
DCL VAR(&R4) TYPE(*CHAR) LEN(50)
DCL VAR(&R5) TYPE(*CHAR) LEN(50)
DCL VAR(&R6) TYPE(*CHAR) LEN(50)
DCLF FILE(SCDRPTW)
CLRPFM FILE(SCDRPT)
RUNSQLSTM SRCFILE(SCDRPT/SOURCES) SRCMBR(SCDRPTS_C) +
COMMIT(*NONE)
OPNQRYF FILE((SCDRPTW)) QRYSLT('STATUS='' ''') +
KEYFLD((DATE) (JOB) (JOBNBR) (TIME))
RF: RCVF RCDFMT(REG)
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(CONT))
CHGVAR VAR(&RPT) VALUE(' ')
CHGVAR VAR(&SJOB) VALUE('''' *TCAT &JOB *TCAT '''')
CHGVAR VAR(&SDATE) VALUE(&DATE)
CHGVAR VAR(&STIME) VALUE(&TIME)
CHGVAR VAR(%SST(&RPT 1 10)) VALUE(&JOB)
CHGVAR VAR(%SST(&RPT 14 4)) VALUE(%SST(&SDATE 1 4))
CHGVAR VAR(%SST(&RPT 18 1)) VALUE('-')
CHGVAR VAR(%SST(&RPT 19 2)) VALUE(%SST(&SDATE 5 2))
CHGVAR VAR(%SST(&RPT 21 1)) VALUE('-')
CHGVAR VAR(%SST(&RPT 22 2)) VALUE(%SST(&SDATE 7 2))
CHGVAR VAR(%SST(&RPT 26 2)) VALUE(%SST(&STIME 1 2))
CHGVAR VAR(%SST(&RPT 28 1)) VALUE(':')
CHGVAR VAR(%SST(&RPT 29 2)) VALUE(%SST(&STIME 3 2))
CHGVAR VAR(%SST(&RPT 31 1)) VALUE(':')
CHGVAR VAR(%SST(&RPT 32 2)) VALUE(%SST(&SDATE 5 2))
CHGVAR VAR(%SST(&RPT 36 7)) VALUE(&MSGID)
CHGVAR VAR(%SST(&RPT 44 132)) VALUE(&MSG)
IF COND(&LJOB = &JOB *AND &LUSER = &USER *AND +
&LJOBNBR = &JOBNBR) THEN(DO)
CHGVAR VAR(%SST(&RPT 1 10)) VALUE(' ')
ENDDO
CHGVAR VAR(&RPT) VALUE('''' *TCAT &RPT *TCAT '''')
CHGVAR VAR(&R1) VALUE(%SST(&RPT 1 50))
CHGVAR VAR(&R2) VALUE(%SST(&RPT 51 50))
CHGVAR VAR(&R3) VALUE(%SST(&RPT 101 50))
CHGVAR VAR(&R4) VALUE(%SST(&RPT 151 50))
CHGVAR VAR(&R5) VALUE(%SST(&RPT 201 50))
CHGVAR VAR(&R6) VALUE(%SST(&RPT 251 50))
STRQMQRY QMQRY(SCDRPTS_G) SETVAR((R1 &R1) (R2 &R2) +
(R3 &R3) (R4 &R4) (R5 &R5) (R6 &R6))
STRQMQRY QMQRY(SCDRPTS_S) SETVAR((DATE &SDATE) (JOB +
&SJOB) (TIME &STIME))
CHGVAR VAR(&LJOB) VALUE(&JOB)
CHGVAR VAR(&LUSER) VALUE(&USER)
CHGVAR VAR(&LJOBNBR) VALUE(&JOBNBR)
GOTO RF
CONT:
CLOF OPNID(SCDRPTW)
* * * * * change the folder name (TEMP) ou create it with command CRTFLR TEMP * * * *
CPYTOPCD FROMFILE(SCDRPT) TOFLR(TEMP) +
TODOC(SCDRPT.TXT) REPLACE(*YES)
* * * * * change the following line to put your mail address * * * *
CHGDOCD DOC(SCDRPT.TXT) FLR(TEMP) DOCD('Schedule +
Daily Report')
SNDDST TYPE(*DOC) TOINTNET((me@mydomain)) +
DSTD('Schedule Daily Report') +
DOC(SCDRPT.TXT) FLR(TEMP) +
SUBJECT('Schedule Daily Report')
ENDPGM
5.2. SCDRPTP Schedule Report Preparation
PGM PARM(&MSGQ)
DCL VAR(&MSGQ) TYPE(*CHAR) LEN(10)
DCL VAR(&MSG) TYPE(*CHAR) LEN(200)
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&SEV) TYPE(*DEC) LEN(2 0)
DCL VAR(&MSGDATA) TYPE(*CHAR) LEN(100)
DCL VAR(&SENDER) TYPE(*CHAR) LEN(80)
DCL VAR(&JOB) TYPE(*CHAR) LEN(10)
DCL VAR(&USER) TYPE(*CHAR) LEN(10)
DCL VAR(&JOBNBR) TYPE(*CHAR) LEN(6)
DCL VAR(&JOBSCD) TYPE(*CHAR) LEN(10)
DCL VAR(&CEN) TYPE(*CHAR) LEN(1)
DCL VAR(&DATE) TYPE(*CHAR) LEN(8)
DCL VAR(&TIME) TYPE(*CHAR) LEN(6)
DCL VAR(&SJOB) TYPE(*CHAR) LEN(16)
DCL VAR(&SJOBNBR) TYPE(*CHAR) LEN(12)
DCL VAR(&SUSER) TYPE(*CHAR) LEN(16)
DCL VAR(®) TYPE(*CHAR) LEN(5)
DCL VAR(&SMSGID) TYPE(*CHAR) LEN(13)
DCL VAR(&SMSG1) TYPE(*CHAR) LEN(61)
DCL VAR(&SMSG2) TYPE(*CHAR) LEN(61)
DCL VAR(&SMSG3) TYPE(*CHAR) LEN(61)
MESSAGE:
RCVMSG MSGQ(&MSGQ) MSGTYPE(*FIRST) RMV(*YES) +
MSG(&MSG) MSGDTA(&MSGDATA) MSGID(&MSGID) +
SEV(&SEV) SENDER(&SENDER) SENDERFMT(*SHORT)
IF COND(&MSG = ' ') THEN(GOTO CMDLBL(CONT))
CHGVAR VAR(&JOB) VALUE(%SST(&SENDER 1 10))
CHGVAR VAR(&USER) VALUE(%SST(&SENDER 11 10))
CHGVAR VAR(&JOBNBR) VALUE(%SST(&SENDER 21 6))
CHGVAR VAR(&CEN) VALUE(%SST(&SENDER 43 7))
CHGVAR VAR(&DATE) VALUE('20' *TCAT %SST(&SENDER 44 7))
CHGVAR VAR(&TIME) VALUE(%SST(&SENDER 50 7))
IF COND(&MSGID = 'CPC1236') THEN(DO)
CHGVAR VAR(&JOB) VALUE(%SST(&MSGDATA 44 10))
CHGVAR VAR(&USER) VALUE(%SST(&MSGDATA 54 10))
CHGVAR VAR(&JOBNBR) VALUE(%SST(&MSGDATA 64 6))
ENDDO
IF COND(&MSGID = 'CPI1143') THEN(DO)
CHGVAR VAR(&JOB) VALUE(%SST(&MSGDATA 21 10))
CHGVAR VAR(&USER) VALUE(' ')
CHGVAR VAR(&JOBNBR) VALUE(%SST(&MSGDATA 31 6))
ENDDO
CHGVAR VAR(&SJOB) VALUE('''' *TCAT &JOB *TCAT '''')
CHGVAR VAR(&SJOBNBR) VALUE('''' *TCAT &JOBNBR *TCAT '''')
CHGVAR VAR(&SUSER) VALUE('''' *TCAT &USER *TCAT '''')
CHGVAR VAR(&SMSGID) VALUE('''' *TCAT &MSGID *TCAT '''')
CHGVAR VAR(&SMSG1) VALUE('''' *TCAT %SST(&MSG 1 52))
CHGVAR VAR(&SMSG2) VALUE(%SST(&MSG 53 55))
CHGVAR VAR(&SMSG3) VALUE(%SST(&MSG 112 52) *TCAT '''')
STRQMQRY QMQRY(SCDRPTS_P) SETVAR((JOB &SJOB) (USER +
&SUSER) (JOBNBR &SJOBNBR) (DATE &DATE) +
(TIME &TIME) (MSGID &SMSGID) (MSG1 +
&SMSG1) (MSG2 &SMSG2) (MSG3 &SMSG3))
GOTO CMDLBL(MESSAGE)
CONT:
OVRDBF FILE(SCDRPTW) SHARE(*YES)
CALL PGM(SCDRPTG)
END: ENDPGM
6. Create SQL command to insert Report Header Member SCDRPTS_C, type TXT, Schedule Report SQL Start
INSERT INTO SCDRPT VALUES ('Schedule Daily Report');
INSERT INTO SCDRPT VALUES (' ');
INSERT INTO SCDRPT VALUES (
'Job Date Time MSGID Message');
INSERT INTO SCDRPT VALUES (
'---------- ---------- -------- ------- -------------');
7. Create SQL command to insert records in report file Member SCDRPTS_G, type TXT, Schedule Report SQL Generation
INSERT INTO SCDRPT VALUES (&R1&R2&R3&R4&R5&R6)
8. Create SQL command to insert records in work file Member SCDRPTS_P, type TXT, Schedule Report SQL Work
INSERT INTO SCDRPTW VALUES (&JOB , &USER , &JOBNBR , &DATE , &TIME , ' ' , &MSGID , &MSG1&MSG2&MSG3)
9. Create SQL command to update status of work file. The status is updated for records already processed Member SCDRPTS_S, type TXT, Schedule Report SQL Status
UPDATE SCDRPTW SET STATUS='*' WHERE DATE=&DATE AND JOB=&JOB AND TIME=&TIME
10. Create Queries
CRTQMQRY QMQRY(SCDRPT/SCDRPTS_G) SRCFILE(SCDRPT/SOURCES)
CRTQMQRY QMQRY(SCDRPT/SCDRPTS_P) SRCFILE(SCDRPT/SOURCES)
CRTQMQRY QMQRY(SCDRPT/SCDRPTS_S) SRCFILE(SCDRPT/SOURCES)
11. To run the process
ADDLIBLE SCDRPT
CALL SCDRPTP PARM(QSECOFR)
* QSECOFR is the name of message queue with the jobs messages. If you have jobs in different queues (different users), execute the program once for each one.
You can also schedule this command to run after all your actual jobs.
Here is one example of the report:
Schedule Daily Report
Job Date Time MSGID Message
---------- ---------- -------- ------- -------------
DRELEY 2002-02-21 10:43:02 CPC1236 Job 050317/QSECOFR/DRELEY submitted for job schedule entry DRELEY number 000071.
2002-02-21 10:43:02 CPF1241 Job 050317/QSECOFR/DRELEY completed normally on 21/02/02 at 10:43:03.
If you don't clear the work file (SCDRPTW), you will be able to make analysis of your jobs in the future, for example, to rearrange the schedule times based on duration of jobs during a period of time. I using an iSeries 400 with V4R4 and have used only CL to explore its capabilities.