1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2008, week 4
2 63 26_The X-Files: Rebuild Index16_Peter Adlersburg22_p.adlersburg@GMAIL.COM31_Mon, 22 Dec 2008 08:49:44 +0000800_utf-8 Dear DB2-Colleagues,
Recently I ran the following Utility:
LISTDEF RECOVERY_LIST INCLUDE TABLESPACE DS01*.* PARTLEVEL EXCLUDE TABLESPACE DS01ASS.* PARTLEVEL EXCLUDE TABLESPACE DS01UDA.TS01USO1 PARTLEVEL
RECOVER LIST RECOVERY_LIST TORBA X'0981A343FC04'
REBUILD INDEX LIST RECOVERY_LIST SORTDEVT SYSDA SORTNUM 40
Everything runs nice and quick until things come to rebuilding the indexes of partitioned tables. Here is one output-example:
PROCESSING LIST ITEM: TABLESPACE DS01UDP.TS01USU1 PARTITION 24 INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS = 7 INDEX NAME= S01.USUKEY2 PART=24 IS EMPTY INDEX NAME= S01.USUKEY1 PART=24 IS EMPTY INDEX NAME= S01.USUKEY3 PART=24 IS EMPTY UNLOAD PHASE STATISTICS - NUMBER OF RECORDS PROCESSED=0 UNLOAD PHASE [...]
66 86 30_Re: The X-Files: Rebuild Index16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 22 Dec 2008 04:22:14 -0500569_UTF-8 A wild guess - are the empty partitions migrated ?
Steen Rasmussen CA
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Adlersburg Sent: Monday, December 22, 2008 2:50 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] The X-Files: Rebuild Index
Dear DB2-Colleagues,
Recently I ran the following Utility:
LISTDEF RECOVERY_LIST INCLUDE TABLESPACE DS01*.* PARTLEVEL EXCLUDE TABLESPACE DS01ASS.* PARTLEVEL EXCLUDE TABLESPACE DS01UDA.TS01USO1 PARTLEVEL [...]
153 48 20_Re: Question on RECP14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Mon, 22 Dec 2008 20:31:38 +1100337_US-ASCII The only way I could think of is to scan the log looking for when the RECP status was applied to DSNDB01.DBD01.
However, I have no clue what you should look for that shows the RECP status going on. The best I can think of is to note when the RECP comes off and see what change happened. Look for the reverse action. [...]
202 346 30_Re: The X-Files: Rebuild Index13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 22 Dec 2008 09:57:07 +0000552_ISO-8859-1 The time will be spent in the BUILD phase for the two NPIs, USUKEY1 and USUKEY3; the BUILD phase is a slow, SQL-type update of the index, not a fast utility-type action. Depending on the index columns (and the cleverness of the optimiser), the full indexes have to be scanned looking for entries that point to the recovered partitions in order to remove them (after the PiT recovery, the partition was empty - but there may have been millions of entries before; and every index entry of all NPIs may need to have its RID list updated). [...]
549 27 30_Re: The X-Files: Rebuild Index16_Peter Adlersburg22_p.adlersburg@GMAIL.COM31_Mon, 22 Dec 2008 10:35:19 +0000486_utf-8 Gentlemen,
Thanks so far for your comments. By the way: DPSIs - as suggested by Mr. Ebert - are out of question, since they must not be unique, which in this case is a requirement for the NPIs of the table.
kind regards,
peter
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________ [...]
577 42 41_[z/OS DB2 V8] CA Log Analyzer DFSORT S0C412_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 22 Dec 2008 11:29:11 +0000270_utf-8 Fellow Listers, I'm trying to run a CA Log Analyzer process to extract DDL for a dropped tablespace and I can't round getting S0C4 abends in the sort. I've reduced the log interval to only 1 (one) second as I know the exact time of the drop but no success... [...]
620 50 30_Re: The X-Files: Rebuild Index16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 22 Dec 2008 06:38:25 -0500598_UTF-8 In fact DPSI's can be unique if they are a superset of the partitioning scheme
Steen Rasmussen CA
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Adlersburg Sent: Monday, December 22, 2008 4:35 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] The X-Files: Rebuild Index
Gentlemen,
Thanks so far for your comments. By the way: DPSIs - as suggested by Mr. Ebert - are out of question, since they must not be unique, which in this case is a requirement for the NPIs of the table. [...]
671 84 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C414_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 22 Dec 2008 11:55:35 -0000387_utf-8 Hi Adam
Two questions in return for you
1. Whose sort package is being called? 2. Did you check with CA support online or CA Tech Support to see if this is a known issue?
A S0C4 in sort seems more that a little odd
Phil Grainger CA Senior Principal Product Manager Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com [...]
756 60 30_Re: The X-Files: Rebuild Index16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 22 Dec 2008 07:02:36 -0500317_UTF-8 Correction - Phil Grainger mentioned to me that Unique DPSIs (being a superset of the partitioning scheme) aren't supported until you're on DB2 9. We are pretty current on DB2 V8 maintenance, so I just tried to create a Unique DPSI - but DB2 issues SQL-628 saying the clauses are mutually exclusive in V8. [...]
817 60 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C413_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 22 Dec 2008 12:04:44 -0000542_utf-8 Install Log Master? ;o)
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin Sent: 22 December 2008 11:29 To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] [z/OS DB2 V8] CA Log Analyzer DFSORT S0C4
Fellow Listers, I'm trying to run a CA Log Analyzer process to extract DDL for a dropped tablespace and I can't round getting S0C4 abends in the sort. I've reduced the log interval to only 1 (one) second as I know the exact time of the drop but no success... [...]
878 42 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C412_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 22 Dec 2008 12:05:12 +0000499_utf-8 Hi Phil:
The sort package is DFSORT:
Z/OS DFSORT V1R5
And I'm getting:
ICE185A 0 AN S0C4 ABEND WAS ISSUED BY DFSORT, ANOTHER PROGRAM OR AN EXIT (PHASE S 3)
So it may be that DFSORT is just reporting an abend in another module.
In the output from the LA job I get:
LAE0032E LAL@REPT terminated with return/abend code: 0C4 BPA0071E: CALLED PROGRAM LAL@LOG ABENDED. ABEND CODE U0200 REASON CODE 00000000 - PROCESSING TERMINATES. RETCODE = 20 [...]
921 21 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C412_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 22 Dec 2008 12:09:25 +0000783_utf-8 Raymond! Where's your Christmas spirit??? Good Will to all Products etc...
Bah humbug.
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, 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 [...]
943 56 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C416_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Mon, 22 Dec 2008 07:14:09 -0500411_UTF-8 Which version of Log Analyzer are you using Adam ?
Steen Rasmussen CA
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin Sent: Monday, December 22, 2008 6:05 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] [z/OS DB2 V8] CA Log Analyzer DFSORT S0C4
Hi Phil:
The sort package is DFSORT: [...]
1000 46 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C413_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 22 Dec 2008 12:17:22 -0000298_utf-8 Hey Adam. I think you just caught me off-guard. I was too shocked to see a non-IBM product in use at IBM. When I was there, to my eternal shame, we got rid of as many ISV tools as we could. How times change, eh?
My Christmas spirit is in Tesco's. I'm off to buy a bottle shortly. [...]
1047 71 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C414_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 22 Dec 2008 12:26:19 -0000345_utf-8 I did a quick search and I see
Title: LAE0032E & S0C4 OR S0C1 ABEND
Description: PROBLEM DESCRIPTION: Log Analyzer job abend with error message LAE0032E & terminated with S0C4 OR S0C1 when it was tried to process null VARCHAR column.
Does this sound likely?
This is on r11.5 and was closed on 5th December [...]
1119 62 41_[z/OS DB2 V8] CA Log Analyzer DFSORT S0C413_Oscar Paalman31_ohcm.paalman@BELASTINGDIENST.NL31_Mon, 22 Dec 2008 13:19:46 +0100672_us-ascii Hi adam,
iis it is possible the pre-allocate the necessary sort-files as a DD-statement in the JCL-flow, so that you don't have this allocation problem ? for example //SW01WK01 DD DSN=&&SORTWK01,SPACE=(CYL,(3000,1)),UNIT=DISK, // VOL=SER=LLD131 //SW01WK02 DD DSN=&&SORTWK02,SPACE=(CYL,(3000,1)),UNIT=DISK, // VOL=SER=LLD132 //SW01WK03 DD DSN=&&SORTWK03,SPACE=(CYL,(3000,1)),UNIT=DISK, // VOL=SER=LLD135 //SW02WK01 DD DSN=&&SORTWK04,SPACE=(CYL,(3000,1)),UNIT=DISK, // VOL=SER=LLD129 //SW02WK02 DD DSN=&&SORTWK05,SPACE=(CYL,(3000,1)),UNIT=DISK, // VOL=SER=LLD134 //SW02WK03 DD DSN=&&SORTWK06,SPACE=(CYL,(3000,1)),UNIT=DISK, // VOL=SER=LLD136 . etc..... [...]
1182 128 41_[z/OS DB2 V8] CA Log Analyzer DFSORT S0C413_Oscar Paalman31_ohcm.paalman@BELASTINGDIENST.NL31_Mon, 22 Dec 2008 13:31:55 +01001074_us-ascii adam,
try to pre-allocate the sortfiles in the jcl as dd-statement. also look in the output what kind of dd-statement you need, eg //sortwk01 or //sw01wk01
example SW01WK01
//SW01WK01 DD DSN=MYFILE.SORTWK01,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW01WK02 DD DSN=MYFILE.SORTWK02,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW01WK03 DD DSN=MYFILE.SORTWK03,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW02WK01 DD DSN=MYFILE.SORTWK04,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW02WK02 DD DSN=MYFILE.SORTWK05,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW02WK03 DD DSN=MYFILE.SORTWK06,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW03WK01 DD DSN=MYFILE.SORTWK07,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW03WK02 DD DSN=MYFILE.SORTWK08,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) //SW03WK03 DD DSN=MYFILE.SORTWK09,SPACE=(CYL,(3000,500)),UNIT=3390, // DISP=(NEW,DELETE,DELETE) / [...]
1311 290 53_AW: [DB2-L] DB2 V8 New Keyword for Create/Alter Table0_26_Walter.Janissen@ITERGO.COM31_Mon, 22 Dec 2008 13:40:11 +0100472_iso-8859-1 Barbara
If you want to choose this option, you must be aware of the sometimes startling access paths. Basically, this option has it's origin from a SAP requirement, so it handles their request at best. This option favours index access and prohibts some other access path options, namely list prefetch. So if an update- or delete-statement requires list prefetch, the access path will be a tablespace scan and that could be a killer for performance. [...]
1602 102 30_Re: The X-Files: Rebuild Index14_Peter Backlund21_BacklundDB2@TELIA.COM31_Mon, 22 Dec 2008 14:08:59 +0100
1705 23 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C412_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 22 Dec 2008 15:08:44 +0000763_utf-8 Thanks for all of the replies - on and off list.
I've now got it to work by going a different route through Log Analyzer.
Cheers, Adam
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, 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 [...]
1729 29 28_DB2 9 Compression Dictionary10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 22 Dec 2008 15:28:16 +0000445_utf-8 In reviewing the manual it is clear that tablespaces using partition-by-growth have one single dictionary. I am assuming this implies that tablespaces for range partitioned objects still use one dictionary per partition. If yes, the question is if I do a LOAD RESUME on a partitioned object that contains data, but no data inthe partition(s) that get loaded, will the utility build a dictionary for those previously empty partitions? [...]
1759 36 36_[FLUFF] Twelve Days of Change Freeze12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 22 Dec 2008 15:54:35 +0000301_utf-8 I can't help it! Despite S0C4s, a recent problem in production, and too much work to do, I'm feeling festive....
So, for those of you also stuck with DB2 up to - and maybe through Christmas - how about some lyrics for:
On the first day of Change Freeze My user sent to me.... [...]
1796 71 40_Re: [FLUFF] Twelve Days of Change Freeze12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 22 Dec 2008 17:54:10 +0200417_UTF-8 ... To Saint Louise Where all the DASD farm went aground And no one was there to help around
Who's next :-)
Isaac Yassin
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin Sent: Monday, December 22, 2008 5:55 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] [FLUFF] Twelve Days of Change Freeze [...]
1868 112 30_Re: The X-Files: Rebuild Index11_Hardy, Dale14_DHardy@MIB.COM31_Mon, 22 Dec 2008 11:05:37 -0500524_us-ascii We are on V8 and have a data partitioned table partitioned and clustered on a single column, col1, with 2 partitioned indexes defined as unique Index1 (col1, col2) Index2 (col1, col3, col4, col2)
DB2 9 will allow you to define a unique index as (col2,col1).
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rasmussen, Steen Sent: Monday, December 22, 2008 7:03 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] The X-Files: Rebuild Index [...]
1981 60 40_Re: [FLUFF] Twelve Days of Change Freeze13_Fung, Chi-Yun23_Chi-Yun.Fung@UNISYS.COM31_Mon, 22 Dec 2008 10:46:57 -0600441_us-ascii One new column to add Two old tables to drop Three new tables to add Four new programs to promote Fi........ve new indexes in need
Merry Christmas to you all..............
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin Sent: Monday, December 22, 2008 10:55 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] [FLUFF] Twelve Days of Change Freeze [...]
2042 76 40_Re: [FLUFF] Twelve Days of Change Freeze14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 22 Dec 2008 13:12:34 -0500516_us-ascii ...an emergency recovery. On the second day of Change Freeze, my user sent to me Two change requests, and an emergency recovery On the second day of Change Freeze, my user sent to me Three SQL Problems, two change requests, and an emergency recovery ...
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin Sent: Monday, December 22, 2008 10:55 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] [FLUFF] Twelve Days of Change Freeze [...]
2119 176 30_Re: The X-Files: Rebuild Index7_Ed Long19_rdhm99a@PRODIGY.NET31_Mon, 22 Dec 2008 10:23:10 -0800386_us-ascii While I haven't seen 20 minute zero row rebuilds I have noticed that the V8 utilities construct large numbers of related TCB's even when there is no data to process. I suspect my longer than expected elapsed times have to do with all those attach's, catalog accesses, SVC99's etc. In one case, 24 tcb's were attached long enough to determine that there was nothing to do. [...]
2296 101 40_Re: [FLUFF] Twelve Days of Change Freeze23_BOLEY Maurice C * Cliff32_Maurice.C.BOLEY@ODOT.STATE.OR.US31_Mon, 22 Dec 2008 10:22:52 -0800439_US-ASCII One new column to add Two old tables to drop Three new tables to add Four new programs to promote Fi........ve new indexes in need Six table reorgs.
cliff:-)
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Fung, Chi-Yun Sent: Monday, December 22, 2008 8:47 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] [FLUFF] Twelve Days of Change Freeze [...]
2398 54 32_Re: DB2 9 Compression Dictionary13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Mon, 22 Dec 2008 12:27:04 -0600607_UTF-8 When a new partition is created for a partition by growth UTS table space, it does have a dictionary inherited from the previous partition.
Willie
Jorg Lueke wrote: > In reviewing the manual it is clear that tablespaces using partition-by-growth > have one single dictionary. I am assuming this implies that tablespaces for > range partitioned objects still use one dictionary per partition. If yes, the > question is if I do a LOAD RESUME on a partitioned object that contains data, > but no data inthe partition(s) that get loaded, will the utility build a dictionary > for those [...]
2453 44 40_Re: [FLUFF] Twelve Days of Change Freeze10_Meir Zohar19_zmeyer@BEZEQINT.NET31_Mon, 22 Dec 2008 20:48:03 +0200359_us-ascii Oh the weather outside is frightful ... And tweaking zparm's so delightful.... Since this geek's got nowhere to go .... Let it snow , let it snow , let it snow
The abends don't show signs of stopping ... And coffee's all I've got for popping ... And maintenance turned all the light down low ... Let it snow , let it snow let it snow ... [...]
2498 295 40_Re: [FLUFF] Twelve Days of Change Freeze12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Mon, 22 Dec 2008 13:03:56 -0600427_US-ASCII On the Fourth day of Change Freeze, my user sent to me 4 Birdie Binds , 3 SQL problems, 2 change requests, and an emergency recovery
"Sevetson, Phil" Sent by: DB2 Data Base Discussion List 12/22/2008 12:43 PM Please respond to DB2 Database Discussion list at IDUG
To DB2-L@www.idugdb2-l.org cc [...]
2794 91 40_Re: [FLUFF] Twelve Days of Change Freeze12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Mon, 22 Dec 2008 13:37:58 -0600364_US-ASCII 1 Partidged table 2 Dovetailed R.I's 3 French programs 4 calling UDF's 5 drops from grace 6 un... loadings 7 gracious grants 8 SQL beauties 9 holy abends 10 stop commands 11 piped threads 12 drumming headaches
Leaving tomorrow for a 74 degree Christmas day in Baton Rouge. Hope you all have a safe and Merry Christmas or Happy Hanukkah. Mark. [...]
2886 364 40_Re: [FLUFF] Twelve Days of Change Freeze16_Wolters, Jeff B.24_Jeff.Wolters@PGNMAIL.COM31_Mon, 22 Dec 2008 14:50:46 -0500469_us-ascii One new column to add Two old tables to drop Three new tables to add Four new programs to promote Fi........ve new indexes in need
Six tasks a-waiting Seven broken keyboards Eight LCDs a-dimming Nine projects behind Ten managers a-screaming Eleven sub-systems crashing Twelve employees on leave
Jeff Wolters DataBase Services Information Technology Progress Energy Internal: 8-230-5325 External: 727-820-5325 Email: jeff.wolters@pgnmail.com [...]
3251 47 24_SQL Access path Question11_Peter Jones18_p19689@YAHOO.CO.UK31_Mon, 22 Dec 2008 21:43:16 +0000510_UTF-8 Could someone can help me on this ?
SELECT COLA , COLB, COLD FROM TABLEA WHERE COLC > ' '
TABLEA has 2 million records and currently does tablespace scan. COLC is CHAR(1) COLC has COLCARDF = 3 TABLEA has Primary index on COLA ( the only index on the TABLEA)
I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0.3 ) qualifies. I think COLC is indexable and stage 1 predicate . [...]
3299 688 40_Re: [FLUFF] Twelve Days of Change Freeze14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 22 Dec 2008 17:07:07 -0500458_us-ascii I think the brownie points for best 12-days have to be split between Jeff and Mark Vickers. With an honorable mention for Meir Z.'s filking of "Let it snow"...
________________________________
From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Wolters, Jeff B. Sent: Monday, December 22, 2008 2:51 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] [FLUFF] Twelve Days of Change Freeze [...]
3988 76 28_Re: SQL Access path Question9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM31_Mon, 22 Dec 2008 17:09:35 -0600412_us-ascii I suppose that the table will have to be clustered on colc.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Jones Sent: Monday, December 22, 2008 3:43 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] SQL Access path Question
Could someone can help me on this ?
SELECT COLA , COLB, COLD FROM TABLEA WHERE COLC > ' ' [...]
4065 72 28_Re: SQL Access path Question15_Campbell, Wayne28_Wayne.Campbell@COURTS.WA.GOV31_Mon, 22 Dec 2008 15:30:55 -0800354_utf-8 You have a cardinality of 3. So unless you have distribution stats on the column, db2 is going to assume that each value is contained in 1/3 of the row. You have a predicate with a greater than, this means you could get 0% or 33% or 66% or 100% matches. Again without the distribution stats, Db2 won't know how many of the Rows would qualify. [...]
4138 71 28_Re: SQL Access path Question13_David Simpson22_dsimpson@THEMISINC.COM31_Mon, 22 Dec 2008 19:00:57 -0500475_UTF-8 Another stat to look at is the cluster ratio of your COLC index. If the ratio is low then optimizer may determine that it will need to visit nearly every page of the tablespace to get the 33% of the data, so a tablespace scan is still the best access path. If you gathered stats on your new index the frequency stats for COLC should also have been gathered, so optimizer will know if one of the 3 values has a much greater or lower filter factor than the other 2. [...]
4210 25 17_Re: Perform Magic15_Michael Bednarz24_michael.bednarz@CITI.COM31_Tue, 23 Dec 2008 06:40:40 +0000684_utf-8 Hi All,
Thank you all for your comments and explains. Helped verry well.
Regards Michael
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events * ______________________________________________________________________
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
4236 63 40_AW: [DB2-L] DB2 9 Compression Dictionary0_26_Walter.Janissen@ITERGO.COM31_Tue, 23 Dec 2008 09:46:06 +0100414_iso-8859-1 Yes
In V8 we had a problem with this, because there was a bug in DB2. But after installing a PTF (haven't the number at hand) DB2 works as expected. The empty partitions got their dictionary, when loaded.
As you may know, in DB2 V9 CM there is a new option for LOAD: COPYDICTIONARY. I have tested it and it works, but the syntax is a bit different than described in the cover letter: [...]
4300 51 61_Mark Vickers is out of the office 12/23 returning 01/05/2009.12_Mark Vickers26_MarkVickers@GROCERYBIZ.COM31_Tue, 23 Dec 2008 04:00:47 -0600523_US-ASCII
I will be out of the office starting 12/23/2008 and will not return until 01/05/2009.
I will respond to your message when I return. If you need immediate assistance, please contact Ted Flowers x6562, Ramona Kipfer x5721 or Scott Stritchko x5669. thank you, Mark Vickers.
______________________________________________________________________
* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events * ______________________________________________________________________
4352 29 45_Re: [z/OS DB2 V8] CA Log Analyzer DFSORT S0C413_Steve Mallett21_malutjuta@HOTMAIL.COM31_Tue, 23 Dec 2008 11:44:46 +0000364_utf-8 Adam,
Probably not much help but we often use more space than that and MSG(Y) gives us UTPRINxxx with occasionally useful messages but apart from that try limiting the log records being sorted with table filters of only the CATALOG tables your interested in (there might be a lot of useless SYSHIST rows or similar that are giving you a problem) [...]
4382 95 28_Re: SQL Access path Question10_Fred Edgar18_fredgarx@YAHOO.COM31_Tue, 23 Dec 2008 08:59:27 -0800526_iso-8859-1 I pretty much agree with what the other replies. DB2 might use your index if you use "optimize for 1 row". This could perform much worse than a tablespace scan if a large percentage of the rows qualify. Or it might use your index if you added COLA, COLB and COLD to your index so DB2 could use index-only access. Bottom line is, how many rows qualify? Looks like all of them might. If they all do, you can't beat a TS scan, unless you have an index with all the columns you reference and it has few pages than [...]
4478 57 20_Re: Question on RECP12_Fred Johnson21_fred.johnson@JNLI.COM31_Tue, 23 Dec 2008 16:59:50 +0000468_utf-8 A query against SYSIBM.SYSCOPY should give you the utilities that ran against the tablespace. My experience is that failed LOADS are recorded in the SYSCOPY(At the beginning of the load phase?). The RBA from SYSCOPY could be used as a recovery point assuming you want a PIT recovery. A query against syscopy can also give you an idea of the jobnames and authids that are running utilities against the tablespace allowing you to investigate archived sysout. [...]
4536 47 57_Re: AW: [DB2-L] DB2 V8 New Keyword for Create/Alter Table24_SUBSCRIBE DB2-L Muthuraj24_muthuraj1983@YAHOO.CO.IN31_Tue, 23 Dec 2008 17:42:04 +0000604_UTF-8 Barbara,
Tables which are having widely varying number of rows should be having this option enabled. the best example is 'physical temporary table' which will be used to keep intermediate result sets. For this kind of tables, the table might be empty when you run the runstats. when you access the table, it might have a good number of records. Since the runstats says zero records, accesspath ll be chosen as 'tablespace scan', which will kill the performance of the SQL. By enabling this option, you can make sure db2 ll use indexes whenever possible rather than tablespace scan. But [...]
4584 49 50_[LUW] Leveraging DB2 Snapshot Administrative Views23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Tue, 23 Dec 2008 14:45:46 -0600408_ISO-8859-1 This article will show the DB2 LUW version 9 administrator how to use Unix shell scripts to build a home-grown database performance history system without resort to third-party tools. Shell scripts are used to automate performance capture and provide 24x7 monitoring support. The author also provides 40 views representing calculated common metrics of the resulting performance history data. [...]
4634 45 53_Cardinality and distribution statistics DB2 v8.1 z/OS10_Nicola Nur17_nick_nur@YAHOO.CA31_Tue, 23 Dec 2008 14:01:24 -0800368_us-ascii Hi colleagues
I have a problem and hope to get some advice and insight from you. To improve the access path of SQL statements in DB2 v8.1 I have been using Visual Explain Analyze button to generate the cardinality and distribution stats. That is a very time consuming process. My boss asked why can't we productionize collecting these statistics? [...]
4680 65 57_Re: Cardinality and distribution statistics DB2 v8.1 z/OS14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 24 Dec 2008 08:49:43 +0000559_UTF-8 Instead of going through all SQL, I would go through all tables. Or actually, all columns of all tables. Well, of course just the ones where you expect benefits. That is: where you expect (high) correlation between pairs of columns. Do you have tables where some logical piece of info is spread over two or more columns? If so, do you expect certain values in one column to be more prominently present when the other column has a certain value? Examples *could* be: - postal code (zip code) and city name - date and order number - product name and [...]
4746 96 31_Presentations from Isaac Yassin9_DB2usa!!!19_db2usa3@HOTMAIL.COM31_Wed, 24 Dec 2008 03:56:45 -0500351_Windows-1252
Hi DB2 user, Here is a non-profit Blog about DB2 for z/OS (IBM mainframes):http://db2usa.blogspot.com Last update on Monday, December 22nd 2008 Here are several presentations from Isaac Yassin about DB2 for z/OS:
- Where did I loose my memory?by Isaac Yassin
- My Memory's not what it used to beby Isaac Yassin [...]
4843 33 70_Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 24 Dec 2008 10:38:47 +0100547_US-ASCII This is a major "challenge" that we all face! We started doing research about three years ago into a tool that would read all static and dynamic SQL, parse the statements for "usable" COLGROUP defs and generate a list of colgroup defs for Runstats utils. It very quickly became clear that most if not all DBA "know" the SQL that needs all this info and so a tool would never make it to market. This is obviously apparent as to my knowledge no other vendor has tried my "Runstats Optimizer Logic" - My firm does do this but purely as [...]
4877 82 74_Re: Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS14_Grainger, Phil20_Phil.Grainger@CA.COM31_Wed, 24 Dec 2008 11:17:54 -0000558_iso-8859-1 Doesn't Statistics Advisor (in the OSC) do this? It may be one of the chargeable components though, I still haven't really figured out IBMs packaging
Of course, there is the added challenge of whether OSC will work with Db2 V8
Phil Grainger CA
PS Happy Christmas to all
________________________________
From: DB2 Data Base Discussion List on behalf of Roy Boxwell Sent: Wed 24/12/2008 09:38 To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS [...]
4960 24 74_Re: Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Wed, 24 Dec 2008 11:36:37 +0000721_utf-8 I don't know if it'll do what you want but in DB2 for z/OS, Version 8, Statistics Advisor was added to Visual Explain.
Cheers, Adam
______________________________________________________________________
* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events * ______________________________________________________________________
IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html
4985 109 74_Re: Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS0_21_tom_moulder@1SCOM.NET31_Wed, 24 Dec 2008 11:16:58 -0500594_ISO-8859-1 Phil
I haven't checked recently, but last time I looked this facility was provided on a statement by statement basis as opposed to looking at all statements executed in -- say the last month...
What I have found is that what is good for one statement is not necessarily good for another and may actually make it worse. What I have experienced is that the best thing to do is get all your statistics set and then trust the optimizer. At least make sure that the objects referenced in statements that cause the greatest problems have correct statistics. Failing all [...]
5095 59 34_Strange -805 after V7/V8 migration10_Meir Zohar19_zmeyer@BEZEQINT.NET31_Wed, 24 Dec 2008 18:42:43 +0200448_us-ascii Happy holidays all...
Just in time for change freeze and right after migrating from v7 to v8 we're seeing a strange DB2 behavior change that perhaps some of you have seen as well.
Site is running a small distributed application.
ProgA accesses DB2A and calls ProgB that accesses DB2B.
ProgB has a bound package on DB2B which is also bound as remote using PKLIST on the ProgA plan (DB2B.collid.package-id). [...]
5155 158 74_Re: Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS13_David Simpson22_dsimpson@THEMISINC.COM31_Wed, 24 Dec 2008 13:24:19 -0500395_us-ascii OSC will allow you to run the statistics advisor against an entire workload. I think this is one of the great DBA challenges of the V8/9 era. The new stats can help solve a myriad of problems, but figuring out how often to gather them (since they are often very expensive to collect) and remembering what "extra" stats to put back after a drop/recreate is a significant challenge. [...]
5314 106 38_Re: Strange -805 after V7/V8 migration10_Fred Edgar18_fredgarx@YAHOO.COM31_Wed, 24 Dec 2008 11:18:56 -0800456_iso-8859-1 I think you'll need to bind one of the packages on both DB2's, probably ProgB in your case. You might need to specify SQLERROR CONTINUE on DB2A to get it to bind "successfully". This has been a pain for us. We've resolved it by having a program in the middle that is bound to all DB2's. We call it and it does the connect to the appropriate DB2 before calling what would be your ProgB. Hope that helps. V7 and before made more sense to me. [...]
5421 83 74_Re: Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 24 Dec 2008 13:18:00 -0800700_iso-8859-1 OSC works fine with DB2 V8. Not 100% of all features work, but most, including all the equivalent of VE. ________________________________ From: "Grainger, Phil" To: DB2-L@www.idugdb2-l.org Sent: Wednesday, December 24, 2008 6:17:54 AM Subject: Re: [DB2-L] Antwort: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS Doesn't Statistics Advisor (in the OSC) do this? It may be one of the chargeable components though, I still haven't really figured out IBMs packaging Of course, there is the added challenge of whether OSC will work with Db2 V8 Phil Grainger CA PS Happy Christmas to all ________________________________ From: DB2 Data Base Discussion [...]
5505 63 38_Re: Strange -805 after V7/V8 migration14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 25 Dec 2008 13:26:13 +1100288_US-ASCII One of the features of DB2 pre-V8 was that a program did not need to be bound locally if it only used a EXEC SQL CONNECT locally (or a restricted set of other statements). In V8 it does. Search the V8 documentation for PKGLDTOL - a V7 zparm that highlighted this situation. [...]
5569 126 43_Interesting Forbes article about IT service23_Joel Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 25 Dec 2008 11:46:42 -0500716_iso-8859-1
----- Original Message ----- From: Joel Responsive Systems To: Goldstein, Joel Sent: Thursday, December 25, 2008 11:14 AM Subject: Forbes.com: Trying Harder In Hard Times
Trying Harder In Hard Times IT departments with poor customer service might not survive the recession. By Mike Schaffner
http://www.forbes.com/2008/12/23/cio-customer-service-tech-cio-cx_ms_1224service.html?partner=email
Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance too www.responsivesystems.com [...]
5696 132 28_Re: SQL Access path Question14_Larry Kintisch19_lkint@IX.NETCOM.COM31_Thu, 25 Dec 2008 13:55:21 -0500375_us-ascii Hi Peter,
Wayne's comment about the "index-only" access with a "fat index" [see Tapio's course description in my link below] is true. If all rows were COLC = ' ' then none would qualify as > ' ' and the index-only access would return the SQLCODE +100 [SQLSTATE '02000'] blazingly fast! The table scan would take about 20 seconds [using QUBE estimation]. [...]
5829 119 40_Re: [FLUFF] Twelve Days of Change Freeze10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 26 Dec 2008 13:06:09 -0500443_US-ASCII Angela.
Good post
A very merry Christmas to you and best wishes for a happy & healthy new year.
Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Fung, Chi-Yun Sent: Monday, December 22, 2008 11:47 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] [FLUFF] Twelve Days of Change Freeze [...]
5949 107 57_Re: Cardinality and distribution statistics DB2 v8.1 z/OS10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 26 Dec 2008 13:20:58 -0500393_US-ASCII Nicola, I do not know how frequently you run Runstats, but you should be generating cardinality and distribution stats for all index columns. This is done by specifying the Table (owner.tb_name) Column (coln1, coln2...) clause in your Runstats.
I have a REXX that will generate the Runstats statement for a specific tablespace. If you are interested, contact me off-list. [...]
6057 245 36_Re: IBM DB2 Admin Tool Survey (z/OS)10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 26 Dec 2008 13:36:07 -0500382_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...]
6303 267 45_Re: DB2 V8 New Keyword for Create/Alter Table10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 26 Dec 2008 13:44:21 -0500382_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. [...]
6571 118 36_Re: IBM DB2 Admin Tool Survey (z/OS)10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 26 Dec 2008 13:48:59 -0500477_iso-8859-1 Agreed, if you don't use or need the tool, then the IBM tool may be satisfactory. If you need a real tool, then you need to buy one, and they cost more.
Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long Sent: Sunday, December 21, 2008 2:48 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] IBM DB2 Admin Tool Survey (z/OS) [...]
6690 153 36_Re: IBM DB2 Admin Tool Survey (z/OS)15_Joehlin, Gary L34_gary.l.joehlin@VERIZONBUSINESS.COM31_Fri, 26 Dec 2008 18:59:02 +0000432_iso-8859-1 I've used the ADB tool on DB2 V7 and V8.
Because of past issues, we are stuck with an older release of the ADB tool.
Have the following been addressed?
The ability for the tool to change the LOAD utility statements to convert to use: LOAD REPLACE (especially when not dropping DDL to another environment) so you don't have to manually change the hundreds) NOCOPYPEND, for the same reasons above [...]
6844 327 45_Re: DB2 V8 New Keyword for Create/Alter Table14_Amsden, John W19_John.Amsden@LFG.COM31_Fri, 26 Dec 2008 14:34:26 -0500628_iso-8859-1 We've done the same thing with successful results. A handy option for this type of table.
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Roger Hecq Sent: Friday, December 26, 2008 1:44 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 V8 New Keyword for Create/Alter Table
We have used the Volatile parameter successfully on tables that are emptied nightly and then repopulated during the next day. With the volatile option, the bind generates index access paths, even if the table was empty when the Runstats utility was run. [...]
7172 90 57_Re: Cardinality and distribution statistics DB2 v8.1 z/OS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 26 Dec 2008 14:57:12 -0500440_us-ascii Roger, I'd like a copy of that -- would you be willing to post it in the Code Place? Or just email it to me here?
--Phil Sevetson psevetson@fisa.nyc.gov
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq Sent: Friday, December 26, 2008 1:21 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Cardinality and distribution statistics DB2 v8.1 z/OS [...]
7263 53 56_[LUW] blog article: DBA Holistic Scheduler Consciousness23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Fri, 26 Dec 2008 21:25:01 -0600426_ISO-8859-1 http://www.ebenner.com/db2dba_blog/?p=433
Summary: A good database administrator develops an awareness of the large number of scheduled processes running day and night that impact the database, inside her team's purview but possibly cutting into the jurisdiction of other departments. To gain this awareness, the DBA in a large organization will require both technical skill and social/political savvy. [...]