1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2009, week 1 2 32 21_HTML source in e-mail8_T Maulik23_TMAULIK@CO.PIERCE.WA.US31_Mon, 30 Nov 2009 22:50:17 +0000723_UTF-8 Hi,

I receive an consolidated e-mail from DB2-L every day. But I see lot of HTML source and other junk in that e-mail. How can I receive only plain text e-mail and no junk.

Thanks,

TM

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 35 81 73_AUTO: Ashvin Amin/Dallas/IBM is out of the office. (returning 12/01/2009)11_Ashvin Amin17_ashvin@US.IBM.COM31_Mon, 30 Nov 2009 18:38:57 -0700478_US-ASCII



I am out of the office until 12/01/2009.

I will be out of the office on vacation until Dec 1. I will repsond to your messages within 24 hours.

If you need immediate assistance, please contact Jack Mason at .... masonja@us.ibm.com or my manager, Maureen Kinard at ...mkinard@us.ibm.com



Note: This is an automated response to your message "DB2-L Digest - 30 Nov 2009 to 1 Dec 2009 (#2009-342)" sent on 11/30/09 18:00:03. [...] 117 531 22_Re: Space issue in DB29_SrinivasG21_SRINIVASG@INFOSYS.COM30_Tue, 1 Dec 2009 08:51:40 +0530377_utf-8 I have found it. The LOB Tablespaces are using up most of the space. The IXQTY and TSQTY set is 3600 in ZPARM. The average Space value for LOB Tablespace is 360448.

Thanks for all who have replied.

Regards, Srinivas G

From: SrinivasG Sent: Thursday, November 26, 2009 10:03 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: RE: [DB2-L] Space issue in DB2 [...] 649 38 51_Anyone found a way to do baseless assembler coding?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM30_Tue, 1 Dec 2009 09:37:01 +0200180_us-ascii The precompiler generates a branch around followed by copying the in-line literals. Has anyone found an option to make it use true literals or avoid *- references?

688 39 42_A manual with detailed IFCID descriptions?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM30_Tue, 1 Dec 2009 09:40:28 +0200191_us-ascii It seems like bits and pieces are everywhere.

Might there be some freely available code (SAS is fine) that does some work on the records. IBM manuals are quite sparse.

728 82 54_AW: [DB2-L] A manual with detailed IFCID descriptions?12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 1 Dec 2009 09:40:23 +0100615_iso-8859-1 May be this is what you are looking for:

http://www.db2expert.com/downloads/db2os390/IFCIDs.pdf

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- P Think before you print. [...] 811 417 18_Re: SQLCODE = -43813_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK30_Tue, 1 Dec 2009 08:50:35 -0000369_us-ascii I think you have answered your own question





Try dropping the MQT first (if you can) before dropping the view





DB2 is trying to prevent you dropping a view and being left with an MQT that now references a view that doesn't exist





(Mind you, the message for the -438 could be more helpful) [...] 1229 142 37_Presentations from Guide Share France10_DB2usa !!!19_db2usa3@HOTMAIL.COM30_Tue, 1 Dec 2009 04:54:19 -0500451_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, November 30th 2009



Here are several presentations from Guide Share France, available on GSE France website:





- DB2 9 for z/OS for Database Administrators by Namik Hrle





- DB2 9 for z/OS for Application Programmers by Namik Hrle [...] 1372 342 18_Re: SQLCODE = -43812_Ayalew Kassa20_ayalakassa@YAHOO.COM30_Tue, 1 Dec 2009 07:10:28 -0800393_utf-8 Hi Phil,  I also tried to drop the MQT but got the same error which is shown below.   DROP TABLE Z1CI002$.CI_BH_BUS_MTRC_1_M ;                                DSNT408I SQLCODE =  -438, SQLSTATE = 00000, UNQUALIFIED SUCCESSFUL                COMPLETION FROM ?   TOKENS PTABDBS: PTLBDBC subtask has abended: S04E        [...] 1715 23 46_Re: A manual with detailed IFCID descriptions?9_Jeff DeBo18_jd6306@HOTMAIL.COM30_Tue, 1 Dec 2009 15:42:22 +0000604_utf-8 Member DSNWMSGS in DB2 installation library SDSNIVPD contains the IFCID descriptions.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 1739 78 46_Re: A manual with detailed IFCID descriptions?13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Tue, 1 Dec 2009 10:44:13 -0500592_utf-8 Our Strobe folks rely on this pub:

http://publib.boulder.ibm.com/infocenter/tivihelp/v15r1/index.jsp?topic= /com.ibm.omegamon.xe_db2.doc_4.1/ko2welcome_pm.htm In the chapter on Report Trace Record Set, there's the IFCID record blocks.

Dave

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 else. If you received it in error please notify us immediately and then destroy it. [...] 1818 143 20_More LOB lock issues12_Denise Gantz22_denise.m.gantz@EDS.COM30_Tue, 1 Dec 2009 18:45:36 +0000473_utf-8 I have a 25-partition base table and 25 LOB auxiliary tables. We were requested to do an unload of data from our production system using specific data parameters to create a load file for a test system.

//SYSTSIN DD * DSN SYSTEM(D2P1) RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') - LIB('D2P1.DSNLOAD') END /* //SYSIN DD * SELECT * FROM PROD.MSG A WHERE A.MSG_ID IN (SELECT MSG_ID FROM PROD.MSG_SEARCH WHERE CRT_TS >=timestamp('2009-10-19-00.00.00')) [...] 1962 60 46_Re: A manual with detailed IFCID descriptions?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Tue, 1 Dec 2009 10:54:29 -0800346_utf-8 Thank you for the link, Dave, this is much better than browsing SDSNIVPD(DSNWMSGS)!

However, I must say that the info center is extremely limited. I tried to print the section for IFCID 001, and got this message:

>>> You have chosen to print 10 individual topics. The maximum number of topics allowed to be printed is 5. [...] 2023 27 73_[AD] Technical Update Part 2: CA Database Management R12 for DB2 for z/OS15_Coleman, Troy L19_Troy.Coleman@CA.COM30_Tue, 1 Dec 2009 14:01:05 -0500489_UTF-8 Hello Everyone,

Join Steen Rasmussen, Senior Engineering Services Architect, Tuesday, December 8, 2009 at 11:00 a.m. ET, for the second of two technical updates. This update will continue to focus on specific product-related enhancements found in CA Database Management r12 for DB2 for z/OS, including:

* Enhancements and updates to CA Database Backup and Recovery for DB2 for z/OS * Enhancements and updates to CA Database Performance Management for DB2 for z/OS [...] 2051 592 65_Re: ICF Catalog how to Split into Sub-Systems per IBM Recommended9_Ali, Omar25_Omar.Ali@CITYOFBOSTON.GOV30_Tue, 1 Dec 2009 15:22:44 -0500650_us-ascii Thank You all Robert I'll read the Blog Thanks Again ________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Catterall Sent: Monday, November 30, 2009 11:22 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] ICF Catalog how to Split into Sub-Systems per IBM Recommended

Hello, Anil -- sorry about the delayed response.

It may be that you've been asked to put the BSDS and active log data sets in a separate ICF catalog so as to enable your organization to utilize the BACKUP SYSTEM and RESTORE SYSTEM utilities that were introduced with DB2 for z/OS V8. I wrote a blog entry on [...] 2644 126 46_Re: A manual with detailed IFCID descriptions?7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 1 Dec 2009 12:23:42 -0800453_us-ascii I'm glad I'm not the only one bewitched bothered and bewildered by the Info Center. It does an excellent job of hiding the desired answer. I did notice that DSNWMSGS comes complete with the ability to load the IFCID descriptions into a DB2 table. That way the terse, er concise, descriptions of the field can be retrieved using an SQL statement. Call me crazy but why isn't this function in SDSNSAMP as one of the DSNTEJXX series of jobs? [...] 2771 103 46_Re: A manual with detailed IFCID descriptions?10_Teldb2kals22_teldb2kals@TELSTRA.COM30_Tue, 1 Dec 2009 20:55:02 +0000544_utf-8 Hi Cathy,

The PDF link is there right above the contents list in Info Center.

Or, it is also at this link :

http://publib.boulder.ibm.com/epubs/pdf/ko2rrd20.pdf

Regards, Kals.

From: "Taddei, Cathy" To: DB2-L@WWW.IDUGDB2-L.ORG Date: 02/12/2009 07:59 a.m. Subject: Re: [DB2-L] A manual with detailed IFCID descriptions? Sent by: IDUG DB2-L





Thank you for the link, Dave, this is much better than browsing SDSNIVPD (DSNWMSGS)! [...] 2875 42 14_DB2 V9 Upgrade10_Darsh Mann19_darsh.mann@SLMA.COM30_Tue, 1 Dec 2009 21:20:27 +0000593_utf-8 Hi All, We are currenty in the planning stages for upgrading from V8.1 RSU0907 to V9.1. in 2010. Given all the other activites that are going on in our shop, we could be dealing with a tight schedule. So, we trying to find out, based other organizations experience, the minimum time frame recommended to implement V9 to conversion mode and new function mode. Although we want to know the time frame from the day of ordering the software to implementatioin in production. But we are particulary interested in the amount of time required to adequatley test the software. I understand [...] 2918 77 18_Re: DB2 V9 Upgrade14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 1 Dec 2009 17:10:17 -0500290_us-ascii We staggered test and production: Day 1 -- implement V9CM in test; Day 31 -- Implement V9CM in prod; Day 61 -- implement V9NFM in test; Day 91 -- implement V9NFM in prod. These times are approximate as we had many more than two environments, but this is the "rhythm" we used. [...] 2996 72 16_prototype tables15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Tue, 1 Dec 2009 16:53:44 -0700260_us-ascii With DB2 for LUW you can use a common table expression to 'prototype' a table without needing to create it:

with temp(a, b) as ( values (1, 'A') , (2, 'B') , (3, 'C') ) select a , b from temp;

Results: A B ----------- - 1 A 2 B 3 C [...] 3069 133 57_Re: Approximate "break-even" when using Rowset processing10_Dave Nance16_dav1mo@YAHOO.COM30_Tue, 1 Dec 2009 16:07:40 -0800695_utf-8  Dan Lukesetich(?sp?) from YL&A has some slides that show the savings/costs associated with multi-row fetch on the YL&A website. I think there is even a behind the bar podcast where he and Susan discuss it. If I remember correct, the best savings was right around 100 records.  David Nance ________________________________ From: "Joehlin, Gary" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Mon, November 30, 2009 4:22:04 PM Subject: [DB2-L] Approximate "break-even" when using Rowset processing Hello Listers:   Has anyone derived an approximate break-even point, rule of thumb, when considering coding options: a regular cursor (open – fetch loop) and using [...] 3203 70 77_Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.10_Mark Stone17_mastone@GMAIL.COM30_Tue, 1 Dec 2009 16:23:07 -0800515_ISO-8859-1 I have an existing SP that I'm migrating to Native SQL. It runs fine in external mode but actually causes a DB2 abend when compiled and executed in Native mode. I tracked the offending statement down to a DELETE statement that follows a number of OPEN CURSOR statements. The intention being that the delete does not occur until the client (in this case VB.NET) application consumes the cursor data. The delete is a cleanup process for a table used to communicate between the application and the SP. [...] 3274 101 20_Re: prototype tables15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Tue, 1 Dec 2009 17:27:55 -0700491_us-ascii >>> On 12/1/2009 at 4:53 PM, in message <4B154A19.6F0F.0085.0@efirstbank.com>, Frank Swarbrick wrote: > With DB2 for LUW you can use a common table expression to 'prototype' a table > without needing to create it: > > with > temp(a, b) as ( > values (1, 'A') > , (2, 'B') > , (3, 'C') > ) > select a > , b > from temp; > > Results: > A B > ----------- - > 1 A > 2 B > 3 C > > 3 record(s) selected. > > I know that DB2 for z/OS does not support [...] 3376 105 81_Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.10_Todd Burch17_toddburch@MAC.COM30_Tue, 1 Dec 2009 18:48:45 -0600587_US-ASCII I'd suggest opening a PMR and providing the ABEND information. If you would have placed it here, I'd have done a search for you already. :)

Todd



On Dec 1, 2009, at 6:23 PM, Mark Stone wrote:

I have an existing SP that I'm migrating to Native SQL. It runs fine in external mode but actually causes a DB2 abend when compiled and executed in Native mode. I tracked the offending statement down to a DELETE statement that follows a number of OPEN CURSOR statements. The intention being that the delete does not occur until the client (in this case [...] 3482 78 18_Re: DB2 V9 Upgrade10_Todd Burch17_toddburch@MAC.COM30_Tue, 1 Dec 2009 18:53:06 -0600396_US-ASCII I would also suggest that you don't get too comfortable developing with DB2 V9 in test to implement in production with V8. Lots of people have been bitten by V9's "conversion mode" changes that work fine in test, but aren't compatible with V8.

It also wouldn't hurt to take inventory of any plans or packages that haven't been rebound in a while on a current version of DB2. [...] 3561 116 20_Re: prototype tables13_David Simpson22_dsimpson@THEMISINC.COM30_Tue, 1 Dec 2009 21:08:00 -0500530_us-ascii How about this.... it's quirky but it works:

with temp(a, b) as ( select 1,'A' from sysibm.sysdummy1 union all select 2,'B' from sysibm.sysdummy1 union all select 3,'C' from sysibm.sysdummy1 ) select a , b from temp;

________________________________________________________________________ ______ 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 [...] 3678 322 57_Re: Approximate "break-even" when using Rowset processing13_David Simpson22_dsimpson@THEMISINC.COM30_Tue, 1 Dec 2009 21:44:35 -0500617_UTF-8 Another thing to keep in mind for this….





People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I’ve seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the stratosphere. If you perform a multi-row operation and receive an SQLCODE of 0 then you generally have no need for GET DIAGNOSTICS, which is VERY expensive. So, check SQLCODE [...] 4001 719 57_Re: Approximate "break-even" when using Rowset processing14_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Tue, 1 Dec 2009 21:24:48 -0600727_iso-8859-1 Nice!





_____

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson Sent: Tuesday, December 01, 2009 8:45 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing





Another thing to keep in mind for this....





People like me hammer home the need to use GET DIAGNOSTICS when doing multi-row fetch & insert. This is true if you want good messages for any errors encountered. HOWEVER, I've seen a few people substitute GET DIAGNOSTICS for the normal SQLCODE check at the end of a multi-row operation. This could KILL your performance and drive your break-even point up in the [...] 4721 38 32_Sajad Javid is out of the office11_Sajad Javid19_sajad.javid@TCS.COM30_Wed, 2 Dec 2009 10:03:23 +0530660_us-ascii I will be out of the office starting 12/01/2009 and will not return until 12/04/2009.



=====-----=====-----===== Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you [...] 4760 300 162_Upcoming IDUG Webcast - "The DB2Night Show Episode #7 - SQL Server vs. DB2 and FREE DB2 - Scott Hayes, DBI Software" - Friday, 4 December 2009 at 10:00 am USA CST13_David Chapman24_David.Chapman@IAG.COM.AU30_Wed, 2 Dec 2009 15:39:37 +1100766_us-ascii

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message. _______________________________________________________________________________________



Hello DB2 List,

The DB2Night Show Episode #7 - 4 Dec 2009 - SQL Server vs. DB2 and FREE DB2 - 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 early. [...] 5061 236 55_Re: Anyone found a way to do baseless assembler coding?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 2 Dec 2009 09:41:19 +0100490_ISO-8859-1 nope. the macro IEABRC solves all the BM and B problems but the two MVCs are killers!!

I am hoping for a pre-compiler option in a "future release"...until then it will "always still generate cr*p code" as one of my colleagues nicely puts it....





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 [...] 5298 424 24_Re: More LOB lock issues11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 2 Dec 2009 09:47:48 +0100339_ISO-8859-1 I see no-one has had a go at answering this....If I recall correctly when you go from TS back to LOB you must REBIND any applications...(It might auto-rebind) so it could well be that either

a) You should rebind to get performance back or b) A rebind killed your performance

Time to check the EXPLAIN data... [...] 5723 292 11_DB2 history12_Jose Antonio17_jamorcillo@CAM.ES30_Wed, 2 Dec 2009 10:03:46 +0100453_iso-8859-1 Hi!





I have to give a basic lecture on DB2 to a group of people that know nothing about the product, I would like to talk about its history,

when it was launched, its versions, and so on.





Could anyone help me please!?!!





Thanks a lot!





__________________________ José A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Producción Informática [...] 6016 221 25_Re: HTML source in e-mail14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Wed, 2 Dec 2009 21:53:47 +1100379_US-ASCII Do you read the bottom of the emails - the line that starts "If you need to change settings"?

However, if, as seems not uncommon recently, the web interface is slow, you can send an email to LISTSERV@WWW.IDUGDB2-L.ORG containing set db2-l nohtml

If you add listserv refcard the listserver will mail you a reference of the email commands you can send. [...] 6238 162 15_Re: DB2 history11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 2 Dec 2009 12:53:23 +0100515_ISO-8859-1 I've got this ...



DB2 Lifecycle History

Version GA Date End of Marketing End of Service 1.1 1985-04-02 ? ? 1.2 1986-03-07 ? 1988-06-30 1.3 1987-06-26 ? ? 2.1 1988-09-?? ? ? 2.2 1989-09-22 ? ? 2.3 1991-10-25 ? ? 3.1 1993-12-17 2000-02-29 2001-01-31 (planned was 1998-12-31) 4.1 1995-11-30 2000-12-01 2001-12-31 5.1 1997-06-27 2001-12-31 2002-12-31 6.1 1998-06-30 2001-12-04 2005-06-30 7.1 2001-03-30 2007-03-05 2008-06-30 8.1 2004-03-26 2009-09-08 tbd 9 2007-03-16 ? ? 10 ? ? ? [...] 6401 317 15_SV: DB2 history13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Wed, 2 Dec 2009 13:04:47 +0100466_iso-8859-1 Try http://en.wikipedia.org/wiki/IBM_DB2

//Olle

________________________________ Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Jose Antonio Skickat: den 2 december 2009 10:04 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: [DB2-L] DB2 history

Hi!

I have to give a basic lecture on DB2 to a group of people that know nothing about the product, I would like to talk about its history, when it was launched, its versions, and so on. [...] 6719 153 28_Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Wed, 2 Dec 2009 14:10:23 +0100638_us-ascii We have a query like this on a 300.000 row table and there is a index on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE FROM CM_BK WHERE COLF iN ('1111410007081000','1111410007081000') OR COLG IN ('111410009H8','111410009H81', '111410009H0','111410009H08','111410009H081', '111410009H0810','111410009H08100','111410009H081000')





According to the documentation : If 2 indexable predicates are combined with an OR, the result is indexable. All the statistics are run, but the query's acces type = R with sort (Distinct). So it takes to long to run. [...] 6873 459 15_Re: DB2 history13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Wed, 2 Dec 2009 08:22:05 -0500643_utf-8 I believe Roger Miller passed this along a few years ago.





http://www.mcjones.org/System_R/SQL_Reunion_95/sqlr95-DB2.html





Roger answered a similar question a few weeks back. Check the archives.





Dave

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jose Antonio Sent: Wednesday, December 02, 2009 4:04 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 history





Hi!





I have to give a basic lecture on DB2 to a group of people that know nothing about the product, I would like to talk about its history, [...] 7333 240 32_Re: Is this access path correct?28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM30_Wed, 2 Dec 2009 18:56:25 +0530711_US-ASCII Hi Harry,

also, give us the cardinality, frequency information of these indexes and the columns... from SYSIBM.SYSCOLDIST (If collected), SYSIBM.SYSCOLUMNS for these two indexes..

Thanks Balachandran Chandrasekaran







Harry Garagoski Sent by: IDUG DB2-L 02/12/2009 18:40 Please respond to IDUG DB2-L



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject [DB2-L] Is this access path correct?













We have a query like this on a 300.000 row table and there is a index on COLF (unique) and on COLG (duplicate (not much)) [...] 7574 71 28_web link for db2 replication10_Zhang Ming16_db2sub@YAHOO.COM30_Wed, 2 Dec 2009 05:29:27 -0800527_iso-8859-1 dear all, I just need to have a study on ibm InfoSphereReplicationServer and InfoSphere CDC for z/os. does anyone know the web link to download the related books. I search the ibm web,and without the result. tks for your kind help. best regards.hange Data Capture & Replication

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 7646 364 40_AW: [DB2-L] Is this access path correct?35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Wed, 2 Dec 2009 14:35:52 +0100653_iso-8859-1 Hi Harry

DB2 could do a multiple index access, but this access path uses list prefetch and AFAIK accesstype 'N', which means IN-predicate, is mutually exclusive with list prefetch. Because of the OR-predicate DB2 can not use the index an COLF.

But I think, the following select will work better:

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE FROM CM_BK WHERE COLF iN ('1111410007081000','1111410007081000') UNION SELECT DISTINCT COLA, COLB , COLC, COLD, COLE FROM CM_BK WHERE COLG IN ('111410009H8','111410009H81', '111410009H0','111410009H08','111410009H081', '111410009H0810','111410009H08100','111410009H081000') [...] 8011 294 32_Re: Is this access path correct?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 2 Dec 2009 14:35:52 +0100495_ISO-8859-1 are colg or even the colf VARCHAR by any chance?? and remember STATS STATS STATS and STATS

ps What version of DB2 are you running?





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 [...] 8306 516 32_Re: Is this access path correct?13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Wed, 2 Dec 2009 07:46:32 -0600709_us-ascii Out of curiosity, what does the plan say when you do this?





SELECT DISTINCT COLA, COLB , COLC, COLD, COLE FROM CM_BK WHERE COLF iN (?,?) OR COLG IN (?,?,?,?,?,?,?,?)





Sometimes if you have hardcodes values, you give DB2 _too much_ information







Scott Hodgin

Database Administrator, MCITP South Carolina Farm Bureau Insurance Company

shodgin@scfbins.com





________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Harry Garagoski Sent: Wednesday, December 02, 2009 8:10 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Is this access path correct? [...] 8823 787 32_Re: Is this access path correct?12_Tony Andrews22_tandrews@THEMISINC.COM30_Wed, 2 Dec 2009 09:11:54 -0500338_us-ascii Are you executing on V8 or V9? A couple of things to consider are:





1) If the optimizer chose tablespace scan, then the filter factors on those predicates are probably high enough

for the optimizer to warrant it. But DB2 may be wrong in its filter factors if it does not have enough statistical [...] 9611 331 46_Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Wed, 2 Dec 2009 15:13:05 +0100558_us-ascii Hello Scott,

Sorry no difference. Same access path



Regards,

Harry Garagoski











Van: "Hodgin, Scott" Aan: DB2-L@WWW.IDUGDB2-L.ORG Datum: 02-12-2009 14:49 Onderwerp: Re: [DB2-L] Is this access path correct? Verzonden door: IDUG DB2-L





Out of curiosity, what does the plan say when you do this?

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE FROM CM_BK WHERE COLF iN (?,?) OR COLG IN (?,?,?,?,?,?,?,?) [...] 9943 423 54_Betr: [DB2-L] AW: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Wed, 2 Dec 2009 15:16:44 +0100463_iso-8859-1 Hello Walter,

The UNION is better , but we are not able to change the SQL. It is part of a external software. We are not happy with it.



Regard,

Harry Garagoski











Van: Walter Janißen Aan: DB2-L@WWW.IDUGDB2-L.ORG Datum: 02-12-2009 14:39 Onderwerp: [DB2-L] AW: [DB2-L] Is this access path correct? Verzonden door: IDUG DB2-L [...] 10367 241 32_Re: Is this access path correct?28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM30_Wed, 2 Dec 2009 20:02:10 +0530624_US-ASCII I just noticed this.

COLF iN ('1111410007081000','1111410007081000')

Any reason why the same value is mentioned twice ?









Harry Garagoski Sent by: IDUG DB2-L 02/12/2009 18:40 Please respond to IDUG DB2-L



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject [DB2-L] Is this access path correct?













We have a query like this on a 300.000 row table and there is a index on COLF (unique) and on COLG (duplicate (not much)) [...] 10609 223 40_AW: [DB2-L] web link for db2 replication12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 2 Dec 2009 16:03:49 +0100998_iso-8859-1 What about





http://www-01.ibm.com/software/data/infosphere/replication-server/





or





http://www-01.ibm.com/support/docview.wss?rs=3572&context=SSDPBH&context=SS2K6Z&context=SS2KAH&context=SS2K84&context=SS2KBU&context=SSVH42&context=SS9UMX&q1=infosphere+replication+server&uid=swg27010439&loc=en_US&cs=utf-8&lang=en+#

HTH.

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- P Think before you print. [...] 10833 929 58_Re: Betr: [DB2-L] AW: [DB2-L] Is this access path correct?13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Wed, 2 Dec 2009 09:12:38 -0600614_iso-8859-1 What happens if you add COLA, COLB, COLC, COLD, COLE to one or both indexes (or create new indexes with these additional columns) so no data pages have to be read.







Scott Hodgin

Database Administrator, MCITP South Carolina Farm Bureau Insurance Company

shodgin@scfbins.com





________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Harry Garagoski Sent: Wednesday, December 02, 2009 9:17 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Betr: [DB2-L] AW: [DB2-L] Is this access path correct? [...] 11763 1272 47_Baltimore/Washington DB2 Users Group Weds, 12/97_Kim May28_kim.may@THEFILLMOREGROUP.COM30_Wed, 2 Dec 2009 10:20:20 -0500535_us-ascii









Join us for presentations by: Curt Cotner, Paul Westfall, Rick Weaver, Roger Miller and Steve Rosenberger!

Two tracks! DB2 for z/OS and DB2 for LUW





The Baltimore/Washington DB2 Users' Group

Meeting Schedule for December 9, 2009 (Wednesday)

Sheraton Hotel, Columbia, Maryland

To register please visit our new website: www.bwdb2ug.org





DB2 for LUW Track Agenda:





8:30 - 9:00 Registration [...] 13036 362 32_Re: web link for db2 replication7_Kim May28_kim.may@THEFILLMOREGROUP.COM30_Wed, 2 Dec 2009 10:38:24 -0500367_us-ascii Are you doing a comparison of the two? When you reach a conclusion can you let me know resource was most helpful? I am putting a white paper together on all the HA options available for DB2 and would love to include some real-life selections. The CDC vs. Q Rep decision seems to be more marketing driven than it should be (from what I've seen). Thanks! [...] 13399 861 39_Experiences converting CLOB/BLOB to XML12_Tony Andrews22_tandrews@THEMISINC.COM30_Wed, 2 Dec 2009 11:56:14 -0500420_us-ascii I am looking for some real world experiences from people that have gone through converting CLOBs/BLOBS that

contained prior XML data to the new V9 XML data types.





What was your approach to converting? Programmatically? and why?





What kind of space issues where encountered? Did you encounter having to execute REORGs to get the XML data compressed after [...] 14261 656 50_Re: Betr: Re: [DB2-L] Is this access path correct?12_Isaac Yassin20_yassini@BEZEQINT.NET30_Wed, 2 Dec 2009 19:40:59 +0200741_utf-8 Hi

Another index on COLG ?





Isaac Yassin

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Harry Garagoski Sent: Wednesday, December 02, 2009 4:13 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?







Hello Scott,

Sorry no difference. Same access path



Regards,

Harry Garagoski













Van:

"Hodgin, Scott"



Aan:

DB2-L@WWW.IDUGDB2-L.ORG



Datum:

02-12-2009 14:49



Onderwerp:

Re: [DB2-L] Is this access path correct?



Verzonden door: [...] 14918 167 58_Re: Betr: [DB2-L] AW: [DB2-L] Is this access path correct?13_Terry Purcell18_tpurcel@US.IBM.COM30_Wed, 2 Dec 2009 17:59:17 +0000681_utf-8 Harry,

I skimmed through the responses, and only Walter's suggestion will allow you to have matching index access given these 2 IN lists. Walter is correct that IN list access and multi-index access are mutually exclusive.

Regards Terry Purcell

On Wed, 2 Dec 2009 15:16:44 +0100, Harry Garagoski wrote:

>Hello Walter, > > The UNION is better , but we are not able to change the SQL. > It is part of a external software. We are not happy with it. > > >Regard, > >Harry Garagoski > >Van: >Walter Janiߥn >Aan: >DB2-L@WWW.IDUGDB2-L.ORG >Datum: >02-12-2009 14:39 >Onderwerp: >[DB2-L] [...] 15086 29 18_Re: DB2 V9 Upgrade10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 2 Dec 2009 17:45:44 +0000459_utf-8 We have a two week testing cycle for apps. So usually we installed it

Week 0 - Sandbox CM mode Week 1 - Test CM Mode Week 5 - Prod CM Mode Week 6 - Sandbox NF Mode Week 7 - test NF Mode Week 9 - Prod NF mode

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 15116 432 32_Re: web link for db2 replication14_Chris Zampogna34_Chris.Zampogna@STERLINGSAVINGS.COM30_Wed, 2 Dec 2009 11:17:37 -0800697_us-ascii Here is an "unofficial" link with info on the topic:

http://www.db2dean.com/Previous/Replicate.html



Best Regards,

Chris Zampogna Program Manager, Enterprise Data Warehouse

Sterling Savings Bank 111 North Wall Street Spokane, WA 99201 Office (509) 625-3402 - VOF(6435) Mobile (509) 368-0686 Fax (509) 458-2391 chris.zampogna@sterlingsavings.com www.sterlingsavingsbank.com

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kim May Sent: Wednesday, December 02, 2009 7:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] web link for db2 replication [...] 15549 49 32_Query CPU time keeps increasing.4_Amar18_amar_moh@YAHOO.COM30_Wed, 2 Dec 2009 19:40:17 +0000524_utf-8 Hi,

Db2 V8 on z/OS

We have a job that executes a static query multiple times(about 600) using host variables. The query has 5 tables joined each having about 10 million rows. Not very big tables. The query uses indexes on all of them. Tables have stats updated.

Initially to run about 600 times that query took about 5 min CPU as per BMC apptune. Everyday the CPU time increases by 1-2 mins. Now its taking 55 mins of CPU time to run that same SQL about 600 times(It varies between 590-650). [...] 15599 55 20_Re: prototype tables12_Bill Cummins18_brcummin@ILSTU.EDU30_Wed, 2 Dec 2009 19:48:24 +0000494_utf-8 Or maybe WITH temp (seedval,seedChar) AS ( Select 65, chr(65) from sysibm.sysdummy1 UNION ALL SELECT seedval + 1, chr(seedval+1) FROM temp WHERE seedval + 1 < 140 ) select * from temp

>With DB2 for LUW you can use a common table expression to 'prototype' a >table without needing to create it: > >with temp(a, b) as ( > values (1, 'A') > , (2, 'B') > , (3, 'C') > ) >select a, b from temp; > >Results: >A B >----------- - > 1 A > 2 B > 3 C > > 3 record(s) selected. > >I know [...] 15655 216 36_Re: Query CPU time keeps increasing.35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Wed, 2 Dec 2009 15:00:09 -0500463_UTF-8 Access paths and rows joined, answer sets. The amount of data you are processing. You will probably find a direct relationship between the number of getpages and the cpu cost..

Regards, Joel



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 [...] 15872 498 26_Re: RUNSTATS' Alternative?17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM30_Wed, 2 Dec 2009 20:48:15 -0000506_us-ascii errrr.... were you calling me Raymond? sorry sorry I was just round the corner ;-)

Josh,

my 0.0000002c worth: I assume you will have to move from V7 to V8 kind of soon - and when you get to V8 RTS are always collected, simply not externalised (at which point of course you will have all the overhead as part of the process anyway...) - incidentally a lot of DB2 customers are finding great reductions in their daily process once they move to V9 so maybe you can plan way ahead. [...] 16371 266 32_Re: web link for db2 replication38_Antonio Paulo Galdeano Damiance Junior18_damiance@GMAIL.COM30_Wed, 2 Dec 2009 19:03:57 -0200565_windows-1252 Kim -

When you are done with your white paper I would like to be one of the first readers!

Thanks!

On Wed, Dec 2, 2009 at 1:38 PM, Kim May wrote:

> Are you doing a comparison of the two? When you reach a conclusion can > you let me know resource was most helpful? I am putting a white paper > together on all the HA options available for DB2 and would love to include > some real-life selections. The CDC vs. Q Rep decision seems to be more > marketing driven than it should be (from what [...] 16638 25 41_Doug Holliday/Alcan is out of the office.13_Doug Holliday23_doug.holliday@ALCAN.COM30_Wed, 2 Dec 2009 16:15:02 -0500642_US-ASCII I will be out of the office starting 12/02/2009 and will not return until 12/04/2009.

I will respond to your message when I return.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________ [...] 16664 97 20_Re: prototype tables15_Frank Swarbrick30_frank.swarbrick@EFIRSTBANK.COM30_Wed, 2 Dec 2009 11:49:30 -0700662_us-ascii That does it. Thanks! Frank

On 12/1/2009 at 7:08 PM, David Simpson wrote: > How about this.... it's quirky but it works: > > with > temp(a, b) as ( > select 1,'A' from sysibm.sysdummy1 > union all > select 2,'B' from sysibm.sysdummy1 > union all > select 3,'C' from sysibm.sysdummy1 > ) > select a > , b > from temp; > > ________________________________________________________________________ > ______ > 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 [...] 16762 118 36_Re: Query CPU time keeps increasing.10_Dave Nance16_dav1mo@YAHOO.COM30_Wed, 2 Dec 2009 15:54:32 -0800550_iso-8859-1 Amar, You mention that stats have been updated. You did not, however, mention if: the table/index are in need of a reorg the statement has been rebound(what are the bind parms, ?reopt?) You, also, mention the query is using indexes for all the tables being joined/ You do not mention if: you have any sorts multi-index scans 1 col match instead of 5 col match and cardinality of that col is 2 do you have a lot of stage 2 predicates I could keep going on To get some useful advice, you need to give us information otherwise there are [...] 16881 185 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.013_David S Waugh16_dsw-dba@JUNO.COM28_Thu, 3 Dec 2009 00:27:31 GMT398_windows-1252 Thanks Othmar:

We applied Gateway-TG4DB2 10.2.0.3 patch 6682743 (plus another patch they said was needed - OSDI for z/OS: Patch 6138068), and set FDS_CLASS_VERSION='10.2.0.3.93' in ORA$ENV.

Still no dice - CHAR(6) columns are still coming across as 18 bytes.

It's not mis-translating the data - that comes across from DB2 OK, it's just followed by 12 blanks. [...] 17067 33 73_AUTO: I am out of office 12/3 - visiting customer. (returning 12/04/2009)17_Norbert Jenninger14_JEN@DE.IBM.COM30_Thu, 3 Dec 2009 04:10:41 +0100370_US-ASCII I am out of the office until 12/04/2009.

I am out of office 12/3 - travelling, I will be back in the office Friday Dec 4.



Note: This is an automated response to your message "DB2-L Digest - 2 Dec 2009 to 3 Dec 2009 (#2009-345)" sent on 3/12/09 2:00:01.

This is the only notification you will receive while this person is away. [...] 17101 38 41_what happens in opening cursor in detail?12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN30_Thu, 3 Dec 2009 14:57:18 +0800520_US-ASCII Dear listers,

In DB2 for z, when a program opens a cursor, I assume that DB2 returns the whole result set. So there are the following questions:

1. What happens if the result set has millions of records?

2. Where does the result set locate? In DBM1 storage or working data sets?

3. What happens if the cursor is with OPTIMIZE FOR N ROWS clause? Will the whole result set or only n rows of the result set present when opening the cursor? What will happen when fetch the n+1 row? [...] 17140 821 46_Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Thu, 3 Dec 2009 08:32:32 +0100349_iso-8859-1 Hello Roy,



COLG = VARCHAR and we are running V9







Index for COLF :





Sel Column Name Seq No O Col Type Length Scale Null Def FP Col Card * * * * * * * * * * --- ------------------ ------ - -------- ------ ------ ---- --- -- ----------- COLF 1 A CHAR 16 0 N N N 290607 [...] 17962 351 46_Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Thu, 3 Dec 2009 08:36:14 +0100504_us-ascii No, Its the product works.

If I split the query in separate predicates both use the index but when you join the predicates to one query with an OR it changes to a TS-scan







Regards,

Harry Garagoski









Van: Balachandran Chandrasekaran1 Aan: DB2-L@WWW.IDUGDB2-L.ORG Datum: 02-12-2009 15:35 Onderwerp: Re: [DB2-L] Is this access path correct? Verzonden door: IDUG DB2-L [...] 18314 693 46_Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Thu, 3 Dec 2009 08:38:16 +0100300_us-ascii Index for COLF :





Sel Column Name Seq No O Col Type Length Scale Null Def FP Col Card * * * * * * * * * * --- ------------------ ------ - -------- ------ ------ ---- --- -- ----------- COLF 1 A CHAR 16 0 N N N 290607





Details for index : ABC [...] 19008 29 15_Re: DB2 history12_Marc Wambeke27_marc.wambeke@REALDOLMEN.COM30_Thu, 3 Dec 2009 07:28:30 +0000868_UTF-8 Willie Favero had a nice series in his blog about the history of the different versions of DB2 for z/OS : http://it.toolbox.com/home/search/blogs/?r=db2+history&=

Kind regards, Marc Wambeke http://mainframe-watch-belgium.blogspot.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...] 19038 270 45_Re: what happens in opening cursor in detail?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 3 Dec 2009 09:51:10 +0100558_ISO-8859-1 Your assumption is wrong. DB2 does not always return the whole result set (This causes junior programmers no end of grief....) it depends on what, exactly, your cursor is doing and how the indices are and how the stats are on the data and finally , of course, the access path and SQL. For example for a 2 million row table perfectly in clustered sequence using an order by based on the clustering index then DB2 might only fetch the first rows that qualify....however the moment it has to do a sort for *any* reason... then it must fetch all [...] 19309 90 45_Re: what happens in opening cursor in detail?13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK30_Thu, 3 Dec 2009 08:52:00 -0000336_US-ASCII Hi Jeremy

"what happens in opening cursor in detail?" - IT DEPENDS (sorry, couldn't resist!)

In simple terms, if DB2 can materialize your results set directly from the table(s) in question, than that's what will happen and an OPEN CURSOR will do almost no work. The full results set will NOT be materialized [...] 19400 76 36_Re: Query CPU time keeps increasing.11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM30_Thu, 3 Dec 2009 09:19:47 +0000575_UTF-8 Hello,

have you recycled your DB2 or IPL the system during that period ? If yes, did it have any effect on CPU ?

Regards, Nenad

On Wed, 2 Dec 2009 19:40:17 +0000, Amar wrote:

>Hi, > >Db2 V8 on z/OS > >We have a job that executes a static query multiple times(about 600) using >host variables. The query has 5 tables joined each having about 10 million >rows. Not very big tables. The query uses indexes on all of them. Tables >have stats updated. > >Initially to run about 600 times that query took about 5 min CPU [...] 19477 937 50_Re: Betr: Re: [DB2-L] Is this access path correct?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 3 Dec 2009 10:48:49 +0100419_ISO-8859-1 varchar and PADDED is not a good mix in this case. I would try the following steps

Create a new COLG index as NOT PADDED, Runstat it with FREQVAL and EXPLAIN the query again





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 [...] 20415 252 36_Re: Query CPU time keeps increasing.11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 3 Dec 2009 11:02:31 +0100400_ISO-8859-1 I agree with Joel on this one - Looks like fragmented data causing higher and higher getpage activity to me. Check your RTS data and do a REORG of IXs and/or TSs





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 [...] 20668 1138 58_AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 3 Dec 2009 12:20:13 +0100456_iso-8859-1 Hi

I think, you can create as much indexes as you want, you won't get a matching index access for this query (as Terry already pointed out). May be you can get a non-matching index-only access.

To get a better access path, you have to rewrite that query as I already suggested, but you said, you can't, because it is a generated query. So make the vendor change their tool, to generate better queries or get rid of that tool. [...] 21807 1038 64_Betr: Re: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Thu, 3 Dec 2009 12:40:16 +0100502_iso-8859-1 Hello Roy,

I tried this already but the result was also negative.

I had a answer of Terry Purcell that i is simple not possible.

This will result in a requirement toe the vendor.





regards,

Harry Garagoski





Van: Roy Boxwell Aan: DB2-L@WWW.IDUGDB2-L.ORG Datum: 03-12-2009 10:57 Onderwerp: Re: [DB2-L] Betr: Re: [DB2-L] Is this access path correct? Verzonden door: IDUG DB2-L [...] 22846 1167 62_Re: AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 3 Dec 2009 12:55:21 +0100586_ISO-8859-1 I just hope its not SAP ...... :)





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







Walter Janißen Gesendet von: IDUG DB2-L 03.12.2009 12:20 Bitte antworten an IDUG DB2-L [...] 24014 193 69_AW: [DB2-L] DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.012_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM30_Thu, 3 Dec 2009 14:22:45 +0100482_iso-8859-1 Hi dear David ! o) Thanks for your feedback. Puhh/Mnnn/Puuu from my only-mainframe-point-of-view. This patch_set 6138068 belongs to the B6682743 too ; this is definitely correct. o) I just 'attached' my dec. oracle-guru's ... they've right now also no (new) idea in this "3_times_longer-area". o) In google I found some info's about ORA-12899 & FRM-40831--> e.g. --> "Did you try to check value of "Data Length Semantics" property?" ! Sorry not more; kinds / Othmar [...] 24208 79 68_Re: Betr: Re: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 3 Dec 2009 14:44:54 +0100984_ISO-8859-1 good luck!





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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...] 24288 225 82_Betr: Re: [DB2-L] Betr: Re: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Thu, 3 Dec 2009 15:28:02 +0100638_iso-8859-1 Hello Roy



Terry gave me some usefull information.

The project made a requirement for the vendor. The vendor will solve this problem. Hopefully with an UNION.



Regards,

Harry Garagoski





Met vriendelijke groeten,

Harry Garagoski Belastingdienst/Centrum voor ICT, MMPS (Mainframe & Midrange Platform & Storage) DBMS Logisch DB-beheer Locatie Quintax BPA Laan van Westenenk 555 7334 DT Apeldoorn Gebouw 15 Kamer A1.67 tel. 2192 Mobiel : 06-18608531 e.garagoski@belastingdienst.nl B/CICT Mainframe Website : http://mainframe.belastingdienst.nl/mainframe/ [...] 24514 1146 72_Betr: [DB2-L] AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?15_Harry Garagoski30_e.garagoski@BELASTINGDIENST.NL30_Thu, 3 Dec 2009 15:29:47 +0100543_iso-8859-1 Hello Walter



Terry gave me some usefull information.

The project made a requirement for the vendor. The vendor will solve this problem. Hopefully with an UNION.

Thanks for your participation



Regards,

Harry Garagoski







Van: Walter Janißen Aan: DB2-L@WWW.IDUGDB2-L.ORG Datum: 03-12-2009 12:22 Onderwerp: [DB2-L] AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct? Verzonden door: IDUG DB2-L [...] 25661 161 17_EDM Pool problems12_Fay, Colin M13_CFay@UNUM.COM30_Thu, 3 Dec 2009 09:40:19 -0500498_us-ascii

Dr Listers.



Graph shows EDM Pool pages by hour for one day(11-14) . At 14:00 experienced EDM Pool Full failures.

At that time EDM_SKPT pages dropped from 17,000 to 7,000. EDM PAGES FREE jumped from 2500 to 11500 .

Total pool pages are 19721. EDM_PAGES_CT and EDM_PAGES_SKCT are legible

There is very little DB2 activity going on during this interval. In fact this re-curring failure seems to strike on weekends periods of low activity only [...] 25823 120 81_Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.16_Robert Catterall21_rfcatterall@GMAIL.COM30_Thu, 3 Dec 2009 10:15:43 -0500596_ISO-8859-1 Does the DELETE statement in the stored procedure target any of the tables that are targeted by one or more of the OPEN CURSOR statements in the same stored procedure?

Robert



On Tue, Dec 1, 2009 at 7:23 PM, Mark Stone wrote:

> I have an existing SP that I'm migrating to Native SQL. It runs fine in > external mode but actually causes a DB2 abend when compiled and executed in > Native mode. I tracked the offending statement down to a DELETE statement > that follows a number of OPEN CURSOR statements. The intention being that > [...] 25944 119 81_Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.0_17_mastone@GMAIL.COM30_Thu, 3 Dec 2009 07:40:26 -0800562_us-ascii No

Sent from my iPhone

On Dec 3, 2009, at 7:15 AM, Robert Catterall wrote:

> Does the DELETE statement in the stored procedure target any of the > tables that are targeted by one or more of the OPEN CURSOR > statements in the same stored procedure? > > Robert > > > On Tue, Dec 1, 2009 at 7:23 PM, Mark Stone wrote: > I have an existing SP that I'm migrating to Native SQL. It runs fine > in external mode but actually causes a DB2 abend when compiled and > executed in Native mode. [...] 26064 494 24_Re: More LOB lock issues16_Robert Catterall21_rfcatterall@GMAIL.COM30_Thu, 3 Dec 2009 11:03:36 -0500589_ISO-8859-1 I don't think that the program will be auto-rebound as a result of an ALTER TABLESPACE LOCKSIZE LOB -- I'm pretty sure that you'll have to manually rebind the batch program to get it to use LOB- versus TABLESPACE-level locking.

When you ALTER to a larger lock size (e.g., LOB to TABLESPACE), a static SQL statement targeting a table in the altered tablespace will use that larger lock size the next time it is executed. When you ALTER to a smaller lock size (e.g., TABLESPACE to LOB), a static SQL statement targeting a table in the altered tablespace will continue [...] 26559 442 26_Re: RUNSTATS' Alternative?7_DB2 DBA21_the.db2.dba@GMAIL.COM30_Thu, 3 Dec 2009 11:53:35 -0500424_windows-1252 Aurora,

