1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2008, week 4 2 16 64_Re: DB2 v8 for z/OS - Parameterized Dynamic SQL vs. Stored Procs10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Fri, 21 Mar 2008 15:19:10 +0000732_utf-8 Java procs should be zAAP-able and in V9 SQL procs will be zIIP-able.

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA * _____________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on [...] 19 24 20_View Materialization10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Fri, 21 Mar 2008 15:23:50 +0000521_utf-8 The SQL reference states "The specification is an SQL SELECT statement that is effectively executed whenever the view is referenced in an SQL statement. At any time, the view consists of the rows that would result if the fullselect were executed." regarding views.

It sounds like if my view is selecting 50 columns from a table but I only need 10 then the view will materialize all 50 and just give me back 10. In other words I'd be better off not using the view and just retrieving the columns I need. [...] 44 141 26_Re: Slow Running SQL Query16_Rajeev Dhanawade28_Rajeev.Dhanawade@STATE.DE.US31_Fri, 21 Mar 2008 14:01:15 +0000285_utf-8 Hi Joel,

Thank you for your response. I have TMON/DB2. We do store the data from it and I will check with tech support to see what level of granularity is generated and stored. If you can guide me regarding the reports I should look for I would really appreciate it. [...] 186 47 18_Re: No read engine5_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Fri, 21 Mar 2008 13:52:20 +0000425_utf-8 It looks like this thread is addressing a problem that I have encountered (probably for some time).

We have been researching some stress to the I/O subsystem within the DB2 sort work pool (DSNDB07). This pool is in an isolated bufferpool with the following def:

-ALTER BUFFERPOOL(BP5) VPSIZE(150000) PGFIX(YES) -ALTER BUFFERPOOL(BP5) VPSEQT(100) -ALTER BUFFERPOOL(BP5) DWQT(50) VDWQT(50) VPPSEQT(50) [...] 234 194 11_job posting15_Ian Stevanovich25_istevanovich@SIPERIAN.COM31_Thu, 20 Mar 2008 14:59:18 -0700 429 53 24_Re: View Materialization14_Peter Vanroose26_peter_vanroose@YAHOO.CO.UK31_Sat, 22 Mar 2008 16:55:20 +0100445_iso-8859-1 > The SQL reference states "The specification is an SQL SELECT > statement that is effectively executed whenever the view is > referenced in an SQL statement. > At any time, the view consists of the rows that would result if > the fullselect were executed." regarding views. > > It sounds like if my view is selecting 50 columns from a table > but I only need 10 then the view will materialize all 50 and > just give me back 10. [...] 483 166 24_Re: View Materialization0_24_Mohammad_Khan@BCBSIL.COM31_Sat, 22 Mar 2008 07:40:36 -0500382_us-ascii I rarely use views so I don't have a first hand experience but "effectively" seems to be the keyword here. The following simple test will clarify the situation. 1. Create a view on a large table with no where clause. 2. Do a query on the view using table's primary key as the selection criteria. 3. Check accounting data for the query to see what happened. HTH Khalid [...] 650 103 26_Re: DB2 V8 and DB2 Connect15_Leon Katsnelson15_leon@CA.IBM.COM31_Thu, 20 Mar 2008 20:49:15 -0400279_US-ASCII If you absolutely have to go to DB2 Connect v8 (and I don't know why that would be the case) than I would recommend that you consider FixPack 15. However, I would strongly recommend that you make plans to go to DB2 Connect v9.5 instead (FP1 is coming out shortly). [...] 754 137 24_Re: View Materialization0_19_askwe@OPTONLINE.NET31_Sat, 22 Mar 2008 13:46:01 +0000368_us-ascii Jorg,

Are you talking about view materialization or view merge? Following is what I know about view merge: If a query only select 10 out of the 50 view columns that was specified in a created view, at bind time the access path will only select the 10 actual base table columns directly from the base table and view materialization does not occur. [...] 892 172 26_Re: DB2 V8 and DB2 Connect12_Myron Miller22_myronwmiller@YAHOO.COM31_Sun, 23 Mar 2008 07:59:20 -0700428_iso-8859-1 Leon, While DB2 Connect V9.5 may be all well and it good, it has one very major and serious flaw that I'm sure is keeping some shops including the one I'm at.

Db2 Connect V8 includes Development Center for developing SQL stored Procedures. which works.

With V9 of DB2 connect, Development Center goes away and you have to migrate to Data Studio. Unfortunately, Data Studio is not production ready. [...] 1065 329 26_Re: Slow Running SQL Query35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Sun, 23 Mar 2008 21:46:13 -0400347_UTF-8 Hi Rajeev,

If you can get them, can you email me the application accounting long trace reports - ?

From the information you mention, aside from access path issues, you appear to have significant buffer pool tuning issues, based on the IO wait, and Other IO wait times. Are your sort/work objects in their own buffer pool? [...] 1395 193 26_Re: DB2 V8 and DB2 Connect7_Ed Long19_rdhm99a@PRODIGY.NET31_Sun, 23 Mar 2008 19:14:48 -0700292_us-ascii Based upon my experience, if DB2 for z/OS v8 conversion has all those dollar signs around it for your site because of dual licensing, I'd stick with DB2 Connect V8 FP 15. I've done a bunch of testing and a couple of installs and that combo seems to work without a lot of drama. [...] 1589 241 26_Re: DB2 V8 and DB2 Connect12_Fay, Colin M13_CFay@UNUM.COM31_Mon, 24 Mar 2008 09:22:35 -0400538_us-ascii Hi Myron,

Can you itemize the functional issues that you had with Data Studio and v9.5. Thanks

Colin



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Myron Miller Sent: Sunday, March 23, 2008 10:59 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 V8 and DB2 Connect

Leon, While DB2 Connect V9.5 may be all well and it good, it has one very major and serious flaw that I'm sure is keeping some shops including the one I'm at. [...] 1831 45 24_Re: View Materialization15_Patrick Bossman18_bossman@US.IBM.COM31_Mon, 24 Mar 2008 14:50:53 +0000450_UTF-8 Hello, Most often, views merge. I've attached a file where I put some text and screen shots - where I answer this question explicitly, and also show you how you could use Optimization Service Center to answer this type of question on your own queries.

Now is as good a time as any to say, Optimization Service Center fixpack 4 is available for download (at no charge!). Willie did a great job covering this in these two blog posts. [...] 1877 20 24_Re: View Materialization15_Patrick Bossman18_bossman@US.IBM.COM31_Mon, 24 Mar 2008 14:56:59 +0000 1898 71 24_Re: View Materialization10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 24 Mar 2008 15:57:30 +0000579_utf-8 Great Thanks!

On Sat, 22 Mar 2008 16:55:20 +0100, Peter Vanroose wrote:

>> The SQL reference states "The specification is an SQL SELECT >> statement that is effectively executed whenever the view is >> referenced in an SQL statement. >> At any time, the view consists of the rows that would result if >> the fullselect were executed." regarding views. >> >> It sounds like if my view is selecting 50 columns from a table >> but I only need 10 then the view will materialize all 50 and >> just give me back 10. > >This is not [...] 1970 22 24_Re: View Materialization10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Mon, 24 Mar 2008 16:05:07 +0000506_utf-8 I hope you get the attachment working since I do use the OSC. I haven't noticed any view info though but that's surely just me. Speaking of OSC is there a way to get an estimate where a process returns at leats one row. What I mean is often I see estimated rows given as the fraction of a row due to the flter factors. That works fine for averages, but it doesn't help me as much when I know that at least a single row is going to find itsway into the end result regardless of the filter factor [...] 1993 90 24_OMEGAMON Batch reporting15_Degtyarev, Lucy24_ldegtyarev@DIVINVEST.COM31_Mon, 24 Mar 2008 14:48:21 -0400348_us-ascii To All of you who uses OMEGAMON for your CPU Usage/Performance batch reporting ( z/OS ). What kind of reports do you use to monitor CPU usage by correlation ID, user ID,e.t.c.? How often do you run it ? Do you load your results into a a DB ? Do you have or know of a better tools than OMEGAMON to monitor DB2 activities on Mainframe? [...] 2084 23 24_Re: View Materialization12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 24 Mar 2008 18:59:52 +0000873_utf-8 Take a look at Willie's blog - recently updated to include:

http://blogs.ittoolbox.com/database/db2zos/archives/a-little-something-on- view-materialization-and-osc-from-pat-bossman-23283

It's well worth getting on the mailing list for Willie's blog.

Hope that helps - Adam.

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA * _____________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you [...] 2108 23 24_Re: View Materialization12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Mon, 24 Mar 2008 19:01:33 +0000877_utf-8 Take a look at Willie's blog. It's just been updated to include:

http://blogs.ittoolbox.com/database/db2zos/archives/a-little-something-on- view-materialization-and-osc-from-pat-bossman-23283

It's well worth getting on the mailing list for Willie's blog.

Hope that helps, Adam.

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA * _____________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you [...] 2132 203 16_DB2 V8 Migration14_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Mon, 24 Mar 2008 14:11:02 -0500312_us-ascii I have migrated a subsystem on DB2D on the Test Lpar. I would like to use the output dataset on panel DSNTIPA1 on the Test LPAR to be the input on Panel DSNTIPA1 on DB2D on the Test LPAR.





A lot of datasets changed from the V7 release .. so I do not want to input them again. [...] 2336 22 28_Re: OMEGAMON Batch reporting17_Edward Krisieiwcz20_ekrisiewicz@BEAR.COM31_Mon, 24 Mar 2008 19:40:08 +0000765_utf-8 We run loads (SHRLEVEL CHANGE INSERTS) to DB2 tables once the log file gets archived.

Omegamon is missing Statement Level Statistics. At least, the version we have.

Ed

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA * _____________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have [...] 2359 27 24_Re: View Materialization15_Patrick Bossman18_bossman@US.IBM.COM31_Mon, 24 Mar 2008 19:40:43 +0000363_UTF-8 Well, Adam scooped me on my own guest entry on Willie's blog. Word of warning if you visited that entry early in the day.

The initial blog posting I sent to Willie was more paired down. I decided it'd be better to show more examples, and Willie obliged me. (Thanks Willie!) So - if you visited earlier in the day, you might want to pop back in. [...] 2387 90 28_Re: OMEGAMON Batch reporting14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Mon, 24 Mar 2008 19:48:41 +0000409_utf-8 Lucy The best (only?) data collector for DB2 monitoring, accounting, system level statistics etc is DB2 its self. What add on products and tools do is provide enhanced services in managing the collected data and producing reports.

This is not true for mainframe products older than DB2. For many of the 'older products' the tools actually produced new, otherwise not available, information. [...] 2478 147 28_Re: OMEGAMON Batch reporting15_Degtyarev, Lucy24_ldegtyarev@DIVINVEST.COM31_Mon, 24 Mar 2008 16:50:50 -0400563_us-ascii Thank you, Avram. The company already has a license and OMEGAMON batch reporting tool is my tool for now. As for the requirements I have so many that it's hard to prioritize them, but I would say the most important one is to find out what application/s(coming from distributed servers mostly) use the most of the CPU time , how many times it runs, how to single out the one/ones that present a problem? Now, the question about storing reports in a DB came from the need to make the output more readable.I can't present such output to my management. [...] 2626 182 36_ADMIN www.idug.org is currently down16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Mon, 24 Mar 2008 17:12:47 -0400496_iso-8859-1 just a quick note that the web site is currently down (as of Monday march 24 17:10 EDT). And we are working on it to get it back as soon as possible.

This has not effected the Listserv service. The Listserv is working







Robert Galambos CIPP/C

Compuware Senior Technical Specialist IBM Certified Solutions Expert - DB2 UDB for OS/390 Database Administration Certified Information Privacy Professional/Canada robert.galambos@compuware.com [...] 2809 251 26_Re: DB2 V8 and DB2 Connect12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 24 Mar 2008 15:00:35 -0700326_iso-8859-1 I can do some of them. There are way too many to list easily.

#1 is distribution issues - I can't put it on a server and have it installed by various users directly. I have to manually take the CD to each user and install it manually thru the fixpacks myself. That's a fun prospect with 100-200+ users. [...] 3061 75 53_ADMIN quick note web site www.idug.org is now back up16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Mon, 24 Mar 2008 18:26:56 -0400401_utf-8 Mobile email message Compuware Corp. Of Canada Mobile +1-416-876-2979 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. [...] 3137 225 28_Re: OMEGAMON Batch reporting14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Mon, 24 Mar 2008 23:05:07 +0000344_utf-8 Lucy, Sorry about my missunderstanding, I thought you were looking for a batch reporting tool.

Let me make some comments about observing high DIST CPU and wanting to know who the bigger users are:

1) High DIST CPU is often normal 2) It is often difficult to tell who the big users are from application accounting data. [...] 3363 54 26_Re: DB2 V8 and DB2 Connect11_Nick Cianci19_ncianci@AU1.IBM.COM31_Tue, 25 Mar 2008 14:39:58 +1100347_ISO-8859-1 Werv,

