1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2009, week 3 2 942 57_Re: Approximate "break-even" when using Rowset processing11_Suresh Sane21_data_arch@HOTMAIL.COM31_Mon, 14 Dec 2009 18:53:27 -0600469_Windows-1252

Just for the record, one more fact which could be important.





You get the generic "you had errors" as the first error in GET DIAGNOSTICS also.





If you had multiple errors, they seem to appear in reverse order. e.g. when inserting 5 rows, row no. 2 and row no. 4 had errors. GET DIAGNOSTICS shows three errors (not two!) - first is the generic, 2nd is error for row #4 and third is error for row #2. [...] 945 32 45_Migrating packages from a V9 to a V8 database14_Frances Warren15_fwarren@CSC.COM31_Tue, 15 Dec 2009 01:30:11 +0000412_utf-8 We are trying to plan an upgrade from V8 to V9 with lots of dependencies including application upgrades. One of our tentative plans involves writing and testing code under V9, but deploying it in production which would still be version 8. We could test it in another V8 system before deployment, but is migration of a package from a V9 database to a V8 database possible? Any comments, tips or ideas? [...] 978 339 57_Re: Approximate "break-even" when using Rowset processing13_David Simpson22_dsimpson@THEMISINC.COM31_Mon, 14 Dec 2009 19:49:41 -0600435_iso-8859-1 I have always wondered why the errors come out in reverse.. Guess it's a last-in-first-out queue.

-----Original Message----- From: Suresh Sane Sent: Monday, December 14, 2009 7:46 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing



Just for the record, one more fact which could be important. [...] 1318 46 69_Is there anything Written on the use and/or misuse of Collectin-ids ?11_Daniel Lapp21_lapp.daniel@GMAIL.COM31_Tue, 15 Dec 2009 02:31:19 +0000570_utf-8 Is there anything Written on the use and/or misuse of Collectin-ids ? I can understand using collection ids to represent a collection of packages in a line of business but what if one was to over use collection ids to represent a much finer granularity ? For instance, the same package bound a hundred different time each with a different collection id, and each collection id utilizing different load and DBRM libs ? This is the sitaution we appear to have and I believe it contributes to the large size of our EDMPOOL, the large size of bufferpool BP0, the [...] 1365 39 32_DB2 for z/OS books updated again12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 15 Dec 2009 02:48:47 +0000370_utf-8 The Information Management Software for z/OS Solutions Information Center was updated for the last time in 2009. This refresh was a small one for the DB2 for z/OS library but many of the IMS and DB2 Tools products had significant updates.

Softcopy formats of the following DB2 for z/OS information was updated: Utility Guide and Reference for V8 and 9 [...] 1405 65 49_Re: Migrating packages from a V9 to a V8 database12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 15 Dec 2009 02:34:45 +0000565_utf-8 The tricky part would be to make sure that the application which you code and test on DB2 9 does not contain anything that is not part of V8. For the application code itself, there are some reasonable options where you are using a precompiler or coprocessor. Only allow the precompiler for V8 or set the NEWFUN value to V8 (new APAR, value NO currently). Where the SQL is dynamic, the challenge is greater. The migration package might include some new DDL that works fine on 9, provides an error on V8, for example. You'd need to be particularly careful [...] 1471 39 41_Sajad Javid is out of the office (Travel)11_Sajad Javid19_sajad.javid@TCS.COM31_Tue, 15 Dec 2009 10:01:43 +0530660_us-ascii I will be out of the office starting 12/15/2009 and will not return until 12/16/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 [...] 1511 663 25_Re: DB2 z/os V9 workfiles13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK31_Tue, 15 Dec 2009 09:40:33 -0000570_iso-8859-1 With regard to Roys “with extents allowed” comment, this sort of depends on what you are using your temp table spaces for





Normal practice is NOT to have secondary allocations for SORT work files, but to HAVE secondary allocations for TEMP work files (such as DGTTs and static scrollable cursors)





