Join IIUG
 for   
 

Informix News
18 Nov 13 - ZDNet - Top 20 mobile skills in demand... Read
09 Sep 13 - telecompaper - Shaspa and Tatung have shown a new smart home platform at Ifa in Berlin. Powered by the IBM Informix software... Read
06 Sep 13 - IBM data magazine - Mission Accomplished - Miami, Florida will be the backdrop for the 2014 IIUG Informix Conference... Read
01 Feb 13 - IBM Data Magazine - Are your database backups safe? Lester Knutsen (IBM Champion) writes about database back up safety using "archecker"... Read
14 Nov 12 - IBM - IBM's Big Data For Smart Grid Goes Live In Texas... Read
3 Oct 12 - The Financial - IBM and TransWorks Collaborate to Help Louisiana-Pacific Corporation Achieve Supply Chain Efficiency... Read
28 Aug 12 - techCLOUD9 - Splunk kicks up a SaaS Storm... Read
10 Aug 12 - businessCLOUD9 - Is this the other half of Cloud monitoring?... Read
3 Aug 12 - IBM data management - Supercharging the data warehouse while keeping costs down IBM Informix Warehouse Accelerator (IWA) delivers superior performance for in-memory analytics processing... Read
2 Aug 12 - channelbiz - Oninit Group launches Pay Per Pulse cloud-based service... Read
28 May 12 - Bloor - David Norfolk on the recent Informix benchmark "pretty impressive results"... Read
23 May 12 - DBTA - Informix Genero: A Way to Modernize Informix 4GL Applications... Read
9 Apr 12 - Mastering Data Management - Upping the Informix Ante: Advanced Data Tools... Read
22 Mar 12 - developerWorks - Optimizing Informix database access... Read
14 Mar 12 - BernieSpang.com - International Informix User Group set to meet in San Diego... Read
1 Mar 12 - IBM Data Management - IIUG Heads West for 2012 - Get ready for sun and sand in San Diego... Read
1 Mar 12 - IBM Data Management - Running Informix on Solid-State Drives.Speed Up Database Access... Read
26 Feb 12 - BernieSpan.com - Better results, lower cost for a broad set of new IBM clients and partners... Read
24 Feb 12 - developerWorks - Informix Warehouse Accelerator: Continuous Acceleration during Data Refresh... Read
6 Feb 12 - PRLOG - Informix port delivers unlimited database scalability for popular SaaS application ... Read
2 Feb 12 - developerWorks - Loading data with the IBM Informix TimeSeries Plug-in for Data Studio... Read
1 Feb 12 - developerWorks - 100 Tech Tips, #47: Log-in to Fix Central... Read
13 Jan 12 - MC Press online - Informix Dynamic Server Entices New Users with Free Production Edition ... Read
11 Jan 12 - Computerworld - Ecologic Analytics and Landis+Gyr -- Suitors Decide to Tie the Knot... Read
9 Jan 12 - planetIDS.com - DNS impact on Informix / Impacto do DNS no Informix... Read
8 Sep 11 - TMCnet.com - IBM Offers Database Solution to Enable Smart Meter Data Capture... Read
1 Aug 11 - IBM Data Management Magazine - IIUG user view: Happy 10th anniversary to IBM and Informix... Read
8 Jul 11 - Database Trends and Applications - Managing Time Series Data with Informix... Read
31 May 11 - Smart Grid - The meter data management pitfall utilities are overlooking... Read
27 May 11 - IBM Data Management Magazine - IIUG user view: Big data, big time ( Series data, warehouse acceleration, and 4GLs )... Read
16 May 11 - Business Wire - HiT Software Announces DBMoto for Enterprise Integration, Adds Informix. Log-based Change Data Capture... Read
21 Mar 11 - Yahoo! Finance - IBM and Cable&Wireless Worldwide Announce UK Smart Energy Cloud... Read
14 Mar 11 - MarketWatch - Fuzzy Logix and IBM Unveil In-Database Analytics for IBM Informix... Read
11 Mar 11 - InvestorPlace - It's Time to Give IBM Props: How many tech stocks are up 53% since the dot-com boom?... Read
9 Mar 11 - DBTA - Database Administration and the Goal of Diminishing Downtime... Read
2 Feb 11 - DBTAs - Informix 11.7 Flexible Grid Provides a Different Way of Looking at Database Servers... Read
27 Jan 11 - exactsolutions - Exact to Add Informix Support to Database Replay, SQL Monitoring Solutions... Read
25 Jan 11 - PR Newswire - Bank of China in the UK Works With IBM to Become a Smarter, Greener Bank... Read
12 Oct 10 - Database Trends and Applications - Informix 11.7: The Beginning of the Next Decade of IBM Informix... Read
20 Sep 10 - planetIDS.com - ITG analyst paper: Cost/Benefit case for IBM Informix as compared to Microsoft SQL Server... Read
20 Jul 10 - IBM Announcements - IBM Informix Choice Edition V11.50 helps deploy low-cost scalable and reliable solutions for Apple Macintosh and Microsoft Windows... Read
20 Jul 10 - IBM Announcements - Software withdrawal: Elite Support for Informix Ultimate-C Edition... Read
24 May 10 - eWeek Europe - IBM Supplies Database Tech For EU Smart Grid... Read
23 May 10 - SiliconIndia - IBM's smart metering system allows wise use of energy... Read
21 May 10 - CNET - IBM to help people monitor energy use... Read
20 May 10 - ebiz - IBM Teams With Hildebrand To Bring Smart Metering To Homes Across Britain... Read
19 May 10 - The New Blog Times - Misurare il consumo energetico: DEHEMS è pronto... Read
19 May 10 - ZDNet - IBM software in your home? Pact enables five-city smart meter pilot in Europe... Read
17 March 10 - ZDNet (blog) David Morgenstern - TCO: New research finds Macs in the enterprise easier, cheaper to manage than... Read
17 March 2010 - Virtualization Review - ...key components of Big Blue's platform to the commercial cloud such as its WebSphere suite of application ser vers and its DB2 and Informix databases... Read
10 February 2010 - The Wall Street Journal - International Business Machines is expanding an initiative to win over students and professors on its products. How do they lure the college crowd?... Read

End of Support Dates
IDS 11.10 - 30 Sep 2012
Subscribe to notifications

IIUG on Facebook IIUG on Twitter

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum

Re: Odd behavior with IDS11 optimizer .....Is ....

Posted By: jpierrot@chubb.com
Date: Wednesday, 1 September 2010, at 6:03 p.m.

In Response To: Re: Odd behavior with IDS11 optimizer .....Is .... (Obnoxio The Clown)

The problem seems to be with the second piece of the query or the union=
.

QUERY:
------
SELECT distinct BP.BP_GIN SA_BP_GIN , B.BP_GIN ACCT_BP_GIN,
A.APPORGID,A.PRDCRORGID,A.APPID

,A.PARNTAPPID,A.CLASSCD,B.ACCTNUM,B.NAME scAcctName, '' scOptSicCd,=

A.MKTCD scMarket,A.MRLINE

,A.POLTYPE,A.APPTYPE scAppType,TRIM(A.LOBCD) scLOB,
A.DATE_EFF,DATEEXPIRE,A.TOTAL_ASSETS

, B.MSTRACCT, A.EXTWIZSCORE,A.MEDMALWIZSCORE,
A.OBJWIZSCORE,A.OBJREASON, A.OBJOVERRIDE

,A.SUBWIZSCORE,TRIM(A.TRNWIZSCORE) scTrnWizScore,
A.APPSTATUS,A.BROKERCONTACTIND

,C.NAME scPrdName,D.DSC scStatus,A.RECVDATE,A.FILEDESTRUCTIND
scDestructInd1,B.NFPID

,A.REVSHEET, A.INDEXPRATE, A.ENVEXPRATE, A.ENVRESPRATE, A.LRCR,
A.EXTRATE,A.PRDCONTPERID

,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL
UWFullName,A.UWPERID ncUWId

,A.ACCTREVIEWED, A.CLAIMREVIEWED,A.FILEDESTRUCTIND scDestructInd2,
B.DOMSTATECD, A.SIZECAT

,A.BOXNO, A.MKTSEG, BI_TRACK, ADM_TRACK,'' NYFTZ, 0 NYFTZ_CLASS,''
TAXSTATUS, '' TAX_EX_STATCD

, '' EXEMPTION_TY_CD,A.PRDCTCD

, TRIM(A.LOBCD)|| '-'||TRIM(A.PRDCTCD)|| ' / '||TRIM(H.PRDCTSHRTNAM=
E)
scLobCdPrdctCd

,A.QUOTEBY,TRIM('') scOptPrdctCd,'' scOptStatus, '' scOptLOB, ''
scOptBrkrContInd

, DATE(NULL) EFFDATE, DATE(NULL) EXPDATE, '' scOptClassCd, 0
ncOptUWPerId, '' scOptMktSeg

, '' scOptFileLoc,DATE(NULL) dcOptQuoteBy, '' scOptMarket, A.PROGRA=
MID,
D.STATCD, D.SORTORD

,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL
sOptUW,TRIM(PREQUOTEQPC) Pre_Quote_Qpc

, PYAUDITQPC, PYAUDITCOMMENT, C.FAX, A.BOR_IND scBorInd, A.BOR_IND
scOptBorInd, A.SUBKIND

