SQL explains differently with SPUFI, compared to program bind...

Dee Reins

SQL explains differently with SPUFI, compared to program bind...

We have a secondary one column index that has unique data. Our SQL tries to select one row based upon that column. When we explain the SQL using Spufi the explain matches on one column. (this is a good thing). When we explain the bind (same SQL, same stats, same database, same table), we get 0 matching columns. Stats are current.

IBM's response is that it is the STATS. Has anyone else seen this.

Thanks
Dee


Good access path from SPUFI

---------+---------+---------+---------+---------+--------
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
---------+---------+---------+---------+--------------+---
1 R 0
2 I 1 CTMR26 XMR11302
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0

Bad access path from package BIND. Same SQL and same environment.

1 R 0
2 I 0 CTMR26 XMR11301
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0


SQL in question...

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(SELECT *
FROM CTMR26.VMR113TN_FACS B,
CTMR26.VMR113TN_FACS C,
CTMR26.VMR104SRQ_IM D,
CTMR26.VXX009EXS_CHK E
WHERE B.SRVC_FACS_ID = ? (should use index 2 matching one column)
AND B.TN_TYP_CD <> '3'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
AND C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.TN_TYP_CD = '1'
AND (B.TN_TYP_CD = '2'
OR C.ST_ID = B.ST_ID)
AND C.SRVC_FACS_STAT_CD = 'P'
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
OR
(C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
AND C.SRQ_TM_STP < B.SRQ_TM_STP))
AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
AND E.EXS_CHK_CD = A.EXS_CHK_CD)



“Coming together is a beginning. Keeping together is progress. Working together is success"




_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jay Reavill

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Dee Reins)
Check the datatype for that host variable and make sure it is compatible
with the column datatype.



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, January 20, 2011 3:11 PM
To: [login to unmask email]
Subject: [DB2-L] SQL explains differently with SPUFI, compared to
program bind...




We have a secondary one column index that has unique data. Our SQL
tries to select one row based upon that column. When we explain the SQL
using Spufi the explain matches on one column. (this is a good thing).
When we explain the bind (same SQL, same stats, same database, same
table), we get 0 matching columns. Stats are current.

IBM's response is that it is the STATS. Has anyone else seen this.

Thanks
Dee


Good access path from SPUFI

---------+---------+---------+---------+---------+--------
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
---------+---------+---------+---------+--------------+---
1 R 0
2 I 1 CTMR26 XMR11302
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0

Bad access path from package BIND. Same SQL and same environment.

1 R 0
2 I 0 CTMR26 XMR11301
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0


SQL in question...

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(SELECT *
FROM CTMR26.VMR113TN_FACS B,
CTMR26.VMR113TN_FACS C,
CTMR26.VMR104SRQ_IM D,
CTMR26.VXX009EXS_CHK E
WHERE B.SRVC_FACS_ID = ? (should use index 2 matching one
column)
AND B.TN_TYP_CD <> '3'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
AND C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.TN_TYP_CD = '1'
AND (B.TN_TYP_CD = '2'
OR C.ST_ID = B.ST_ID)
AND C.SRVC_FACS_STAT_CD = 'P'
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
OR
(C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
AND C.SRQ_TM_STP < B.SRQ_TM_STP))
AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
AND E.EXS_CHK_CD = A.EXS_CHK_CD)



"Coming together is a beginning. Keeping together is progress. Working
together is success"





________________________________

Introducing IBM(r) DB2(r) 10 for z/OS
< http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dee Reins

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Jay Reavill)
First thing we checked.


“Coming together is a beginning. Keeping together is progress. Working together is success"



----- Original Message -----
From: "Jay Reavill" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, January 20, 2011 2:13:51 PM
Subject: Re: [DB2-L] SQL explains differently with SPUFI, compared to program bind...




Check the datatype for that host variable and make sure it is compatible with the column datatype.




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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg , FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

j [login to unmask email]

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




From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, January 20, 2011 3:11 PM
To: [login to unmask email]
Subject: [DB2-L] SQL explains differently with SPUFI, compared to program bind...





We have a secondary one column index that has unique data. Our SQL tries to select one row based upon that column. When we explain the SQL using Spufi the explain matches on one column. (this is a good thing). When we explain the bind (same SQL, same stats, same database, same table), we get 0 matching columns. Stats are current.

IBM's response is that it is the STATS. Has anyone else seen this.

Thanks
Dee


Good access path from SPUFI

---------+---------+---------+---------+---------+--------
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
---------+---------+---------+---------+--------------+---
1 R 0
2 I 1 CTMR26 XMR11302
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0

Bad access path from package BIND. Same SQL and same environment.

1 R 0
2 I 0 CTMR26 XMR11301
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0


SQL in question...

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(SELECT *
FROM CTMR26.VMR113TN_FACS B,
CTMR26.VMR113TN_FACS C,
CTMR26.VMR104SRQ_IM D,
CTMR26.VXX009EXS_CHK E
WHERE B.SRVC_FACS_ID = ? (should use index 2 matching one column)
AND B.TN_TYP_CD <> '3'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
AND C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.TN_TYP_CD = '1'
AND (B.TN_TYP_CD = '2'
OR C.ST_ID = B.ST_ID)
AND C.SRVC_FACS_STAT_CD = 'P'
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
OR
(C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
AND C.SRQ_TM_STP < B.SRQ_TM_STP))
AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
AND E.EXS_CHK_CD = A.EXS_CHK_CD)



“Coming together is a beginning. Keeping together is progress. Working together is success"



_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________




Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.



Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Daniel Luksetich

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Dee Reins)
I’ve seen it when the explain is on two different subsystems. Double check your connections.

Dan



Daniel L Luksetich

IBM Information Champion

IBM Certified Database Administrator - DB2 10 for z/OS

IBM Certified System Administrator - DB2 9 for z/OS

IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database Administration for UNIX, Windows, and OS/2

IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development

IBM Certified Advanced Technical Expert - DB2 Data Replication



Vice President of Global Database Operations

YL&A, Inc.

Database Performance Professionals

http://www.ylassoc.com

http://www.db2expert.com

http://www-01.ibm.com/software/data/champion/profiles/luksetich.html







From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, January 20, 2011 2:20 PM
To: [login to unmask email]
Subject: [SPAM] Re: SQL explains differently with SPUFI, compared to program bind...



First thing we checked.


“Coming together is a beginning. Keeping together is progress. Working together is success"



----- Original Message -----
From: "Jay Reavill" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, January 20, 2011 2:13:51 PM
Subject: Re: [DB2-L] SQL explains differently with SPUFI, compared to program bind...

Check the datatype for that host variable and make sure it is compatible with the column datatype.



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

Jay Reavill

DBA

Fidelity National Information Services, Inc.

11601 Roosevelt Blvd., St. Petersburg, FL. 33716

Office: 727-227-2144 | Cell: 727-215-5794

[login to unmask email] <mailto:[login to unmask email]>

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

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, January 20, 2011 3:11 PM
To: [login to unmask email]
Subject: [DB2-L] SQL explains differently with SPUFI, compared to program bind...




We have a secondary one column index that has unique data. Our SQL tries to select one row based upon that column. When we explain the SQL using Spufi the explain matches on one column. (this is a good thing). When we explain the bind (same SQL, same stats, same database, same table), we get 0 matching columns. Stats are current.

IBM's response is that it is the STATS. Has anyone else seen this.

Thanks
Dee


Good access path from SPUFI

---------+---------+---------+---------+---------+--------
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
---------+---------+---------+---------+--------------+---
1 R 0
2 I 1 CTMR26 XMR11302
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0

Bad access path from package BIND. Same SQL and same environment.

1 R 0
2 I 0 CTMR26 XMR11301
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0


SQL in question...

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(SELECT *
FROM CTMR26.VMR113TN_FACS B,
CTMR26.VMR113TN_FACS C,
CTMR26.VMR104SRQ_IM D,
CTMR26.VXX009EXS_CHK E
WHERE B.SRVC_FACS_ID = ? (should use index 2 matching one column)
AND B.TN_TYP_CD <> '3'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
AND C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.TN_TYP_CD = '1'
AND (B.TN_TYP_CD = '2'
OR C.ST_ID = B.ST_ID)
AND C.SRVC_FACS_STAT_CD = 'P'
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
OR
(C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
AND C.SRQ_TM_STP < B.SRQ_TM_STP))
AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
AND E.EXS_CHK_CD = A.EXS_CHK_CD)



“Coming together is a beginning. Keeping together is progress. Working together is success"


_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
_____________



_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



_____

< http://www-01.ibm.com/software/data/db2/zos/db2-10/ > Introducing IBM® DB2® 10 for z/OS

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.872 / Virus Database: 271.1.1/3383 - Release Date: 01/19/11 13:34:00


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Larry Kintisch

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Daniel Luksetich)
Hi Dee,
I noticed that when you listed the bad EXPLAIN
you showed the XMR11301 index with a non-matching
scan [MC=0] instead of the XMR11302 MC=1. Is
that the problem: a different index was
chosen? If so, please show us the index columns
for each of the indexes referenced.
Still that doesn't explain the discrepancy.
You said "IBM's response": from whom? What version?

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 15:10 2011-01-20, you wrote:

>We have a secondary one column index that has
>unique data. Our SQL tries to select one row
>based upon that column. When we explain the SQL
>using Spufi the explain matches on one column.
>(this is a good thing). When we explain the
>bind (same SQL, same stats, same database, same
>table), we get 0 matching columns. Stats are current.
>
>IBM's response is that it is the STATS. Has anyone else seen this.
>
>Thanks
>Dee
>
>
>Good access path from SPUFI
>
>---------+---------+---------+---------+---------+--------
>TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
>---------+---------+---------+---------+--------------+---
> 1 R 0
> 2 I 1 CTMR26 XMR11302
> 3 I 3 CTMR26 XMR11301
> 4 I 1 CTMR26 XMR10404
> 5 R 0
>
>Bad access path from package BIND. Same SQL and same environment.
>
> 1 R 0
> 2 I 0 CTMR26 XMR11301
> 3 I 3 CTMR26 XMR11301
> 4 I 1 CTMR26 XMR10404
> 5 R 0
>
>
>SQL in question...
>
>SELECT EXS_CHK_CD
>FROM CTMR26.VXX009EXS_CHK A
>WHERE EXISTS
> (SELECT *
> FROM CTMR26.VMR113TN_FACS B,
> CTMR26.VMR113TN_FACS C,
> CTMR26.VMR104SRQ_IM D,
> CTMR26.VXX009EXS_CHK E
> WHERE B.SRVC_FACS_ID = ? (should use
> index 2 matching one column)
> AND B.TN_TYP_CD <> '3'
> AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
> AND C.TN_NPA = B.TN_NPA
> AND C.TN_NXX = B.TN_NXX
> AND C.TN_LN_NO = B.TN_LN_NO
> AND C.TN_TYP_CD = '1'
> AND (B.TN_TYP_CD = '2'
> OR C.ST_ID = B.ST_ID)
> AND C.SRVC_FACS_STAT_CD = 'P'
> AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
> OR
> (C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
> AND C.SRQ_TM_STP < B.SRQ_TM_STP))
> AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
> AND D..SRQ_TYP_CD NOT LIKE ?
> AND E.EXS_CHK_CD = A.EXS_CHK_CD)
>
>
>
>“Coming together is a beginning. Keeping
>together is progress. Working together is success"
>
>
>
>
>No virus found in this message.
>Checked by AVG - < http://www.avg.com > www.avg.com
>Version: 10.0.1191 / Virus Database: 1435/3391 - Release Date: 01/19/11
>
>
> < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >
>Introducing IBM® DB2® 10 for z/OS
>
>
>The IDUG DB2-L Listserv is only part of your
>membership in IDUG. If you are not already an
>IDUG member, < http://www.idug.org/register > please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Joe Geller

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Larry Kintisch)
Dee,
You said you Explained the Bind. Do you mean you looked
at the Plan_Table or that you did a Rebind with EXPLAIN(YES)
and then looked at the Plan_Table? If the former, then how
long ago was the Bind? The stats (or the indexes) could have
been different then.

Joe



Hi Dee,
I noticed that when you listed the bad EXPLAIN
you showed the XMR11301 index with a non-matching
scan [MC=0] instead of the XMR11302 MC=1. Is
that the problem: a different index was
chosen? If so, please show us the index columns
for each of the indexes referenced.
Still that doesn't explain the discrepancy.
You said "IBM's response": from whom? What version?

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 15:10 2011-01-20, you wrote:

