Is there a better(more efficient) way to write this query?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
wandilly
Member
Posts: 1
Joined: Thu Jun 07, 2012 8:01 pm

Is there a better(more efficient) way to write this query?

Post by wandilly » Wed Jun 17, 2015 3:07 am

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

Post Reply

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



Other References
Mainframe Tools and others