I was hoping for a faster answer. Deadlines and all that. Guess I'll
be writing RPG code while I wait for the PTF's to get installed.
Thanks Zak and Elvis for your replies.
Rick
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Wednesday, November 28, 2007 3:04 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL query processor error
Looks like a bug in query optimizer. There are a lot of PTFs relating
to QDT problems.
Get current on db fixpacks and if that doesn't help, open a PMR with
IBM.
Elvis
Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and
OS/400 www.centerfieldtechnology.com
-----Original Message-----
Subject: SQL query processor error
I have an SQL statement that was working fine until I added another
'temp'
table select. The added piece runs fine by itself. I only get an error
when I combine the two pieces.
Running the job in debug I see the following errors in the job log.
*** DBOP open FAILED. Exception from call to SLIC! ***.
Internal failure occurred in query processor.
PROBLEM WITH QDT FOR STMT# 1, QDT# 1, OCLE 1
Trying to Google a solution wasn't any help.
This is the SQL statement. The branches select is the new piece. If
anyone has any ideas I'd really appreciate hearing them.
With IntTypes as (
Select cvLoan as Account, cvIntT as LoanType,
cast(cvBrch as numeric(3,0)) as LoanBranch
from tmods.cvcnvdtl
order by cvLoan),
MaxLoan as (
Select AccountNumber as MaxAcct, Max(LoanNumber) as MaxLoan
from tmods.lbtrupmt
group by AccountNumber
order by AccountNumber),
-- New stuff
Branches as (
select Cast(SubStr(strAccount, 1, 7) as Numeric(7,0)) as
BranchNote,
intLoan as BranchLoan, intBranch as BranchNumber
from tmods.newmm
union all
select Cast(substr(lnCstLnID, 1, 10) as Numeric(7,0)) as
BranchNote,
Cast(SubStr(lnSeqnbr, 1, 4) as Numeric(4,0)) as
BranchLoan,
Cast(SubStr(lnbranch, 1, 4) as Numeric(4,0)) as
BranchNumber
from tmods.lbloans exception join tmods.newmm on
SubStr(lnCstLnID, 1, 7) = SubStr(StrAccount, 1, 7)
order by 1, 2)
-- End new stuff
Select pmBank, pmAcct, pmLoan, pmVoidCd, pmPrcDt, pmEffDt, pmTranCd,
pmPrnAmt, pmIntAmt, pmMscAmt, pmMonTot, pmLnBal, ACFTrnCd1,
Amt1Act, Amt2Act, Amt3Act, Amt4Act, Amt5Act, PrinBal, IntBal,
pmDesc, pmCmt901, pmCmt902, BranchNumber, pmPmtID, pmTranID,
acfLoan, SepTran, ACFTrnCd2, LoanType, memo
from tmods.lbTruPmt join MaxLoan on pmAcct = MaxAcct and pmLoan =
MaxLoan join tmods.cvpmtdef on pmBank = KeyValue1 and pmTranCd =
KeyValue2
join tmods.lbacxref on pmacct = xrLBLoan join IntTypes on acfLoan =
Account join Branches on pmAcct = BranchNote and pmLoan = BranchLoan
where pmacct = 1160583
order by pmAcct, pmPrcDt, pmTranID
Rick Chevalier
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.
midrange.com