just a word of caution. Beware of MS-Vista.

The security changes in Vista make it a right royal pain in the #%@$ !

Allegedly v8 FP14 (I think) gets you over the line, although when I left my last place of employ, we were still having "fun" with both v8 & v9 on the build of Vista our clients were using. [...] 3418 44 26_planning to migrate db2 v814_Renzo razzetti24_renzo.razzetti@GMAIL.COM31_Tue, 25 Mar 2008 13:06:53 +0800286_ISO-8859-1 I 'm plannig to migrate db2 zOs v8. I want to know if there are special consideration abouts the application. Can I migrate to v8 without change the application ? or which are the changes that I have to do in the application to migrate ?

Thank you very much ! RR [...] 3463 385 20_Rotate First To Last14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Tue, 25 Mar 2008 16:45:06 +1100582_us-ascii Hi DB2 Colleagues,

We are on DB2 V7 and have a partitioned table with 30 parts, it has 172 million rows, the data retention is 2 years, the partitioning index is duplicate , it has 2 NPIs , one unique & one duplicate, the partitioning criteria is a date column that represents end of quarter (ex. Pt1 200?-03-31, Pt2 200?-06-30, Pt3 200?-09-30, Pt4 200?-12-31 etc.), parts 1-16 are obsolete & empty, currently we are using parts 17-25, and parts 26-30 will cover us for the next 5 quarters, also we have a quarterly Reorg-Discard that deletes data older that 2 [...] 3849 132 24_Re: Rotate First To Last11_Nick Cianci19_ncianci@AU1.IBM.COM31_Tue, 25 Mar 2008 17:25:02 +1100570_UTF-8 John, some points:

Be wary of what you point at after rotating. The physical partition numbers remain the same, it's just the logical partitions & key ranges are the same. If you are not careful you may end up emptying a partition you didn't intend to. RTM carefully I'd still empty out the old partition with a ReOrg discard. It's a good fast way to get your Archive file, and if you don't empty the partition before the rotate, DB2 will "DELETE" the rows for you (If I remember correctly), which might give you a performance hit. Remember to specify [...] 3982 665 24_Re: Rotate First To Last16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 25 Mar 2008 04:01:27 -0400581_us-ascii Hello John,





Nick pointed out some areas in his reply where you need to pay attention - and I completely agree.

I have one additional comment - you mentioned you will "recreate" (I read this as DROP and CREATE) - in order to convert from Index Controlled Partitioned to TCP (Table Controlled Partitioned). All you need to do is to execute an ALTER statement which is valid only for these object types. So if you remove the CLUSTER indicator on the current partitioned index - DB2 has converted your object from ICP to TCP in a heartbeat. [...] 4648 71 26_Re: DB2 V8 and DB2 Connect14_Adrian Collett24_adriancollett@TISCALI.IT31_Tue, 25 Mar 2008 10:32:08 +0100341_ISO-8859-1 Leon,

I'd like to jump in on this topic if I may.....

I have a customer which has a subsystem running DB2 for z/OS V7 with DB2 Connect V7!! Aren't I lucky :-) Even though this particular subsystem is destined to die very shortly, the customer needs to migrate it to DB2 z/OS V8 to avoid double licensing costs. [...] 4720 298 26_Re: DB2 V8 and DB2 Connect0_26_MarkVickers@GROCERYBIZ.COM31_Tue, 25 Mar 2008 06:31:13 -0500547_US-ASCII Adrian, This page is a great starting point:

DB2 for z/OS Information Roadmap and if the link does not work it is:

http://www-306.ibm.com/software/data/db2/zos/roadmap.html#planning

You may need to clarify whether they are using DB2 Connect EE and /or PE.

Mark Vickers.







Adrian Collett Sent by: DB2 Data Base Discussion List 03/25/2008 04:32 AM Please respond to DB2 Database Discussion list at IDUG [...] 5019 46 24_Re: View Materialization15_Patrick Bossman18_bossman@US.IBM.COM31_Tue, 25 Mar 2008 12:15:01 +0000657_UTF-8 Hi Jorg, Optimization Service Center and Visual Explain are reporting the optimizers estimates. So if the estimates seem low, then the optimizer is estimating low. Often the reasons for the optimizer to estimate low are:

