Skip to content

Instantly share code, notes, and snippets.

@forstie
Created May 13, 2021
Embed
What would you like to do?
I was asked how SQL could transform Collection Services data, in this case Job Performance Data, into a more consumable form. SQL built-in functions and CASE expressions get the job done.
--
-- Collection Services data file: QAPMJOBL (JOB PERFORMANCE DATA)
--
-- Resource:
-- https://www.ibm.com/docs/en/i/7.4?topic=data-collection-services-files-qapmjobs-qapmjobl
--
--
-- Raw
--
select INTNUM, DTETIM, INTSEC, JBSSYS, JBSLIB, JBNAME, JBUSER, JBNBR, JBACCO, JBTYPE, JBSTYP,
JBTTYP, JBTTYE, JBFLAG, JBS36E, JBPOOL, JBPRTY, JBCPU, JBRSP, JBSLC, JBNTR, JBDBR, JBNDB,
JBWRT, JBAW, JBWI, JBAI, JBPLN, JBPPG, JBPFL, JBLWT, JBLRD, JBDBU, JBCPT, JBCGT, JBSPD,
JBRRT, JBLND, JBCUD, JB2LND, JB2CUD, JBBRG, JBPRG, JBNDW, JBDBW, JBANDW, JBADBW, JBANDR,
JBADBR, JBPW, JBCS, JBPAGF, JBEAO, JBOBIN, JBODEC, JBOFLP, JBIPF, JBWIO, JBIRN, JBDRN, JIOPB,
JIOPA, JBPORT, JBSTN, JBPTSF, JBPTTF, JBEAF, JBPCSF, JBDDMF, JBMRTF, JBROUT, JBAPT, JBNSW,
JBSST, JBQT2, JBCDR, JBCDS, JBAIQT, JBNAIQ, JBRUT, JBNRU, JBQT, JBMMT, JBNEQT, JBPUTN,
JBPUTA, JBGETN, JBGETA, JBPGIN, JBPGIL, JBGGIL, JBRTI, JBRRI, JBSZWT, JBSKSC, JBSKBS, JBSKRC,
JBSKBR, JBXRFR, JBXRFW, JBXSLR, JBXDYR, JBDLCH, JBDLCM, JBSJNM, JBSJUS, JBSJNB, JBSJFG,
JBRSYS, JBDEVN, JBRLNM, JBLLNM, JBMODE, JBRMNT, JBINSX, JBBUP, JBBDL, JBBFE, JBBCO, JBBRO,
JBLBO, JBLBC, JBLBI, JBLBS, JBDQS, JBDQR, JBNDA, JBNUS, JBSIT1, JBSIT2, JBSIT3, JBTCPU,
JBTHDF, JBTHID, JBTHAC, JBTHCT, JBMTXT, JBIBM1, JBSTSF, JBSVIF, JBTFLT, JBEDBC, JBTDBC,
JBSVRT, JBCOP, JBCOS, JBDOP, JBDOS, JBPJE, JBNSJE, JBUJD, JBSJD, JBBFW, JBBFA, JBBTW, JBBTA,
JBTWT, JBTNW, JBXRRR, JBXRRW, JBXRFS, JBXRBR, JBXRBW, JBFSH, JBASH, JBFSHA, JBASHA, JBPGA,
JBPGD, JBCUSR, JBFSOPN, JBFSDC, JBFSNDC, JBFSDD, JBFSNDD, JBACPU, JBIPAF, JBIPAD, JBIPPT,
JBUAUF, JBPGRQ, JBPGRL, JBMSLR, JBMDYR, JBMLCH, JBMLCM, JBMOPN, JBMNDC, JBMNDD, JBSCPU,
JBSTCPU, JBSQLCPU
from QPFRDATA.QAPMJOBL;
--
-- Refined
--
select INTNUM, timestamp_format(DTETIM, 'YYMMDDHHMISS') as Interval_Time, INTSEC, JBSSYS, JBSLIB, JBNAME,
JBUSER, JBNBR, JBACCO,
case JBTYPE
when 'A' then 'Autostart'
when 'B' then 'Batch'
when 'I' then 'Interactive'
when 'M' then 'Subsystem monitor'
when 'R' then 'Spool reader'
when 'S' then 'System'
when 'V' then 'SLIC task'
when 'W' then 'Spool writer'
when 'X' then 'SCPF job'
else jbtype
end as job_type,
case JBSTYP
when 'T' then 'MRT (System/36 environment only)'
when 'E' then 'Evoke (communications batch)'
when 'P' then 'Print driver job'
when 'J' then 'Prestart job'
when 'F' then 'M36 (Advanced/36 server job)'
when 'D' then 'Batch immediate job'
when 'U' then 'Alternative spool user'
else jbstyp
end as job_subtype, JBTTYP, JBTTYE, JBFLAG, JBS36E, JBPOOL, JBPRTY, JBCPU, JBRSP, JBSLC,
JBNTR, JBDBR, JBNDB, JBWRT, JBAW, JBWI, JBAI, JBPLN, JBPPG, JBPFL, JBLWT, JBLRD, JBDBU,
JBCPT, JBCGT, JBSPD, JBRRT, JBLND, JBCUD, JB2LND, JB2CUD, JBBRG, JBPRG, JBNDW, JBDBW, JBANDW,
JBADBW, JBANDR, JBADBR, JBPW, JBCS, JBPAGF, JBEAO, JBOBIN, JBODEC, JBOFLP, JBIPF, JBWIO,
JBIRN, JBDRN, JIOPB, JIOPA, JBPORT, JBSTN, JBPTSF, JBPTTF, JBEAF, JBPCSF, JBDDMF, JBMRTF,
JBROUT, JBAPT, JBNSW, JBSST, JBQT2, JBCDR, JBCDS, JBAIQT, JBNAIQ, JBRUT, JBNRU, JBQT, JBMMT,
JBNEQT, JBPUTN, JBPUTA, JBGETN, JBGETA, JBPGIN, JBPGIL, JBGGIL, JBRTI, JBRRI, JBSZWT, JBSKSC,
JBSKBS, JBSKRC, JBSKBR, JBXRFR, JBXRFW, JBXSLR, JBXDYR, JBDLCH, JBDLCM, JBSJNM, JBSJUS,
JBSJNB, JBSJFG, JBRSYS, JBDEVN, JBRLNM, JBLLNM, JBMODE, JBRMNT, JBINSX, JBBUP, JBBDL, JBBFE,
JBBCO, JBBRO, JBLBO, JBLBC, JBLBI, JBLBS, JBDQS, JBDQR, JBNDA, JBNUS, JBSIT1, JBSIT2, JBSIT3,
JBTCPU, JBTHDF, JBTHID, JBTHAC, JBTHCT, JBMTXT, JBIBM1, JBSTSF, JBSVIF, JBTFLT, JBEDBC,
JBTDBC, JBSVRT, JBCOP, JBCOS, JBDOP, JBDOS, JBPJE, JBNSJE, JBUJD, JBSJD, JBBFW, JBBFA, JBBTW,
JBBTA, JBTWT, JBTNW, JBXRRR, JBXRRW, JBXRFS, JBXRBR, JBXRBW, JBFSH, JBASH, JBFSHA, JBASHA,
JBPGA, JBPGD, JBCUSR, JBFSOPN, JBFSDC, JBFSNDC, JBFSDD, JBFSNDD, JBACPU, JBIPAF, JBIPAD,
JBIPPT, JBUAUF, JBPGRQ, JBPGRL, JBMSLR, JBMDYR, JBMLCH, JBMLCM, JBMOPN, JBMNDC, JBMNDD,
JBSCPU, JBSTCPU, JBSQLCPU
from QPFRDATA.QAPMJOBL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment