I was told to provide summary counts by month from two tables
the rows are added to table1 first(current month) then content of monthly
is saved in TABLE2
Hence both tables are identical, and date field record every change down to the microsecond (AUD_INSERT_TS) and the source of the change (if LAST_UPDATE_USER is “...BTCH”, it was a Batch change, otherwise it was Online). But unfortunately that only works for Inserts and Updates (UPDATE_FUNC_IND = ‘I’ or ‘U’). For Deletes (UPDATE_FUNC_IND = ‘D’), the LAST_UPDATE_USER element is actually the value before the change. So the logic below uses LAST_UPDATE_USER only for Insert or Update changes; but if UPDATE_FUNC_IND = ‘D’, it assumes it is a Batch change if it occurred before 6 a.m., otherwise it was an Online change.
TABLE1 (current month)
(current) Colum name Data description
COL1 AUD_INSERT_TS Date that the row was created/modified
COL2 LAST_UPDATE_USER transaction via Batch Process or Online
COL3 UPDATE_FUNC_IND
and
TABLE2 (historic - all other past months)
(current) Colum name Data description
COL1 AUD_INSERT_TS Date that the row was created/modified
COL2 LAST_UPDATE_USER transaction via Batch Process or Online
COL3 UPDATE_FUNC_IND
But I am not sure if there is a better way to write the DB2 query below:
SELECT MONTH(A.AUD_INSERT_TS) AS MTH_YR,
SUM(CASE WHEN (A.LAST_UPDATE_USER LIKE '%BTCH' AND
A.UPDATE_FUNC_IND IN ('I','U'))
OR (HOUR(TIME(A.AUD_INSERT_TS)) < 6 AND
A.UPDATE_FUNC_IND = 'D')
THEN 1 ELSE 0 END) AS CT_BTCH,
SUM(CASE WHEN (A.LAST_UPDATE_USER NOT LIKE '%BTCH' AND
A.UPDATE_FUNC_IND IN ('I','U'))
OR (HOUR(TIME(A.AUD_INSERT_TS)) >= 6 AND
A.UPDATE_FUNC_IND = 'D')
THEN 1 ELSE 0 END) AS CT_ONLN,
COUNT(*) AS CT_ALL
FROM (SELECT * FROM TABLE2 UNION
SELECT * FROM TABLE1) A
WHERE YEAR(A.AUD_INSERT_TS) = 2015
GROUP BY MONTH(A.AUD_INSERT_TS);
MTH_YR CT_BTCH CT_ONLN CT_ALL
1 548997 7052 556049
2 507036 5684 512720
3 561471 7019 568490
4 526635 6568 533203
5 530777 6078 536855
6 80378 2549 82927
Is there a better(more efficient) way to write this query?
Moderators: Kalicharan, Moderator Group
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page