I thought RTS ARE available (and only collected, as we need to externalize 'em) in V7 too. Atleast, that's what I remember reading from the admin guide. Correct me if I am wrong.

Also, based on some of the responses to the original post, I AM considering a few approaches. Should I decide to get back to my original idea of "looking" at an alternative, I shall certainly talk to you. [...] 27002 255 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.013_David S Waugh16_dsw-dba@JUNO.COM28_Thu, 3 Dec 2009 18:42:57 GMT512_windows-1252 Othmar:

Yes, we checked "Data Length Semantics", at least as far as NLS_LENGTH_SEMANTICS, and it is set to 'BYTE' in the Oracle database. We tried ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR'; and it didn't help.

We're going to try setting BLANK_TRIMMING = 'TRUE' in init.ora to see if that makes any difference, but we're a little leery of messing up something else, since it's a global thing that affects everybody, not just the people who are having this expansion problem. [...] 27258 441 26_Re: RUNSTATS' Alternative?9_DB2DBAzOS21_bala.db2dba@GMAIL.COM30_Fri, 4 Dec 2009 11:10:45 +0530407_windows-1252 DB2 v7 had/have the RTS through few PTFs.. Recently (we are still on v7 in one DB2 ), we applied those PTFs and considering having RTS ON as it has several benefits.

What has been change in DB2 v8 is that RTS is looked up by the IBM DB2 utilities (I think I am right here too). So, if RTS is there, IBM utilities would look upto RTS for sortkeys, dynamic sort file allocation etc.. [...] 27700 123 45_AIX DB2 V9.1Experiences using Recovery Expert13_Gerard Le Roy24_gerard.le.roy@WANADOO.FR30_Fri, 4 Dec 2009 07:23:49 +0100708_iso-8859-1 Hello everybody,

Looking for experience with Recovery Expert V2.1.

Does anybody use this product ? Which feature ?





Thanks for advices.

Gerard



_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ [...] 27824 29 5_NPSI.34_SUBSCRIBE DB2-L Tejas V Jadhav_IBM17_tejasvj@GMAIL.COM30_Fri, 4 Dec 2009 08:39:19 +0000699_utf-8 All -

Please may i request you to share with me a query to DB2 Catalog to find out 'NPSI' existing on a tablespace other than Partitioned indexes.



Thanks in advance TJ

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ [...] 27854 308 26_Re: RUNSTATS' Alternative?10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 4 Dec 2009 09:47:08 +0100781_UTF-8 I used RTS even in V7.



Massimo Scarpa - Ufficio Sistemi Cesve Servizi Informatici Bancari v. Longhin, 1 - 35129 Padova Tel. 049.8067111 - Fax. 049.8067300 E-Mail: mscarpa@cesve.it - Web: http://www.cesve.it





Il presente messaggio non ha natura personale e le eventuali risposte allo stesso potranno essere conosciute nell’ambito dell'organizzazione di appartenenza del mittente. Esso , corredato degli eventuali relativi allegati , contiene informazioni da considerarsi strettamente riservate ai sensi della vigente normativa in materia di protezione di dati personali ed è destinato esclusivamente al destinatario(i) sopra indicato. Chiunque ricevesse questo messaggio per errore o comunque lo leggesse senza esserne legittimato [...] 28163 99 81_Re: Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 4 Dec 2009 10:45:28 +0100470_utf-8 Speaking of iPhones... http://www.youtube.com/watch?v=tUreshlWqNM

At least I waited ‘til Friday...



Raymond



From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of mastone@GMAIL.COM Sent: 03 December 2009 15:40 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Native SQL Stored Procedure abend when DELETE follows OPEN CURSOR statements.

No

Sent from my iPhone 28263 448 57_Re: Approximate "break-even" when using Rowset processing13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 4 Dec 2009 12:36:57 +0100559_us-ascii Hi Gary,

On 5th November BMC hosted a Webinar/Webex/Web-based presentation in conjunction with YL&A. About 17 minutes into it, Dan Luksetich showed a slide that charted the approx. CPU and elapsed times of a read process with varying number of rows in the rowset - 1, 2, 5, 10, 20, 50 and 100. For his particular test the performance started to tail off about 10 rows per rowset but continued to improve until about 50 when it started to flat-line. Your mileage will almost certainly vary so I'm afraid you'll need to try it out and see. [...] 28712 43 9_Re: NPSI.14_Peter Vanroose17_pvanroose@ABIS.BE30_Fri, 4 Dec 2009 11:49:21 +0000535_UTF-8 This query will give all NPSIs:

SELECT rtrim(ixcreator) || '.' || ixname FROM SYSIBM.SYSINDEXPART WHERE partition = 0

This also gives the indexes on non-partitioned tables, of course. In order to just see indexes on tables in partitioned tablespaces, add

AND (IXNAME, IXCREATOR) IN ( SELECT NAME, CREATOR FROM SYSIBM.SYSINDEXES WHERE (TBNAME, TBCREATOR) IN ( SELECT NAME, CREATOR FROM SYSIBM.SYSTABLES WHERE (DBNAME, TSNAME) IN ( SELECT DBNAME, NAME FROM SYSIBM.SYSTABLESPACE WHERE PARTITIONS > 0 ) ) ) [...] 28756 29 9_Re: NPSI.34_SUBSCRIBE DB2-L Tejas V Jadhav_IBM17_tejasvj@GMAIL.COM30_Fri, 4 Dec 2009 12:34:45 +0000701_utf-8 Hi Pvanroose -

Many thanks for providing the query.



Cheers ! Tejas J

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L 28786 868 34_SV: [DB2-L] RUNSTATS' Alternative?13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO30_Fri, 4 Dec 2009 13:57:58 +0100522_iso-8859-1 If I was in V7 an started to get questions on the cost of runstats I would use savings as argument for moving fast to V9 ;-)

Best regards Hanne



Fra: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] På vegne av DB2DBAzOS Sendt: 4. desember 2009 06:41 Til: DB2-L@WWW.IDUGDB2-L.ORG Emne: Re: [DB2-L] RUNSTATS' Alternative?

DB2 v7 had/have the RTS through few PTFs.. Recently (we are still on v7 in one DB2 ), we applied those PTFs and considering having RTS ON as it has several benefits. [...] 29655 494 26_Re: RUNSTATS' Alternative?7_DB2 DBA21_the.db2.dba@GMAIL.COM30_Fri, 4 Dec 2009 10:41:16 -0500565_windows-1252 Bala - Wow! That sounds like a time-saver. Could you share the info on those PTFs? I would appreciate if you did it.





-Josh





On Fri, Dec 4, 2009 at 12:40 AM, DB2DBAzOS wrote:

> DB2 v7 had/have the RTS through few PTFs.. Recently (we are still on v7 > in one DB2 ), we applied those PTFs and considering having RTS ON as it has > several benefits. > > What has been change in DB2 v8 is that RTS is looked up by the IBM DB2 > utilities (I think I am right here too). So, if [...] 30150 272 45_Re: what happens in opening cursor in detail?16_Robert Catterall21_rfcatterall@GMAIL.COM30_Fri, 4 Dec 2009 11:01:29 -0500367_ISO-8859-1 I believe that FETCH FIRST n ROWS ONLY will not necessarily limit result set materialization. If I want the first 10 rows of a result set that is ordered by a non-indexed column (or set of columns), and if the target table has 1 million qualifying rows, DB2 is going to have to sort those 1 million rows before it can do the FETCH FIRST 10 ROWS ONLY. [...] 30423 22 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.010_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 4 Dec 2009 16:19:57 +0000669_utf-8 Just have them substring everything on the Oracle side :-)

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L 30446 98 45_Re: what happens in opening cursor in detail?14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM30_Fri, 4 Dec 2009 10:34:22 -0600348_us-ascii That's true but there is further twist in version 9 - it may use "smart sort" where it goes through the million qualifying rows exactly once picking up the first 10 per the order by specification. No intermediate runs or merges will be needed for such sort. I won't call it materialization although some sort of sort (!) is involved. [...] 30545 564 45_Re: what happens in opening cursor in detail?13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK30_Fri, 4 Dec 2009 16:43:09 -0000510_us-ascii Good point Robert





I was thinking of using GET FIRST "n" to limit the size of a static scrollable cursor





I knew I shouldn't have taken all those weeks off....





Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Catterall Sent: 04 December 2009 16:01 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] what happens in opening cursor in detail? [...] 31110 296 60_New Version of DARWIN available on UK Db2 User group website19_Thomas, Steven (UK)21_Steven_Thomas@BMC.COM30_Fri, 4 Dec 2009 18:33:37 +0100602_us-ascii Hi everyone, I believe many of you have used a utility called DARWIN which has been available from the UK DB2 User Group website (http://www.gseukdb2.org.uk) for some time. It provides an alternative to using DSNTEP2 for customers who don't use PL/I. It is provided in COBOL source form, with sample JCL and usage notes. Thanks to the generosity of Gary Joehlin and Sallie Mae Inc. we are now pleased to be able to share an improved version of DARWIN with the DB2 Community. This new version corrects truncation of DSNTIAR messages, increases the maximum size of SQL statements from 32Kb [...] 31407 395 57_Re: Approximate "break-even" when using Rowset processing11_Suresh Sane21_data_arch@HOTMAIL.COM30_Fri, 4 Dec 2009 11:50:38 -0600377_Windows-1252

Gary,





I presented some early benchmarks on this at IDUG 2006 (session E05). CPU savings can be obtained for as few as 5 rows, but taper off around 50-100.





However, there is a bigger issue here which has not been touched on - especially if you are contemplating retro-fitting into an existing application. [...] 31803 356 45_Re: what happens in opening cursor in detail?13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK30_Fri, 4 Dec 2009 18:30:01 -0000427_us-ascii And, of course, this "smart sort" must happen during OPEN CURSOR, otherwise the first FETCH won't know which row to return





Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of M. Khalid Khan Sent: 04 December 2009 16:34 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] what happens in opening cursor in detail? [...] 32160 399 64_Re: New Version of DARWIN available on UK Db2 User group website13_Joehlin, Gary26_Gary.Joehlin@SALLIEMAE.COM30_Fri, 4 Dec 2009 13:44:09 -0500650_us-ascii I'd like to make a correction to the features, the SQL Statement size max is 2 Mb.

Thanks, Gary

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Thomas, Steven (UK) Sent: Friday, December 04, 2009 12:34 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] New Version of DARWIN available on UK Db2 User group website