- Lack of correlation and skew statistics to accurately estimate local filtering (this would manifest itself in a low "qualified rows estimate". - Lack of ability to use skew statistics due to use of host variable, parameter marker, special register and not using REOPT. - Lack of KEYCARD statistics on multi-column joins. Without KEYCARD statistics, optimizer assumes columns are independent and not correlated - the [...] 5066 27 28_Re: OMEGAMON Batch reporting17_Gugel, Rich - DOA24_rich.gugel@WISCONSIN.GOV31_Tue, 25 Mar 2008 07:16:08 -0500583_us-ascii Lucy,

We take advantage of the ability of Omegamon's ability to extract and create data suitable for the DB2 load utility from the DB2 SMF data. We run this job at night when cycles are low.

As far as reporting to management you can then use reporting tools like QMF or using Excel via DB2 connect to generate graphs to highlight areas of interest. Nothing like a picture to get a point across. The DBA's and application folks can also query this data and figure problems on their own. Keeping this data available for historical data purposes can be very [...] 5094 69 47_FREE IDUG Webcast - What's New in SQL for DB2 90_21_leyba0224@COMCAST.NET31_Tue, 25 Mar 2008 13:57:54 +0000597_- What's New in SQL for DB2 9 Part 1 Wednesday, April 9, 11 a.m. ET The International DB2 Users Group (IDUG) is pleased to partner with CA to deliver a free Webcast by renowned presenter Phil Grainger, CA Product Management Director. 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. About the Webcast: In this installment of the IDUG and CA co-sponsored DB2 9 for z/OS Webcast Series, join Phil Grainger, CA Product Management Director as he looks at the new object types [...] 5164 266 20_Re: DB2 V8 Migration12_Daniel Allen17_DAllen@SERENA.COM31_Tue, 25 Mar 2008 06:59:27 -0700478_us-ascii Why not use the jobs you used to migrate your first subsystem and change the dataset names. That is what we do to clone a DB2 subsystem.

Daniel Allen | Senior Systems Programmer | 1-800-457-3736x11241





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Stevens, Wayne Sent: Monday, March 24, 2008 12:11 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] DB2 V8 Migration [...] 5431 81 24_Re: View Materialization10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 14:05:04 +0000320_utf-8 Hi Pat,

I understand what you are saying and that makes perfect sense all in all. I don't think what I'm asking is quite the same, or at least I think it is different. Or perhaps you can help me understand what statistics might be off. But, the individual stats are all updated based on stats advisor. [...] 5513 443 26_Re: DB2 V8 and DB2 Connect14_Adrian Collett24_adriancollett@TISCALI.IT31_Tue, 25 Mar 2008 15:14:21 +0100726_ISO-8859-1 Thanks Mark,

I thought i'd RAEOTFW ... obviously not... :-)

Hidden in the program directory and/or product announcement it states quite explicitly that DB2 Connect V7.2 can connect to V8... it is out if service, of course, but that's another problem :-)

Thanks a lot,

Ciao, Adrian



MarkVickers@GROCERYBIZ.COM ha scritto: > > Adrian, This page is a great starting point: > > DB2 for z/OS Information Roadmap > > and if the link does not work it is: > > http://www-306.ibm.com/software/data/db2/zos/roadmap.html#planning > > You may need to clarify whether they are using DB2 Connect EE and /or PE. > [...] 5957 284 26_Re: DB2 V8 and DB2 Connect10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 14:33:37 +0000406_utf-8 Thanks for this information. As we go to V9 for z/OS we are planning to go to V9 of DB2 Connect as well. I suppose we could just update the gateway and leave the clients alone for now. Why does IBM keep changing these tools? Stored Procedure Builder, Development Center, andnnow another one. It's hard enough to get some people to embrace tools at all, it's not helpful when they keep changing. [...] 6242 21 32_RI Without Enforcing Index Check10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 14:36:34 +0000778_utf-8 Does anyone out there have a handy-dandy query that will show those constrainsts that are not currently backed with a primary key or alternate unique index?

Yes, it can be quite prevalent!

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU * ______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. [...] 6264 859 26_Re: DB2 V8 and DB2 Connect14_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Tue, 25 Mar 2008 09:38:58 -0500299_us-ascii I know of a shop who was on DB2 Connect V7.2, migrated to V8 CM. There were some problems with using this version of DB2 connect. They did a fallback to V7 because of the problems. They then upgraded DB2 Connect to V8.2 and migrated back to V8 CM.





Wayne Stevens [...] 7124 19 18_Re: No read engine10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 15:03:00 +0000785_utf-8 Why would running flashcopy impact the number of read engines available? Aren't there 300 read engines within DB2? External factors shouldn't impact their availability.

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU * ______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for [...] 7144 28 28_Re: OMEGAMON Batch reporting10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 15:07:38 +0000589_utf-8 I used to use Omegamon all the time. They had some DDL and jobs to load tables as well which we did. It was invaluable to have the accounting, especialiiy the package accounting data. A few months history was sufficient.

Now we have the new version of Omegamon which is DB2 PM/PE toolset. We also load the data into what they now call a performance warehouse. Otherwise it's much the same as the Omegamon reports with more details. Again for us loading the data is critical. Sifting through flatfiles to find performance trends and abberations would be impractical in my [...] 7173 37 24_Re: View Materialization15_Patrick Bossman18_bossman@US.IBM.COM31_Tue, 25 Mar 2008 15:22:35 +0000320_UTF-8 Hi Jorg, The filter factor on the join predicate looks small because the join predicate is pretty selective. But the filter factor is multiplied by CARDF.

You say we estimate that we're getting 8 rows back from the inner? Then it's 8 inner rows times however many rows are coming from the outer table. [...] 7211 57 18_Re: No read engine14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Tue, 25 Mar 2008 15:36:14 +0000369_utf-8 Why would an external issue like Flash Copy impact the requirement for DB2 read engines?

As I/O slows down due to contention any where in the I/O path the length of time a read engine is needed for a single request increases. As service time in general increases either then number of servers (read engines in this case) or the queue debth increases. [...] 7269 95 11_unsubscribe9_Ambre Kay28_AmbreMKathleen@JOHNDEERE.COM31_Tue, 25 Mar 2008 11:23:12 -0500402_iso-8859-1



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Wayne Driscoll Sent: Friday, March 14, 2008 12:29 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] (Reply destination) [DB2-L] Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM

Then fix your e-mail client so the reply-to is not your direct address. [...] 7365 202 26_Re: DB2 V8 and DB2 Connect14_Adrian Collett24_adriancollett@TISCALI.IT31_Tue, 25 Mar 2008 17:50:28 +0100569_ISO-8859-1 Thanks for that Wayne... don't suppose you know exactly what sort of problems do you ?? As you mention they had to fallback, I presume it was something that didn't crop up in testing... which is just the sort of thing that worries me.... Thanks again, Ciao, Adrian







Stevens, Wayne ha scritto: > > I know of a shop who was on DB2 Connect V7.2, migrated to V8 CM. > There were some problems with using this version of DB2 connect. They > did a fallback to V7 because of the problems. They then upgraded DB2 > Connect to V8.2 [...] 7568 98 26_Re: DB2 V8 and DB2 Connect14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Tue, 25 Mar 2008 17:42:22 +0000476_utf-8 There was a very important structural change that occured at the introduction of DB2 connect V8. Prior to connect V8 the rules, structures and catalog interfaces for the DB2 server were hard coded in connect. At connect V8 the DB2 product / platform code was replaced with stored procedure calls. The intent was to issolate DB2 connect from changes in the server software levels. It became the DB2's servers responsibility to provide the current stored procedures. [...] 7667 293 26_Re: DB2 V8 and DB2 Connect14_Adrian Collett24_adriancollett@TISCALI.IT31_Tue, 25 Mar 2008 20:51:13 +0100330_UTF-8 Avram,

thanks for taking your time to reply... looks like you and I went to the same school :-)))

You hit the nail on the head with ".. because no one has an administrative clue as to were to begin."

As you say in this case(and many others) a bleeding back end is worse than a bleeding edge... :-) [...] 7961 125 24_Re: View Materialization10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 19:59:39 +0000641_- Name Value ---------------------------------------------------------------------------------------------------------------------- -------------------- Input Cardinality 8 Scanned Rows 8 Stage 1 Predicates Filter Factor V.FST_SVC_DT>='2007-01-01' 0.1934 V.LST_SVC_DT<='2008-12-31' 1 V.DISP_CD IN ('1','2') 0.9631 V.LOGL_DEL_IND='N' 1 Stage 1 Returned Rows 1.4484 Stage 2 Predicates Filter Factor V.PRCS_TS=((SELECT MAX(PRD10.CLM_VERS.PRCS_TS) FROM CLM_VERS WHERE PRD10.CLM_VERS.UNADJ_CLM_NBR=V.UNADJ_CLM_NBR)) 9.20335310183873E-8 Stage 2 Returned Rows 1.3329782E-7 Output Cardinality 1.3329782E-7 Stage 1 Columns 8 Page Range Prefetch [...] 8087 41 18_Re: No read engine10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 25 Mar 2008 20:00:27 +0000622_utf-8 I see, thanks for the explanation.

On Tue, 25 Mar 2008 15:36:14 +0000, Avram Friedman wrote:

>Why would an external issue like Flash Copy impact the requirement for DB2 >read engines? > >As I/O slows down due to contention any where in the I/O path the length of >time a read engine is needed for a single request increases. As service time in >general increases either then number of servers (read engines in this case) or >the queue debth increases. > >This relationship is discribed in operational analysis as "Little's Law" >It is not complicated compared to most [...] 8129 492 24_Re: Rotate First To Last11_Hardy, Dale14_DHardy@MIB.COM31_Tue, 25 Mar 2008 16:18:43 -0400555_US-ASCII John,

Agree with all the previous comments.

Our process is similar to what you outlined. 1. Unload all the partitions - the previous image copies cannot be used for recovery to a point in time after the alter. 2. Gen the alter statement. 3. Gen the load statement for the physical partition corresponding to the oldest logical partition. 4. Load dummy the oldest logical/physical partition. 5. Alter the table. 6. Image copy all partitions to re-establish recovery. 7. Runstats 8. Rebind all packages invalidated by the alter. [...] 8622 150 26_Re: DB2 V8 and DB2 Connect12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 25 Mar 2008 14:40:03 -0700568_iso-8859-1 In a way I'd agree with. It's almost impossible to upgrade a fixpack versus a version where I'm at. But it's not because of the way DB2 connect was installed. It was planned for full remote PC/Server access to both Production and test.

When DB2 Connect is on several hundred servers and close to a thousand user's PCs, it's a real chore to roll out the new fixpack/version. Remember that you have to have the machine not using DB2 connect when you apply the fixpack. With 24x7 servers, that is not the easiest thing in the world to schedule. As [...] 8773 188 36_Re: RI Without Enforcing Index Check12_Amit Agarwal20_agarwalami@GMAIL.COM31_Wed, 26 Mar 2008 04:03:04 +0530687_ISO-8859-1 This might help ------- -Amit Agarwal

SELECT F.CREATOR

,F.tbname

,F.COLNAME

,F.RELNAME

,F.COLSEQ

,R.REFTBNAME

FROM SYSIBM.SYSTABLES T

join SYSIBM.SYSFOREIGNKEYS F

on T.NAME = F.TBNAME

AND T.CREATOR = F.CREATOR

join SYSIBM.SYSRELS R

on T.CREATOR = R.CREATOR

AND T.NAME = R.TBNAME

AND F.CREATOR = R.CREATOR

AND F.RELNAME = R.RELNAME

WHERE T.CREATOR in (.....)

AND R.REFTBCREATOR in (.....)

AND R.REFTBNAME LIKE '...%'

AND NOT EXISTS (

SELECT 1

FROM SYSIBM.SYSKEYS K

join SYSIBM.SYSINDEXES I

on K.IXNAME = I.NAME [...] 8962 75 26_Re: DB2 V8 and DB2 Connect13_Kenney, Marty24_Marty.Kenney@RAILINC.COM31_Tue, 25 Mar 2008 19:03:34 -0400327_iso-8859-1 We had issues with DB2 Connect V7.2 and DB2V8 CM and had to fall back to DB2V7 early last year.

Our issue was applications that used WebSphere 5.1 and DB2 Connect V7.2 connecting to DB2V8 and issuing dynamic SQL via JAVA calls were getting garbage results back, and occasionally hanging the application. [...] 9038 100 26_Re: DB2 V8 and DB2 Connect14_Adrian Collett24_adriancollett@TISCALI.IT31_Wed, 26 Mar 2008 00:47:53 +0100608_ISO-8859-1 Marty,

Thanks very much for the info... that's exactly what I'm worried about - testing sql calls directly and everything ok... and then some application doing the "same" and not working...

Looks like I've got an interesting 8 weeks ahead of me .. :-)))

Thanks for your time

Ciao, Adrian



Kenney, Marty ha scritto: > We had issues with DB2 Connect V7.2 and DB2V8 CM and had to fall back to DB2V7 early last year. > > Our issue was applications that used WebSphere 5.1 and DB2 Connect V7.2 connecting > to DB2V8 and issuing dynamic SQL via JAVA calls [...] 9139 59 18_Re: No read engine35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Tue, 25 Mar 2008 19:53:28 -0400570_UTF-8 There are 600 read engines.

Avram's answer was right on the money.

Regards, Joel



Joel Goldstein Responsive Systems Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts Group Buffer Pool performance too! www.responsivesystems.com tel. (732) 972-1261 fax.(732) 972-9416 ----- Original Message ----- From: "Jorg Lueke" Newsgroups: bit.listserv.db2-l To: Sent: Tuesday, March 25, 2008 11:03 AM Subject: Re: [DB2-L] No read engine [...] 9199 91 18_Re: No read engine12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 26 Mar 2008 01:49:38 +0000540_utf-8 At first V8 had 600 write engines, but the virtual storage consumption was too high, so the number was reduced to 300 again as a default back in 2006. Willie's blog: http://blogs.ittoolbox.com/database/db2zos/archives/the-report-of-excessive- cpu-consumption-was-an-exaggeration-9970 APAR PK21237 May 2006 http://www.ibm.com/support/docview.wss? rs=64&context=SSEPEK&q1=PK21237&uid=swg1PK21237

Roger Miller

On Tue, 25 Mar 2008 19:53:28 -0400, Joel Goldstein - Responsive Systems wrote: [...] 9291 113 23_Re: DB2 v8 new features12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 26 Mar 2008 02:00:21 +0000835_utf-8 One of the easiest lists of new features is on the web in this Redbooks publication: SG24-6079. It's a very long list. http://www.redbooks.ibm.com/redbooks/pdfs/sg246079.pdf The performance topics book continued to change and has more. http://www.redbooks.ibm.com/redbooks/pdfs/sg246465.pdf

Roger Miller

On Sat, 15 Mar 2008 16:01:02 -0400, Amie Isemann wrote:

> >Hi Renzo, > >Here's a list; > >Introduction >64-Bit Architecture >Environment/System Changes >Catalog changes >MDC Multidimensional Clustering >Long Names >MQT Materialized Query Tables >Sequence & Identity Column Changes >EDM DBD Cache >Buffer Manage/Pools >Unicode >Temporary Tables >Multilevel Security >PIT Recovery >XES Contentition >Castout changes >Locking Changes >SQL >SQL limits >DISTINCT >Multiple Inserts [...] 9405 45 13_Rule of Thumb14_Renzo razzetti24_renzo.razzetti@GMAIL.COM31_Wed, 26 Mar 2008 10:07:15 +0800313_ISO-8859-1 Please, Anybody tell me a rule of thumb about : if I increase the total BufferPools about 50% of memory, how is the % I will gain in I/O ???? I know that exist many products that they do the projection. I need only a Rule of Thumb in this moment, a serious rule of thumb.... anybody can help me ? [...] 9451 269 59_Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 26 Mar 2008 02:35:14 +0000422_utf-8 Sorry that one was missed. I checked the book and found this in the incompatibilities section back as far as the -00 level. Is this a match? The SQL standard semantics are those of V8, while many customers depended upon the earlier flavor, except for some Java work that only functions with standard behavior. What were you setting for MAX_ST_PROC MAX STORED PROCS ===> Maximium active stored procs per thread? [...] 9721 136 17_Re: Rule of Thumb14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 26 Mar 2008 03:57:57 +0000417_utf-8 The answer is "it depends" and there are lots of ways of looking at this. The following is a personal preference based on two assumptions 1. index and pagesets are in diffrent pools 2. the assumption for index and data pagesets are diffrent.

In both cases I/O reduction tends in my opinion to be a step function and not a smooth function. the 'steps' are diffrent between a data pool vs index pool. [...] 9858 130 34_AW: [DB2-L] DB2 V8 and DB2 Connect12_Peter, Georg15_G.Peter@DZBW.DE31_Wed, 26 Mar 2008 08:29:33 +0100517_iso-8859-1 Noch ebbes zur Kenntnis ;-))

With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- This e-mail is environment friendly and was made only from recycled electrons.

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Avram Friedman Gesendet: Dienstag, 25. März 2008 18:42 An: DB2-L@www.idugdb2-l.org Betreff: Re: [DB2-L] DB2 V8 and DB2 Connect [...] 9989 29 31_[zos] unload lob >32k very slow1_i22_ivan.losadajuan@BDE.ES31_Wed, 26 Mar 2008 12:06:46 +0000374_utf-8 Hi, list

we're unloading a table with lobs > 32 k, using new funcionality (pk22910), but it goes very slowly. we're in v8 nfm, table with 500000 rows, lob size < 110k, and elapsed of unload > 13 hours.

we see in mainview that 97% of elapsed time is in UNIT SWITCH EVENTS ... OTHER. number of events = number of lobs, and average aprox. 80 ms/event. [...] 10019 1177 63_[off topic] EARLY FRIDAY (was RE: [DB2-L] Rotate First To Last)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 26 Mar 2008 09:36:49 -0400343_us-ascii Hey, Dale, I couldn't help noticing - I thought the Men in Black (MIB) didn't officially exist, so how can you be working for them? Say hi to J and K for me, eh?





--Phil Sevetson, NYCAPS DBA Support

Financial Information Services Agency of The City of New York

450 West 33rd Street, 4th Floor [...] 11197 243 17_Re: Rule of Thumb35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 26 Mar 2008 09:39:38 -0400467_iso-8859-1 Renzo,

There is no "rule of thumb".

If your pool is far too small, then adding 50% will help a lot, doubling will help even more. But there is no relationship like you would like to have. It's always dependant on your abject, access, and workload.

As an example, taking a pool with 10,000 buffers, and adding 5,000 to make it 15,000 might save you 2 IOs, or 50 IOs. The heavier the IO rate/sec, the greater the potential payback. [...] 11441 371 54_Where can I find "end user information" in EPDM tables19_Calleja Vilar, Juan22_jcallejav@IBERDROLA.ES31_Wed, 26 Mar 2008 14:46:25 +0100314_us-ascii

Hello everybody. I'm a DB2 z/OS DBA and I'm trying to get as much information as possible from our Websphere applications that use DB2 Connect. We are planning to use some APIs that provide end user information:

* End user ID. * End user transaction name. * End user workstation name. [...] 11813 142 18_Re: No read engine35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 26 Mar 2008 09:58:30 -0400386_UTF-8 Hi Roger,

V6 and V7 also had 600 read engines, and the V8 assemblies I have all show 600. Don't remember when the assemblies were performed. Obviously before that apar was applied.

Aside from memory constraint relief, are there any performance throughput measurements of before/after the engine reduction?

Thanks for the update on the # of read engines. [...] 11956 140 30_distributed threads for DB2 V820_Barth, Stephen (IHG)19_Steve.Barth@IHG.COM31_Wed, 26 Mar 2008 10:41:06 -0400424_us-ascii Hi All,





We just installed V8 CM on our production subsystems this weekend. Everything looks good except that some of our distributed threads seem to be experiencing significantly greater CPU and elapsed times. We've kept the same ddf zparms that we had in V7. I'd appreciate any feedback from any of you who might have had similar experiences. We've also opened an ETR with IBM. Thanks. [...] 12097 59 35_Re: [zos] unload lob >32k very slow15_Kalena, Michael16_MKALENA@BEAR.COM31_Wed, 26 Mar 2008 10:58:04 -0400501_us-ascii Have you tried unloading to HFS (zFS) instead of PDSE?

A co-worker here found that to be faster, I'll look for his email to get you the details.

Michael Kalena 973-793-2133 mkalena@bear.com

DB2 Info Page at BSC (http://whsysops1/db2/)

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of i Sent: Wednesday, March 26, 2008 8:07 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] [zos] unload lob >32k very slow [...] 12157 71 35_Re: [zos] unload lob >32k very slow13_Bright, Randy20_Randy_Bright@BMC.COM31_Wed, 26 Mar 2008 10:04:36 -0500355_us-ascii Are you using DSNUTILB?

From our experiences, we see the use of SQL file reference host variables in DSNUTILB. This causes allocation of the PDS referenced by the host variable with member name included for each row unloaded, followed by de-allocation. The next row drives allocation/de-allocation again. This is a very slow process. [...] 12229 128 35_Re: [zos] unload lob >32k very slow15_Kalena, Michael16_MKALENA@BEAR.COM31_Wed, 26 Mar 2008 11:05:26 -0400321_us-ascii I found the write up Joe Frank did for us (hope it helps):

FYI...I've put together some sample JCL for unloading tables containing LOB columns. The samples are:

UNLOBPDS: Unload table using a PDSE to hold to LOB column values UNLOBHFS: Unload table using an HFS path to hold LOB column values [...] 12358 743 25_QUESTION ON DYNAMIC QUERY13_Robert Knight28_rknight@CONTEMPORARYTECH.COM31_Wed, 26 Mar 2008 11:13:45 -0400850_us-ascii







OS: AIX 5.3

Database: DB2 UDB V8.2





Query Performance:

The query below usually executes in about 45min. But after the reorg/runstats it is taking 4hrs. When I did a comparison of the old and new access plans, I find one index of table MASTPRD as the culprit for this increase in cost.





select unpd_offstcd_no as stcdnbr, unpd_offstcd_da_id as stcddash, unpd_prod_fac_ty as fac_type, unpd_prod_fac_no as fac_nbr, unpd_prod_dt as prod_dt, unpd_prod_turn_in as turn, unpd_adj_gross_am as onweight, unpd_lrts_seq_no as lrts_seq, unpd_onstcd_no as onscdnbr, unpd_onstcd_da_id as onscdash, unpd_onmfgord_id as mfgordid, unpd_setoff_in as setoff, unpd_bu_bd_in as bubd_in, mspd_scrap_wgt_am as crscrap, mspd_ctd_scrap_am as ctdscrap, mspd_net_wgt_am as [...] 13102 170 38_Re: AW: [DB2-L] DB2 V8 and DB2 Connect7_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 26 Mar 2008 09:12:38 -0700412_iso-8859-1 There is one other difference between V7 and V8 of Connect that caused us some entertainment. The scrollable cursor support had a modified API in V8 which caused havoc with one of our applications.

The z/OS stored procedures described below can chew up a fair amount of processor on a busy system and in some cases may require rerunning sample jobs particularly after a maintenance event. [...] 13273 61 34_Re: distributed threads for DB2 V814_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 26 Mar 2008 16:33:53 +0000349_utf-8 Is it possible that you may be comparing an apple to a fruit basket here? One of the new 'features' 'improvements' in DB2 V8 is accounting roll up for DDF and RRSAF ... And this new (supply your own word) is the default!

The related ZPARM is DSN6SYSP ACCUMACC and can be changed on the install pannel "Traceing Parameters" DSNTIPN [...] 13335 144 35_Re: [zos] unload lob >32k very slow13_Palko, George16_gpalko@OPERS.ORG31_Wed, 26 Mar 2008 13:21:24 -0400351_UTF-8 Hi,

I would recommend that you try using the DB2 Cross Loader to unload/load LOB data. I just implemented it at our shop, and the DBA's are very happy with it's performance and functionality. Note: the columns lengths now can exceed 32K.

Below are some notes on the Cross Loader. Note: the columns lengths now can exceed 32K. [...] 13480 27 46_DB2 for Z version 9 with DB2 connect version 85_Larry34_L-IS.Kirkpatrick@MUTUALOFOMAHA.COM31_Wed, 26 Mar 2008 15:27:54 -0500400_us-ascii We are strongly considering going to version 9 of DB2 on the mainframe and currently have DB2 Connect V8.1 fixpack 6 deployed to the enterprise. It looks like there is a strong recommendation that we upgrade DB2 connect to version 9 before we upgrade mainframe DB2. Has anyone out there installed version 9 of DB2 on the mainframe using DB2 connect version 8? What was your experience? [...] 13508 97 17_Re: Rule of Thumb0_17_jtonchick@AOL.COM31_Wed, 26 Mar 2008 16:45:27 -0400510_us-ascii I don't suggest jumping changing the values in increments more than 10-20% at a time.? You need to take a base line of your performance first, then measure it after you change the value.? If you meet your target performance values, STOP.? If not, then increase again by 10-20% and measure your performance.? Repeat this cycle until you hit your target, or no longer see an improvement in performance.? If you don't see an appreciable improvement, you've reached the point of diminishing returns. [...] 13606 119 11_Job Posting15_Ian Stevanovich25_istevanovich@SIPERIAN.COM31_Wed, 26 Mar 2008 15:15:38 -0700 13726 258 59_Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 26 Mar 2008 17:20:21 -0700411_iso-8859-1 Roger, It's a bit more serious about the multiple calls than just the associate locator issue. Each call sets up an area in the EDM pool for the result set of about 6k, give or take. Now do the calls about 500,000 times and figure the resulting size of the EDM pool. It's slightly larger than 2g (by a large bunch). And when you run out of EDM pool, you obviously know where DB2 is going, DOWN! [...] 13985 24 50_Re: DB2 for Z version 9 with DB2 connect version 812_Roger Miller19_millerrl@US.IBM.COM31_Thu, 27 Mar 2008 00:35:38 +0000304_utf-8 I predict that there is an upgrade in your future. The testing experience here in the lab convinced us to make the minimum supported level DB2 Connect Version 8 fixpack 13 or DB2 9 fixpack 1. Our service and test groups tend to suggest moving later yet, to fixpack 15 for V8 and 4a for DB2 9. [...] 14010 69 34_Re: distributed threads for DB2 V812_Roger Miller19_millerrl@US.IBM.COM31_Thu, 27 Mar 2008 00:58:39 +0000289_utf-8 Comparing class 2 CPU times will often show an increase that is offset by reductions in the DB2 address spaces. For data sharing, the DB2 address spaces often are reduced by as much as 50%. When you get to new function and take advantage of the improvements, more help arrives. [...] 14080 47 35_Re: [zos] unload lob >32k very slow12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 27 Mar 2008 01:15:57 +0000581_utf-8 With small LOB sizes, it's likely that most of the time (11 hours) is being spent opening and closing those small data sets. File reference variables are most useful with LOBs that are many megabytes, too big for memory sizes. You could look at the performance for those data set opens and closes, check on caching, but half a million can give you scaling problems. Breaking that up might give you some relief. Rather than one file or half a million, maybe 50 is a better choice, but that would be application programming, defining the LOB boundaries. Records and files [...] 14128 142 58_Re: Where can I find "end user information" in EPDM tables9_Tony Saul30_generalemaillists@YAHOO.COM.AU31_Thu, 27 Mar 2008 12:20:54 +1100593_iso-8859-1 Hi Juan,

The TDS (EPDM) forum is through www.yahoogroups.com and is PRMVSUG.

E-mail me direct and I can assist with some further information.



--- "Calleja Vilar, Juan" wrote:

> > Hello everybody. I'm a DB2 z/OS DBA and I'm trying > to get as much > information as possible from our Websphere > applications that use DB2 > Connect. We are planning to use some APIs that > provide end user > information: > > * End user ID. > * End user transaction name. > * End user workstation name. > > > We have already made some [...] 14271 110 18_Re: No read engine12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 27 Mar 2008 02:20:15 +0000583_utf-8 The change was for deferred write engines, rather than for read or other engines and is a default that can be tuned as needed. The specifics of the workloads, IO configurations and DB2 parameters have so much variation that a benchmark did not seem very useful. Virtual storage is still a common issue. Running out of write engines or read engines is generally related to the IO load and configuration more than to the number of engines. Needing that many read engines means quite a few agents or lots of parallelism combined with disk subsystems that are slower than the [...] 14382 19 43_Bud Greenman/IT/ONGOV is out of the office.0_21_BudGreenman@ONGOV.NET31_Wed, 26 Mar 2008 22:00:16 -0400759_US-ASCII I will be out of the office starting 03/25/2008 and will not return until 03/31/2008.

I will respond to your message when I return.

______________________________________________________________________

* IDUG 08 Bangalore, India * 21-23 August 2008 * http://IDUG.ORG/lsIN * ______________________________________________________________________

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for [...] 14402 323 59_Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM12_Roger Miller19_millerrl@US.IBM.COM31_Thu, 27 Mar 2008 02:39:04 +0000543_utf-8 The first part of the statement is exactly the point. > In previous versions of DB2, if a stored procedure was called twice from the > same program and at the same nesting level, DB2 closed the result set > cursors and released storage for the first instance of the stored procedure > before making the second call. In DB2 Version 8, if the requester and the > server are both DB2 Version 8 subsystems in new-function mode, when the > second call is made, both instances of the stored procedure can run at the > same time. DB2 does [...] 14726 1050 24_Re: Rotate First To Last14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Thu, 27 Mar 2008 15:21:46 +1100599_us-ascii Folks...





All your replies are greatly appreciate, its given me enough info to plan my approach.









Regards

John





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hardy, Dale Sent: Wednesday, 26 March 2008 7:19 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Rotate First To Last





John,





Agree with all the previous comments.





Our process is similar to what you outlined. [...] 15777 167 47_Question re. Dclgen host vars & Cobol host vars14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Thu, 27 Mar 2008 17:31:15 +1100398_us-ascii Hi DB2 Colleagues,

I always thought that a host variable whether its from the Dclgen copybook or a Cobol WS field, gets the same treatment when used in a query !

Is there a difference in the way the optimizer treats an equal predicate host var. that comes from the Dclgen, as opposed to a host var. that is not from the Dclgen but its a WS field defined in the program. [...] 15945 127 35_Re: [zos] unload lob >32k very slow1_i22_ivan.losadajuan@BDE.ES31_Thu, 27 Mar 2008 08:57:02 +0100352_us-ascii Hi,

Roger, Randy, opening and close half a million times..., havent think about this..., maybe the time reported in UNITS SWITCH EVENTS (OTHER) is the time spent in open/close? Do you think 80 ms per event is too much? Thanks for the clue.

Michael, thanks for the information, we're going to test the performance using HFS. [...] 16073 286 41_DB2 for z/OS - ***URGENT*** SPT01 problem12_Jim McAlpine29_Jim.McAlpine@COASOLUTIONS.COM31_Thu, 27 Mar 2008 10:13:40 -0000543_us-ascii Getting the following -

STC06210 DSNP008I + DSNPXTN0 - REQUIRED USER-DEFINED DATA SET 740 NOT AVAILABLE- DB2B.DSNDBC.DSNDB01.SPT01.I0001.A002. RC=00D70002 CONNECTION-ID=BATCH, CORRELATION-ID=TSO141A, LUW-ID=* STC06210 DSNP007I + DSNPXTN0 - EXTEND FAILED FOR 741 DB2B.DSNDBD.DSNDB01.SPT01.I0001.A002. RC=00D70002 CONNECTION-ID=BATCH, CORRELATION-ID=TSO141A, LUW-ID=* STC06210 DSNT501I + DSNISGPI RESOURCE UNAVAILABLE 742 CORRELATION-ID=TSO141A CONNECTION-ID=BATCH LUW-ID=* REASON 00D70002 TYPE 00000200 NAME DSNDB01 .SPT01 [...] 16360 330 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem12_Jim McAlpine29_Jim.McAlpine@COASOLUTIONS.COM31_Thu, 27 Mar 2008 10:25:50 -0000751_us-ascii I forgot to add, I would rather continue using the SPT01......A001 dataset which is now back down to 2 extents,. If that is possible.

Jim McAlpine

> _____________________________________________ > From: Jim McAlpine > Sent: 27 March 2008 10:14 > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: DB2 for z/OS - ***URGENT*** SPT01 problem > > Getting the following - > > STC06210 DSNP008I + DSNPXTN0 - REQUIRED USER-DEFINED DATA SET 740 > NOT AVAILABLE- > DB2B.DSNDBC.DSNDB01.SPT01.I0001.A002. > RC=00D70002 > CONNECTION-ID=BATCH, CORRELATION-ID=TSO141A, > LUW-ID=* > STC06210 DSNP007I + DSNPXTN0 - EXTEND FAILED FOR 741 > DB2B.DSNDBD.DSNDB01.SPT01.I0001.A002. > RC=00D70002 > CONNECTION-ID=BATCH, CORRELATION-ID=TSO141A, > LUW-ID=* > [...] 16691 402 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem13_Michael Ebert18_mebert@AMADEUS.COM31_Thu, 27 Mar 2008 11:39:08 +0100419_ISO-8859-1 Check the HI-U-RBA (used space) in the A001 file. It may be that you've hit the 2GB limit. Then you have to add another dataset, until you clean out superfluous packages and run a REORG.

Dr. Michael Ebert DB2 & Oracle Database Administrator aMaDEUS Data Processing Erding / Munich, Germany







Jim McAlpine To DB2-L@www.idugdb2-l.org cc [...] 17094 760 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 Mar 2008 10:41:40 -0000466_us-ascii Hey Jim,

Yes, it is just a matter of creating an A002 dataset. The clue is, 'user-defined dataset not available'. The Cat/Dir are user-defined, so its up to you to allocate them.

As to getting it all just into A001, how big's SPT01? If you're back to 2 extents you don't need A002, unless you've hit the dataset limit (2GB? 4GB?). So, although you're down to 2 extents you might not have a choice but to allocate and use the 2nd dataset. [...] 17855 701 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 27 Mar 2008 06:47:01 -0400364_us-ascii Hi Jim,





It would be nice to keep SPT01 small and not having to allocate A002 or even A003, A004, ....

One of our DB2 subsystems are now on A009 - and the only way to get it smaller is to FREE packages. Especially if you are using package versioning and not cleaning up older versions, you will see SPT01 grow over time. [...] 18557 823 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem12_Jim McAlpine29_Jim.McAlpine@COASOLUTIONS.COM31_Thu, 27 Mar 2008 11:23:18 -0000527_us-ascii Raymond, thanks for the reply. I had already allocated the .A002 dataset earlier and everything is now fine. I've checked the .A001 allocation and it looks like it's at 2GB so it seems the new allocation is required.

Thanks again

Jim McAlpine

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond Sent: 27 March 2008 10:42 To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 for z/OS - ***URGENT*** SPT01 problem [...] 19381 604 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem12_Jim McAlpine29_Jim.McAlpine@COASOLUTIONS.COM31_Thu, 27 Mar 2008 11:24:31 -0000587_iso-8859-1 Yes, I'm at the 2GB limit. Looks like it's time for some housekeeping.

Thanks

Jim McAlpine

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Michael Ebert Sent: 27 March 2008 10:39 To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 for z/OS - ***URGENT*** SPT01 problem





Check the HI-U-RBA (used space) in the A001 file. It may be that you've hit the 2GB limit. Then you have to add another dataset, until you clean out superfluous packages and run a REORG. [...] 19986 396 45_Re: DB2 for z/OS - ***URGENT*** SPT01 problem13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 Mar 2008 12:02:31 -0000285_us-ascii Hey, no probs. The Good Doktor reminded me it's 2GB so now you know why you need the 2nd cluster. But as Steen (I think it was) suggested, it might be time to free a few packages and unload/delete-define/reload. Or just live with the 2nd cluster. Guess which I'd go for? [...] 20383 146 51_Re: Question re. Dclgen host vars & Cobol host vars12_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Thu, 27 Mar 2008 09:16:34 -0500 20530 27 20_Book Recommendations10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Thu, 27 Mar 2008 15:03:22 +0000357_utf-8 I am looking for some book recommendations. First, for myself. I have always been fond of the Yevich/Lawson book DB2 for High performance, but it is getting out of date. I really liked the way it summarized the technical details and it was an excellent blend of overview and techncial detail. Is there anything comparable out there for DB2 V8/9? [...] 20558 28 59_Re: DB2 for z/OS - ***URGENT*** SPT01 problem - now [FLUFF]0_16_R.Boxwell@SEG.DE31_Thu, 27 Mar 2008 16:14:07 +0100364_US-ASCII I know what Ray would do -

Reallocate all DSNDB01 datasets to a VTS Reorg with CHANGE all clusters after doing IDCAMS ALTERs on random clusters REBIND all packages (twice, one with EXPLAIN and one without) Run to the nearest bar and drink BEER - *lots* of BEER while waiting for IBM to figure out what on earth happened to this DB2 sub-system [...] 20587 131 12_Re: 00D3003C14_Krnic, Ivan B.23_Ivan.Krnic@ALLSTATE.COM31_Thu, 27 Mar 2008 10:50:32 -0500567_us-ascii Has anyone seen REASON CODE 00D3003C





Ivan





------------------------------------------------------------------------ --------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm [...] 20719 64 59_Re: DB2 for z/OS - ***URGENT*** SPT01 problem - now [FLUFF]13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 Mar 2008 16:02:07 -0000552_us-ascii Cheeky bugger. You only get half points, for 'run to the nearest bar'. The other steps are optional...

Hope your hols are good and you get all the snow/sun* you're after. Hopefully see you at this year's GSE?

Cheers,



Raymond * delete as appropriate

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of R.Boxwell@SEG.DE Sent: 27 March 2008 15:14 To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 for z/OS - ***URGENT*** SPT01 problem - now [FLUFF] [...] 20784 56 28_Converting utilities to BMC.0_23_bob.kota@EXELONCORP.COM31_Thu, 27 Mar 2008 12:13:21 -0500440_iso-8859-1 We are currently in the process of converting all of our DB utilities to BMC. We have several REORG jobs that delete and roll data to tape based on a basic SQL like statement. The following for example:

REORG TABLESPACE x.ts DISCARDDN DELETES DISCARD FROM TABLE x.tb WHERE (DATE(colname1) < (CURRENT DATE - 1 MONTHS) AND colname2 IN ('literal1', 'literal2')) COPY TABLESPACE x.ts COPYDDN(COPY0001) SHRLEVEL REFERENCE [...] 20841 92 32_Re: Converting utilities to BMC.14_Fazio, Richard21_RFAZIO@TRANSUNION.COM31_Thu, 27 Mar 2008 13:10:12 -0500605_us-ascii First...did you ask the opinion of BMC Tech Support? I've known many of the tech support folks and they are both helpful and creative.

As for an approach, perhaps you generate control cards from say...Rexx?!?!

Convert the "function" inside the Rexx code and generate "WHERE colname1 < 'calculated.timestamp.value' "

faz

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of bob.kota@EXELONCORP.COM Sent: Thursday, March 27, 2008 12:13 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Converting utilities to BMC. [...] 20934 34 44_Getting Ready to Convert from DB2V7 to DB2V89_Ken Bateh22_ken.bateh@MILLIKEN.COM31_Thu, 27 Mar 2008 18:18:48 +0000440_utf-8 We need to start looking at what it will take to move legacy from DB2 V7 to DB2 V8.

Some initial questions we have:

What PTFs and/or migration level do we need to be on?

We heard it will take more storage and more mips to run it. How much more?

If we don't get converted by June 2008, what does 'unsupported' mean? i.e. We can't get any help or fixes, or it just costs more, and if so, how much more? [...] 20969 61 56_Is SQL Procedure Debugger still available for DB2 z/OS ?0_19_askwe@OPTONLINE.NET31_Thu, 27 Mar 2008 18:28:35 +0000561_us-ascii I was able to invoke the SQL Procedure Debugger to execute a DB2 z/OS SQL Procedure in debug mode from DB2 Development Center. When I use the DB2 Developer Workbench and DB2 Data Studio the enable debugging option is only given when I deploy an SQL Procedure in my Windows system, and I can then run the SQL Procedure in debug mode. The enable debugging option is not given when I deploy a z/OS SQL Procedure and the debug option is not available for z/OS SQL Procedure execution under DB2 Developer Workbench or DB2 Data Studio. Is this correct? [...] 21031 89 32_Re: Converting utilities to BMC.15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Thu, 27 Mar 2008 13:37:14 -0500463_us-ascii As is often said here, please contact the BMC Technical Support people at 1-800-537-1813. They should be able to help you. They are available 24X7.

Chris Blaicher Developer BMC Software, Inc.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of bob.kota@EXELONCORP.COM Sent: Thursday, March 27, 2008 12:13 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Converting utilities to BMC. [...] 21121 86 32_Re: Converting utilities to BMC.14_Krnic, Ivan B.23_Ivan.Krnic@ALLSTATE.COM31_Thu, 27 Mar 2008 13:51:18 -0500541_us-ascii We never had a problem with BMC discard reorg.

Ivan



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of bob.kota@EXELONCORP.COM Sent: Thursday, March 27, 2008 12:13 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Converting utilities to BMC.

We are currently in the process of converting all of our DB utilities to BMC. We have several REORG jobs that delete and roll data to tape based on a basic SQL like statement. The following for example: [...] 21208 74 48_Re: Getting Ready to Convert from DB2V7 to DB2V814_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV31_Thu, 27 Mar 2008 14:19:52 -0500331_us-ascii We are currently running v7 on a z9. We just transitioned to this machine a few months back. We have had no problems with V7 from this transition.

I may be wrong on this, my take on out of support is, they no longer will make any new PTFs for V7. You can still receive and apply existing PTF's to your system. [...] 21283 66 12_Re: 00D3003C12_Troy Coleman25_troy.coleman@SOFTBASE.COM31_Thu, 27 Mar 2008 15:32:45 -0500689_ISO-8859-1 I could not find this code in the latest DB2 V8 or DB2 9 codes manual. I would go to IBM on this one.

Troy Coleman, Support Engineer IBM Certified Solutions Expert

SoftBase Systems, Inc. 847-776-0618 828-670-9900 ext. 334 troy.coleman@softbase.com

Compliance Challenged with Test Data Privacy? White Papers and More at http://www.softbase.com/

The information contained in this message may be CONFIDENTIAL and is for the intended addressee only. Any unauthorized use, dissemination of the information, or copying of this message is prohibited. If you are not the intended addressee, please notify the sender immediately and delete this message. [...] 21350 294 48_Re: Getting Ready to Convert from DB2V7 to DB2V80_26_MarkVickers@GROCERYBIZ.COM31_Thu, 27 Mar 2008 15:39:21 -0500444_US-ASCII Ken, This page is a great starting point: DB2 for z/OS Information Roadmap and if the link does not work it is: http://www-306.ibm.com/software/data/db2/zos/roadmap.html#planning

The best document I found for planning was kindly compiled by Willie Favero and I cannot find the link, so if anyone can help here, please do.

I have that document and a bunch more, so if you email me off-line, I will attach it for you. [...] 21645 266 51_Stored Procedure SQLCODE Handling - DB2 V7 for z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM31_Thu, 27 Mar 2008 15:58:11 -0500451_us-ascii Ok, I feel like I should know the answer to this question, but I'm unable to confirm it...





If a triggered stored procedure receives a negative SQLCODE such as a -805, -803, -811, etc.; will that SQLCODE automatically be returned to the calling program via SQLCA as the return code from the triggering action, or does the stored procedure have to explicitly set an output parm for the calling program to interpret? [...] 21912 230 51_Re: Question re. Dclgen host vars & Cobol host vars10_Dave Churn19_davechurn@GMAIL.COM31_Thu, 27 Mar 2008 16:12:09 -0500336_ISO-8859-1 John,

Are you using the precompiler or co-processor? There is an issue with the v8 co-processor that may affect you in the future.

I talked with a colleague and his opinion is memory is memory. I agree with Troy that you want to have your developers use the DCLGEN fields so they get the right field definition. 22143 250 59_Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 27 Mar 2008 14:37:30 -0700370_iso-8859-1 No question that the programmers should have been closing the result set cursor before the next call. No commit was needed nor should be taken that often, think of committing after every single read, over 500,000 of them, that seems excessive and unnecessary. Most programmers never commit on READ-only type processing and I can't say that I blame them. [...] 22394 94 35_Re: [zos] unload lob >32k very slow12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 27 Mar 2008 14:47:48 -0700494_iso-8859-1 Why in the world does it have to open and close the PDS for every single record in the LOB?

Understand that with LOBS that have a size > 32k file reference variables are the only option, regardless of the overall number of lob rows.

So it's not like those of us that use LOBS have a whole lot of choice here. It's either use a PDS or use a HFS system, both of which have the file reference variables. And this is true whether we have 100 rows or 100 million rows. [...] 22489 98 55_Re: Stored Procedure SQLCODE Handling - DB2 V7 for z/OS12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 27 Mar 2008 14:50:47 -0700311_iso-8859-1 Jay,

The catch is where is the SQLCODE being generated. If it is by the "call" to the SP, then it will be returned directly to the program. If it is in the SP, then it has to be captured by the SP and returned via output parms to the caller. And this is regardless of who invokes the SP> [...] 22588 25 59_Re: Revert to DB2 z/OS V7 after Upgrade to DB2 z/OS V8 ENFM28_=?utf-8?B?VGVkIE1hY05FSUw=?=18_eamacneil@YAHOO.CA31_Fri, 28 Mar 2008 00:02:28 +0000444_- >This behaviour is a lot similar to the old days where in COBOL if you left a file open when you terminated the program you received a S0C4. After a while IBM changed the compiler such that it automatically closed the files upon close.

Not always. Now, you can get an SC03 if you leave a file open in a DB2/COBOL Batch environment. Especially, if it's SYSOUT. But, it's because you called an assembler programme in a non-LE mode. [...] 22614 81 12_Re: 00D3003C9_Tony Saul30_generalemaillists@YAHOO.COM.AU31_Fri, 28 Mar 2008 11:10:02 +1100354_iso-8859-1 Ivan,

I can see - PMR 02623,79L,000 mentions this code and it refers to APARs PK44257 and PK53806,

PMR 35820,756,000 mentions PK61561 PMR 35508,6XM,760 mentions PK61054 and PK59077

So a call to IBM to at least subscribe to the APARs or maybe provide another dump that might shed some additional light on the problem. [...] 22696 102 24_Re: Book Recommendations16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 27 Mar 2008 20:15:38 -0400649_ISO-8859-1 With respect to the overview, my recommendation is the manual titled "Introduction to DB2 for z/OS." You can access it on the Web at this url:

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

Robert



On Thu, Mar 27, 2008 at 11:03 AM, Jorg Lueke wrote:

> I am looking for some book recommendations. First, for myself. I have > always > been fond of the Yevich/Lawson book DB2 for High performance, but it is > getting out of date. I really liked the way it summarized the technical > details > and it was an excellent blend of overview and techncial detail. Is there > [...] 22799 144 55_Re: Stored Procedure SQLCODE Handling - DB2 V7 for z/OS0_19_askwe@OPTONLINE.NET31_Fri, 28 Mar 2008 00:28:32 +0000699_us-ascii Jay,Are you talking about a stored procedure invoked using'EXEC SQL CALL' by the calling program ora stored procedure which is triggered by INSERT/UPDATE/DELETE in an application program?Regards,Agus KweeThemis Traininghttp://www.themisinc.com----- Original Message -----From: "Reavill, Jay" Date: Thursday, March 27, 2008 7:59 pmSubject: [DB2-L] Stored Procedure SQLCODE Handling - DB2 V7 for z/OSTo: DB2-L@www.idugdb2-l.org> Ok, I feel like I should know the answer to this question, but I'm> unable to confirm it...> > > > If a triggered stored procedure receives a negative SQLCODE such > as a> -805, -803, -811, etc.; will that SQLCODE automatically be > returned to> the calling [...] 22944 191 55_Re: Stored Procedure SQLCODE Handling - DB2 V7 for z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM31_Thu, 27 Mar 2008 22:25:54 -0500629_utf-8 Thanks Myron! That's what I suspected and thanks for the clarification.



----- Original Message ----- From: DB2 Data Base Discussion List To: DB2-L@www.idugdb2-l.org Sent: Thu Mar 27 16:50:47 2008 Subject: Re: [DB2-L] Stored Procedure SQLCODE Handling - DB2 V7 for z/OS

Jay,

The catch is where is the SQLCODE being generated. If it is by the "call" to the SP, then it will be returned directly to the program. If it is in the SP, then it has to be captured by the SP and returned via output parms to the caller. And this is regardless of who [...] 23136 141 48_Re: Getting Ready to Convert from DB2V7 to DB2V812_Roger Miller19_millerrl@US.IBM.COM31_Fri, 28 Mar 2008 05:24:42 +0000794_utf-8 Willie's paper is here, with the ftp server reorganized a bit, sorry. ftp://ftp.software.ibm.com/software/data/db2zos/whitepapers/DB2zOS_v8_upg rade082407.pdf

The John Campbell presentation is good, and he had two good webcasts on migration and virtual storage, with audio and the slides. ftp://ftp.software.ibm.com/software/data/db2zos/presentations/2006/misc/v8e arlyexperience.pdf

Here is a customer experience story ftp://ftp.software.ibm.com/software/data/db2zos/presentations/2006/idug- na/c11-v8migration.pdf

Performance tuning from Akira Shibamiya: ftp://ftp.software.ibm.com/software/data/db2zos/presentations/2007/share/au g/v7tov8perf2007aug.pdf ftp://ftp.software.ibm.com/software/data/db2zos/presentations/2007/idug/na/ v8newfunctionperformance.pdf [...] 23278 824 51_Re: Question re. Dclgen host vars & Cobol host vars14_Alexander John28_JAlexander@WOOLWORTHS.COM.AU31_Fri, 28 Mar 2008 16:52:30 +1100369_us-ascii Our normal practice is to use the Dclgen fields as host vars, in this case they did not, & the only way we came across it was because of a performance issue! , also we checked the Cobol WS definitions for these columns, they match the Dclgen.





Btw..the program has been changed to use the Dclgen fields & its now using matchcols=4 . [...] 24103 1065 51_Re: Question re. Dclgen host vars & Cobol host vars12_Isaac Yassin20_yassini@BEZEQINT.NET31_Fri, 28 Mar 2008 09:58:07 +0300557_us-ascii Hi,





Visually, the only difference is

AND LOCATION_NO = :WS-VARIABLES.WS-STORE-NO

Compared to

AND LOCATION_NO = :DCLDSD-GV-WET.LOCATION-NO





Can you please verify the attributes of ws-store-no vs. location-no





Isaac Yassin

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Alexander John Sent: Friday, March 28, 2008 8:53 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Question re. Dclgen host vars & Cobol host vars [...] 25169 124 24_Re: Book Recommendations13_Maja Markotic36_Maja.Markotic@UNICREDITGROUP.ZABA.HR31_Fri, 28 Mar 2008 09:02:08 +0100495_UTF-8 "CA Performance Handbook for DB2 for zOS" by Dan Luksetich & Susan Lawson of YL&A.

You can download it from www.ca.com/db

Maja









Robert Catterall To Sent by: DB2 Data DB2-L@www.idugdb2-l.org Base Discussion cc List Re: [DB2-L] Book Recommendations



28.03.2008 01:15



Please respond to DB2 Database Discussion list at IDUG [...] 25294 56 24_Re: Book Recommendations21_Cuneyt Goksu (Vizyon)23_cuneyt.goksu@VIZYON.BIZ31_Fri, 28 Mar 2008 11:02:11 +0200669_UTF-8 Excellent book for Management ;-)

The Business Value of DB2 UDB for z/OS http://www.redbooks.ibm.com/abstracts/SG246763.html?Open

Regards, Cuneyt

> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On > Behalf Of Jorg Lueke > Sent: Thursday, March 27, 2008 5:03 PM > To: DB2-L@www.idugdb2-l.org > Subject: [DB2-L] Book Recommendations > > > I am looking for some book recommendations. First, for myself. > I have always > been fond of the Yevich/Lawson book DB2 for High performance, but it is > getting out of date. I really liked the way it summarized the > technical details > and it was an [...] 25351 18 38_Marianne X Smith is out of the office.14_Marianne Smith23_Marianne.X.Smith@KP.ORG31_Fri, 28 Mar 2008 04:02:45 -0700743_us-ascii I will be out of the office starting 03/28/2008 and will not return until 03/31/2008.

I will respond to your message when I return.

_____________________________________________________________________

* IDUG 08 Dallas, TX, USA * May 18-22, 2008 * http://IDUG.ORG/lsNA * _____________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up [...]