,0 SERVBRANCHNUM, TRIM('') BRANCHABBR,0 ncSLPrdcrOrgId, 0
SLPRDCONTPERID,A.RTLBRKRID,A.RTLBRKRCONTID

, '' ASRFullName, '' CSRFullName, 0 ncASRId,0 ncCSRId,0 ncOptStatus=
Cd,
0 dmy01, P.RENSTATCD, P.CARRINIT

, P.FORMPOLNUM, H.EMP_CNT_REQ,C.TOTALREVENUE, C.ESTABLISHEDDATE,
B.CMP_EMPL_CNT, TODAY dtSysServerDate

, B.OWNERSHIP,'' policy_types,' ' UWAFullName,0 PERID, '' scIndustr=
y,
IO.COMPUSTAT_GICS_KEY
FROM APP A

, ORG B

, ORG C

, OUTER (POL P

, OUTER EMPLOYEE Z)

, STAT D

, EMPLOYEE F

, OUTER QPC Q

, ORG_PRODUCER I

,PRDCT H

, OUTER ORG_INSURED IO

, outer allnc_bp bp
WHERE(A.APPORGID =3D B.ORGID)
AND A.PRDCRORGID=3DC.ORGID
AND A.APPID =3D P.APPID
AND A.PRDCTCD =3D P.PRDCTCD
AND A.APPSTATUS=3DD.STATCD
AND A.APPSTATUS !=3D 40
AND A.UWPERID=3DF.PERID
AND A.APPID=3DQ.APPID
AND C.ORGID =3D I.ORGID
AND A.PRDCTCD =3D H.PRDCTCD
AND P.UWCONTPERID=3DZ.PERID
AND NOT EXISTS (SELECT 1 FROM OPT Z WHERE A.APPID=3DZ.APPID)
AND B.NAME Like 'AGT%'
AND B.ORGID =3D IO.ORGID
and b.bp_gin =3D bp.bp_gin

UNION ALL

SELECT distinct BP.BP_GIN SA_BP_GIN , B.BP_GIN ACCT_BP_GIN, A.APPORGID=
,
A.PRDCRORGID

, A.APPID, A.PARNTAPPID, A.CLASSCD, B.ACCTNUM, B.NAME scAcctName,
E.SICCD scOptSicCd

, A.MKTCD scMarket,A.MRLINE, A.POLTYPE,E.APPTYPCD scAppType,E.LOBCD=

scLOB, A.DATE_EFF

,A.DATEEXPIRE, A.TOTAL_ASSETS, B.MSTRACCT,A.EXTWIZSCORE,
A.MEDMALWIZSCORE, A.OBJWIZSCORE

, A.OBJREASON,A.OBJOVERRIDE,A.SUBWIZSCORE, TRIM(A.TRNWIZSCORE)
scTrnWizScore

, A.APPSTATUS,A.BROKERCONTACTIND,C.NAME scPrdName, E.STATUS scStatu=
s,
A.RECVDATE

, A.FILEDESTRUCTIND scDestructInd1,B.NFPID, A.REVSHEET, A.INDEXPRAT=
E,
A.ENVEXPRATE

,A.ENVRESPRATE,A.LRCR, A.EXTRATE, E.PRDCONTPERID

,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL
name01,E.UWPERID ncUWId, A.ACCTREVIEWED

, A.CLAIMREVIEWED,A.FILEDESTRUCTIND scDestructInd2,B.DOMSTATECD,
A.SIZECAT,A.BOXNO, A.MKTSEG

, BI_TRACK, ADM_TRACK,E.GEOCD NYFTZ, E.NYFTZ_CLASS,B.TAXSTATUS,
B.TAX_EX_STATCD, OI.EXEMPTION_TY_CD

,E.PRDCTCD, E.LOBCD||'-'||E.PRDCTCD||' / '||TRIM(H.PRDCTSHRTNAME)
scLobCdPrdctCd,A.QUOTEBY

, TRIM(E.PRDCTCD) scOptPrdctCd,E.STATUS scOptStatus, TRIM(E.LOBCD)
scOptLOB

, E.BROKERCONTACTIND scOptBrkrContInd,E.EFFDATE EFFDATE,E.EXPDATE
EXPDATE, TRIM(E.CLASSCD) scOptClassCd

, E.UWPERID ncOptUWPerId, TRIM(E.MKTSEG) scOptMktSeg,TRIM(E.FILELOC=
)
scOptFileLoc, E.QUOTEBY dcOptQuoteBy

, E.MKTCD scOptMarket, A.PROGRAMID, 0 ncOptStatusCd, 0 dmy01

,F.LASTNAME || ', ' || F.FIRSTNAME || ' ' || F.MIDDLEINITIAL sOptU=
W