>We have a secondary one column index that has
>unique data. Our SQL tries to select one row
>based upon that column. When we explain the SQL
>using Spufi the explain matches on one column.
>(this is a good thing). When we explain the
>bind (same SQL, same stats, same database, same
>table), we get 0 matching columns. Stats are current.
>
>IBM's response is that it is the STATS. Has anyone else seen this.
>
>Thanks
>Dee
>
>
>Good access path from SPUFI
>
>---------+---------+---------+---------+---------+--------
>TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
>---------+---------+---------+---------+--------------+---
> 1 R 0
> 2 I 1 CTMR26 XMR11302
> 3 I 3 CTMR26 XMR11301
> 4 I 1 CTMR26 XMR10404
> 5 R 0
>
>Bad access path from package BIND. Same SQL and same environment.
>
> 1 R 0
> 2 I 0 CTMR26 XMR11301
> 3 I 3 CTMR26 XMR11301
> 4 I 1 CTMR26 XMR10404
> 5 R 0
>
>
>SQL in question...
>
>SELECT EXS_CHK_CD
>FROM CTMR26.VXX009EXS_CHK A
>WHERE EXISTS
> (SELECT *
> FROM CTMR26.VMR113TN_FACS B,
> CTMR26.VMR113TN_FACS C,
> CTMR26.VMR104SRQ_IM D,
> CTMR26.VXX009EXS_CHK E
> WHERE B.SRVC_FACS_ID = ? (should use
> index 2 matching one column)
> AND B.TN_TYP_CD <> '3'
> AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
> AND C.TN_NPA = B.TN_NPA
> AND C.TN_NXX = B.TN_NXX
> AND C.TN_LN_NO = B.TN_LN_NO
> AND C.TN_TYP_CD = '1'
> AND (B.TN_TYP_CD = '2'
> OR C.ST_ID = B.ST_ID)
> AND C.SRVC_FACS_STAT_CD = 'P'
> AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
> OR
> (C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
> AND C.SRQ_TM_STP < B.SRQ_TM_STP))
> AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
> AND D..SRQ_TYP_CD NOT LIKE ?
> AND E.EXS_CHK_CD = A.EXS_CHK_CD)
>

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Sam Baugh

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Joe Geller)
Does your query produce the desired results? Table A only has relation to E, which is the same table. B,C,D are independent, unless I missed something somewhere. If correct as is, I believe the JOIN to the E table is not needed.

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(
SELECT '*'
FROM CTMR26.VMR113TN_FACS B
join CTMR26.VMR113TN_FACS C
on C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.SRVC_FACS_STAT_CD = 'P'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
join CTMR26.VMR104SRQ_IM D
on D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
join CTMR26.VXX009EXS_CHK E – shouldn’t need this
on E.EXS_CHK_CD = A.EXS_CHK_CD – shouldn’t need this
WHERE B.SRVC_FACS_ID = ?
AND B.TN_TYP_CD <> '3'
AND (B.TN_TYP_CD = '2' OR C.ST_ID = B.ST_ID)
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT OR (C.SRQ_DUE_DAT = B.SRQ_DUE_DAT AND C.SRQ_TM_STP < B.SRQ_TM_STP))
)
Fetch first row only

Or without EXISTS

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
join (
SELECT '*'
FROM CTMR26.VMR113TN_FACS B
join CTMR26.VMR113TN_FACS C
on C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.SRVC_FACS_STAT_CD = 'P'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
join CTMR26.VMR104SRQ_IM D
on D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
WHERE B.SRVC_FACS_ID = ?
AND B.TN_TYP_CD <> '3'
AND (B.TN_TYP_CD = '2' OR C.ST_ID = B.ST_ID)
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT OR (C.SRQ_DUE_DAT = B.SRQ_DUE_DAT AND C.SRQ_TM_STP < B.SRQ_TM_STP))
) as x
on 1 = 1
fetch first row only



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Dee Reins
Sent: Thursday, January 20, 2011 2:11 PM
To: [login to unmask email]
Subject: [DB2-L] SQL explains differently with SPUFI, compared to program bind...


We have a secondary one column index that has unique data. Our SQL tries to select one row based upon that column. When we explain the SQL using Spufi the explain matches on one column. (this is a good thing). When we explain the bind (same SQL, same stats, same database, same table), we get 0 matching columns. Stats are current.

IBM's response is that it is the STATS. Has anyone else seen this.

Thanks
Dee


Good access path from SPUFI

---------+---------+---------+---------+---------+--------
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
---------+---------+---------+---------+--------------+---
1 R 0
2 I 1 CTMR26 XMR11302
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0

Bad access path from package BIND. Same SQL and same environment.

1 R 0
2 I 0 CTMR26 XMR11301
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0


SQL in question...

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(SELECT *
FROM CTMR26.VMR113TN_FACS B,
CTMR26.VMR113TN_FACS C,
CTMR26.VMR104SRQ_IM D,
CTMR26.VXX009EXS_CHK E
WHERE B.SRVC_FACS_ID = ? (should use index 2 matching one column)
AND B.TN_TYP_CD <> '3'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
AND C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.TN_TYP_CD = '1'
AND (B.TN_TYP_CD = '2'
OR C.ST_ID = B.ST_ID)
AND C.SRVC_FACS_STAT_CD = 'P'
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
OR
(C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
AND C.SRQ_TM_STP < B.SRQ_TM_STP))
AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
AND E.EXS_CHK_CD = A.EXS_CHK_CD)



“Coming together is a beginning. Keeping together is progress. Working together is success"



________________________________

[ http://www.idug.org/images/stories/db2/db2_10_savings.jpg ] < http://www-01.ibm.com/software/data/db2/zos/db2-10/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Terry Purcell

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Sam Baugh)
Hello Dee,

I found the PMR you have opened, and L2 support have not confirmed that this is a statistics issue. What I read in the PMR is that they have suggested running statistics advisor. This is standard practice for L2, as historically, 60-70% of access path issues are related to inadequate or inconsistent stats.

At this point, this was the first suggestion made, and there isn't any further information provided in the PMR such that a more detailed investigation can occur.

Regards
Terry Purcell



Subject: SQL explains differently with SPUFI, compared to program bind...

From: Dee Reins <[login to unmask email]>

Reply-To: IDUG DB2-L <[login to unmask email]>

Date: Thu, 20 Jan 2011 20:10:38 +0000


We have a secondary one column index that has unique data. Our SQL tries to select one row based upon that column. When we explain the SQL using Spufi the explain matches on one column. (this is a good thing). When we explain the bind (same SQL, same stats, same database, same table), we get 0 matching columns. Stats are current.

IBM's response is that it is the STATS. Has anyone else seen this.

Thanks
Dee


Good access path from SPUFI

---------+---------+---------+---------+---------+--------
TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
---------+---------+---------+---------+--------------+---
1 R 0
2 I 1 CTMR26 XMR11302
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0

Bad access path from package BIND. Same SQL and same environment.

1 R 0
2 I 0 CTMR26 XMR11301
3 I 3 CTMR26 XMR11301
4 I 1 CTMR26 XMR10404
5 R 0


SQL in question...

SELECT EXS_CHK_CD
FROM CTMR26.VXX009EXS_CHK A
WHERE EXISTS
(SELECT *
FROM CTMR26.VMR113TN_FACS B,
CTMR26.VMR113TN_FACS C,
CTMR26.VMR104SRQ_IM D,
CTMR26.VXX009EXS_CHK E
WHERE B.SRVC_FACS_ID = ? (should use index 2 matching one column)
AND B.TN_TYP_CD <> '3'
AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
AND C.TN_NPA = B.TN_NPA
AND C.TN_NXX = B.TN_NXX
AND C.TN_LN_NO = B.TN_LN_NO
AND C.TN_TYP_CD = '1'
AND (B.TN_TYP_CD = '2'
OR C.ST_ID = B.ST_ID)
AND C.SRVC_FACS_STAT_CD = 'P'
AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
OR
(C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
AND C.SRQ_TM_STP < B.SRQ_TM_STP))
AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
AND D.SRQ_TYP_CD NOT LIKE ?
AND E.EXS_CHK_CD = A.EXS_CHK_CD)



“Coming together is a beginning. Keeping together is progress. Working together is success"


_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dee Reins

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Terry Purcell)
The rebinds and explains were done within 30 minutes of each other.


“Coming together is a beginning. Keeping together is progress. Working together is success"