Of course, now that both of these activities are contained within the sort work database (as opposed to having a separate TEMP database a la DB2 V8), you can’t both HAVE and NOT HAVE secondaries at the same time [...] 2175 169 73_Re: Is there anything Written on the use and/or misuse of Collectin-ids ?16_Robert Catterall21_rfcatterall@GMAIL.COM31_Tue, 15 Dec 2009 09:50:43 -0500590_ISO-8859-1 Regarding stuff written on package management, you might want to check out (if you haven't already) the IBM red book titled "DB2 9 for z/OS: Packages Revisited." You can download a copy in PDF format at this url:

http://www.redbooks.ibm.com/abstracts/sg247688.html?Open

Yeah, binding a given package into a whole lot of collections versus very few collections would tend to increase the amount of virtual storage you'd need for your EDM pool, particularly the part that holds the skeleton package table. And, it would mean a larger SPT01 directory tablespace. [...] 2345 576 57_Re: Approximate "break-even" when using Rowset processing11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 15 Dec 2009 09:41:35 -0600728_Windows-1252

Don't know why. Last-in-first-out queue = stack.





Thx

Suresh

> Date: Mon, 14 Dec 2009 19:49:41 -0600 > From: dsimpson@THEMISINC.COM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing > > I have always wondered why the errors come out in reverse.. Guess it's a last-in-first-out queue. > > -----Original Message----- > From: Suresh Sane > Sent: Monday, December 14, 2009 7:46 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing > > > Just for the record, one more fact which could be important. > > > > [...] 2922 692 26_Re: RUNSTATS' Alternative?7_DB2 DBA21_the.db2.dba@GMAIL.COM31_Tue, 15 Dec 2009 11:48:41 -0500354_UTF-8 Hello:

DB2 V7 z/OS 1.4

We have decided to install/deploy RTS in our shop. However, I realized that the sample DDL in DSN710.SDSNSAMP(DSNTESS) has storage-group as SYSDEFLT. I look at RTS as another catalog table and I think STOGROUP should be SYSDEFLT and BUFFERPOOL should also be the default one used for other catalog tables. [...] 3615 1529 26_Re: RUNSTATS' Alternative?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 15 Dec 2009 12:17:36 -0500419_iso-8859-2 Josh: If you ever close an application bufferpool, you lose the ability to read all objects associated with that bufferpool. For that reason, I'd recommend putting the RTS spaces in the same BP you use for (other) system objects. For STOGROUPs, if you're recovering the system datasets, you'd want to recover the RTS objects to the same point. So, you're right; your system programmer is wrong on this. [...] 5145 38 16_Re: DB2 V8 & OSC11_Bob Markway21_bob.markway@OA.MO.GOV31_Tue, 15 Dec 2009 18:16:43 +0000272_utf-8 An FYI and a question. As I was reseaching the install on the z/OS side for OSC (I can't seem to find one spot for all the information) I came across a link stating that the OSC is being depreciated. It will no longer be available in V-next. Below is the link. [...] 5184 72 16_Re: DB2 V8 & OSC10_Todd Burch17_toddburch@MAC.COM31_Tue, 15 Dec 2009 12:35:21 -0600351_US-ASCII Hi Bob.

Yes, that is correct.

Remember last week, when I said OSC, "as of today", was the "wave of the future"? Well, that wave has now gone back out to sea and the new wave is Data Studio (et al).

I can't speak for pricing - I don't know.

Todd



On Dec 15, 2009, at 12:16 PM, Bob Markway wrote: [...] 5257 120 16_Re: DB2 V8 & OSC13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Tue, 15 Dec 2009 13:45:11 -0500648_utf-8 Funny, moments ago on the other db2l ('db2-l@Groups.ITtoolbox.com') someone asked the same question about Control Center. See my answer below based on what I learned from Curt Cotner at IOD and from Paul Wirth at Mich DB2 User Group. I believe the answer is the same for OSC.

Data Studio or an Optim component will replace the control center functions. At IOD North America in October, the statement was made that the no-charge offerings will continue and will be under the Data Studio umbrella. The for-charge versions will be under the Optim name. See http://www.mdug.org/currentmeeting.html and download Paul Wirth's afternoon [...] 5378 81 59_Using MS Access to read DB2 z/Os generates too many threads15_Tami Van Dreese27_tami.vandreese@LANDSEND.COM31_Tue, 15 Dec 2009 20:15:14 +0000339_utf-8 We have an MS Access application that sends an excessive number of sql threads when it reads from our DB2 tables. To get an understanding of what is going on, I set up a simple query in MS Access that joins 2 tables SELECT cat.CATEGORY_NUM, gmm.GMM_NUM FROM CATEGORY_TBL cat INNER JOIN GMM_TBL gmm ON cat.GMM_NUM = gmm.GMM_NUM; [...] 5460 83 56_Strange error message from DB2 Express C version 9.5.0.016_Harold Pritchett14_harold@UGA.EDU31_Tue, 15 Dec 2009 15:46:30 -0500289_ISO-8859-1 I have two servers running DB2 Express C version 9.5.0.0 on Red Hat Enterprise Linux 5.4.

Each morning a cron job on server 1 does a offline backup of the database ANIMALS. This backup is then sent to server 2 where it is restored. The SQL to do this is as follows: [...] 5544 1060 26_Re: RUNSTATS' Alternative?7_DB2 DBA21_the.db2.dba@GMAIL.COM31_Tue, 15 Dec 2009 16:19:06 -0500408_ISO-8859-2 Phil,

Thanks for confirming my take on it, with an apt example. One more question, do you think there could be any ill-effect on the overall performance of DB2 due to the installation of RTS? As, this also works like RUNSTATS, as far as collecting statistics goes.

