Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Red face Unanswered: Querying against table that has 10.5 mill records

    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;

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Give the query to the Design Advisor and let it tell you what to do.

    Andy

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Quote Originally Posted by ARWinner
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    3
    Quote Originally Posted by ARWinner
    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.

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •