DB2 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
mithunthakre
Member
Posts: 8
Joined: Mon Feb 27, 2006 4:31 pm
Location: India

DB2 Query

Post by mithunthakre » Fri Dec 07, 2007 6:10 pm

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.

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

Post by Krishna » Mon Dec 10, 2007 11:18 am

Try following

GROUP BY DATE(CLASS1_TIME_BEG),CORRNAME
HAVING COUNT(*) > 1

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