I need to find Failed Jobs from Mainframe DB2 Table.
In the table three fileds(JOBNAME START_TIMESTAMP , END_TIMESTAMP)
on this basis i want to search failed jobs from this.
when the Job abend on that day there are 2 entry for that job in the table for successful exectution only one entry.
I wrote this query.
Query:
SELECT COUNT(CORRNAME),CORRNAME,DATE(CLASS1_TIME_BEG)
FROM WEDW.WIMT017
WHERE CORRNAME <= 'WSWN95X0' AND CORRNAME >= 'WSWN41X0'
AND DATE(CLASS1_TIME_BEG) >= '2007-09-01'
AND DATE(CLASS1_TIME_BEG) <= '2007-09-30'
GROUP BY CORRNAME,DATE(CLASS1_TIME_BEG)
HAVING COUNT(CORRNAME) >1
Result:
Count CORRNAME DATE
--+---------+---------+-------
4 WSWN4102 2007-09-01
4 WSWN4102 2007-09-04
4 WSWN4102 2007-09-05
4 WSWN4102 2007-09-06
4 WSWN4102 2007-09-07
4 WSWN4102 2007-09-08
4 WSWN4102 2007-09-11
4 WSWN4102 2007-09-12
CORRNAME
--+---------+---------+-------
4 WSWN4102 2007-09-13
4 WSWN4102 2007-09-14
4 WSWN4102 2007-09-15
4 WSWN4102 2007-09-18
4 WSWN4102 2007-09-19
4 WSWN4102 2007-09-20
4 WSWN4102 2007-09-21
3 WSWN4305 2007-09-01
3 WSWN4305 2007-09-01
3 WSWN4305 2007-09-04
3 WSWN4305 2007-09-05
3 WSWN4305 2007-09-06
3 WSWN4305 2007-09-07
3 WSWN4305 2007-09-08
3 WSWN4305 2007-09-11
3 WSWN4305 2007-09-12
3 WSWN4305 2007-09-13
3 WSWN4305 2007-09-14
3 WSWN4305 2007-09-15
3 WSWN4305 2007-09-18
This query gave me the result fro Each day not for that failed jobs.
I want only Failed job History (names & date.)
Please help me out for this Query.
DB2 Query
Moderators: Kalicharan, Moderator Group
Try following
GROUP BY DATE(CLASS1_TIME_BEG),CORRNAME
HAVING COUNT(*) > 1
GROUP BY DATE(CLASS1_TIME_BEG),CORRNAME
HAVING COUNT(*) > 1
Regards,
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
Krishna
Facebook --> http://www.facebook.com/mainframegurukul
Connect with RAMESH KRISHNA REDDY --> http://www.linkedin.com/in/rameshkrishnareddy
Twitter --> https://twitter.com/mainframegurkul
http://www.mainframetutorials.com
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