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

Odd behavior with IDS11 optimizer .....Is there an

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

I have a query that works fine with IDS7.31.UD8 but taking forever with=

IDS11.FC6.W2 on AIX5.3. Both structures are the same except the engine
version. Update statistics/oncheck(s) were also run after the conversio=
n to
IDS11. Is there any other settings that may influence the optimizer pat=
h or
make it behave differently. The problem seems to be in the second SQL i=
n
the union. See sql statement at the end of the email.

Values in onconfig for IDS11

OPTCOMPIND 0
OPT_GOAL -1
DIRECTIVES 1
EXT_DIRECTIVES 0
IFX_FOLDVIEW 0

VALUES IN ONCONFIG for IDS7
OPTCOMPIND 0
OPT_GOAL -1
DIRECTIVES 1

Look at the estimated cost below for IDS11.FC6.W2 versus IDS7.31.UD8

The first plan is from IDS11

Estimated Cost: 9223372036854775807
Estimated # of Rows Returned: 9223372036854775807
Temporary Files Required For: Order By

1) informix.b: INDEX PATH

(1) Index Name: sysadm.ix_org5

Index Keys: name

Lower Index Filter: informix.b.name LIKE 'AGT%'

2) informix.a: INDEX PATH

Filters: (informix.a.appstatus !=3D 40 AND NOT EXISTS <subquery=
> )

(1) Index Name: sysadm.ix_app3

Index Keys: apporgid date_eff lobcd

Lower Index Filter: informix.a.apporgid =3D informix.b.orgid
NESTED LOOP JOIN

3) informix.i: INDEX PATH

(1) Index Name: sysadm. 515_699

Index Keys: orgid (Key-Only)

Lower Index Filter: informix.a.prdcrorgid =3D informix.i.orgid
NESTED LOOP JOIN

4) informix.c: INDEX PATH

(1) Index Name: sysadm. 627_1011

Index Keys: orgid

Lower Index Filter: informix.c.orgid =3D informix.i.orgid
NESTED LOOP JOIN

5) informix.h: INDEX PATH

(1) Index Name: informix. 767_1502

Index Keys: prdctcd

Lower Index Filter: informix.a.prdctcd =3D informix.h.prdctcd
NESTED LOOP JOIN

6) informix.d: INDEX PATH

(1) Index Name: sysadm.stat01u

Index Keys: statcd (Serial, fragments: ALL)

Lower Index Filter: informix.a.appstatus =3D informix.d.statcd
NESTED LOOP JOIN

7) informix.f: INDEX PATH

(1) Index Name: sysadm.i01employee

Index Keys: perid (Serial, fragments: ALL)

Lower Index Filter: informix.a.uwperid =3D informix.f.perid
NESTED LOOP JOIN

8) informix.io: INDEX PATH

(1) Index Name: sysadm. 739_1382

Index Keys: orgid

Lower Index Filter: informix.b.orgid =3D informix.io.orgid
NESTED LOOP JOIN

9) informix.q: INDEX PATH

(1) Index Name: sysadm. 540_740

Index Keys: appid

Lower Index Filter: informix.a.appid =3D informix.q.appid
NESTED LOOP JOIN

10) informix.p: INDEX PATH

(1) Index Name: sysadm.i12pol

Index Keys: appid prdctcd (Serial, fragments: ALL)

Lower Index Filter: (informix.a.appid =3D informix.p.appid AND
informix.a.prdctcd =3D informix.p.prdctcd )
NESTED LOOP JOIN

11) informix.z: INDEX PATH

(1) Index Name: sysadm.i01employee

Index Keys: perid (Key-Only) (Serial, fragments: ALL)

Lower Index Filter: informix.p.uwcontperid =3D informix.z.perid=

NESTED LOOP JOIN

12) informix.bp: INDEX PATH

(1) Index Name: sysadm. 1218_6096

Index Keys: bp_gin (Key-Only)

Lower Index Filter: informix.b.bp_gin =3D informix.bp.bp_gin
NESTED LOOP JOIN

Subquery:

---------

Estimated Cost: 1

Estimated # of Rows Returned: 1

1) informix.z: INDEX PATH

(1) Index Name: sysadm.i08_opt

Index Keys: appid status (Key-Only) (Serial, fragments: =
ALL)

Lower Index Filter: informix.z.appid =3D informix.a.appid

UDRs in query:

--------------

UDR id : 155

UDR name: sp_poltype

Union Query:
------------

1) informix.f: SEQUENTIAL SCAN

2) informix.e: INDEX PATH

Filters: informix.e.status !=3D 'COMBINED'

(1) Index Name: sysadm.i06opt

Index Keys: uwperid effdate

Lower Index Filter: informix.e.uwperid =3D informix.f.perid
NESTED LOOP JOIN

3) informix.a: INDEX PATH

(1) Index Name: sysadm. 408_493

Index Keys: appid

Lower Index Filter: informix.a.appid =3D informix.e.appid
NESTED LOOP JOIN

4) informix.h: INDEX PATH

(1) Index Name: informix. 767_1502

Index Keys: prdctcd

Lower Index Filter: informix.e.prdctcd =3D informix.h.prdctcd
NESTED LOOP JOIN

5) informix.b: INDEX PATH

Filters: informix.b.name LIKE 'AGT%'

(1) Index Name: sysadm. 627_1011

Index Keys: orgid

Lower Index Filter: informix.a.apporgid =3D informix.b.orgid
NESTED LOOP JOIN

6) informix.i: INDEX PATH

(1) Index Name: sysadm. 515_699

Index Keys: orgid (Key-Only)

Lower Index Filter: informix.a.prdcrorgid =3D informix.i.orgid
NESTED LOOP JOIN

7) informix.oi: INDEX PATH

(1) Index Name: sysadm. 739_1382

Index Keys: orgid

Lower Index Filter: informix.b.orgid =3D informix.oi.orgid
NESTED LOOP JOIN

8) informix.c: INDEX PATH

(1) Index Name: sysadm. 627_1011

Index Keys: orgid

Lower Index Filter: informix.c.orgid =3D informix.i.orgid
NESTED LOOP JOIN

9) informix.g: INDEX PATH

(1) Index Name: sysadm. 517_708

Index Keys: branchnum

Lower Index Filter: informix.g.branchnum =3D informix.e.servbra=
nchnum

NESTED LOOP JOIN

10) informix.s: INDEX PATH

(1) Index Name: sysadm.ix_op_statdesc

Index Keys: dsc

Lower Index Filter: informix.e.status =3D informix.s.dsc
NESTED LOOP JOIN

11) informix.bp: INDEX PATH

(1) Index Name: sysadm. 1218_6096

Index Keys: bp_gin (Key-Only)

Lower Index Filter: informix.b.bp_gin =3D informix.bp.bp_gin
NESTED LOOP JOIN

12) informix.p: INDEX PATH

(1) Index Name: sysadm.i12pol

Index Keys: appid prdctcd (Serial, fragments: ALL)

Lower Index Filter: (informix.e.appid =3D informix.p.appid AND
informix.e.prdctcd =3D informix.p.prdctcd )
NESTED LOOP JOIN

13) informix.z: INDEX PATH

(1) Index Name: sysadm.i01employee

Index Keys: perid (Key-Only) (Serial, fragments: ALL)

Lower Index Filter: informix.p.uwcontperid =3D informix.z.perid=

NESTED LOOP JOIN

14) informix.or1: INDEX PATH

Filters: (informix.or1.opt_role_c =3D 'ASR' AND informix.or1.ex=
p_d >
TODAY )

(1) Index Name: sysadm.ix_optrole01

Index Keys: appid prdctcd

Lower Index Filter: (informix.e.appid =3D informix.or1.appid AN=
D
informix.e.prdctcd =3D informix.or1.prdctcd )
NESTED LOOP JOIN

15) informix.bp1: INDEX PATH

Filters: informix.bp1.exp_d > TODAY

(1) Index Name: informix. 697_2115

Index Keys: bp_gin

Lower Index Filter: informix.or1.bp_gin =3D informix.bp1.bp_gin=

NESTED LOOP JOIN

16) informix.or2: INDEX PATH

Filters: (informix.or2.opt_role_c =3D 'CSR' AND informix.or2.ex=
p_d >
TODAY )

(1) Index Name: sysadm.ix_optrole01

Index Keys: appid prdctcd

Lower Index Filter: (informix.e.appid =3D informix.or2.appid AN=
D
informix.e.prdctcd =3D informix.or2.prdctcd )
NESTED LOOP JOIN

17) informix.bp2: INDEX PATH

Filters: informix.bp2.exp_d > TODAY

(1) Index Name: informix. 697_2115

Index Keys: bp_gin

Lower Index Filter: informix.or2.bp_gin =3D informix.bp2.bp_gin=

NESTED LOOP JOIN

18) informix.or3: INDEX PATH

Filters: (informix.or3.opt_role_c =3D 'UWA' AND informix.or3.ex=
p_d >
TODAY )

(1) Index Name: sysadm.ix_optrole01

Index Keys: appid prdctcd

Lower Index Filter: (informix.e.appid =3D informix.or3.appid AN=
D
informix.e.prdctcd =3D informix.or3.prdctcd )
NESTED LOOP JOIN

19) informix.bp3: INDEX PATH

Filters: informix.bp3.exp_d > TODAY

(1) Index Name: informix. 697_2115

Index Keys: bp_gin

Lower Index Filter: informix.or3.bp_gin =3D informix.bp3.bp_gin=

NESTED LOOP JOIN

20) informix.q: INDEX PATH

(1) Index Name: sysadm. 540_740

Index Keys: appid

Lower Index Filter: informix.a.appid =3D informix.q.appid
NESTED LOOP JOIN

UDRs in query:
--------------

UDR id : 155

UDR name: sp_poltype
UDRs in query:
--------------

UDR id : 155

UDR name: sp_poltype

The second plan is from IDS7.31

Estimated Cost: 562
Estimated # of Rows Returned: 16
Temporary Files Required For: Order By

1) informix.b: INDEX PATH

(1) Index Keys: name

Lower Index Filter: informix.b.name LIKE 'AGT%'

2) informix.a: INDEX PATH

Filters: (informix.a.appstatus !=3D 40 AND NOT EXISTS <subquery> )

(1) Index Keys: apporgid date_eff lobcd

Lower Index Filter: informix.a.apporgid =3D informix.b.orgid
NESTED LOOP JOIN

3) informix.c: INDEX PATH

(1) Index Keys: orgid

Lower Index Filter: informix.c.orgid =3D informix.a.prdcrorgid
NESTED LOOP JOIN

4) informix.i: INDEX PATH

(1) Index Keys: orgid (Key-Only)

Lower Index Filter: informix.i.orgid =3D informix.c.orgid
NESTED LOOP JOIN

5) informix.h: INDEX PATH

(1) Index Keys: prdctcd

Lower Index Filter: informix.h.prdctcd =3D informix.a.prdctcd
NESTED LOOP JOIN

6) informix.f: INDEX PATH

(1) Index Keys: perid (Serial, fragments: ALL)

Lower Index Filter: informix.f.perid =3D informix.a.uwperid
NESTED LOOP JOIN

7) informix.d: INDEX PATH

(1) Index Keys: statcd (Serial, fragments: ALL)

Lower Index Filter: informix.d.statcd =3D informix.a.appstatus
NESTED LOOP JOIN

8) informix.io: INDEX PATH

(1) Index Keys: orgid

Lower Index Filter: informix.io.orgid =3D informix.b.orgid
NESTED LOOP JOIN

9) informix.q: INDEX PATH

(1) Index Keys: appid

Lower Index Filter: informix.q.appid =3D informix.a.appid
NESTED LOOP JOIN

10) informix.p: INDEX PATH

(1) Index Keys: appid prdctcd (Serial, fragments: ALL)

Lower Index Filter: (informix.p.prdctcd =3D informix.a.prdctcd =
AND
informix.p.appid =3D informix.a.appid )
NESTED LOOP JOIN

11) informix.z: INDEX PATH

(1) Index Keys: perid (Key-Only) (Serial, fragments: ALL)

Lower Index Filter: informix.z.perid =3D informix.p.uwcontperid=

NESTED LOOP JOIN

12) informix.bp: INDEX PATH

(1) Index Keys: bp_gin (Key-Only)

Lower Index Filter: informix.bp.bp_gin =3D informix.b.bp_gin
NESTED LOOP JOIN

Subquery:

---------

Estimated Cost: 1

Estimated # of Rows Returned: 1

1) informix.z: INDEX PATH

(1) Index Keys: appid status (Key-Only) (Serial, fragments: =
ALL)

Lower Index Filter: informix.z.appid =3D informix.a.appid

Union Query:
------------

1) informix.b: INDEX PATH

(1) Index Keys: name

Lower Index Filter: informix.b.name LIKE 'AGT%'

2) informix.a: INDEX PATH

(1) Index Keys: apporgid date_eff lobcd

Lower Index Filter: informix.a.apporgid =3D informix.b.orgid
NESTED LOOP JOIN

3) informix.c: INDEX PATH