Hi everyone, I believe many of you have used a utility called DARWIN which has been available from the UK DB2 User Group website (http://www.gseukdb2.org.uk) for some time. It provides an alternative to using DSNTEP2 for customers who don't use PL/I. It is provided in COBOL source form, with [...] 32560 85 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.013_David S Waugh16_dsw-dba@JUNO.COM28_Fri, 4 Dec 2009 18:43:08 GMT597_windows-1252 Jorg:

Yep, that would work just fine (as would RTRIM). However, when we broached this subject with the Applications Developers, this was their response:

"We can’t change a million lines of interface code. There has to be another solution."

I kinda doubt if they'd have to change a million lines of code (probably more like thousands of lines), but it means searching through hundreds of interface programs, some of which they may or may not still have the source code for. Or so I'm told. I can't say I blame them for not wanting to change a ton of programs [...] 32646 27 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.010_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 4 Dec 2009 18:58:49 +0000492_utf-8 I wouldn't blame them either. My experience with the TG has been similar. It works for some releases on some platforms but when you get some weird issue it's unlikely to be fixed anytime soon. I take it it's too hard to replicate the data as well?

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 32674 302 57_Re: Approximate "break-even" when using Rowset processing12_Myron Miller22_myronwmiller@YAHOO.COM30_Fri, 4 Dec 2009 11:03:25 -0800603_utf-8 Amen, brother. The COBOL programming complexity is way beyond what I've seen many COBOL programmers able to handle at my site. In addition, we had a very very experienced programmer try it in some careful benchmarks. We tried it on several situations where lots of rows were being returned. And in every one of our cases, the result was a net negative difference. The processing and CPU were faster on singleton fetches versus multiple rowsets (ranging from tests on 10 to 200). Understand that your mileage may vary, these were our carefully benchmarked and documented tests on our data and [...] 32977 103 57_Re: Approximate "break-even" when using Rowset processing14_M. Khalid Khan24_Mohammad_Khan@BCBSIL.COM30_Fri, 4 Dec 2009 13:32:00 -0600330_us-ascii There is another case where it might be worthwile to retrofit multi-row fetch - a CICS transaction that's not threadsafe. Such a transaction incurrs a task switch before and another after every fetch plus any delays involved with the switch. There is a potential for savings on both cpu and suspend time. HTH Khalid [...] 33081 26 42_Identity Column Restart and need for reorg14_Frederick Moor17_signup@AZNEWT.COM30_Fri, 4 Dec 2009 21:19:00 +0000454_utf-8 Having a hard time finding a answer to this question: if I do an alter table to restart an identity column, do I need to do a reorg if I don't want to change any of the values that are already in the database.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 33108 104 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.013_David S Waugh16_dsw-dba@JUNO.COM28_Sat, 5 Dec 2009 00:25:39 GMT552_windows-1252 Jorg:

What they're doing, essentially, is using application programs to try to keep certain "Legacy" data on DB2 in synch with data on Oracle. So data goes both directions. And there are hundreds of programs doing it, some near-realtime, some overnight, some on-demand...

Oddly enough, when they update DB2 data from an Oracle app via OTG, it doesn't have that same expansion problem - it's only when they try to select DB2 data from an Oracle app via OTG that the CHAR columns are getting tripled in length. Go figure. [...] 33213 34 46_Re: Identity Column Restart and need for reorg16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Fri, 4 Dec 2009 21:54:54 -0500544_utf-8 NO reorg is needed - this command just tells DB2 where to start the next INSERT from. You are good to go :-)

Steen Rasmussen CA Sr Engineering Services Architect IBM Certified Database Associate - DB2 9 Fundamentals IBM Certified Database Administrator - DB2 9 DBA for z/OS





-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Frederick Moor Sent: Friday, December 04, 2009 3:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Identity Column Restart and need for reorg [...] 33248 54 29_Agnaldo Silva is on vacation.13_Agnaldo Silva19_agnaldos@BR.IBM.COM30_Sat, 5 Dec 2009 04:00:22 -0200644_US-ASCII

I will be out of the office starting 12/04/2009 and will not return until 12/07/2009.





Thanks.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups, there is probably one near you! Regional User Groups are your local connection to the Worldwide DB2 User Community _____________________________________________________________________ [...] 33303 97 45_Re: what happens in opening cursor in detail?12_Martin Hubel17_Martin@MHUBEL.COM30_Sat, 5 Dec 2009 11:39:11 -0500 33401 28 45_Re: what happens in opening cursor in detail?14_Peter Vanroose17_pvanroose@ABIS.BE30_Sat, 5 Dec 2009 16:46:39 +0000711_UTF-8 > In DB2 LUW, explain shows both the full cost and first row cost.

Anybody knows whether this information is also available on z/OS through one of the new explain tables?

-- Peter.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups, there is probably one near you! Regional User Groups are your local connection to the Worldwide DB2 User Community _____________________________________________________________________ [...] 33430 80 46_Re: Identity Column Restart and need for reorg12_Myron Miller22_myronwmiller@YAHOO.COM30_Sat, 5 Dec 2009 13:18:38 -0800765_iso-8859-1 Nope. Just alter and go. Nothing else is needed at all. Myron ________________________________ From: Frederick Moor To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Fri, December 4, 2009 4:19:00 PM Subject: [DB2-L] Identity Column Restart and need for reorg Having a hard time finding a answer to this question: if I do an alter table to restart an identity column, do I need to do a reorg if I don't want to change any of the values that are already in the database. _____________________________________________________________________ * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ http://www.idug.org/solutions-journal.html - home of the [...] 33511 231 49_Re: AIX DB2 V9.1Experiences using Recovery Expert13_Gerard Le Roy24_gerard.le.roy@WANADOO.FR30_Sun, 6 Dec 2009 07:35:23 +0100465_iso-8859-1 Oups !? No reply !

Does it mean that we are the only customer in the world ?

Regards

Gerard





_____

De : IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] De la part de Gerard Le Roy Envoyé : vendredi 4 décembre 2009 07:24 À : DB2-L@WWW.IDUGDB2-L.ORG Objet : [DB2-L] AIX DB2 V9.1Experiences using Recovery Expert





Hello everybody,

Looking for experience with Recovery Expert V2.1. [...] 33743 585 26_Re: RUNSTATS' Alternative?9_DB2DBAzOS21_bala.db2dba@GMAIL.COM30_Mon, 7 Dec 2009 11:04:19 +0530450_windows-1252 Hi Josh,

If you have not looked it up on internet yet, these are the links for those V7's PTFs.

http://www-01.ibm.com/support/docview.wss?rs=0&q1=Real+time+Statistics&uid=swg1PQ48448&loc=en_US&cs=utf-8&cc=us&lang=en

And, there are plenty of IBM docs that can be looked up at www.ibm.com/support and IDUG presentations (available on IDUG website) that I referred while implementing RTS in my shop (v7 z/OS 1.4). [...] 34329 167 38_z/OS V8 and V9 SQL Optimizer weirdness11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 7 Dec 2009 09:41:09 +0100502_ISO-8859-1 Hi all!

OK, ok ,I know the SQL below is not 100% politically correct but please try it at your site and check the EXPLAIN output...

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1; SELECT * FROM SYSIBM.SYSTABLESPACE WHERE RTRIM(DBNAME) = 'DSNDB06' AND RTRIM(NAME) = 'SYSDDF'; SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1; SELECT * FROM SYSIBM.SYSTABLESPACE WHERE RTRIM(DBNAME) LIKE 'DSNDB06' AND RTRIM(NAME) LIKE 'SYSDDF'; SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1; [...] 34497 80 15_RTRIM weirdness11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 7 Dec 2009 10:01:06 +0100376_ISO-8859-1 ok, I have found out that RTRIM was "optimized" out of the SQL in V6 with PQ53700 so now the only question is why is = doing a TS Scan...





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 [...] 34578 282 50_AW: [DB2-L] z/OS V8 and V9 SQL Optimizer weirdness35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Mon, 7 Dec 2009 10:33:32 +0100525_iso-8859-1 Hi Roy

I was surprized, that the query with the RTRIM-function and LIKE returned a row at all. But, if you use OSC, you see, that DB2 indeed removes the RTRIM-function.It looks like:

SYSIBM.SYSTABLESPACE.DBNAME LIKE 'DSNDB06'

But, if that woulf be the real predicate, no row should qualify

Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com [...] 34861 174 27_AW: [DB2-L] RTRIM weirdness35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Mon, 7 Dec 2009 10:36:06 +0100434_iso-8859-1 Because according to OSC it is a stage2-predicate and the function is not removed. Apparently only with LIKE the function is removed. That's strange or a you called it weird.

Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf Tel.: +49 211 477-2928 Fax: +49 211 477-2615 mailto:walter.janissen@itergo.com [...] 35036 374 54_Re: AW: [DB2-L] z/OS V8 and V9 SQL Optimizer weirdness11_Roy Boxwell16_R.Boxwell@SEG.DE30_Mon, 7 Dec 2009 10:50:42 +0100569_ISO-8859-1 odd is the word!





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







Walter Janißen Gesendet von: IDUG DB2-L 07.12.2009 10:33 Bitte antworten an IDUG DB2-L [...] 35411 250 35_DB2 LUW V8.2 Tablespace Unavailable15_Schade, Jeffrey15_JSchade@ISO.COM30_Mon, 7 Dec 2009 08:55:55 -0500555_us-ascii I have a weekly database backup which fails sometimes because the catalog tablespace is in an unavailable status. By the time I log in and check the errors the status has cleared and I am having trouble identifying the culprit. Is there some catalog table I can access which will show me the utilities that have been run against the catalogs (i.e. similar to SYSCOPY in the DB2 for Z/Os environment)? I plan on setting up a job that will run if my backup fails which will do a LIST APPLICATIONS and LIST TABLESPACES SHOW DETAIL to try an id [...] 35662 361 29_DB2 People Soft Compare issue9_SrinivasG21_SRINIVASG@INFOSYS.COM30_Mon, 7 Dec 2009 20:27:51 +0530558_us-ascii Hi,

The user is running compare reports in People Soft that is accessing DB2 V9 Database. Consistently we are getting the below error :

- DSNILMCL RESOURCE UNAVAILABLE 776 CORRELATION-ID=pside.exe CONNECTION-ID=SERVER LUW-ID=AC15670A.G1BE.C5334BCE40ED=6 REASON 00C90096 TYPE 00000304 NAME PSHRDMO .PTPRJWK .X'0009C9' '.X'0A'