,TRIM(PREQUOTEQPC) Pre_Quote_Qpc, PYAUDITQPC, PYAUDITCOMMENT, C.FAX=
,
A.BOR_IND scBorInd

, A.BOR_IND scOptBorInd,A.SUBKIND,E.SERVBRANCHNUM, G.BRANCHABBR,
SLPRDCRORGID ncSLPrdcrOrgId

, SLPRDCONTPERID,A.RTLBRKRID,A.RTLBRKRCONTID,BP1.last_na || ', ' ||=

BP1.first_na || ' ' || BP1.mid_init ASRFullName

, BP2.last_na || ', ' || BP2.first_na || ' ' || BP2.mid_init
CSRFullName,F.PERID ncASRId

, F.PERID ncCSRId,S.STATCD xcOptStatusCd, S.SORTORD, P.RENSTATCD,
P.CARRINIT,P.FORMPOLNUM, H.EMP_CNT_REQ

,C.TOTALREVENUE, C.ESTABLISHEDDATE, B.CMP_EMPL_CNT,TODAY
dtSysServerDate, B.OWNERSHIP

, sp_poltype(E.appid, E.prdctcd) policy_types,BP3.last_na || ', ' |=
|
BP3.first_na || ' ' || BP3.mid_init UWAFullName

,F.PERID, TRIM(E.INDRYCD) scIndustry, OI.COMPUSTAT_GICS_KEY
FROM APP A

,ORG B

,ORG C

, ORG_INSURED OI

, OPT E

, OUTER (POL P

, OUTER EMPLOYEE Z)

, EMPLOYEE F

, OUTER QPC Q

,OUTER CHB_BRANCH G

, ORG_PRODUCER I

, PRDCT H

, OUTER STAT S

,OUTER (OPT_ROLE OR1

, BP_NAME BP1)

, OUTER (OPT_ROLE OR2

, BP_NAME BP2)

, OUTER (OPT_ROLE OR3

, BP_NAME BP3)

, outer allnc_bp bp
WHERE(A.APPORGID =3D B.ORGID)
AND B.ORGID =3D OI.ORGID
AND A.PRDCRORGID=3DC.ORGID
AND E.APPID =3D P.APPID
AND E.PRDCTCD =3D P.PRDCTCD
AND E.UWPERID =3DF.PERID
AND E.STATUS =3D S.DSC
AND E.STATUS!=3D 'COMBINED'
AND A.APPID =3D Q.APPID
AND A.APPID =3D E.APPID
AND E.PRDCTCD =3D H.PRDCTCD
AND P.UWCONTPERID=3DZ.PERID
AND E.APPID =3D OR1.APPID
AND E.PRDCTCD =3D OR1.PRDCTCD
AND E.APPID =3D OR2.APPID
AND E.PRDCTCD =3D OR2.PRDCTCD
AND OR1.BP_GIN =3D BP1.BP_GIN
AND OR1.OPT_ROLE_C =3D 'ASR'
AND OR1.EXP_D > TODAY
AND OR2.BP_GIN =3D BP2.BP_GIN
AND OR2.OPT_ROLE_C =3D'CSR'
AND OR2.EXP_D > TODAY
AND BP1.EXP_D > TODAY
AND BP2.EXP_D > TODAY
AND BP3.EXP_D > TODAY
AND C.ORGID =3D I.ORGID
AND G.BRANCHNUM =3D E.SERVBRANCHNUM
AND OR3.BP_GIN =3D BP3.BP_GIN
AND OR3.OPT_ROLE_C =3D 'UWA'
AND OR3.EXP_D > TODAY
AND B.NAME Like 'AGT%'
AND E.APPID =3D OR3.APPID
AND E.PRDCTCD =3D OR3.PRDCTCD
and b.bp_gin =3D bp.bp_gin

ORDER BY 9, 5

into temp xx_pbm with no log

=

From: "Obnoxio The Clown" <obnoxio@serendipita.com> =

=

To: ids@iiug.org =

=

Date: 09/01/2010 05:58 PM =

=

Subject: Re: Odd behavior with IDS11 optimizer .....Is .... [2110=
7]

=

Sent by: ids-bounces@iiug.org =

=

jpierrot@chubb.com wrote:
> Yes, did drop distributions too.

Is there any chance of seeing the SQL

--
Cheers,
Obnoxio The Clown

http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

***********************************************************************=
********

Forum Note: Use "Reply" to post a response in the discussion forum.

=

Messages In This Thread

[ View Thread ] [ Post Response ] [ Return to Index ] [ Read Prev Msg ] [ Read Next Msg ]

IDS Forum is maintained by Administrator with WebBBS 5.12.