Also, how does RTS collect statistics of the tables that are loaded using BMC utilities that run outside of DB2? [...] 6605 1937 26_Re: RUNSTATS' Alternative?13_Bright, Randy20_Randy_Bright@BMC.COM31_Tue, 15 Dec 2009 15:41:30 -0600449_iso-8859-2 I'll answer the second half of your question first. The BMC Utilities update the RTS tables in much the same way as the IBM Utilities. There are some subtle differences because the BMC Utilities have some capabilities that differ from IBM so sometimes there isn't a "one-to-one" match between what the IBM utilities and the BMC Utilities do. But the end result should be the same. The RTS tables are maintained by the BMC Utilities. [...] 8543 1368 26_Re: RUNSTATS' Alternative?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 15 Dec 2009 17:10:46 -0500514_iso-8859-2 Josh,

I defer to Randy on the second question, of course. And I agree with him on the first question.

The last place I worked at which had RTS turned on, found that three hours was a fine interval. I'd go thirty minutes, myself. Not much less.

--Phil

________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bright, Randy Sent: Tuesday, December 15, 2009 4:42 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] RUNSTATS' Alternative? [...] 9912 1298 26_Re: RUNSTATS' Alternative?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 15 Dec 2009 16:09:08 -0800570_iso-8859-2 V7, eh?

Phil and Josh, I agree with you that the RTS tables may as well utilize BP0, because in DB2 9 they will be moved to the catalog (DSNDB06). What I am unclear on is the concern about sharing bufferpools and stogroups with applications -- how do you "close" a bufferpool and why would you do it? For me, the criteria for which bufferpool to use would be the type and level of activity on the tablespaces being compatible with other users of the bufferpool; the stogroup only matters in terms of DASD management. That said, when I implemented [...] 11211 107 60_Re: Strange error message from DB2 Express C version 9.5.0.016_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Wed, 16 Dec 2009 09:52:45 +0800485_us-ascii Harold,

Not using DB2 Express C here but 9.5 ESE.

Having done far too many restores recently in preparation for a project, try DROP DATABASE ANIMALS and then try the restore again.

I've found that DB2 can get confused occasionally on repeated restores of the same database and/or something in the db directory gets corrupted, making jobs that have worked fine umpteen times go haywire for no obvious reason. DROP and RESTORE has worked for me. YMMV. [...] 11319 303 188_Upcoming IDUG Webcast - "The DB2Night Show Episode #9 - "Crunching the Numbers - Analyzing DB2 LUW Snapshot Data", Scott Hayes, DBI Software" - Friday, 18 December 2009 at 10:00 am USA CST13_David Chapman24_David.Chapman@IAG.COM.AU31_Wed, 16 Dec 2009 15:02:59 +1100527_us-ascii

_______________________________________________________________________________________

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



Hello DB2 List,

Here is an upcoming webcast that you may be interested in.

The DB2Night Show Episode #9 - 18 Dec 2009 - "Crunching the Numbers - Analyzing DB2 LUW Snapshot Data", Scott Hayes, DBI Software [...] 11623 55 60_Re: Strange error message from DB2 Express C version 9.5.0.016_Harold Pritchett14_harold@UGA.EDU31_Tue, 15 Dec 2009 23:09:18 -0500566_ISO-8859-1 Gregory Palgrave wrote: > Harold, > > Not using DB2 Express C here but 9.5 ESE. > > Having done far too many restores recently in preparation for a project, try DROP DATABASE ANIMALS and then try the restore again. > > I've found that DB2 can get confused occasionally on repeated restores of the same database and/or something in the db directory gets corrupted, making jobs that have worked fine umpteen times go haywire for no obvious reason. DROP and RESTORE has worked for me. YMMV. > > Also - are there any other messages in the db2diag.log to [...] 11679 272 60_Re: Strange error message from DB2 Express C version 9.5.0.015_Leon Katsnelson15_leon@CA.IBM.COM31_Tue, 15 Dec 2009 23:30:47 -0500559_US-ASCII

Harold, I am glad you fixed the problem. In your earlier posting you indicated that you did not want to upgrade to 9.7. I know many people don't want to go to a new release until there was a FixPack. With DB2 Express-C we do not have FixPacks but we do update the product periodically whenever we feel such an update is warranted and would be beneficial to the community and would increase adoption of the new release. So, we shipped DB2 Express-C v9.7.1 (i.e. FixPack 1 level) on the same date as FixPack 1 was made available. There is [...] 11952 34 40_DB2 for z/OS V9 - Workfile utilization ?11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Wed, 16 Dec 2009 09:35:31 +0000414_UTF-8 Hello, It may have been discussed before (can't do search - guess it's our firewall /proxy )... Looking for a way to monitor workfile utilization. I.e. would like to get a some kind of warning when utilization exceeds some threshold and be able to identify main consumers.... Also MAXTEMPS zparm relates to overall temporary storage - sum of 4k and 32k ? Can't define storage for different page sizes ? [...] 11987 106 63_Re: Using MS Access to read DB2 z/Os generates too many threads12_Fay, Colin M13_CFay@UNUM.COM31_Wed, 16 Dec 2009 06:05:40 -0500451_us-ascii Hi,

My recommendation is to use the 'PASSTHRU' option on building the query SQL.

This bypasses the (in)famous 'Jet Engine' that is rewritinging your SQL's

Colin

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Tami Van Dreese Sent: Tuesday, December 15, 2009 3:15 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Using MS Access to read DB2 z/Os generates too many threads [...] 12094 168 45_Re: DB2 9.5 UDB on LUW index reorg monitoring16_Frederik Engelen25_engelenfrederik@GMAIL.COM31_Wed, 16 Dec 2009 14:00:23 +0100754_ISO-8859-1 The newly released FixPack 5 has added this information to db2pd:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.wn.doc/doc/c0053324.html

Kind regards,

Frederik Engelen

On 12/8/09, Phil Gunning wrote: > Georg, after re-reading your post, you could do an application snapshot and > look at details regarding db2reorg application. You should see changes in > rows read and rows written, and bufferpool activity, as well as cpu time. PG > > > > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Horrocks > Sent: Tuesday, December 08, 2009 3:45 PM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] DB2 9.5 UDB on LUW index reorg monitoring > > [...] 12263 29 41_DB2 z/os array parameters in visual basic12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Wed, 16 Dec 2009 14:22:26 +0000484_utf-8 We are attempting to use arrays as parms to DB2 z/os Stored Procedures but need an example of the call mechanism in visual basic. Anyone doing this and willing to provide an example of the app. setup call and run?

Thanks in advance.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 12293 1591 26_Re: RUNSTATS' Alternative?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 16 Dec 2009 10:05:47 -0500415_iso-8859-2 Cathy, I was, perhaps, imprecise with the term "close" for bufferpools. In cases where systems managers are dealing with limited amounts of real memory, it is sometimes expedient to reduce the size of bufferpools assigned to daytime applications to leave more space for overnight processing, and vice versa. I'm told that some companies use this strategy, though I haven't worked at one which does. [...] 13885 135 63_Re: Using MS Access to read DB2 z/Os generates too many threads18_Van Dreese, Tami S27_Tami.Vandreese@LANDSEND.COM31_Wed, 16 Dec 2009 10:11:00 -0600423_us-ascii Thanks Colin! That made a huge difference in my query. We're meeting with the application developers later today. Hopefully this is the solution. Tami

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Fay, Colin M Sent: Wednesday, December 16, 2009 5:06 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Using MS Access to read DB2 z/Os generates too many threads [...] 14021 31 73_Re: Is there anything Written on the use and/or misuse of Collectin-ids ?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 16 Dec 2009 17:17:16 +0000389_utf-8 Are you talking literally hundreds of times? That would really seem excessive. We have 20 test environments here, which I think is excessive, and each of those has it;s own plan/collection due to using different CICS/IMS regions and the fact that changes are rolled to different regions at different times. I can't imagine the reason behind hundreds of collections per package. [...] 14053 1097 26_Re: RUNSTATS' Alternative?7_DB2 DBA21_the.db2.dba@GMAIL.COM31_Wed, 16 Dec 2009 14:21:56 -0500365_ISO-8859-2 Phil, Randy & Cathy - Thank you all for the help. I just enabled the RTS environment. It is 30 mins (which is default) interval for now. I shall wait and see, if there's any 'ill-effect' on the performance, to decide whether or not to increase the interval. However, I feel there shouldn't be any, if I understand you all and the manuals correctly. [...] 15151 45 60_Re: Strange error message from DB2 Express C version 9.5.0.016_Harold Pritchett14_harold@UGA.EDU31_Wed, 16 Dec 2009 17:40:23 -0500536_ISO-8859-1 Leon Katsnelson wrote: > Harold, I am glad you fixed the problem. In your earlier posting you > indicated that you did not want to upgrade to 9.7. I know many people > don't want to go to a new release until there was a FixPack. With DB2 > Express-C we do not have FixPacks but we do update the product > periodically whenever we feel such an update is warranted and would be > beneficial to the community and would increase adoption of the new > release. So, we shipped DB2 Express-C v9.7.1 (i.e. FixPack 1 level) on > [...] 15197 32 65_AUTO: Roopa Mahendra is out of the office. (returning 22/12/2009)14_Roopa Mahendra25_roopa.mahendra@IN.IBM.COM31_Thu, 17 Dec 2009 10:02:11 +0530415_US-ASCII I am out of the office until 22/12/2009.

I am travelling from 17/12/2009 until 22/12/2009 and will have limited access to my mails. I will respond to your message when I return. Please contact jdominic@in.ibm.com for any urgent PMR/APAR issues.



Note: This is an automated response to your message "DB2-L Digest - 16 Dec 2009 to 17 Dec 2009 (#2009-359)" sent on 17/12/09 6:30:00. [...] 15230 452 34_Re: DB2 z/os v8 partitioned tables0_29_Onder.CAGATAY@HALKBANK.COM.TR31_Thu, 17 Dec 2009 10:47:12 +0200468_iso-8859-9

Hi Scott,

