1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2010, week 1
2 28 41_Garth Oldham/Australia/IBM is unavailable12_Garth Oldham19_vcgarth@AU1.IBM.COM30_Fri, 1 Jan 2010 16:08:54 +1100394_US-ASCII I will be out of the office starting 01/01/2010 and will not return until 25/01/2010.
I am unavailable to attend to your email between the 1st and 25th of January 2010. For urgent requests please email the DBDCSUP@AU1.IBM.COM mailbox. For any Requested product maintenance please send the request to DBDCSUP@AU1.IBM.COM it will be sent to a senior DB2 person for approval. [...]
31 69 65_AUTO: John Iczkovits is out of the office. (returning 01/04/2010)14_John Iczkovits19_iczkovit@US.IBM.COM30_Fri, 1 Jan 2010 04:05:51 -0700542_US-ASCII
I am out of the office until 01/04/2010.
Note: This is an automated response to your message "?DB2-L Digest - 31 Dec 2009 to 1 Jan 2010 (#2010-1)" sent on 12/31/09 18:16:13.
This is the only notification you will receive while this person is away. _____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
101 196 49_Redbook DB2 9 for z/OS: Using the Utilities Suite10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Mon, 4 Jan 2010 04:39:51 -0500721_iso-8859-1
Hi DB2 user,
Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com
Last update on Monday, January 4th 2010
IBM just published a draft version of a new Redbook, "DB2 9 for z/OS: Using the Utilities Suite, SG24-6289-01":
Abstract IBM continues to enhance the functionality, performance, availability, and ease of use of DB2 utilities. This IBM Redbooks publication is the result of a project dedicated to the current DB2 Version 9 Utilities Suite product. It provides information on introducing the functions which help in setting up and invoking the utilities in operational scenarios, it shows how to [...]
298 41 44_Re: DB2 STORED PROCEDURE FROM COBOL PROGRAM.7_Jan tje25_jan.moeyersons@ADELIOR.BE30_Mon, 4 Jan 2010 13:16:15 +0000380_utf-8 On Mon, 28 Dec 2009 13:02:35 -0800, mallik vemugunta wrote:
>When I call using the below I am getting compilation error on SQLSTATE as undefined host variable where I defined SQLSTATE AS >? >EXEC SQL BEGIN DECLARE SECTION END-EXEC.??????????? >??? 01 SQLSTATE PIC X(5).?????????????????????????? >EXEC SQL END DECLARE SECTION END-EXEC.???? [...]
340 37 31_DEFAULT STATS AND STATS REMOVAL5_David17_mairecj@YAHOO.COM30_Mon, 4 Jan 2010 16:50:41 +0000368_utf-8 Anyone know the default statistics DB2 (V8) uses for a table and index against which statistics have never been gathered (CARD = -1/NLEAF = - 1) ? Does DB2 dynamically sample the object without stats to gather a better idea for the optimizer? Has anyone ever removed all stats for an object in order to improve/restore performance? If so, how is that done? [...]
378 317 148_Upcoming IDUG Webcast - "The DB2Night Show Episode #10 - "DEAR IBM, ... ...", Scott Hayes, DBI Software" - Friday 8 January 2010 at 10:00 am USA CST13_David Chapman24_David.Chapman@IAG.COM.AU30_Tue, 5 Jan 2010 17:17:17 +1100814_us-ascii
_______________________________________________________________________________________
Note: This e-mail is subject to the disclaimer contained at the bottom of this message. _______________________________________________________________________________________
Hello DB2 List,
Here is an upcoming webcast that you may be interested in.
The DB2Night Show Episode #10 - 08 Jan 2010 - "DEAR IBM, ... ...", Scott Hayes, DBI Software
The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. This live event will run approximately 60 minutes and participants will have the opportunity to pose questions to the presenter. Capacity is limited, so register [...]
696 238 35_Re: DEFAULT STATS AND STATS REMOVAL11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 5 Jan 2010 07:52:23 +0100512_ISO-8859-1 Answers in order of questions
NLEAF -1 is equal to SYSTABLES.CARDF / 300 CARDF -1 is equal to 10,000
No it does no sampling *ever*
Yes - Lots of times! I do it two ways (1) Drop and recreate object (2) SQL UPDATEs against the catalog
Not that I know of!
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: R.Boxwell@seg.de http://www.seg.de [...]
935 144 6_ignore6_Leslie21_db2dba@BTINTERNET.COM30_Tue, 5 Jan 2010 15:21:11 -0000540_us-ascii is the list working?
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...]
1080 149 9_DB2-L Up?18_Khanal Robby (DTI)24_Robby.Khanal@STATE.DE.US30_Tue, 5 Jan 2010 10:23:31 -0500613_us-ascii Is the list up? Haven't received anything lately.
Thanks, Robby Khanal
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...]
1230 24 10_Re: ignore12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Tue, 5 Jan 2010 15:29:34 +0000614_utf-8 ...I couldn't ignore it...The list wasn't working earlier today but now it is! New Year Gremlims.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...]
1255 27 35_CLOB effects on a Coupling Facility13_Robert Glover21_bob.glover@LPSVCS.COM30_Tue, 5 Jan 2010 15:54:01 +0000521_utf-8 We run a mixed V81 environemnt (CMP/NFM). We will soon be introducing CLOBs into a data sharing environment. Does anyone who has done this know how to calculate the Coupling Facility storage needs for CLOBS ? What other types of fallout should I be looking for?
Thanks.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
1283 214 41_Buffer pool settings. DB2 V8 on Z/OS 1.1016_Proctor, William25_William.Proctor@TGSLC.ORG30_Tue, 5 Jan 2010 10:07:19 -0600542_us-ascii Thanks for all replies in advance. We are experimenting with pinning certain tablespaces into their on bufferpools along with the primary index in it's own pool. I expected to see the hit ratio's on those pools to start out low but get to pretty close to 100% after most of the data was accessed. On several of the pools I am getting good numbers but on a couple of them the numbers are staying low around 27% and on one of the pools it is -132. I have enough buffers assigned so that the tablespace will fit. Can someone point [...]
1498 193 35_Re: DEFAULT STATS AND STATS REMOVAL16_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 5 Jan 2010 11:37:16 -0500577_ISO-8859-1 With respect to getting a handle on the percentage of dynamic versus static SQL statements executed on a DB2 for z/OS subsystem, you might want to use your DB2 monitor product to generate a Statistics Long Report (might be called a Statistics Detail Report -- the name of the report varies somewhat from one vendor's DB2 monitor to another) for a time period of interest (a 2-hour period of peak activity, a 24-hour period, whatever). In the report, you'll see a block of figures under the heading SQL DML (as with the name of the report, the actual wording of [...]
1692 34 22_New Redbook on Locking11_Mike Turner19_mike.turner@GMX.COM30_Tue, 5 Jan 2010 16:54:19 -0000613_iso-8859-1 For the locking anoraks amongst you:
http://www.redbooks.ibm.com/abstracts/sg244725.html?Open
Regards Mike Turner Email: mike.turner@gmx.com Tel: +44 (0)1565-873702 Web: www.michael-turner.ltd.uk Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
1727 38 35_Re: DEFAULT STATS AND STATS REMOVAL25_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM30_Tue, 5 Jan 2010 17:04:05 +0000569_utf-8 Thank you, Roy and Robert, for your very prompt and helpful responses.
Is there a utility or sample code in the Admin manual to use to restore stats on an object back to the default? In our case re-creating the object is not currently an option.
In V8 and V9, if the RTS is not being externalized to tables, does DB2 still collect the RTS stats by default every 30 mins? I recall reading that RTS may take an additional 4-5% overhead. Does this overhead apply to the externalization alone or the ongoing collection whether externalized or not? [...]
1766 241 45_Re: Buffer pool settings. DB2 V8 on Z/OS 1.1016_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 5 Jan 2010 14:48:43 -0500432_windows-1252 Where are you getting the numbers you've provided, Bill? From a DB2 monitor? If so, do you know how the figures were calculated (sometimes this can be found in the monitor's documentation)? For example, the 27% figure -- what's that? Depending on how the ratio is calculated, it's conceivable that the negative value you're seeing has something to do with a preponderance of asynchronous versus synchronous reads. [...]
2008 144 13_Re: DB2-L Up?16_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 5 Jan 2010 15:54:58 -0500309_windows-1252 Looks as though the service provider used by DB2-L has again ended up on some spam-blocker lists. Check your spam folder for DB2-L messages until they get this cleared up. The DB2-L admin folks have to deal with this problem every once in a while -- hard to avoid, and it's not their fault. [...]
2153 390 45_Re: Buffer pool settings. DB2 V8 on Z/OS 1.1035_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Tue, 5 Jan 2010 16:33:52 -0500421_Windows-1252 Without seeing any numbers or data - One obvious thought here - if you are still having I/O, then the objects do not fit into the pools.
Even though the hit ratio is useless for performance measurement, it would be 100% if all pages stayed in the pool.
A negative hit ratio is quite commonly cause by dynamic prefetch, because many pages are read in that the applications never reference. [...]
2544 49 46_CLOB / BLOB data move in federated environment8_Nagasuri20_r_nagasuri@YAHOO.COM30_Tue, 5 Jan 2010 14:09:36 -0800743_us-ascii Hello All,
Can anyone please let me know the possible ways to move BLOB/CLOB data across databases (federated environment) thro java or stored procedure.
Thanks in advance, -Ram
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...]
2594 31 29_decription support DB2 v8 NFM20_Arthur Lee Robertson26_robertsona@FINANCE.NYC.GOV30_Tue, 5 Jan 2010 22:27:21 +0000388_utf-8 Can I get AES data decryption support with DB2 v8 NFM running on z/OS as a server? I think the answer is no, not until DB2 v9, correct? Under DB2 v8 NFM I maybe able to get AES credential decryption support if I have the latest and greatest PTF's and ICSF properly installed, but I will have to settle for DES data decryption until DB2 v9. Let me know. Many thanks in advance. [...]
2626 133 39_Re: CLOB effects on a Coupling Facility16_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 5 Jan 2010 18:01:46 -0500593_ISO-8859-1 For a LOB tablespace, the default value for the GBPCACHE option is SYSTEM. With this GBPCACHE value in effect (and it is only valid for a LOB tablespace), only changed system pages (space map pages and other pages that don't contain actual LOB data) will be written to the associated group buffer pool in the coupling facility. There is a formula for calculating the size of a group buffer pool needed to hold LOB tablespace system pages (assuming GBPCACHE SYSTEM is in effect for a LOB tablespace) in the DB2 for z/OS V8 Data Sharing Planning and Administration Guide. Here's [...]
2760 57 61_DB2 SQL error: SQLCODE: -930, SQLSTATE: 57011, SQLERRMC: null10_Paul Chang18_pyfchang@YAHOO.COM30_Tue, 5 Jan 2010 18:42:56 -0800302_iso-8859-1
Hi, I am new to the db2 v9.(it is not z/OS) When I connect db2 v9, I got Cause: DB2 SQL error: SQLCODE: -930, SQLSTATE: 57011, SQLERRMC: null error. I cannot find error code with -930 in the db2 doc. If anybody knows, please explain the error messages. thanks in advance. Paul. [...]
2818 506 65_Re: DB2 SQL error: SQLCODE: -930, SQLSTATE: 57011, SQLERRMC: null12_Phil Gunning19_pkgunning@GMAIL.COM30_Tue, 5 Jan 2010 22:32:20 -0500578_iso-8859-1 See below, it is in the doc. Below from DB2 command line:
è/gts1/ db2 ? sql0930n
SQL0930N There is not enough storage available to process the
statement.
Explanation:
A request was made to the database that required another memory page but
no more pages are available to the database manager.
The statement cannot be processed.
User response:
Possible solutions include: [...]
3325 134 35_Re: DEFAULT STATS AND STATS REMOVAL11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 6 Jan 2010 07:11:38 +0100697_ISO-8859-1 not that I am aware of....to reset an objects statistics is not that hard...
Just set the relevant optimizer used columns in the catalog to their default values. To find the columns use the UTILITY guide and check out RUNSTATS updates for access - from these docu tables you can see that you have to update six catalog tables SYSCOLUMNS (COLCARDF, HIGH2KEY, LOW2KEY) SYSCOLSTATS (COLCARD, HIGHKEY, LOWKEY) - If PK62804 is applied SYSINDEXES (CLUSTERRATIOF, FIRSTKEYCARDF, FULLKEYCARDF, NLEAF, NLEVELS if in V9 DATAREPEATFACTOR) SYSTABLES (CARDF, NPAGES, NPAGESF, PCTROWCOMP) SYSTABLESPACE (NACTIVE, NACTIVEF) SYSTABSTATS (CARDF, NPAGES) and finally delete all rows for your [...]
3460 71 40_DB2 for z/OS V9 utility and RACF Lockout13_Irwin Deutsch28_ideutsch@PRINCESSCRUISES.COM30_Wed, 6 Jan 2010 10:09:24 -0800297_US-ASCII Hi,
Kind of a long shot, but for the second time we had three LPARs basically stop due to a lockout on RACF database. Both times a DB2 image copy job was running and hung too, plus it held. This job was an owner (shr) of SYSZRACF qname for 5 min. this last time two days ago. [...]
3532 34 35_What is DB2 Doing Index Compression10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 6 Jan 2010 19:19:53 +0000476_utf-8 This is for DB2 9 on z/OS.
We can alter the index bufferpool to BP8K2 then Compress Y. This puts the index in rebuild pending status. But if one removes that flag the index is accessible via BP8K2 with no obvious adverse affects. Obviously the index dataset is not compressed and presumable the software decompression is running. But would there be any adverse affects in removing the rebuild idnex flag? I suppose we should see what happens on an Insert... [...]
3567 40 39_Re: What is DB2 Doing Index Compression15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Wed, 6 Jan 2010 14:13:50 -0700355_utf-8 I have not tried something like that, but my impression is that it is like loading a double-barreled shotgun, aiming it at your foot and pulling the trigger.
At best, you now have a catalog that says it is compressed and an index that is not. How different parts of DB2 and other utility vendors products play with that is up for grabs. [...]
3608 126 44_Re: DB2 for z/OS V9 utility and RACF Lockout11_Nick Cianci19_ncianci@AU1.IBM.COM30_Thu, 7 Jan 2010 09:45:22 +1100395_ISO-8859-1 Hi Irwin We are v8-NFM and were occasionally getting a similar RACF issue with our ReOrgs. Our version of z/OS is "some-what" older.
DSNU060I - DSNUGMAP - USER NOT AUTHORIZED FOR REORG UTILITY ON DATABASE DSNU1027I DSNUGULM - PROCESSING CONTINUES DUE TO OPTIONS ITEMERROR SKIP DSNU1033I DSNUGULM - PROCESSING LIST ITEM: TABLESPACE . [...]
3735 271 44_Re: BMC Aptune SQL statement DEtail analysis10_Dave Barry14_dbarry@UPS.COM30_Wed, 6 Jan 2010 18:07:44 -0500410_windows-874 Check with BMC. My guess is it's their measure of what IBM refers to as "unaccounted time." We see it sometimes in PM reports, and usually it is a sign of CPU starvation. Other causes are discussed at: http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg21045823
DB2 Class 2 Not Accounted Time =
DB2 Class 2 Elapsed time - (DB2 Class 2 CPU time + DB2 Class 3 suspension time) [...]
4007 144 33_Re: decription support DB2 v8 NFM16_Robert Catterall21_rfcatterall@GMAIL.COM30_Wed, 6 Jan 2010 18:50:39 -0500559_ISO-8859-1 My understanding, Arthur, is that AES *data* encryption and decryption (versus AES *password and user ID *decryption for remotely originating requests to a DB2 for z/OS server) is not built into DB2 for z/OS, whether you're talking about DB2 V8 or V9. I believe that AES data encryption and decryption in a DB2 subsystem depends on the use of an EDITPROC or a FIELDPROC that utilizes the ICSF encrypt and decrypt data stored in a table. You can write your own edit or field procedure to do this, or you can use a tool to do that. The IBM Data [...]
4152 102 27_DSNU000I message in z/OS V911_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 7 Jan 2010 14:27:31 +0100491_ISO-8859-1 Hi all!
Just wondering if I've gone mad or not....when I look at my last utility job I see the changed V9 message DSNU000I
DSNU000I 007 13:44:45.72 DSNUGUTC - OUTPUT START FOR UTILITY,
Now I like being James Bond but I thought that aJulian date was of the form YYDDD and not just DDD....has anyone got a DB2 V9 utility output from 2009 ?!?!? Just in case its anohter year 2010 bug (we have loads here in germany at the moment...BCD to Hex is not good!!) [...]
4255 80 16_DSNU000I message11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 7 Jan 2010 14:33:19 +0100461_ISO-8859-1 hmmm I just found some more in the V9 docu so I guess it "works as designed" but it is not "julian"....
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: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
4336 229 39_AW: [DB2-L] DSNU000I message in z/OS V915_Martin Ketterer31_MARTIN.KETTERER@DE.KAERCHER.COM30_Thu, 7 Jan 2010 15:04:01 +0100362_iso-8859-1 Hi Roy I hope you still can get money with your EC card ( I managed it). But not everything that seems to be a bug is actually one:
DSNU000I 339 10:03:51.19 DSNUGUTC - OUTPUT DSNU1044I 339 10:03:51.24 DSNUGTIS - PROCESS DSNU050I 339 10:03:51.25 DSNUGUTC - REORG
is the beginning of a reorg utility job output which ran in december. [...]
4566 500 39_AW: [DB2-L] DSNU000I message in z/OS V912_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM30_Thu, 7 Jan 2010 15:31:06 +0100531_iso-8859-1 Hi dear Roy !
I just checked a DSNUTILB (copy) on 24.XII.2009 under DB2/V9 & z/OS-1.9 --> 2009 same as 2010 (*ggg*) --> PS: DSNUTILB (reorg) just the same ... year is missin' too in 2009 !
DSNU000I 358 02:25:18.56 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = userid:jobname
Kinds / Othmar
Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Roy Boxwell Gesendet: Donnerstag, 07. Jänner 2010 14:28 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: [DB2-L] DSNU000I message in z/OS V9 [...]
5067 118 31_Re: DSNU000I message in z/OS V914_Peter Backlund21_BacklundDB2@TELIA.COM30_Thu, 7 Jan 2010 15:32:07 +0100
5186 30 50_[TO ADMIN] - List not available again this morning12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Thu, 7 Jan 2010 15:10:12 +0000538_utf-8 Hi. On tuesday morning and again this morning (European time) the list hasn't been available. On entering: http://www.idug.org/cgi-bin/wa?A0=DB2- L the url just returns a blank page. I see from other postings that there have been some problems this week. Any info re a fix?
Cheers, Adam
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
5217 216 35_[AD] IBM DB2 Connect Training Class7_Kim May28_kim.may@THEFILLMOREGROUP.COM30_Thu, 7 Jan 2010 10:17:15 -0500363_us-ascii The Fillmore Group is scheduled to deliver IBM authorized training class CF602, DB2 Connect 9 for z/OS DRDA Implementation with TCP/IP, in our Baltimore, Maryland classroom beginning Monday, January 25th. The class is 4 days in length and costs $2600.
If you are interested in attending please register on the IBM website at: [...]
5434 69 31_MQT not recognized by optimizer12_Mark Vickers26_markvickers@GROCERYBIZ.COM30_Thu, 7 Jan 2010 16:18:29 +0000432_utf-8 I created my first MQT and cannot get the optimizer to pick it up ?
Original Query: SELECT SUM ( DRW_QTY ) , SUM ( DRW_QTY * AD_VAL ) INTO : H : H , : H : H FROM WCSSTRQH WHERE RQ_TYP_CD = : H AND ( : H = : H OR STA_DT >= : H ) AND ( : H = : H OR END_DT <= : H ) AND STR_RLP_MBR_ID IN ( SELECT RLP_CHLD_MBR_ID FROM WCSSTRPA WHERE RLP_PRNT_MBR_ID = : H AND RLP_PRNT_LVL_ID = : H AND RLP_CHLD_LVL_ID = : H ) WITH UR [...]
5504 46 35_Re: MQT not recognized by optimizer14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Thu, 7 Jan 2010 10:23:18 -0600342_utf-8 Forgot to add that I did a refresh on the MQT before runstats.
-----Original Message----- From: Mark E Vickers Sent: Thursday, January 07, 2010 10:18 AM To: DB2-L@WWW.IDUGDB2-L.ORG Cc: Mark E Vickers Subject: MQT not recognized by optimizer
I created my first MQT and cannot get the optimizer to pick it up ? [...]
5551 57 35_Re: MQT not recognized by optimizer13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 7 Jan 2010 12:28:34 -0500642_UTF-8 This is buried in the doc somewhere....
MQTs are only for dynamic SQL. Bound package will not use it.
______________________________________________________________________________ David Simpson | Senior Technical Advisor | Themis Education 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com For more information about Themis, visit www.themisinc.com
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark E Vickers Sent: Thursday, January 07, 2010 10:23 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] MQT not recognized by optimizer [...]
5609 120 35_Re: MQT not recognized by optimizer7_Ed Long19_rdhm99a@PRODIGY.NET30_Thu, 7 Jan 2010 10:01:44 -0800452_us-ascii Interesting. So, if a statically bound program prepared and then executed a statement against an MQT, would DB2 should see the MQT ? First guess is yes but I also had the Pats over Houston!
Edward Long
________________________________ From: David Simpson To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, January 7, 2010 12:28:34 PM Subject: Re: [DB2-L] MQT not recognized by optimizer [...]
5730 534 35_Re: MQT not recognized by optimizer13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 7 Jan 2010 13:38:13 -0500469_us-ascii Someone else emailed me off list for this clarification... my original post was a bit terse.
MQTs may be directly referenced by any SQL static or dynamic. Automatic query rewrite is only for dynamic. The zParms mentioned my Mark control the defaults for special registers CURRENT REFRESH AGE and CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION, but we don't have any equivalent bind options to control this behavior for static SQL. [...]
6265 524 35_Re: MQT not recognized by optimizer13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM30_Thu, 7 Jan 2010 18:38:56 -0000607_us-ascii Indeed yes
An MQT also behaves like a regular DB2 table if you reference it directly
Phil Grainger
Grainger Database Solutions Ltd
_____
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long Sent: 07 January 2010 18:02 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] MQT not recognized by optimizer
Interesting. So, if a statically bound program prepared and then executed a statement against an MQT, would DB2 should see the MQT ? First guess is yes but I also had the Pats over Houston! [...]
6790 382 35_Re: MQT not recognized by optimizer14_Mark E Vickers26_MarkVickers@GROCERYBIZ.COM30_Thu, 7 Jan 2010 12:53:17 -0600462_us-ascii Well after David's response, I tried to prepare a Text SQL in OCS and it did not pick up the MQT. I tried rebinding the program with REOPT(ALWAYS) and still no MQT. It baffles me why they would put this in just for Dynamic - another SAS agreement !?!
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long Sent: Thursday, January 07, 2010 12:02 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] MQT not recognized by optimizer [...]
7173 624 35_Re: MQT not recognized by optimizer13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 7 Jan 2010 14:15:30 -0500409_us-ascii An interesting experiment might be to explain a query to the MQT directly using OSC and compare the cost estimates to your query that will not re-write. If optimizer perceives the direct table access to be less costly it will go directly to the tables.
Make sure in the OSC "Query Context" window that you have CURRENT REFRESH AGE set to ANY and the other one set to ALL. [...]
7798 175 35_Re: MQT not recognized by optimizer23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM30_Thu, 7 Jan 2010 13:21:28 -0600389_ISO-8859-1 How the optimizer uses your summary table depends heavily on the CURRENT REFRESH AGE special register. If that register is set to the default value it will not use your MQT because you created it REFRESH DEFERRED. The optimizer will only use your summary table if CURRENT REFRESH AGE is set to ANY (or all 9's). Or you could change your summary table to REFRESH IMMEDIATE. [...]
7974 447 35_Re: MQT not recognized by optimizer13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 7 Jan 2010 15:02:10 -0500607_us-ascii Hi Jeff,
This may be a difference between DB2 for z/OS and LUW...
The Performance Monitoring and Tuning guide for DB2 on z/OS says:
"DB2 supports automatic query rewrite only for read-only, dynamic queries. DB2 cannot automatically rewrite statically bound queries."
________________________________________________________________________ ______ David Simpson | Senior Technical Advisor | Themis Education 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com [...]
8422 95 54_Re: [TO ADMIN] - List not available again this morning17_Billy Sundarrajan21_billysundar@YAHOO.COM30_Thu, 7 Jan 2010 15:17:17 -0800694_iso-8859-1 Adam, There were a couple of issues, and the webteam has since addressed them: 1. idug.org IP was in multiple blacklists. The root cause was due to an incorrect rDNS entry which has since been fixed. 2. listserv software/apache configuration resulted in the web archive(wa) interface not functioning properly from time to time. the configuration issue has been fixed. Please send a email to listadmin@idug.org if you still face issues with the listserv. Cheers. ________________________________ From: Adam Baldwin To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, January 7, 2010 10:10:12 AM Subject: [DB2-L] [TO ADMIN] - List not available again this morning Hi. [...]