1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2007, week 5
2 51 29_Interesting Optimizer Problem12_Sheldon Rich16_zisher@GMAIL.COM31_Sun, 30 Dec 2007 13:50:23 +0000434_utf-8 This is an actual production problem. Consider the following table and SQLs:
Table: LOAN_TAB 3,005 rows LOAN_ID dec(9) cardinality 2,721 CUST_ID int cardinality 2,425 COLX char(20) Etc….
The table LOAN_TAB has two Indexes both non-unique. Different tests were run with clustering on each index:
Index 1: LOAN_ID asc CUST_ID asc
Index 2: CUST_ID asc LOAN_ID asc [...]
54 26 36_Drew Bradstock is out of the office.14_Drew Bradstock17_drewkb@CA.IBM.COM31_Sun, 30 Dec 2007 10:02:22 -0500436_US-ASCII I will be out of the office starting 12/29/2007 and will not return until 01/06/2008.
I am out of the office and will have no access to email or phone. I will respond when I return.
If the matter is urgent please contact Roger Rea at rrea@us.ibm.com.
Regards, Drew
Drew Bradstock Product Manager DB2 for Linux, UNIX, Windows Office: (905) 413-5949 Cell: (416) 577-9373 Email: drewkb@ca.ibm.com [...]
81 15 46_Mark Maziarczyk/TOR2/SYM is out of the office.0_22_MMaziarczyk@SYMCOR.COM31_Sun, 30 Dec 2007 10:05:07 -0500638_US-ASCII I will be out of the office starting 12/28/2007 and will not return until 01/02/2008.
If you need immediate assistance please contact the Lotus Notes Group Archive Development and Support or contact the CNS Service Desk.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership [...]
97 97 33_Re: Interesting Optimizer Problem11_Nick Dordea20_ndordea@COMPUTER.ORG31_Sun, 30 Dec 2007 15:11:46 +0000479_UTF-8 Hi Sheldon,
The issue is related to the so-called "sarg-able" predicates.
The sql query where clause hints that : cust_id = :HV1 is sargable whereas loan_id > :HV2 is not sarg-able .
Subsequently, the optimizer is going to use that index for which cust_id is a proper sub_index ( i.e. if an index is defined as col-x, col-y, col-z, .... a proper sub-index is any subset of the key that is col-x OR col-x,col-y OR col-x,col-y,col-z ... and so on ) [...]
195 51 33_Re: Interesting Optimizer Problem0_18_RENUSHARMA@AOL.COM29_Sun, 30 Dec 2007 11:16:12 EST725_US-ASCII Try giving bogus predicates to see if it takes right index (in other words force db2 to choose right index. Renu
**************************************See AOL's top rated recipes (http://food.aol.com/top-rated-recipes?NCID=aoltop00030000000004)
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member [...]
247 81 33_Re: Interesting Optimizer Problem12_Ayalew Kassa20_ayalakassa@YAHOO.COM31_Sun, 30 Dec 2007 12:46:40 -0800430_iso-8859-1 Do you have a COL Group runstats on the two columns ? May be that will give the optimizer more info and then decided to chose the right index..
cheers!!! AK
Sheldon Rich wrote: This is an actual production problem. Consider the following table and SQLs:
Table: LOAN_TAB 3,005 rows LOAN_ID dec(9) cardinality 2,721 CUST_ID int cardinality 2,425 COLX char(20) Etc…. [...]
329 383 34_Re: Uncataloged image copy dataset14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Mon, 31 Dec 2007 10:39:07 +1100383_us-ascii Really appreciated the great feed back, Thankyou.
We have been using DISP=(,KEEP) cart image copies for a while now, which I could not figure out, but occasionally I would still get an 'Image Exists' error, which Mark's explanation has cleared up, also our small scratch pool exasperated the problem.
Best wishes for 2008. [...]
713 171 33_Re: Interesting Optimizer Problem19_Sysdba_AHE/CORP/TPG34_Sysdba_AHE/CORP/TPG@GB.TNTPOST.COM31_Mon, 31 Dec 2007 10:18:23 +0000453_UTF-8 A possible explanation might be that the host variable HV1 could have the wrong data type for CUST_ID.
HTH
Neil Price TNT Express ICS, UK
Sheldon Rich Sent by: DB2 Data Base Discussion List 30/12/2007 13:50 Please respond to DB2 Database Discussion list at IDUG
To: DB2-L@www.idugdb2-l.org cc: Subject: [DB2-L] Interesting Optimizer Problem [...]
885 264 147_at which DSNZPARM values should we think of adding another data sharing member?CHTREAD-IDBACK-MAXDBAT-IDFORE,CONDBAT parameters for DB2 for z/os V80_22_Kadir.Meric@AKBANK.COM31_Mon, 31 Dec 2007 16:07:43 +0200396_iso-8859-9 Hi to all,
According to db2 for z/os v8 installation guide:
Max values for CHTREAD-IDBACK-MAXDBAT-IDFORE = 2000
And In most situations, the amount of real and virtual storage determines the maximum number of threads that DB2 can handle.
Let's say we have CHTREAD=1000 for a datasharing member, and our transactions per second is increasind day by day. [...]
1150 353 33_Re: Interesting Optimizer Problem11_Tom Moulder21_tom_moulder@1SCOM.NET31_Mon, 31 Dec 2007 09:36:37 -0600542_UTF-8 IF this is a Version 8 subsystem, then this will change the optimizer choice. Prior to version 8 you would have to use DSTATS or some ISV statistics package to populate the distribution statistics in the catalog like Version 8 does through Runstats.
Tom Moulder
_____
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ayalew Kassa Sent: Sunday, December 30, 2007 2:47 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Interesting Optimizer Problem [...]
1504 62 33_Re: Interesting Optimizer Problem13_Terry Purcell18_tpurcel@US.IBM.COM31_Mon, 31 Dec 2007 16:10:35 +0000630_utf-8 Sheldon,
I suggest you open a PMR on this, as this case needs further investigation.
Regards Terry Purcell
On Sun, 30 Dec 2007 13:50:23 +0000, Sheldon Rich wrote:
>This is an actual production problem. Consider the following table and SQLs: > > >Table: LOAN_TAB 3,005 rows > LOAN_ID dec(9) cardinality 2,721 > CUST_ID int cardinality 2,425 > COLX char(20) > Etc…. > > > >The table LOAN_TAB has two Indexes both non-unique. Different tests were >run with clustering on each index: > > >Index 1: >LOAN_ID asc >CUST_ID asc > >Index 2: >CUST_ID asc >LOAN_ID asc > >The SQL: [...]
1567 13 34_COLGROUP Support in IBM Utilities?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 31 Dec 2007 16:51:54 +0000590_utf-8 Will the Colgroup stats be supported by IBM utilities at some forseeable point in the future? As it is the inline stats seem to do more harm than good in V8.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no [...]
1581 97 33_Re: Interesting Optimizer Problem10_Meir Zohar19_zmeyer@BEZEQINT.NET31_Mon, 31 Dec 2007 19:00:09 +0200415_US-ASCII Sheldon
I'm assuming that your CUST_ID is the customer's National ID number.
Is it (and :HV1) actually defined in the application as an INT and not a Dec(9) or similar - thus possibly causing it to be non-indexable?
Regards,
Meir Zohar CISSP, IBM Certified DBA for DB2 for z/OS
Tel: +972 3 5747860 Fax: +972 3 5747864 Mob: +972 54 5747350 email: zmeyer@bezeqint.net [...]
1679 76 13_ADMIN: digest16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Mon, 31 Dec 2007 17:07:29 -0500548_utf-8 Just to let everyone know that we had to turn off the digest option within the DB2-L list. This was due to a couple of issue we were having with the list.
The plan is to turn it back 'on' some time this week.
I will let you know further status as soon as it changes Mobile email message The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone [...]