if you want to define 2 unique index in a table partitioned tablespace, i can say that's possible. We have one exemple of partitioned tablespace created with 2 unique index (defined as not partionned and NOT CLUSTER) and 1 DPSI (defined as CLUSTER and non unique, DPSI's are not permitted to be unique). But i don't know if it's possible to define 2 unique partitioned index with not cluster option. Any experience with that ? [...] 15683 37 34_Re: DB2 z/os v8 partitioned tables14_Peter Vanroose17_pvanroose@ABIS.BE31_Thu, 17 Dec 2009 09:06:07 +0000415_UTF-8 Hi, Onder,

Since v8, "cluster" is no longer relevant w.r.t. partitioning or partitioned indexes. So you can create any number of UNIQUE, non-partitioned indexes (cluster or not) on a partitioned table. And you can create any number of UNIQUE, partitioned indexes (cluster or not) as long as they follow the partitioning, i.e., as long as they contain at least all the partitioning columns. [...] 15721 768 34_Re: DB2 z/os v8 partitioned tables13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Thu, 17 Dec 2009 05:55:56 -0600642_us-ascii Yea, I was trying to partition _all_ my indexes on partitioned tables (mostly for reorg performance reasons). We mostly use partitioning here to break up our larger tables to smaller reorgable chunks.







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 Onder.CAGATAY@HALKBANK.COM.TR Sent: Thursday, December 17, 2009 3:47 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables [...] 16490 34 57_DB2 UDBL: Content Manager and intra-partition parallelism14_Linda Billings30_linda.billings@DOT.STATE.WI.US31_Thu, 17 Dec 2009 14:38:20 +0000340_utf-8 We have Content Manager V8.2 fix pack 11 installed on Windows 2003 SP1 using DB2 V8 fix pack 14. (Yeah, I know. None of this is even supported. Long story.) I've read in performance documentation for CM V8.3 that IBM doesn't recommend that intra-partition parallelism be turned on. Why is that and is this also true for CM V8.2? [...] 16525 487 34_Re: DB2 z/os v8 partitioned tables16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 17 Dec 2009 12:07:53 -0500609_windows-1252 In case anyone is confused by the "yes you can" versus "no you can't" concerning unique DPSIs, keep in mind that, as Peter Backlund pointed out, the ability to create unique DPSIs is *a DB2 for z/OS V9 enhancement.*

*In a DB2 for z/OS V8* environment, a DPSI cannot be defined as unique, regardless of whether or not it contains the table's "partition by" columns. Try to create an index with PARTITIONED and UNIQUE specified, and you'll get a -628 SQL code ("clauses are mutually exclusive") unless the index is a partitioning index (meaning, it starts with the table's partition-by [...] 17013 63 61_Re: DB2 UDBL: Content Manager and intra-partition parallelism12_Phil Gunning19_pkgunning@GMAIL.COM31_Thu, 17 Dec 2009 12:53:47 -0500509_UTF-8 The CM workload would generally not benefit from intra_partition parallelism being used. You didn't say you were non-dpf but I assume you aren't. Intra_partition parallelism generally benefits select queries and work that can be distributed. PG

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Linda Billings Sent: Thursday, December 17, 2009 9:38 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 UDBL: Content Manager and intra-partition parallelism [...] 17077 109 56_DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?11_Mike Jessen22_jessenmike@HOTMAIL.COM31_Thu, 17 Dec 2009 12:41:03 -0600340_iso-8859-1





We have a 72 partition large (dssize 8g) tablespace that contains LOB data(3Meg blobs).

In reading the DB2 Version 9.1 for z/OS Admin Guide one of the restrictions for adding a partition is: "You cannot add a new partition to an existing partitioned table space if the table has LOB columns." [...] 17187 382 60_Re: DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK31_Thu, 17 Dec 2009 19:43:18 -0000561_us-ascii Hi Mark





Take a look at PK50692





I think you'll find this to be good news :-)





Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mike Jessen Sent: 17 December 2009 18:41 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?









We have a 72 partition large (dssize 8g) tablespace that contains LOB data(3Meg blobs). [...] 17570 85 42_Re: DSNTIAUL to DSNUTILB unload conversion13_Gerard Le Roy24_gerard.le.roy@WANADOO.FR31_Thu, 17 Dec 2009 21:47:23 +0100353_iso-8859-1 Hi Amit, In case of empty field : with DSNUTILB you'll get X'FF' before an empty field instead of X'6F' (?) after the field with DSNTIAUL. For non empty field, the indicator variable is still X'00' in either case. Another difference is the padding character of VARCHAR field : X'40' (space) with DSNUTILB instead of X'00' with DSNTIAUL. [...] 17656 105 36_DB2 V9 CM on z/os1.10 - Java drivers0_24_carol.sutfin@REGIONS.COM31_Thu, 17 Dec 2009 16:01:33 -0600370_US-ASCII We have just upgraded from DB2 V8 to DB2 V9 CM

Everything is working except our one Java application. It has stopped altogether.

The DB2 Systems guy has done everything he can find to get this working again. Bound Plan/packages, I have made sure that access has been granted, etc.

This "thing" runs in USS and just refuses to connect. [...] 17762 25 42_Mike Hopkins/COR/AWG is out of the office.12_Mike Hopkins19_mhopkins@AWGINC.COM31_Thu, 17 Dec 2009 20:01:03 -0600676_US-ASCII I will be out of the office starting 12/17/2009 and will not return until 12/21/2009.

If you need immediate assistance, please e-mail edi@awginc.com.

_____________________________________________________________________

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

http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...] 17788 98 40_Re: DB2 V9 CM on z/os1.10 - Java drivers11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Fri, 18 Dec 2009 11:37:57 +0000360_UTF-8 Hello,

When migrating to V9, you have also migrated to the new JDBC driver that (probably) has different path for its part under USS. ( for example our new path is "/shared/db2/db2910_jdbc/classes/" ). Make sure that Tomcat ( and applications on it) is aware of this. I.e. that they use new jars (db2jcc.jar and db2jcc_license_cisuz.jar) ... [...] 17887 42 86_Are the current registers, such as packageset and owner, locateable via memory access?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Fri, 18 Dec 2009 14:17:26 +0200321_us-ascii I see that DB2 places a DCIB control block off of the FSA of the DB2 task. I cannot find a mapping for this block.

Is there some memory access that can get to these special registers or must SQL be used? I would like to do it from an exit without EXEC SQL since I would prefer to not bind with the caller. 17930 383 19_SER.TASK SWTCH high12_Jose Antonio17_jamorcillo@CAM.ES31_Fri, 18 Dec 2009 14:20:02 +0100469_iso-8859-1 Hello everyone again!





After running an accounting report we have an average time of:





SER.TASK SWTCH 1.850374 and almost everything is UPDATE COMMIT 1.845896





Reading Administration Guide it suggests:





Wait for phase 2 commit processing for updates, inserts, and deletes (UPDATE

COMMIT). You can reduce this wait time by allocating the DB2 primary log on a [...] 18314 383 23_SV: SER.TASK SWTCH high13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Fri, 18 Dec 2009 15:23:09 +0100459_iso-8859-1 I assume this is a batch job. Can be too frequent commits, check number of commits/checkpoints vs elapsed time.

regards, Olle



________________________________ Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Jose Antonio Skickat: den 18 december 2009 14:20 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: [DB2-L] SER.TASK SWTCH high

Hello everyone again!

After running an accounting report we have an average time of: [...] 18698 515 34_Re: DB2 z/os v8 partitioned tables11_Hardy, Dale14_DHardy@MIB.COM31_Fri, 18 Dec 2009 10:13:05 -0500469_US-ASCII Yes, you can have two unique partitioned indexes in DB2 V8.





We have a table partitioned on COL1, clustered on a primary key COL1, COL2.





CREATE UNIQUE INDEX INDEX1 ON TABLEA

( COL1, COL2 )

CLUSTER

PARTITIONED

( PARTITION 01 USING STOGROUP ....





And a 2nd partitioned index defined as

CREATE UNIQUE INDEX INDEX2 ON TABLEA

( COL1, COL3, COL4, COL5 [...] 19214 957 34_Re: DB2 z/os v8 partitioned tables16_Robert Catterall21_rfcatterall@GMAIL.COM31_Fri, 18 Dec 2009 11:06:52 -0500338_windows-1252 Hello, Dale.

I pointed out that specification of UNIQUE and PARTITIONED in a CREATE INDEX statement would result in a -628 SQL code in a DB2 V8 environment "unless the index is a partitioning index (meaning, it starts with the table's partition-by columns, in the order specified on the CREATE TABLE statement)." [...] 20172 26 61_Re: DB2 UDBL: Content Manager and intra-partition parallelism14_Linda Billings30_linda.billings@DOT.STATE.WI.US31_Fri, 18 Dec 2009 15:21:02 +0000629_utf-8 We are not dpf.

Thanks, Phil. That's what I thought. Now I just have to convince our DBA that this is so.

_____________________________________________________________________

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

http://www.IDUG.org membership is now free. Do you have people in your office who are not an IDUG member? Show them how to access the information and help train the next generation of DB2 Users! _____________________________________________________________________ [...] 20199 1417 34_Re: DB2 z/os v8 partitioned tables13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK31_Fri, 18 Dec 2009 16:20:17 -0000441_us-ascii Hi Dale





For table controlled partitioning, they are both PARTITIONING indexes!!





In V8 (and beyond) a PARTITIONING index is one THAT SHARES the partitioning key of the underlying table





A PARTITIONED index is one that just happens to be in partitions





A DPSI is an index that is PARTITIONED but does NOT share the tables partitioning key [...] 21617 550 34_Re: DB2 z/os v8 partitioned tables14_Peter Backlund21_BacklundDB2@TELIA.COM31_Fri, 18 Dec 2009 17:20:58 +0100 22168 162 60_Re: DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?16_Robert Catterall21_rfcatterall@GMAIL.COM31_Fri, 18 Dec 2009 11:45:40 -0500412_ISO-8859-1 Mike, I believe that the DB2 for z/OS V9 Administration Guide is incorrect in this instance (and Phil Grainger mentioned to me that he thought that this might be the case, as the DB2 V9 SQL Reference mentions no such restriction). On a DB2 V9 subsystem, I can successfully execute both ALTER TABLE ADD PARTITION and ALTER TABLE ROTATE PARTITION FIRST TO LAST for a table containing a LOB column. [...] 22331 34 25_Is IRLM shutdown needed ?10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 18 Dec 2009 17:04:43 +0000530_utf-8 Hi,

Our test DB2 subsystems are brought down and restarted on a weekly basis for some storage housekeeping. Is it necessary to shutdown the corresponding IRLM address space as well, or can we leave that running during the subsystem shutdown ?

Thanks,

Regards, Kals

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 22366 37 45_Evaluate uncommitted and Skip Inserted ZPARMs10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Fri, 18 Dec 2009 17:12:27 +0000347_utf-8 Hi,

Does anyone have these parms turned ON ? Is there any specific issues we need to consider ?

Unfortunately, this is at the subsystem level. We have some applications that can use these two zparms, but that may impact other existing apps. We are still trying to analyse the options.

Thanks,

Regards, Kals. [...] 22404 66 49_Re: Evaluate uncommitted and Skip Inserted ZPARMs13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Fri, 18 Dec 2009 11:43:01 -0600703_UTF-8 Hi,

You might be interested in the DSNZPARM evaluation project we are working on... check out what we are trying to accomplish in my blog entry... http://it.toolbox.com/blogs/db2zos/how-do-your-dsnzparms-measure-up-to-everyone-else-35602

Willie

Teldb2kals wrote: > Hi, > > Does anyone have these parms turned ON ? Is there any specific issues we > need to consider ? > > Unfortunately, this is at the subsystem level. We have some applications that > can use these two zparms, but that may impact other existing apps. We are > still trying to analyse the options. > > Thanks, > > Regards, > Kals. > > _____________________________________________________________________ [...] 22471 164 42_Re: DSNTIAUL to DSNUTILB unload conversion9_Don Leahy19_don.leahy@LEACOM.CA31_Fri, 18 Dec 2009 14:29:28 -0500409_ISO-8859-1 I would recommend that you change the downstream processes to accept DSNUTILB. In the long run that is the better way to go. IMO, it would be better to go back to using DSNTIAUL than to reformat the file.

You must have had a good reason to convert from DSNTIAUL to DSNUTILB in the first place. If necessary, use that reason to convince the owners of the downstream processes to change. [...] 22636 440 62_Job Posting - BMC Software Mainframe Technical Account Manager12_Estrada, Amy19_Amy_Estrada@BMC.COM31_Fri, 18 Dec 2009 17:20:01 -0700774_us-ascii BMC Software has multiple opportunities for a Mainframe Technical Account Manager!

The BMC Software Mainframe Technical Account Manager is responsible for creating and executing the Mainframe Technical Account Plan to existing and prospective clients with minimal supervision. The successful candidate will work closely with numerous teams such as: Sales, R&D, Support, Marketing and the extended technical team to ensure achievement of account/area/regional business objectives. Successful candidates possess the following: * Solid Mainframe Systems Management background in at least one the following Mainframe areas: o operating system monitoring (BMC, Omegamon/ASG T-mon/Sysview), o scheduling ( Control/M preferred) o IMS Database Administration o [...] 23077 38 42_Re: DSNTIAUL to DSNUTILB unload conversion14_Peter Vanroose17_pvanroose@ABIS.BE31_Sat, 19 Dec 2009 11:21:08 +0000555_UTF-8 > [...] But i am getting spaces X'40' in DSNUTILB unload file > instead of Null X'00' which are in DSNTIAUL unload file.

This *should* not affect the end application which is going to read the unload file, I would expect: Suppose you have a VARCHAR(40) field, which thus takes 42 bytes (not counting the NULL indicator): 2 bytes for the length and 40 bytes for the data. But suppose the actual length is 10 (as will be indicated by the two length bytes): in that case the last 30 bytes may be whatever (in practice X'00' or X'40') since [...] 23116 156 29_Re: Is IRLM shutdown needed ?0_17_jtonchick@AOL.COM31_Sun, 20 Dec 2009 12:44:12 -0500388_us-ascii

First, most configurations let the DB2 subsystem manage the matching IRLM task, so it is started and stopped by DB2 with the subsystem. Yes, you can leave it active, but you said that you are performing storage housekeeping. The IRLM does all of its work in storage, mostly above the line (PC=YES). Very few shops still configure the IRLM using CSA storage (PC=NO). [...] 23273 33 77_AUTO: Merrill Bani/Australia/IBM is out of the office. (returning 07/01/2010)12_Merrill Bani20_merrillb@AU1.IBM.COM31_Mon, 21 Dec 2009 16:06:42 +1100430_US-ASCII I am out of the office until 07/01/2010.

Please refer any critical issues to Mohammad Zanjani (mzanjani@au1.ibm.com) & Heath Dewson (dewson@au1.ibm.com) raised during this time.



Note: This is an automated response to your message "DB2-L Digest - 20 Dec 2009 to 21 Dec 2009 (#2009-363)" sent on 21/12/09 12:00:00.

This is the only notification you will receive while this person is away. [...] 23307 782 27_Re: SV: SER.TASK SWTCH high12_Jose Antonio17_jamorcillo@CAM.ES31_Mon, 21 Dec 2009 09:33:20 +0100687_iso-8859-1 Hi Olle!





No, no it was a problem with a transaction





__________________________ José A Morcillo Valenciano Tfno.: +34 965 90 51 43 747-Producción Informática



__________________________









________________________________

De: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] En nombre de Olle Brostrom Enviado el: viernes, 18 de diciembre de 2009 15:23 Para: DB2-L@WWW.IDUGDB2-L.ORG Asunto: [DB2-L] SV: SER.TASK SWTCH high





I assume this is a batch job. Can be too frequent commits, check number of commits/checkpoints vs elapsed time. [...] 24090 154 80_Redpaper DB2 9 for z/OS: Configuring SSL for Secure Client-Server Communications10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 21 Dec 2009 04:46:00 -0500753_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, December 21st 2009





IBM just published a new Redpaper, "DB2 9 for z/OS: Configuring SSL for Secure Client-Server Communications, REDP-4630-00":





Abstract This IBM Redpaper provides information about how to setup and configure DB2 9 for z/OS with Secure Sockets Layer (SSL) using the z/OS V1R10 Communications Server: IP Application Transparent -Transport Layer Security (AT-TLS) services. This paper also covers the basic configuration steps for configuring SSL support for non-Java DB2 clients and IBM Data Server Driver for JDBC and SQLJ type [...] 24245 218 29_Re: Is IRLM shutdown needed ?14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Mon, 21 Dec 2009 06:08:14 -0700581_US-ASCII Jim, I may be wrong but IIRC the only version of IRLM supported for DB2 (V8 and above) ignores PC=NO and always uses private storage, and also no longer supports a single IRLM for multiple subsystems.

=============================================== Wayne Driscoll OMEGAMON DB2 L3 Support/Development wdrisco(AT)us.ibm.com ===============================================





From: jtonchick@AOL.COM To: DB2-L@WWW.IDUGDB2-L.ORG Date: 12/20/2009 12:12 PM Subject: Re: [DB2-L] Is IRLM shutdown needed ? Sent by: IDUG DB2-L [...] 24464 66 34_Applying lots of PTFs a DB2 system13_Keith Gooding17_kwg25@YAHOO.CO.UK31_Mon, 21 Dec 2009 08:25:09 -0800505_iso-8859-1 Could anyone offer any advice about the best way to apply lots of maintenance to a DB2 system (or point me to some documentation) ?. I was asked to apply 1 PTF to a z/OS DB2 9.1 system at the GA level but the fix chain involves several hundred PTFs. It is for a test system for an ISV. I decided to get a new DB2 9.1 serverpac from Shopz but now I am not sure how to implement it. Many of the PTFs require rebinds so I assume I cannot escape that action if I upgrade the target libraries. [...] 24531 58 22_Re: Too many STOGROUPs12_Roy Reynolds17_royr@BERKELEY.EDU31_Mon, 21 Dec 2009 17:05:33 +0000428_UTF-8 I agree with Mike and Roger and Cathy, My approach has been to segregate by subsystem using STOGROUP SYSDEFLT having the vcat name the same as the SSID. Beyond that I use sms to segregate the catalog, logs, bsds from application data. Yes, large objects are also segregated usng SMS but the HLQs are all the same for each subsystem. And yes, DataSharing introduces some variation for the member-specific tablespaces. [...] 24590 176 38_Re: Applying lots of PTFs a DB2 system15_Chris Hoelscher21_choelscher@HUMANA.COM31_Mon, 21 Dec 2009 12:29:41 -0500537_US-ASCII i can only relate what we do - we apply all RSUs to bring us up to date - we then install in our TARGET loadlib - when the day comes - we copy (target) load module to (runtime) load module (iebcopy) - we then bring the subsystems back up in maint mode - do all required bind syntax changes and binds (if needed) - and object drop/recreates needed, and then ... we run a script for the subsystem that creates rebind syntax for ALL packages (slight syntax variation for trigger packages) and all plans - and then execute the [...] 24767 606 38_Re: Applying lots of PTFs a DB2 system13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Mon, 21 Dec 2009 14:12:59 -0800515_us-ascii Hi Keith. When faced with long prereq chains, I decide it must be time to do mass maintenance, so I would upgrade to the most recent RSU level. I'm not sure how you would introduce a DB2 serverpac into an environment, as I have never done it. I just do it the old fashioned way, receive all put maintenance there is, do an apply check on all of the RSU sourceid's plus hipers, etc. Chris gave a pretty good sketch of installing into a single subsystem. It's slightly different if you're data sharing. [...]