(1) Index Keys: orgid

Lower Index Filter: informix.c.orgid =3D informix.a.prdcrorgid
NESTED LOOP JOIN

4) informix.i: INDEX PATH

(1) Index Keys: orgid (Key-Only)

Lower Index Filter: informix.i.orgid =3D informix.c.orgid
NESTED LOOP JOIN

5) informix.e: INDEX PATH

(1) Index Keys: appid status (Key-First) (Serial, fragments: ALL=
)

Lower Index Filter: informix.e.appid =3D informix.a.appid

Key-First Filters: (informix.e.status !=3D 'COMBINED' )
NESTED LOOP JOIN

6) informix.oi: INDEX PATH

(1) Index Keys: orgid

Lower Index Filter: informix.oi.orgid =3D informix.b.orgid
NESTED LOOP JOIN

7) informix.h: INDEX PATH

(1) Index Keys: prdctcd

Lower Index Filter: informix.h.prdctcd =3D informix.e.prdctcd
NESTED LOOP JOIN

8) informix.f: INDEX PATH

(1) Index Keys: perid (Serial, fragments: ALL)

Lower Index Filter: informix.f.perid =3D informix.e.uwperid
NESTED LOOP JOIN

9) informix.p: INDEX PATH

(1) Index Keys: appid prdctcd (Serial, fragments: ALL)

Lower Index Filter: (informix.p.prdctcd =3D informix.e.prdctcd =
AND
informix.p.appid =3D informix.e.appid )
NESTED LOOP JOIN

10) informix.z: INDEX PATH

(1) Index Keys: perid (Key-Only) (Serial, fragments: ALL)

Lower Index Filter: informix.z.perid =3D informix.p.uwcontperid=

NESTED LOOP JOIN

11) informix.q: INDEX PATH

(1) Index Keys: appid

Lower Index Filter: informix.q.appid =3D informix.a.appid
NESTED LOOP JOIN

12) informix.s: INDEX PATH

(1) Index Keys: dsc

Lower Index Filter: informix.s.dsc =3D informix.e.status
NESTED LOOP JOIN

13) informix.g: INDEX PATH

(1) Index Keys: branchnum

Lower Index Filter: informix.g.branchnum =3D informix.e.servbra=
nchnum

NESTED LOOP JOIN

14) informix.or3: INDEX PATH

Filters: (informix.or3.opt_role_c =3D 'UWA' AND informix.or3.exp_d =
>
TODAY )

(1) Index Keys: appid prdctcd

Lower Index Filter: (informix.or3.appid =3D informix.e.appid AN=
D
informix.or3.prdctcd =3D informix.e.prdctcd )
NESTED LOOP JOIN

15) informix.bp3: INDEX PATH

Filters: informix.bp3.exp_d > TODAY

(1) Index Keys: bp_gin

Lower Index Filter: informix.bp3.bp_gin =3D informix.or3.bp_gin=

NESTED LOOP JOIN

16) informix.or2: INDEX PATH

Filters: (informix.or2.opt_role_c =3D 'CSR' AND informix.or2.exp_d =
>
TODAY )

(1) Index Keys: appid prdctcd

Lower Index Filter: (informix.or2.appid =3D informix.e.appid AN=
D
informix.or2.prdctcd =3D informix.e.prdctcd )
NESTED LOOP JOIN

17) informix.bp2: INDEX PATH

Filters: informix.bp2.exp_d > TODAY

(1) Index Keys: bp_gin

Lower Index Filter: informix.bp2.bp_gin =3D informix.or2.bp_gin=

NESTED LOOP JOIN

18) informix.or1: INDEX PATH

Filters: (informix.or1.opt_role_c =3D 'ASR' AND informix.or1.exp_d =
>
TODAY )

(1) Index Keys: appid prdctcd

Lower Index Filter: (informix.or1.appid =3D informix.e.appid AN=
D
informix.or1.prdctcd =3D informix.e.prdctcd )
NESTED LOOP JOIN

19) informix.bp1: INDEX PATH

Filters: informix.bp1.exp_d > TODAY

(1) Index Keys: bp_gin

Lower Index Filter: informix.bp1.bp_gin =3D informix.or1.bp_gin=

NESTED LOOP JOIN

20) informix.bp: INDEX PATH

(1) Index Keys: bp_gin (Key-Only)

Lower Index Filter: informix.bp.bp_gin =3D informix.b.bp_gin
NESTED LOOP JOIN

SQL statement below

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=

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.