----- Original Message -----
From: "Joe Geller" <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, January 20, 2011 1:41:49 PM
Subject: Re: [DB2-L] SQL explains differently with SPUFI, compared to program bind...

Dee,
You said you Explained the Bind. Do you mean you looked
at the Plan_Table or that you did a Rebind with EXPLAIN(YES)
and then looked at the Plan_Table? If the former, then how
long ago was the Bind? The stats (or the indexes) could have
been different then.

Joe



Hi Dee,
I noticed that when you listed the bad EXPLAIN
you showed the XMR11301 index with a non-matching
scan [MC=0] instead of the XMR11302 MC=1. Is
that the problem: a different index was
chosen? If so, please show us the index columns
for each of the indexes referenced.
Still that doesn't explain the discrepancy.
You said "IBM's response": from whom? What version?

Larry Kintisch, Pres. ABLE Information Services 845-353-0885

At 15:10 2011-01-20, you wrote:

>We have a secondary one column index that has
>unique data. Our SQL tries to select one row
>based upon that column. When we explain the SQL
>using Spufi the explain matches on one column.
>(this is a good thing). When we explain the
>bind (same SQL, same stats, same database, same
>table), we get 0 matching columns. Stats are current.
>
>IBM's response is that it is the STATS. Has anyone else seen this.
>
>Thanks
>Dee
>
>
>Good access path from SPUFI
>
>---------+---------+---------+---------+---------+--------
>TABNO ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME
>---------+---------+---------+---------+--------------+---
> 1 R 0
> 2 I 1 CTMR26 XMR11302
> 3 I 3 CTMR26 XMR11301
> 4 I 1 CTMR26 XMR10404
> 5 R 0
>
>Bad access path from package BIND. Same SQL and same environment.
>
> 1 R 0
> 2 I 0 CTMR26 XMR11301
> 3 I 3 CTMR26 XMR11301
> 4 I 1 CTMR26 XMR10404
> 5 R 0
>
>
>SQL in question...
>
>SELECT EXS_CHK_CD
>FROM CTMR26.VXX009EXS_CHK A
>WHERE EXISTS
> (SELECT *
> FROM CTMR26.VMR113TN_FACS B,
> CTMR26.VMR113TN_FACS C,
> CTMR26.VMR104SRQ_IM D,
> CTMR26.VXX009EXS_CHK E
> WHERE B.SRVC_FACS_ID = ? (should use
> index 2 matching one column)
> AND B.TN_TYP_CD <> '3'
> AND C.SRVC_FACS_ID <> B.SRVC_FACS_ID
> AND C.TN_NPA = B.TN_NPA
> AND C.TN_NXX = B.TN_NXX
> AND C.TN_LN_NO = B.TN_LN_NO
> AND C.TN_TYP_CD = '1'
> AND (B.TN_TYP_CD = '2'
> OR C.ST_ID = B.ST_ID)
> AND C.SRVC_FACS_STAT_CD = 'P'
> AND (C.SRQ_DUE_DAT < B.SRQ_DUE_DAT
> OR
> (C.SRQ_DUE_DAT = B.SRQ_DUE_DAT
> AND C.SRQ_TM_STP < B.SRQ_TM_STP))
> AND D.SRVC_FACS_ID = C.SRVC_FACS_ID
> AND D..SRQ_TYP_CD NOT LIKE ?
> AND E.EXS_CHK_CD = A.EXS_CHK_CD)
>

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Roy Boxwell

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Dee Reins)
Hi!

I've said this before but I'll say it again as I like the sound of my own
typing

download and run our StatisticsHealthCheck -- It will tell you which
runstats you need now. Ask us *very* nicely and you can get the version
that creates corrective RUNSTATS - 90% of all bad/strange/odd/impossible
optimizer choices lands at the feet of statistics the rest are typically
bugs, missing APARs or bad ZPARMS

If you would like a curent list of RUNSTATS or SQL PERFORMANCE APARs for
V8, 9 and 10 drop me aline offlist!


Hope that all helps!

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Adam Baldwin

Re: SQL explains differently with SPUFI, compared to program bind...
(in response to Roy Boxwell)
Dee, I'm sure that you've checked this as well but are the bind parms for your SPUFI plan and application plan the same??

Cheers, Adam

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv