I am just posting the query below that is run against one of the table SLEOCPP that has about 10.5 mill records. When I run the below query it just keeps executing. Rest of the tables that are used in queries has records that ranges from 100K to400K. I am looking for your technical suggestions/ideas, at least something new to work on but for now I am juts stuck. Thansk in advance for your help. The d/b used is DB2/AS400 and I am running this thru iSeries Navigator Tool

select distinct a1.BR_SSN as ssn, b.GR_DAYS_PAST_DUE as dayspastdue,b.GR_DUE_DATE as duedate,b.GR_PARTIAL_PMT_AMT as partialpayment,
a1.LN_CURR_INTEREST as currentinterest,a1.LN_CURR_PRINCIPAL as currentprinciplal,a1.LN_CURR_FEES as currentfees,a1.LN_CURR_INTEREST+a1.LN_CURR_PRINCIP AL+a1.LN_CURR_FEES as totalbalance,
a1.GR_ID,a1.LN_LOAN_TYPE as loantype,c.TY_OFFICIAL_NAME,c.ty_nslds_loan_type_c ode as privatefederal,a1.LN_STATUS as loanstatus,
d.BR_DATE_OF_BIRTH,a1.LN_NAME_FIRST as firstname,a1.LN_NAME_LAST as lastname
from SLSQARDB.SLLNREP a1,SLSQARDB.SLGRREP b, SLSQARDB.SLC1REP c,SLSQARDB.SLBRREP d
where a1.BR_SSN = b.BR_SSN and a1.LN_LOAN_TYPE=c.ty_loan_type and a1.BR_SSN = d.BR_SSN and a1.gr_id=b.gr_id
and ((a1.LN_STATUS like 'F%'or a1.LN_STATUS like 'D%')
and EXISTS (SELECT distinct e.* FROM SLSQARDB.SLF1REP e,SLSQARDB.SLF2REP f
WHERE a1.BR_SSN = e.BR_SSN and e.F1_TYPE_OF_DEFER_FORB = f.F2_TYPE_OF_DEFER_FORB and f.F2_LOW_PAY_ALLOWED != 'B'))
and exists (select distinct a.* from SLSQARDB.SLF1REP a,SLSQARDB.SLEOCPP b
where a1.br_ssn=a.br_ssn and a.br_ssn=b.sm_ssn and a.gr_id=b.sm_group
and date(substring(a.F1_END_DATE,4,2)||'/'|| substring(a.F1_END_DATE,6,2)||'/20'|| substring(a.F1_END_DATE,2,2)) <= date(substring(b.EON8DT,4,2)||'/'|| substring(b.EON8DT,6,2)||'/20'|| substring(b.EON8DT,2,2)))
and NOT EXISTS (SELECT * FROM SLSQARDB.SLLNREP a2
WHERE a2.BR_SSN = a1.BR_SSN AND a2.LN_LOAN_TYPE NOT IN
(SELECT TY_LOAN_TYPE FROM SLSQARDB.SLC1REP WHERE TY_NSLDS_LOAN_TYPE_CODE != ''))
order by 1;

Give the query to the Design Advisor and let it tell you what to do.

Andy

Can you please tell what is "design Advisor" Is it an option in iSeries Navigator? I have worked in MSSQL & Oracle and never with DB2 d/b.
BTW thanks for your reply.

Can you please tell what is "design Advisor" Is it an option in iSeries Navigator? I have worked in MSSQL & Oracle and never with DB2 d/b.
BTW thanks for your reply.

This will help you get started -

Part 1
Using the DB2 V8.2 Design Advisor, Part 1: Expert help for designing OLTP databases

and

Part 2
Using the DB2 V8.2 Design Advisor, Part 2: Expert help for designing data warehouses