I have Altered the Tablespaces to have lock size Table or Tablespace. In addition I have reset NUMLKUS to 0. Earlier NUMLKUS was 10000 and then 30000. Out of desperation I have set it to 0. [...] 36024 373 33_Re: DB2 People Soft Compare issue15_Schade, Jeffrey15_JSchade@ISO.COM30_Mon, 7 Dec 2009 10:56:58 -0500469_utf-8 Hi Srinivas, The error you are getting is because the application is obtaining more locks than are allowed for a single user. You must either increase the NUMLKUS zparm value to allow more locks for a single user or have the application perform more frequent commits to release locks held. We have also, in some cases, had the application code a LOCK TABLE which eliminates all concurrent access but does not acquire more than one lock for the entire table. [...] 36398 88 10_2 Redbooks20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Mon, 7 Dec 2009 18:15:43 +0200751_US-ASCII

MySQL to DB2 Conversion Guide Published: December 1, 2009 ISBN: 0738433659 476 pages Explore the book online at http://www.redbooks.ibm.com/abstracts/sg247093.html?Open



DB2 9 for z/OS Performance Topics Revised: December 2, 2009 ISBN: 0738488836 426 pages Explore the book online at http://www.redbooks.ibm.com/abstracts/sg247473.html?Open

Regards, Cuneyt

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 36487 466 33_Re: DB2 People Soft Compare issue11_Mike Turner19_mike.turner@GMX.COM30_Mon, 7 Dec 2009 16:23:08 -0000357_iso-8859-1 Hi Srinivas

The message indicates that row level locking is being used (Type 304), so your alters of locksize to tablespace may not have worked if you are still seeing Type 304 on the errors. Also, the tablespace may be defined with LOCKMAX n (i.e. not SYSTEM). In this case the NUMLKUS is overridden by the tablespace specification. [...] 36954 521 33_Re: DB2 People Soft Compare issue11_Mike Turner19_mike.turner@GMX.COM30_Mon, 7 Dec 2009 16:28:47 -0000493_iso-8859-1 Oops. LOCKMAX overrides NUMLKTS not NUMLKUS. I hate Mondays!

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. ----- Original Message ----- From: Mike Turner To: IDUG DB2-L Sent: Monday, December 07, 2009 4:23 PM Subject: Re: [DB2-L] DB2 People Soft Compare issue [...] 37476 29 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.010_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Mon, 7 Dec 2009 16:33:26 +0000496_UTF-8 David,

Interesting! Having been part of an Oracle to DB2 effort I recognize some of the solutions. "Interim" indeed. The transparent gateway has never struck me as an enterprise class solution there always seems to be something that goes wrong.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 37506 30 44_Optimize for 1 Row as a Default BCA Madness?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Mon, 7 Dec 2009 16:36:20 +0000332_UTF-8 Why oh why would anyone make Optimize for 1 row a global parm? There's nothing like getting a question about poor performance and seeing

OPTIMIZE FOR 1 ROW FETCH FIRST 400 ROWS ONLY

With the clause 300+ seconds without 3 seconds. The underlying tablespace has a fantastic rate if 1.13 getpages per sync/io. [...] 37537 288 33_Re: DB2 People Soft Compare issue16_Robert Catterall21_rfcatterall@GMAIL.COM30_Mon, 7 Dec 2009 11:53:40 -0500528_ISO-8859-1 If the PeopleSoft process is using dynamic SQL, and if dynamic statement caching is being utilized, is it possible that the ALTER TABLESPACE LOCKSIZE change has had no effect because the statements in question are cached and have not been re-prepared since the execution of the ALTER TABLESPACE statement? If so, you could try invalidating the cached statements by way of a RUNSTATS job with UPDATE NONE and REPORT NO. Then, when the statements are re-prepared, the new LOCKSIZE specification should be honored. [...] 37826 810 33_Re: DB2 People Soft Compare issue9_SrinivasG21_SRINIVASG@INFOSYS.COM30_Mon, 7 Dec 2009 22:25:33 +0530386_us-ascii Hi Mike,

I set LOCKMAX to System for this Tablespace and the compare is working !! I will keep monitoring.

Thanks to all who replied.

Regards, Srinivas G

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Turner Sent: Monday, December 07, 2009 9:59 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 People Soft Compare issue [...] 38637 636 26_Re: RUNSTATS' Alternative?7_DB2 DBA21_the.db2.dba@GMAIL.COM30_Mon, 7 Dec 2009 12:15:32 -0500902_windows-1252 Thanks Bala, that's plenty.



-Josh





On Mon, Dec 7, 2009 at 12:34 AM, DB2DBAzOS wrote:

> Hi Josh, > > If you have not looked it up on internet yet, these are the links for those > V7's PTFs. > > > http://www-01.ibm.com/support/docview.wss?rs=0&q1=Real+time+Statistics&uid=swg1PQ48448&loc=en_US&cs=utf-8&cc=us&lang=en > > And, there are plenty of IBM docs that can be looked up at > www.ibm.com/support and IDUG presentations (available on IDUG website) > that I referred while implementing RTS in my shop (v7 z/OS 1.4). > > References. > > http://www-01.ibm.com/support/docview.wss?rs=0&q1=Real+Time+Statistics&uid=swg27004021&loc=en_US&cs=utf-8&cc=us&lang=en > > > http://www-01.ibm.com/support/docview.wss?rs=0&q1=Real+Time+Statistics&uid=swg27005106&loc=en_US&cs=utf-8&cc=us&lang=en > > On Fri, Dec 4, 2009 at 9:11 [...] 39274 227 49_Re: AIX DB2 V9.1Experiences using Recovery Expert13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM30_Mon, 7 Dec 2009 10:54:02 -0700307_ISO-8859-1 I think it just means that LUW customers don't make mistakes that require Recovery Expert. (ha ha ha, yeah right!)

I had a customer that used RE once (back in the 1.1 days) and was able to undo a really nasty transaction -- but other than that I haven't ever run into it in the wild. [...] 39502 260 29_ZOS - V8 DB2 trigger question17_Janes, Ray {PBSG}18_ray.janes@PBSG.COM30_Mon, 7 Dec 2009 12:34:37 -0600350_us-ascii Hello all, Can triggers be used to replicate data changes on the same LPAR, but to different DB2's?

Assume the same LPAR. TB1 on DB2A, TB2 is identical in structure and data but on DB2B. An insert, update or delete occurs on TB1. Can it fire triggers and insert, update or delete the same data on TB2? TB2 is on a different DB2. [...] 39763 104 61_Re: DB2 z/OS V8 NFM and Oracle Transparent Gateway 10.2.0.3.013_David S Waugh16_dsw-dba@JUNO.COM28_Mon, 7 Dec 2009 18:36:29 GMT697_windows-1252 All:

Thought I should provide some followup, in case others run into this same problem down the road...

Oracle Metalink doc ID 374744.1 says: ------------------------------------------------------------------ Symptoms

Using any Oracle gateway up to release 11.1.0.7 with an Oracle unicode database character set will triple the varchar/char columns precision.

[snip]

Cause

In certain cases, the the length of Oracle CHAR and VARCHAR columns might be different from the original DB2 columns. This is due to character set differences that require more (or fewer) bytes in the Oracle representation than in the original DB2 representation. [...] 39868 52 57_Re: Approximate "break-even" when using Rowset processing14_Peter Vanroose17_pvanroose@ABIS.BE30_Mon, 7 Dec 2009 19:17:16 +0000357_UTF-8 When searching for a "break-even" (runtime), don't forget to take into consideration the length of the data rows being fetched. Multi-row fetch will be especially beneficial (even for just a few rows) if the rows are very *short*.

E.g.: you want to retrieve a list of PKs (INTs or SMALLINTs), to be stored locally for some (limited) time. [...] 39921 177 46_Re: Identity Column Restart and need for reorg11_Suresh Sane21_data_arch@HOTMAIL.COM30_Mon, 7 Dec 2009 13:19:10 -0600587_iso-8859-1

As Steen and Myron have pointed out, no REORG is needed.





However, if you have static SQL, you should be aware of this also:





All dependent packages are invalidated (why? I do not know and have never heard a satisfactory answer from IBM).





Assuming autobind, next execution, you will be fine. If you are paranoid (like we are) about auto rebind failing, making the package inoperative and causing serious outages, even this seemingly simple alter may cause trouble. Unlikely but possible. BEWARE! [...] 40099 34 36_Re: Query CPU time keeps increasing.10_Jorg Lueke23_jorg_h_Lueke@BCBSMN.COM30_Mon, 7 Dec 2009 19:19:38 +0000401_UTF-8 I remember this happening when there were joins and one table was also being inserted into during the day. CICS would keep populating the one table and over time the resulting joins would return more data requiring more CPU. That scenario also suffered from unstable access paths because the table size was not too large and what was optimal for the optimizer changed for even 50,000 rows. [...] 40134 242 33_Re: ZOS - V8 DB2 trigger question16_Robert Catterall21_rfcatterall@GMAIL.COM30_Mon, 7 Dec 2009 18:31:12 -0500619_ISO-8859-1 A trigger defined on a table in subsystem DB2A can't directly reference objects in subsystem DB2B. The trigger could call a stored procedure defined on the local subsystem (DB2A), and that stored procedure could reference objects in DB2B; however, if the rate of data change for the table in DB2A (the table for which you want to replicate changes to a "mirror" table in DB2B) is high (maybe many changed per second), I'd be concerned about the impact on throughput of defining triggers that call stored procedures that perform remote data change operations -- since the triggered action is synchronous [...]