Optimizing a DISTINCT 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
Active Member
Posts: 67
Joined: Thu Apr 05, 2012 1:29 am
Location: WI, USA

Optimizing a DISTINCT query

Post by goldyroshan » Tue Feb 24, 2015 1:46 am

Here is the query I'm trying to run -

Code: Select all

SELECT DISTINCT A.Field1, A.Field2, A.Field3
FROM Creator.Table1 A, Creator.Table2 B
WHERE A.Field4 = 'LA'
AND A.Field5_1 = B.Field5_2 [Field 5_1 : of Table1, Field 5_2 : of Table2]
AND A.Field6 = 0
AND B.Field7 IN ('1,'2','3','4','5')
AND A.Field8 IN ('11','12','13','14','15)' WITH UR; 
But regardless of whether I run it through QMF or a Batch Job, I get a time-out.
Please suggest ways of improving this query.

I also tried the same by removing DISTINCT, still no success.

Note : Both the tables have a huge volume of data.
Logically yours,
:-) GRS :-)

Posts: 48
Joined: Tue May 06, 2014 11:22 am

Re: Optimizing a DISTINCT query

Post by dhiraj » Fri Feb 27, 2015 10:59 pm

goldyroshan wrote: ......

AND A.Field5_1 = B.Field5_2

AND A.Field8 IN ('11','12','13','14','15)' WITH UR;
Typo AND A.Field8 IN ('11','12','13','14','15') WITH UR;

Try to JOIN on the basis of A.Field5_1 = B.Field5_2 ..
--Dhiraj Mishra


Topic deleted by Admin

Post by academyindia4 » Fri Jan 15, 2016 8:49 pm

<< Content deleted By Admin >>

Post Reply


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.
Mainframe Interview questions

Other References
Mainframe Tools and others