1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 2008, week 1 2 57 11_Re: Goodbye12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 1 Oct 2008 02:07:21 +0000359_utf-8 It has been our privilege and pleasure to work with you for many years. Thanks for your sense of humor, a little bit self-deprecating, but always worth listening to. Your unique character and touch will be sorely missed. Thanks for your contributions and for all that you brought. I hope that you'll be doing something enjoyable in this next step. [...] 60 269 11_Re: Goodbye11_Nick Dordea20_ndordea@COMPUTER.ORG31_Tue, 30 Sep 2008 21:26:57 -0500692_us-ascii Good Bye ! All the Best!

nd





_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andy Lankester Sent: Tuesday, September 30, 2008 5:38 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Goodbye





Much to Rick Weaver's delight I am finally signing off!

Having worked from IMS/360 V2 (first with logical relationships) and DB2 since 1982 (19 years with IBM) then with BMC and latterly with CDB it has been a spectacular database journey via IMS/370 1.1.1 (aka cripple one) through DB2 making 50 tx/sec on a 3033 (Ted Codd sent 50 roses to Marilyn Bohl, then DB2 development manager) and [...] 330 97 24_Re: PPRC and Log Suspend12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 1 Oct 2008 02:34:15 +0000583_utf-8 Without serialization, the worst situation can be days for recovery and loss of data integrity. The multi-volume consistency needed for DB2 data integrity is not provided by PPRC, though it is by XRC.

In power outages and in copies without some serialization, there can be a ragged edge, where some disks and processing are copied at one time, and others are copied at other times. If the timing is very short, the systems are not busy, and you are lucky, then you just get the easy recoveries and don't lose too much. There is no simple indication to discover the [...] 428 352 11_Re: Goodbye22_Leslie Pendlebury-Bowe21_db2dba@BTINTERNET.COM30_Wed, 1 Oct 2008 05:11:55 +0100552_US-ASCII Andy

I left and came back .. you might find the pull of DB2 too much :-), I did (or was it the money).





If there is one thing that I have learnt from you it is to reorg your indexes, and I even use your name when telling/advising/moaning/screaming at/with/through people (mostly my *new* Colleagues from the various corners of the world, who look / stare at me with blank expressions as I discuss pseudo deleted etc ... I might be in a job for a while with this one) . it's even made me look the hero on many [...] 781 206 11_Re: Goodbye10_Todd Burch17_toddburch@MAC.COM31_Tue, 30 Sep 2008 23:36:04 -0500654_US-ASCII Andy, it was good working with you. Take care of yourself.

Todd

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andy Lankester Sent: Tuesday, September 30, 2008 5:38 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Goodbye

Much to Rick Weaver's delight I am finally signing off!

Having worked from IMS/360 V2 (first with logical relationships) and DB2 since 1982 (19 years with IBM) then with BMC and latterly with CDB it has been a spectacular database journey via IMS/370 1.1.1 (aka cripple one) through DB2 making 50 tx/sec on a 3033 (Ted Codd sent 50 roses to Marilyn Bohl, then [...] 988 221 11_Re: Goodbye0_23_j.p.slot@RN.RABOBANK.NL30_Wed, 1 Oct 2008 07:15:24 +0200725_us-ascii Andy,

Thank you for all your efforts and good advice. I wish you all the best

Kind regards,

Jaap Slot

________________________________

Van: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Namens Andy Lankester Verzonden: woensdag 1 oktober 2008 0:38 Aan: DB2-L@www.idugdb2-l.org Onderwerp: [DB2-L] Goodbye





Much to Rick Weaver's delight I am finally signing off!

Having worked from IMS/360 V2 (first with logical relationships) and DB2 since 1982 (19 years with IBM) then with BMC and latterly with CDB it has been a spectacular database journey via IMS/370 1.1.1 (aka cripple one) through DB2 making 50 tx/sec on a 3033 (Ted Codd sent [...] 1210 60 11_Re: Goodbye10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Wed, 1 Oct 2008 08:43:52 +0200335_US-ASCII Hi Andy

Good luck !!! It seems it was a long and full trip in DB2 history (and mvs - z/OS) so it's time to stop and enjoy life :-))).

I think anyway it'd not be easy to leave DB2 after so many years, as it's happening to me that in few moths I'll leave mainframe after 20+ years and not to retire.....:-))) [...] 1271 84 11_Re: Goodbye14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 1 Oct 2008 08:37:51 +0100489_iso-8859-1 Good luck in your future endeavours Andy - and whatever else happens. HAVE FUN

Thanks for all your help and encouragement all those years ago - your enthusiasm helped me get where I am today, so it is VERY MUCH appreciated

I'm sure we'll all miss you

Phil G CA

________________________________

From: DB2 Data Base Discussion List on behalf of Andy Lankester Sent: Tue 30/09/2008 23:37 To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Goodbye [...] 1356 27 24_Re: PPRC and Log Suspend13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 1 Oct 2008 08:51:17 +0100302_UTF-8 ...You might want to get DB2 9, so you can RECOVER to a consistent point on the log, rolling back the in flight work...

Or you can get some smart ISV's Recover utility and do it today. Well, as soon as you can download the software/get the cart sent to you. [...] 1384 333 11_Re: Goodbye13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 1 Oct 2008 09:02:30 +0100403_us-ascii Hey Andy,





Enjoy your new-found full-time occupation and think fondly (and possibly with a small dose of my favourite German word, schadenfreude?) of us lot still slaving away at the coal face of DB2. Perhaps the 'old lags' as I believe it's called can pull finger and find an opportunity to celebrate by jointly consuming some of my 2nd favourite German word, bier. [...] 1718 119 11_Re: Goodbye14_Peter Backlund21_BacklundDB2@TELIA.COM30_Wed, 1 Oct 2008 10:38:14 +0200 1838 81 11_Re: Goodbye14_Avram Friedman25_IBMsysProg@GEEK-SITES.COM30_Wed, 1 Oct 2008 13:53:12 +0000519_utf-8 Andy Sorry to see you leave and thank you for all your help on issues from the various Listservs. You have always been one of the good ones both in public posts and private follow follow ups.

I would like to suggest a minor variation to one of your closing tips ... Call the operator to cancel your session if ones does a drop on the wrong subsystem. I have found it to be more effective to ask a person in a cube near me ... this avoids questions like What is the syntax of the cancel command Do you [...] 1920 254 28_Help on update SQL statement14_Sniatecki, Jim31_Jim.Sniatecki@ERIEINSURANCE.COM30_Wed, 1 Oct 2008 10:09:14 -0400386_US-ASCII I am not a programmer but I do do some SQL.





I have three tables: POL_QT , POL_ROL and ROL





POL_QT has columns: pol_id, and bus_nm

Pol_id is a primary key.

Bus_nm was just added to the tablE and is currently empty and nulls are allowed.





POL_ROL has columns: pol_id , rol_id and rol_typ_cd . [...] 2175 54 20_DB2 V8 for z/OS ZIIP0_29_Sherri.Sterling@BPD.TREAS.GOV30_Wed, 1 Oct 2008 10:45:24 -0400284_US-ASCII Ok, now management has bought a ZIIP processor they want us to use it. So my question is how do you get db2 batch rebuild index job to run under WLM (SRB). Maybe the term is how do I redirect my db2 batch to use the ZIIP processor.

Any help would be appreciated. [...] 2230 145 24_Re: DB2 V8 for z/OS ZIIP14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 1 Oct 2008 16:01:22 +0100335_iso-8859-1 Hi Sherri

You don't

Unfortunately the ONLY DB2 work that is currently zIIP eligible is that which is run from a stored procedure that is called across DRDA

So far as I am aware, NO local (i.e. within z/OS) workloads are currently zIIP eligible in DB2 V8 or 9 - nor can they be "made" zIIP eligible [...] 2376 27 32_Re: Help on update SQL statement12_Adam Baldwin22_adambaldwin@ES.IBM.COM30_Wed, 1 Oct 2008 15:04:13 +0000317_utf-8 Hi Jim...I haven't looked at it in depth but off the top of my head adding a DISTINCT should do it - SELECT DISTINCT(B.BUS_NM).

Having said that it looks to me as if you need a WHERE clause on your UPDATE - ....WHERE A.POL_ID =....

Otherwise you are going to be doing an un qualified update. [...] 2404 294 47_Determining reads and writes against a database11_Kelman, Tom30_Thomas.Kelman@COMMERCEBANK.COM30_Wed, 1 Oct 2008 10:27:35 -0500583_us-ascii I am the mainframe capacity planner in my shop and by no means an expert on DB2 or the SMF type 100-102 record information. So I need a little help to analyze a problem.





On Monday we experienced a slow down across all our systems, mainframe and distributed. During the slow down the network folks were seeing a large amount of traffic going across the links to our DR site coming from the mainframe. We use EMC disk and do synchronous mirroring. Everything done on the mainframe is mirrored. I analyzed the SMFINTRV in my MXG data (for those who [...] 2699 88 32_Re: Help on update SQL statement15_Joehlin, Gary L34_gary.l.joehlin@VERIZONBUSINESS.COM30_Wed, 1 Oct 2008 15:23:15 +0000411_us-ascii Jim,



The first part of your SQL Update says it wants to update a single column in a table with all the values on the right-hand side because the type of predicate you're using; a "basic" predicate.

Basic predicates use the operators (<, <=, =, >=, >) this means that the value on the right-hand side of this operator must evaluate to one and only one value, and it does not. [...] 2788 521 32_Re: Help on update SQL statement15_Douwe van Sluis21_d.b.van.sluis@HOME.NL30_Wed, 1 Oct 2008 17:30:31 +0200272_US-ASCII So, if you execute the SELECT it comes with more than one row? Are the values of B.BUS_NM all the same? If so, try using FETCH FIRST 1 ROW ONLY. If not, you need to check your tables, join, etc. to come up with the right one value...

Hope this helps. [...] 3310 328 24_Re: DB2 V8 for z/OS ZIIP0_27_Jeff.D.Jerry@WELLSFARGO.COM30_Wed, 1 Oct 2008 10:31:55 -0500340_us-ascii You truly have to do nothing to have workload redirected to a zIIP once it is put online, but it has to be work that is zIIP eligible (uses enclave SRBs).





We have seen up to 50% of our DDF workload redirected to a zIIP and up to 20% of the index processing in reorgs and load jobs redirected to a zIIP. [...] 3639 138 24_Re: DB2 V8 for z/OS ZIIP14_Peter Backlund21_BacklundDB2@TELIA.COM30_Wed, 1 Oct 2008 17:34:14 +0200 3778 123 24_Re: DB2 V8 for z/OS ZIIP14_Avram Friedman25_IBMsysProg@GEEK-SITES.COM30_Wed, 1 Oct 2008 15:37:35 +0000341_utf-8 Phil, I disagree with 1/2 of your comment. It is true that nothing is required to utitility jobs toget DB2 V8 to use the zIIP for index rebuilds. zIIp support for some utility functions was part of the original zIIp announcement. In addition there is zIIp support for DB2 parallism and the IBM DB2 utilities always use parallism. [...] 3902 250 24_Re: DB2 V8 for z/OS ZIIP14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 1 Oct 2008 16:43:36 +0100467_iso-8859-1 Apologies

Let me try answering that again (and thanks to Steen Rasmussen for pointing out my "minor" error)

You don't need to do ANYTHING

Apart from the DRDA stuff that is zIIP eligible, IBM also state (and this was the bit I'd missed) that "DB2 Utilities LOAD, REORG, and REBUILD will now use enclave SRBs for the portion of the processing that is related to index maintenance. z/OS directs a portion of this work to the zIIP. " [...] 4153 519 24_Re: DB2 V8 for z/OS ZIIP13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 1 Oct 2008 16:46:30 +0100419_us-ascii Hi Sherri,





Phil's right, you can't just say, 'Oi! DB2 workload. See that zIIP processor over there? USE IT'. The keywords are, I believe, 'enclave SRB'. Currently that includes some index update activity for utilities (sorting basically), some stored procedure activity, DDF connection work and, er, not much else. But you can't just migrate general CP workload wholesale to zIIPs. [...] 4673 269 24_Re: DB2 V8 for z/OS ZIIP16_Hampton, James K26_kirk.hampton@CAPGEMINI.COM30_Wed, 1 Oct 2008 11:55:29 -0400354_us-ascii Whatever batch DB2 utility work is zIIP-eligible ( a retrofit to db2 v8, I believe) will automatically use the zIIP as soon as your z/OS person activates it on the LPAR. The same goes for the DDF/ENCLAVES/SRB workload. AFAIK, you cannot force WLM to use the zIIP, nor prevent WLM from using it other than varying the engine online/offline. [...] 4943 695 32_Re: Help on update SQL statement14_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV30_Wed, 1 Oct 2008 11:01:39 -0500783_us-ascii UPDATE JIM.POL_QT A

SET A.BUS_NM =

(SELECT B.BUS_NM

FROM EIG.ROL B,

EIG.POL_ROL C, JIM.POL_QT A

WHERE A.POL_ID = C.POL_ID

AND B.ROL_ID = C.ROL_ID

AND C.ROL_TYP_CD = 'LX')

WHERE A.POL_ID =

(SELECT C.POL_ID

FROM EIG.ROL B,

EIG.POL_ROL C, JIM.POL_QT A

WHERE A.POL_ID = C.POL_ID

AND B.ROL_ID = C.ROL_ID);





Wayne Stevens

Wayne.Stevens@dhr.alabama.gov





________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sniatecki, Jim Sent: Wednesday, October 01, 2008 9:09 AM To: DB2-L@www.idugdb2-l.org Subject: Help on update SQL statement [...] 5639 183 24_Re: DB2 V8 for z/OS ZIIP12_Weaver, Rick19_Rick_Weaver@BMC.COM30_Wed, 1 Oct 2008 11:04:20 -0500451_US-ASCII Thank you Avram. Just to verify, all BMC DB2 utilities that invoke BMCSORT do push work to zIIP processors if they are available.

Rick Weaver Product Manager DB2 z/OS Solutions BMC Software

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Avram Friedman Sent: Wednesday, October 01, 2008 10:38 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 V8 for z/OS ZIIP [...] 5823 449 51_Re: Determining reads and writes against a database12_Weaver, Rick19_Rick_Weaver@BMC.COM30_Wed, 1 Oct 2008 11:10:05 -0500599_US-ASCII There are several ISV products that can read the DB2 logs and determine the write activity (and who did them). Unfortunately, reads are not logged but as you say they should not have driving the EMC mirroring process to go wild.





Rick Weaver Product Manager DB2 z/OS Solutions BMC Software

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kelman, Tom Sent: Wednesday, October 01, 2008 10:28 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] Determining reads and writes against a database [...] 6273 30 26_zOS Expensive SET :hostvar14_Gwyn Pritchard27_gWYN.pRITCHARD@BARCLAYS.COM30_Wed, 1 Oct 2008 16:20:16 +0000460_utf-8 I am investigating a performance problem with 2 plans and using my 3rd Party DB2 monitor I am seeing that the most expensive SQL Statement in both cases is a SET :Host variable = Current Timestamp. Average elapsed time over 3 seconds. We are on V7 (V8 soon honest). Transaction are IMS MPP trans, we run 200+ per second. Usual things being looked at : - Dasd, Bufferpools, and we are looking at what's changed in the infrastructure/application etc. [...] 6304 946 32_Re: Help on update SQL statement14_Stevens, Wayne29_Wayne.Stevens@DHR.ALABAMA.GOV30_Wed, 1 Oct 2008 11:20:29 -0500806_us-ascii



UPDATE JIM.POL_QT A

SET A.BUS_NM =

(SELECT MAX(B.BUS_NM)

FROM EIG.ROL B,

EIG.POL_ROL C, JIM.POL_QT A

WHERE A.POL_ID = C.POL_ID

AND B.ROL_ID = C.ROL_ID

AND C.ROL_TYP_CD = 'LX')

WHERE A.POL_ID =

(SELECT C.POL_ID

FROM EIG.ROL B,

EIG.POL_ROL C, JIM.POL_QT A

WHERE A.POL_ID = C.POL_ID

AND B.ROL_ID = C.ROL_ID);









Wayne Stevens

Wayne.Stevens@dhr.alabama.gov





________________________________

From: Stevens, Wayne Sent: Wednesday, October 01, 2008 11:02 AM To: 'DB2 Database Discussion list at IDUG' Cc: 'Jim.Sniatecki@ERIEINSURANCE.COM' Subject: RE: Help on update SQL statement [...] 7251 68 24_Re: DB2 V8 for z/OS ZIIP15_Blaicher, Chris22_Chris_Blaicher@BMC.COM30_Wed, 1 Oct 2008 11:22:13 -0500451_US-ASCII To clarify a point Avram made, the current version of BMCSORT (2.3.1) does support the use of z/IIP processors. BMCSORT is a modified version of SyncSort licensed by BMC for use by BMC products.

________________________________

Christopher Y. Blaicher Senior Software Developer Austin Development Lab

phone: 512.340.6154 moble: 512.627.3803 fax: 512.340.6647

10431 Morado Circle Austin, TX 78759 BMC Software [...] 7320 27 24_Re: DB2 V8 for z/OS ZIIP11_Ted MacNEIL18_eamacneil@YAHOO.CA30_Wed, 1 Oct 2008 16:33:23 +0000487_- >Unfortunately the ONLY DB2 work that is currently zIIP eligible is that which is run from a stored procedure that is called across DRDA

I must have mis-read the announcement, then. I thought that some DB2 utilities will also use zIIPs. Or, was that a statement of intent.

In either case, you cannot direct work to either a zIIP, or a zAAP. The dispatcher and the WLM makes that decision; not all eligible work is directed there. - Too busy driving to stop for gas! [...] 7348 390 24_Re: DB2 V8 for z/OS ZIIP15_Douwe van Sluis21_d.b.van.sluis@HOME.NL30_Wed, 1 Oct 2008 18:50:47 +0200328_US-ASCII DRDA is for about 42% zIIP, Utility index build 100%. We see however that sometimes the zIIP is not used anymore. It looks like at some point in time the zIIP is used up after some time and becomes available again. Not sure yet what causes this behavior.





Vriendelijke groet, Douwe van Sluis [...] 7739 215 47_DB2 V8 ZOS - S locking & deadlocking on INSERT.13_Case, Missy J24_Missy.Case@FIRSTDATA.COM30_Wed, 1 Oct 2008 12:12:57 -0500488_us-ascii All,

Thanks in advance for any help. We do have an askq in to IBM on this, but perhaps we can get a faster response from the pros on the list!! See - flattery works!!









We have an insert process that is timing out due to another of the same transaction processes holding an X lock. It is very curious & is quickly becoming more of an issue... Some details & questions we've asked/answered in the past couple of days regarding it. [...] 7955 83 30_Re: zOS Expensive SET :hostvar10_Roger Hecq18_Roger.Hecq@UBS.COM30_Wed, 1 Oct 2008 13:19:43 -0400541_us-ascii If the SET statement is the first statement executed in the package, then the time could also include the authorization checking that occurs when the package is invoked. You can use the PE Record Trace to confirm this.

Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gwyn Pritchard Sent: Wednesday, October 01, 2008 12:20 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] zOS Expensive SET :hostvar [...] 8039 278 24_Re: DB2 V8 for z/OS ZIIP13_Jones, Robert20_RJones@ONEBEACON.COM30_Wed, 1 Oct 2008 13:35:15 -0400715_us-ascii

Sherri





You don't. The rebuild index utility will automagically redirect to the ZIIP.





Rob Jones

DBA One Beacon





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sherri.Sterling@BPD.TREAS.GOV Sent: Wednesday, October 01, 2008 10:45 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] DB2 V8 for z/OS ZIIP







Ok, now management has bought a ZIIP processor they want us to use it. So my question is how do you get db2 batch rebuild index job to run under WLM (SRB). Maybe the term is how do I redirect my db2 batch to use the ZIIP processor. [...] 8318 1312 24_Re: DB2 V8 for z/OS ZIIP13_Kenney, Marty24_Marty.Kenney@RAILINC.COM30_Wed, 1 Oct 2008 13:35:37 -0400654_us-ascii Sherri, here is a link with lots of good information on zIIP specialty engine. http://it.toolbox.com/blogs/db2zos/ziip-specialty-engine-new-information -and-a-summary-index-11847

Here are the items that are zIIP eligible:







Getting the Most out of DB2 for z/OS and System z by Willie (IBM DB2 for z/OS SME)



Blog Main / Archive / Invite Peers / Connect to this blog [...] 9631 51 39_[z/os] export / import plans / packages14_Avram Friedman25_IBMsysProg@GEEK-SITES.COM30_Wed, 1 Oct 2008 18:11:49 +0000555_utf-8 I am working on a data center move for a customer who is going from one facilities manager to another. One of the restrictions the original facilities manager is trying to place on this project is data moves must be done by FDR backups done by them for application data only. This restriction is viewed as preventing catalog / directory backups. The original facilities manager feels that objects like the DB2 catalog and directory may contain information that is propriatary to there services and they are under no obligation to give it away. [...] 9683 495 51_Re: Determining reads and writes against a database20_Govan, Hal (RET-DAY)29_Harold.Govan@REEDELSEVIER.COM30_Wed, 1 Oct 2008 14:13:43 -0400416_us-ascii







DB2 reads will not drive mirroring I/O activity but extreme read activity can bog down channels and overrun the DASD cache impacting disk I/O significantly. In extreme cases, we have seen EMC DASD devices with average access times in excess of 100 ms. If this phenomenon occurred in combination with high write activity, you could easily see serious performance issues. [...] 10179 148 24_Re: PPRC and Log Suspend13_Steve Runtsch26_steve.runtsch@ASSURANT.COM30_Wed, 1 Oct 2008 13:36:01 -0500534_US-ASCII Thank you Carol and Roger. We issue log suspend at 4:00 AM CT. Prior to the DS8100 and PPRC, when the time between log suspend and resume was several minutes, this had been a big issue for our associates in Ireland who are in the middle of their on-line day. Nowadays, the whole automated process takes only seconds -- not long enough for Ireland (we do have the luck of the Irish with us) to notice, but it is a process that has to be managed and maintained, and I do think that our current process is simpler and more [...] 10328 45 24_Re: PPRC and Log Suspend13_Steve Runtsch26_steve.runtsch@ASSURANT.COM30_Wed, 1 Oct 2008 13:41:44 -0500582_US-ASCII We used to use some smart ISV's Recover utility. Not all of our businesses used it so it became a victim of one of our software consolidation projects.

Steve Runtsch

DB2 Data Base Discussion List wrote on 10/01/2008 02:51:17 AM:

> ...You might want to get DB2 9, so you can RECOVER to a > consistent point on the log, rolling back the in flight work... > > Or you can get some smart ISV's Recover utility and do it > today. Well, as soon as you can download the software/get the cart > sent to you. [...] 10374 32 51_Re: Determining reads and writes against a database11_Ted MacNEIL18_eamacneil@YAHOO.CA30_Wed, 1 Oct 2008 18:46:01 +0000426_Windows-1252 >DB2 reads will not drive mirroring I/O activity but extreme read activity can bog down channels and overrun the DASD cache impacting disk I/O significantly.

This is a known fact.

>In extreme cases, we have seen EMC DASD devices with average access times in excess of 100 ms. If this phenomenon occurred in combination with high write activity, you could easily see serious performance issues. [...] 10407 118 43_Re: [z/os] export / import plans / packages16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Wed, 1 Oct 2008 14:58:26 -0400334_us-ascii Hi Avram - seems like you have got a nice interesting and challenging job here :-)

A couple of issues come to mind regarding the package/plan challenge. If you don't have access to the DBRMLIB (or not sure if the DBRM's) even exist, you can use CA Bind Analyzer to create the DBRM's based on the catalog content. [...] 10526 43 20_DB2 V8 for z/OS ZIIP0_29_Sherri.Sterling@BPD.TREAS.GOV30_Wed, 1 Oct 2008 14:58:09 -0400385_US-ASCII Thanks everyone for your comments, it sure has cleared the mud.

Sherri Sterling Bureau of the Public Debt DB2 sys prog

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________





10570 54 24_Re: DB2 V8 for z/OS ZIIP13_Terry Purcell18_tpurcel@US.IBM.COM30_Wed, 1 Oct 2008 19:03:49 +0000545_utf-8 Sherri,

I just wanted to clarify an additional point regarding zIIP redirect. There has been comments regarding DDF, index portion of utilities, and "not much else" redirected to zIIP.

One major redirect that hasn't been mentioned is the parallel child tasks of any local or remote query. The first x milliseconds (let's call it the OLTP portion of the query) is not redirected, but after this x milliseconds, we have publicly stated at conferences that 80% of the parallel child tasks are redirected to zIIP. Yep, 80%. [...] 10625 693 51_Re: Determining reads and writes against a database11_Kelman, Tom30_Thomas.Kelman@COMMERCEBANK.COM30_Wed, 1 Oct 2008 14:10:03 -0500546_us-ascii Thanks to everyone for your suggestions. This has certainly been a education for me. What I actually ended up doing is to start at the high level look and checked the type 74 device records for high I/O rates and then the type 42 records for reads and writes against the datasets. What I found was exactly what the DBA had told us. The process he was doing had a very large number of reads against the database, but few, if any, writes. Also, there were no other datasets or volumes experiencing high I/O. Others in my shop are now [...] 11319 51 34_DB2 subsystem checkpoint frequency11_Daniel Lapp21_lapp.daniel@GMAIL.COM30_Wed, 1 Oct 2008 15:32:08 -0400273_ISO-8859-1 Does anyone have any rule of thumb on what our subsystem checkpoint frequency should be ? We have a very active system and currently have it set for 15 minutes. Is ther any way to determine if it should be less than 15 minutes or even more than 15 minutes? [...] 11371 120 30_Re: zOS Expensive SET :hostvar10_Dave Nance16_dav1mo@YAHOO.COM30_Wed, 1 Oct 2008 12:44:47 -0700414_iso-8859-1 Are you sure this host variable is only being set once or could it several thousand times? I've never seen nor heard ot it taking more than milliseconds to accomplish. We did have an issue once that a date routine in a stored procedure was being run over and over and... it caused massive issues on our CPUs and subsystems, in fact, brought our subsystem down a couple of times, due to its misuse. [...] 11492 336 24_Re: DB2 V8 for z/OS ZIIP14_Grainger, Phil20_Phil.Grainger@CA.COM30_Wed, 1 Oct 2008 20:48:32 +0100599_us-ascii Hopefully, including my accidentally added extra mud





Apologies again all round - I'm having a BAD day





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc a company registered in England and Wales under company registration number 1282495 with its registered office at the address set out above. VAT number 697904179. [...] 11829 23 24_Re: PPRC and Log Suspend10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 1 Oct 2008 20:08:55 +0000792_utf-8 Hmm,

Well we have just started using EMC SRDF/Synchronous replication which promises zero data exposure. The first DR test last year went great, although the cutover was at a quiet time for the system.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________





The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information [...] 11853 91 38_Re: DB2 subsystem checkpoint frequency14_Avram Friedman25_IBMsysProg@GEEK-SITES.COM30_Wed, 1 Oct 2008 20:09:41 +0000377_utf-8 The key to answering this question is related to two availability service lever objectives: 1) Mean Time To Repair or MTTR. How long should it take to get a failed DB2 subsystem back up after an unexpected, uncontroled failure. 2) Does MTTR include appliction repair (backout)

I am assuming that item 2 does not apply because it is an entirly diffrent issue. [...] 11945 495 51_Re: DB2 V8 ZOS - S locking & deadlocking on INSERT.15_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM30_Wed, 1 Oct 2008 16:40:12 -0400602_us-ascii Missy,

If the second process is trying to insert the same key (unique constraint) value as the first process which is holding the X lock on page, then the second process is timed out, that is what happened in your case.

I just tested it and this is what happens.





Regards Vidya

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Case, Missy J Sent: Wednesday, October 01, 2008 1:13 PM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] DB2 V8 ZOS - S locking & deadlocking on INSERT. [...] 12441 21 51_Re: Determining reads and writes against a database10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 1 Oct 2008 20:50:11 +0000445_utf-8 It might be best to look at the LOG_RECS_CREATED (DB2 PM name) in the DB2 statistics records. That will include I/U/D activity plus any logs written because someone ran a Load Log Yes with a ton of records.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________ [...] 12463 77 24_Re: DB2 V8 for z/OS ZIIP10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 1 Oct 2008 20:55:10 +0000633_utf-8 We see about 65% CPU redirected to zIIP for local and remote warehousing queries.

On Wed, 1 Oct 2008 19:03:49 +0000, Terry Purcell wrote:

>Sherri, > >I just wanted to clarify an additional point regarding zIIP redirect. There has >been comments regarding DDF, index portion of utilities, and "not much else" >redirected to zIIP. > >One major redirect that hasn't been mentioned is the parallel child tasks of >any local or remote query. The first x milliseconds (let's call it the OLTP >portion of the query) is not redirected, but after this x milliseconds, we have >publicly stated at [...] 12541 79 7_Goodbye14_Andy Lankester26_alankester@CDBSOFTWARE.COM30_Wed, 1 Oct 2008 16:21:00 -0500550_iso-8859-1 Many thanks to the amazing number of you who have sent me compliments, kind words and good wishes for the future on and off list. I go with a glow!

If you REALLY want to get in touch then please use db2zos@blueyonder.co.uk - true to the faith to the last!

Goodbye,

Andy





______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________ [...] 12621 334 35_DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Wed, 1 Oct 2008 16:32:13 -0500612_us-ascii Hello everyone,





We recently implemented database partitioning on one of our large tables (300+ million rows) and the access that appears to be taken has us questioning why and what's really going on...





The SQL has the partitioning column along with another column in the where clause that are both the leading columns in a DPSI. The access path shows a matchcol of 2 on the DPSI which is expected. In addition is the indication of a page range scan which is also somewhat expected since we figured it's being limited to the one partition as it should. [...] 12956 105 43_Re: [z/os] export / import plans / packages12_tim malamphy20_timalamphy@YAHOO.COM30_Wed, 1 Oct 2008 14:30:44 -0700487_us-ascii Avram-

I bet you could run db2look against this mainframe from an luw version of db2 and get most of what you need. You'll need DBA authority to bind the plans if they aren't already bound there. Other than that, it would be a pretty stealthy exercise. The site manager may not allow a DDF connection between the two datacenters, but surely there are existing PCs (ie your laptop) with some kind of DB2CONNECT software on them that could be used as an intermediary. [...] 13062 56 38_Re: DB2 subsystem checkpoint frequency12_Martin Hubel17_Martin@MHUBEL.COM30_Wed, 1 Oct 2008 17:34:42 -0400 13119 128 24_Re: DB2 V8 for z/OS ZIIP15_Debabrata Ghosh21_the_ghosh@HOTMAIL.COM30_Wed, 1 Oct 2008 17:41:55 -0400601_Windows-1252

On the TOP of this, DB2 V9 SQL procedures can run on ZIIP.> Date: Wed, 1 Oct 2008 19:03:49 +0000> From: tpurcel@US.IBM.COM> Subject: Re: [DB2-L] DB2 V8 for z/OS ZIIP> To: DB2-L@www.idugdb2-l.org> > Sherri,> > I just wanted to clarify an additional point regarding zIIP redirect. There has > been comments regarding DDF, index portion of utilities, and "not much else" > redirected to zIIP.> > One major redirect that hasn't been mentioned is the parallel child tasks of > any local or remote query. The first x milliseconds (let's call it the OLTP > portion of the query) is [...] 13248 86 24_Re: DB2 V8 for z/OS ZIIP13_Terry Purcell18_tpurcel@US.IBM.COM30_Wed, 1 Oct 2008 21:55:01 +0000348_utf-8 For local queries: Parent task - 0% redirect Child task - 1st "x" millisec 0%, > "x" millisec 80% redirect

For remote: Parent task - 55% redirect Child task - 1st (accumulated) "x" millisec 55%, > "x" millisec 80% redirect

So 65% implies intensive parallelism (especially if there is a good mix of local & remote queries). [...] 13335 124 11_Re: Goodbye14_Andy Lankester26_alankester@CDBSOFTWARE.COM30_Wed, 1 Oct 2008 17:04:06 -0500438_iso-8859-1 Schadenfreude (wonderful word - completely untranslatable in less than about six English words)! But the Angst (Abaracorcix - the village chief in the Asterix books - defines this as 'Peur que le ciel me tombe sur la tete') about the current Zietgeist (monetary meltdown) is driving me straight to the gemuetliche ('cosy' is a pale imitation of its German implications) Weinstube(much more 'cosy' than a wine bar!) - Hic! [...] 13460 53 38_Re: DB2 subsystem checkpoint frequency0_25_jacampbell@ACSLINK.NET.AU30_Thu, 2 Oct 2008 08:11:29 +1000299_US-ASCII After a recent upgrade to V8 CM, one of our systems started doing a CLEANUP on one of our larger LOB tablespaces - which locked it out for an hour or so. This started to happen every day. Not a great time, management started to think it was V8 related and were considering a rollback. [...] 13514 119 24_Re: DB2 V8 for z/OS ZIIP12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 1 Oct 2008 14:23:17 -0700381_iso-8859-1 Terry, Part of the reason at least some of us are not pursuing it is that people within IBM are coming into shops with some types of "tools" and telling management that ZIIP would not pay for itself at all due to the workload that is running. I've seen this case even where probably over 60% of the total workload is coming from DDF or is stored procedure related. [...] 13634 142 51_Re: Determining reads and writes against a database9_Tony Saul30_generalemaillists@YAHOO.COM.AU30_Wed, 1 Oct 2008 17:04:22 -0700702_utf-8 Tom,

SMF101(IFCID0003) can include section QXST (DSNDQXST in SDSNMACS), which contains QXSELECT, QXINSRT, QXUPDTE and QXDELET (DB2 macro names). The header will contain Correlation Id and Authid for identification. Sorry, I don't have MXG so I can't give it's names.

Regards, Tony



--- On Wed, 1/10/08, Kelman, Tom wrote:

> From: Kelman, Tom > Subject: [DB2-L] Determining reads and writes against a database > To: DB2-L@www.idugdb2-l.org > Received: Wednesday, 1 October, 2008, 11:27 PM > I am the mainframe capacity planner in my shop and by no > means an expert > on DB2 or the SMF type [...] 13777 25 35_Franco Vitale is out of the office.13_Franco Vitale19_VITALEFR@UK.IBM.COM30_Thu, 2 Oct 2008 04:02:28 +0100379_US-ASCII I will be out of the office starting 02/10/2008 and will not return until 07/10/2008.

For urgent matters, please email the taskid UK SD-TE DB2 Support/UK/IBM. For problems/changes please use our Manage Now group EGBNR_MFDSIGADB. My mail will not be monitored in my absence. If you need any assistance while I am out of the office please contact Pete Lickiss [...] 13803 608 51_Re: DB2 V8 ZOS - S locking & deadlocking on INSERT.13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM30_Wed, 1 Oct 2008 23:20:28 -0500567_us-ascii Another potential implication of this would be psuedo deleted index rows. Is the table somewhat variable with many insert/deletes of keys that may re-occur over time? If that is the case, even if you are not currently inserting a duplicate value, you may be inserting a value that exists on a psuedo-deleted index key, but DB2 would need to get an s-lock on the data page to verify that the row does not actually exist. A dsn1prnt of the index would verify if this was the case (look for pseudo deleted index entries that point to the page indicated in [...] 14412 146 11_Re: Goodbye10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Thu, 2 Oct 2008 08:36:41 +0200564_US-ASCII The only word I understood was Weinstube......But all the rest sounds great and alchoolic......

It's always a sad thing to loose a good and skilled lister for the knowledge she/he has and because it's one of the good boyz of DB2-L.

Anyway so's is life...enjoy your winestube !!

Max Scarpa









Andy Lankester Sent by: DB2 Data Base Discussion List 02/10/08 00.04 Please respond to DB2 Database Discussion list at IDUG [...] 14559 430 11_Re: Goodbye13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 2 Oct 2008 08:59:31 +0100517_iso-8859-1 You got the most important word, Max. The rest is just noise about Andy's poor excuses for wanting a liquid refreshment. Mind you, is there such a thing as a poor excuse for a drink? Naaahhh...





Bis (not much, hopefully) später, Andy.









Raymond





From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Max Scarpa Sent: 02 October 2008 07:37 To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Goodbye [...] 14990 221 11_Re: Goodbye10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Thu, 2 Oct 2008 12:14:07 +0200414_UTF-8 Hi Raymond

Thank you so much for your clarification. Luckily I didn't miss the 'core' of the problem as I bought some wines in a winestube some months ago...Lagrein, St. Magdalener, Gewurtztraminer....

Still in my wine cellar, waiting for a good occasion to broach (so says my dictionary) one of them. Maybe here's to Andy (hoping this expression is correct and moreover meaningful....) . [...] 15212 71 43_Re: [z/os] export / import plans / packages0_25_jacampbell@ACSLINK.NET.AU30_Thu, 2 Oct 2008 20:37:31 +1000568_US-ASCII In a separate email, I have sent Avram a copy of Venkat's dbrmcnv (aka unbind) rexx exec's.

Possibly need a little work depending on the DB2 Version. But it should be a start.

James Campbell

On 1 Oct 2008 at 18:11, Avram Friedman wrote:

> I am working on a data center move for a customer who is going from one > facilities manager to another. One of the restrictions the original facilities > manager is trying to place on this project is data moves must be done by FDR > backups done by them for application data only. This [...] 15284 649 39_Re: DPSI Access Confusion - DB2 V8 z/OS16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 2 Oct 2008 07:54:27 -0400323_us-ascii Jay - if the DPSI columns is a subset of the Table Controlled Partitioning LIMITKEY, there is no guarantee DB2 will find the requested data in one partition only - look at this example where the limitkey is three columns and the DPSI has the first two columns:









C1 C2 C3 [...] 15934 128 11_Re: Goodbye12_Martin Hubel17_Martin@MHUBEL.COM30_Thu, 2 Oct 2008 08:35:21 -0400 16063 172 11_Re: Goodbye10_Roger Hecq18_Roger.Hecq@UBS.COM30_Thu, 2 Oct 2008 08:35:40 -0400459_us-ascii You should do like Obelix. Sit down, relax, and enjoy another sanglier. Have a good glass of wine, if you can't have some of Panoramix's potion.



Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andy Lankester Sent: Wednesday, October 01, 2008 6:04 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Goodbye [...] 16236 1303 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Thu, 2 Oct 2008 08:16:18 -0500584_us-ascii Hi Steen,





Just to clarify... I meant data partitioning, not database partitioning which you obviously picked up on. Slip of the pen there.





The table is partitioned by AUTH_TS only so the where clause is not a subset of the limitkey. We have one day per part. As you'll see below the explain shows a sort which is odd since the index supports the sort order. If we change the SQL to span multiple days or be an equal to on AUTH_TS then the sort goes away, but the page range stays. However, we don't believe that the sort is [...] 17540 34 24_Antwort: [DB2-L] Goodbye11_Roy Boxwell16_R.Boxwell@SEG.DE30_Thu, 2 Oct 2008 15:16:46 +0200 17575 1546 39_Re: DPSI Access Confusion - DB2 V8 z/OS14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 2 Oct 2008 14:53:28 +0100460_us-ascii What's the "OPTIMIZE FOR 1 ROW" doing there?





You're telling DB2 to pick the best access path to satisfy a single row - is that what you meant?





Not sure whether it would make that much of a difference, but I doubt that it's helping





I wonder if DB2 is smart enough to know that the AUTH_TS part of your predicate is not qualifying ANY rows from a partition - it matches all rows [...] 19122 104 43_Re: [z/os] export / import plans / packages14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 2 Oct 2008 10:21:48 -0400654_us-ascii I like that. Good guerrilla (sp?) DBAing.

Avram -- let us know how it goes?

--Phil S.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of tim malamphy Sent: Wednesday, October 01, 2008 5:31 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] [z/os] export / import plans / packages

Avram-

I bet you could run db2look against this mainframe from an luw version of db2 and get most of what you need. You'll need DBA authority to bind the plans if they aren't already bound there. Other than that, it would be a pretty stealthy exercise. The site manager [...] 19227 1206 39_Re: DPSI Access Confusion - DB2 V8 z/OS16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 2 Oct 2008 10:53:47 -0400400_us-ascii So the DPSI used with MC=2 (BIN and timestamp range).

The table is partitioned by timestamp, so one partition has all the rows for one day.

I see what you're saying - the DPSI has the rids for those rows that qualify, so why scan the entire partition.

I don't know how important the Clusterratio of 50% is in this scenario when you also have FETCH FIRST 500 ROWS ? [...] 20434 196 11_Re: Goodbye7_Ed Long19_rdhm99a@PRODIGY.NET30_Thu, 2 Oct 2008 07:56:39 -0700342_utf-8 Yet another bit of linguistic piracy.

To Broach in sailing means to capsize violently due to bad manners, bad sailing, or bad luck.

Hopefully, none of the wines in Max's fine cellar respond to his broaching of their repose with similar enthusiasm.

We who yet toil salute the retiree with joy, thanks and envy. [...] 20631 138 51_Re: Determining reads and writes against a database10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 2 Oct 2008 15:07:23 +0000286_utf-8 But then you are still missing Load Log Yes which is much better at generating piles of updates.

Load Log No will of course be hardest to track down even though it can change data the fastest and thus is likely to have the most impact when replicating data real time. [...] 20770 25 24_Re: DB2 V8 for z/OS ZIIP10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 2 Oct 2008 15:12:22 +0000331_utf-8 Well we focus on warehousing queries because that and the dynamic sql via distributed access is all that we get on zIIPs :-)

I attached the numbers for last month. The processes with the high parallel numbers are the warehousing / BI type workloads.

It would be nice if Neon ODBC calls were zIIP eligible. [...] 20796 27 24_Re: DB2 V8 for z/OS ZIIP10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 2 Oct 2008 15:13:55 +0000331_utf-8 Well we focus on warehousing queries because that and the dynamic sql via distributed access is all that we get on zIIPs :-)

I attached the numbers for last month. The processes with the high parallel numbers are the warehousing / BI type workloads.

It would be nice if Neon ODBC calls were zIIP eligible. [...] 20824 20 24_Re: DB2 V8 for z/OS ZIIP10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 2 Oct 2008 15:16:19 +0000774_utf-8 Bah I can't get the attachment to work



______________________________________________________________________

* 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 Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms 20845 29 11_Re: Goodbye19_MALIK, MUNWAR (CTR)31_munwar.malik@ASSOCIATES.DHS.GOV30_Thu, 2 Oct 2008 11:22:09 -0400490_us-ascii Hi,

I have a requirement to set up Row Level Security in Z environment for DB2 tables. I understand the multilevel security within DB2 but my question is what as I need? We manage DB2 security through Topsecret.

Regards

M Malik

______________________________________________________________________

* IDUG 08 Warsaw, Poland * 13-17 October 2008 * http://IDUG.ORG/lsEU * ______________________________________________________________________ [...] 20875 86 39_Re: Multilevel security with Top Secret14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 2 Oct 2008 17:08:59 +0100454_us-ascii Hi Malik

The setup is basically the same as it would be with RACF

1. Define your security hierarchy to Top Secret 2. Define which users have which security labels 3. Create your DB2 table(s) with SECLABEL columns 4. Tag the data with the relevant seclabels

And hit some of the snags that are lurking around for the unwary - such as, what happens when there is a clash between what DB2 security and MLS says you can do? [...] 20962 46 51_Re: DB2 V8 ZOS - S locking & deadlocking on INSERT.13_Jack Campbell23_jackrcampbell@YAHOO.COM30_Thu, 2 Oct 2008 16:52:05 +0000324_utf-8 Missy,

from what I can see these are TWO separate UOW's accessing the same page (00f195db), one had correlation id (TRSNRW03) the other (TRSNRW01)? If this is true then the tran with the s-lock is simply waiting for the x-lock to be released by a commit to ensure the data is consistent before reading it. [...] 21009 69 30_Re: zOS Expensive SET :hostvar12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 2 Oct 2008 17:30:22 +0000406_utf-8 Here are couple of additional thoughts. For elapsed time, some monitoring does not distinguish well between waiting for a transaction to arrive and waiting for the first statement to run. Does the CPU time match the elapsed time or not? If there is a lot of CPU time, then authorization is one possibility. Incremental binds are another we see too often, and you'd expect more IO with that CPU. [...] 21079 91 51_Re: DB2 V8 ZOS - S locking & deadlocking on INSERT.13_Case, Missy J24_Missy.Case@FIRSTDATA.COM30_Thu, 2 Oct 2008 13:06:47 -0500425_utf-8 Jack, There are 2 different threads from the same transaction that's just so the CICS guys could define them as a group (don't ask!). They are inserting & locking & waiting to read a tablespace page. On an insert - we have decided that it could possibly the EVALUNC (set to NO in this system) zparm, as that could cause a wait state. We are planning to test this theory & will keep the list posted of the results. [...] 21171 58 24_Re: DB2 V8 for z/OS ZIIP14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 2 Oct 2008 14:21:22 -0400499_us-ascii Jorg, DB2-L strips attachments, it's aimed at minimizing (a) the load on the server (b) the possibility of transmitting malware

You'll probably have to ship an attachment by individual email to any requestor you want to have it.

--Phil

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jorg Lueke Sent: Thursday, October 02, 2008 11:12 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DB2 V8 for z/OS ZIIP [...] 21230 213 11_Re: Goodbye14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 2 Oct 2008 14:22:33 -0400479_iso-8859-1 There's another meaning to broach, referring to opening and not suitable for a PG-rated forum :-)

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long Sent: Thursday, October 02, 2008 10:57 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Goodbye

Yet another bit of linguistic piracy.

To Broach in sailing means to capsize violently due to bad manners, bad sailing, or bad luck. [...] 21444 337 15_DSNZPARM Survey13_Craig Mullins27_Craig.Mullins@NEONESOFT.COM30_Thu, 2 Oct 2008 13:24:59 -0500342_us-ascii Hello everybody. Sorry for the intrusion, but I have another survey I'd like to ask folks to complete, this time on DSNZPARMs. If you could, please respond to the following questions and send it to me at craig.mullins@neonesoft.com. And thanks to everybody who responded to my previous survey on stored procedures and triggers! [...] 21782 113 24_Re: DB2 V8 for z/OS ZIIP12_Roger Miller19_millerrl@US.IBM.COM30_Thu, 2 Oct 2008 18:40:18 +0000561_utf-8 I don't know people who can make that change. The zIIP story has changed a lot, and that tool was only part of the story in 2006. The more accurate information is on the web:

Stored procedures redirect very little to the zIIP, usually in the 5% to 13% range, with the exception of DB2 9 native remote SQL procedures. The other work that redirects ranges from • some utility index time Usually we don't emphasize utilities, as that time is not during customer peak workload, so there is nothing saved on software. Also, in DB2 9, many of the [...] 21896 1553 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Thu, 2 Oct 2008 14:06:28 -0500392_us-ascii Hi Phil and Steen,





The optimize clause was put there by the developer without our input and hasn't been causing any issues, at least when it was index partitioning which was a different partitioning limitkey. Without it list-prefect does return as would be expected given the clusterratio. We haven't tested without it, but can to see if it helps at all. [...] 23450 431 39_Re: DPSI Access Confusion - DB2 V8 z/OS9_Mike Bell21_mbell11a1@VERIZON.NET30_Thu, 2 Oct 2008 14:26:05 -0500445_us-ascii OPTIMIZE FOR 1 ROW has some special implications - among others it turns off multiiple index and list prefetch. See the admin guide for the full list.

Mike HLS Technologies

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Reavill, Jay Sent: Thursday, October 02, 2008 2:06 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DPSI Access Confusion - DB2 V8 z/OS [...] 23882 49 51_Re: DB2 V8 ZOS - S locking & deadlocking on INSERT.13_Jack Campbell23_jackrcampbell@YAHOO.COM30_Thu, 2 Oct 2008 19:40:07 +0000389_utf-8 Missy,

I have just finished a long effort reducing 16k deadlocks/day to < 300/day.

Things that I found (which may or may not apply to you):

1) If a page has an X-lock, then the "index access" requires an s-lock on the page to check the PUNC (possibly uncommited bit) and CLSN (commit LSN)...check the web there are some nice articles on the how/why of this [...] 23932 247 24_Re: DB2 V8 for z/OS ZIIP10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 2 Oct 2008 20:02:21 +0000774_utf-8 I see. Then maybe the option to add them should be removed to confuse poor people such as myself :)





______________________________________________________________________

* 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 Basic Membership in IDUG, [...] 24180 16 4_Test10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Thu, 2 Oct 2008 20:06:52 +0000 24197 1807 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Thu, 2 Oct 2008 15:14:02 -0500402_us-ascii Well Phil, you're the winner!





Surprisingly to me, in this case running without the optimize clause did the trick. Guess that list-prefetch is more useful than we thought.





It's interesting that this did not cause an issue as an NPI. However, we did have a different partitioning in place and I don't know what the clusterratio was previously. [...] 26005 309 19_Re: DSNZPARM Survey0_18_Pia.Velazco@KP.ORG30_Thu, 2 Oct 2008 13:21:16 -0700633_ISO-8859-1 Hi Craig, my answers in RED.

Best regards, Pia Velazco, Data Management Senior Information Management

Enterprise Engineering Kaiser Permanente Information Technology 626-564-5274 (8-338-5274) Email: pia.velazco@kp.org



NOTICE TO RECIPIENT: If you are not the intended recipient of this e-mail, you are prohibited from sharing, copying, or otherwise using or disclosing its contents. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and permanently delete this e-mail and any attachments without reading, forwarding or saving them. Thank you. [...] 26315 2048 39_Re: DPSI Access Confusion - DB2 V8 z/OS16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM30_Thu, 2 Oct 2008 16:31:37 -0400385_us-ascii It could be interesting to KEEP the OPTIMIZEclause and do a temporary update of the CLUSTERRATIO to be 100. I think you will get the same result as you just did.





Steen Rasmussen CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS [...] 28364 22 42_Re: Performance impact of update statement10_Adin Sofer27_adin.s@MOSTTECHNOLOGIES.COM30_Fri, 3 Oct 2008 05:35:21 +0000773_utf-8 The main question here is whether DB2 checks the updated field content, to see if the content had been changed. Trying to avoid validity check, constraints check Compression etc, in case of same content. Does it?

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________





The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG [...] 28387 604 11_Re: Goodbye10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Fri, 3 Oct 2008 08:46:23 +0200365_ISO-8859-1 Thank you all for clarifications. Every mistake/misunderstanding is due to my poor english (no offensive language/intention at all !!) so blame me but even online dictionaries :-))))

I'll be obliged to open one of these bottles this evening, at dinner with 2 (maybe 3) ladies. Thinking to Andy :-)))

(in Italian) Alla salute di Andy ! [...] 28992 147 8_Re: Test10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Fri, 3 Oct 2008 08:48:10 +0200528_US-ASCII This is only a reply to test

THis is only a reply to test

Luckily today's friday

Luckili today's friday

....................................

Max Scarpa (sorry today ot's a bad day)







Jorg Lueke Sent by: DB2 Data Base Discussion List 02/10/08 22.06 Please respond to DB2 Database Discussion list at IDUG



To DB2-L@www.idugdb2-l.org cc

Subject [DB2-L] Test [...] 29140 233 51_Re: DB2 V8 ZOS - S locking & deadlocking on INSERT.10_Max Scarpa28_Massimo.Scarpa@PHOENIXSPA.IT30_Fri, 3 Oct 2008 09:02:41 +0200293_US-ASCII Just 0,00002 cents: I saw in a recent past deadlocks on updates due to a wrong lock size (bigger than PAGE) AND a machine at 100% (and often CAPPED). Even with a locksize smaller (ROW or PAGE) in some circumstances we had deadlocks as Db2 address spaces were not served quickly. [...] 29374 136 30_Re: zOS Expensive SET :hostvar14_Gwyn Pritchard27_gwyn.pritchard@BARCLAYS.COM30_Fri, 3 Oct 2008 10:00:03 +0100356_- Roger,

Thanks for the pointers - I have just red the Red Book and it makes some interesting considerations. Our packages are all fairly small, and our Bind defaults are Acquire(Use) Release(Commit), Current Data(NO), Valid(Bind) all SQL I this Plan is Static and Binds are fresh (bound in last 3 months). No incremental binds being reported. [...] 29511 72 66_DB2 z/OS v8 - error 00c90101 on tablespace after migration from v714_Martin Flavell24_Martin.Flavell@I-TCS.COM30_Fri, 3 Oct 2008 10:34:44 +0100 29584 269 70_Re: DB2 z/OS v8 - error 00c90101 on tablespace after migration from v79_Stan Hoey22_shoey@CIRCLE-GROUP.COM30_Fri, 3 Oct 2008 11:59:51 +0100407_us-ascii This reason code is almost always a bug and not a physical data issue. I would raise a PMR with IBM.

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Martin Flavell Sent: Friday, October 03, 2008 10:35 AM To: DB2-L@www.idugdb2-l.org Subject: [DB2-L] DB2 z/OS v8 - error 00c90101 on tablespace after migration from v7 [...] 29854 71 42_Re: Performance impact of update statement10_Roger Hecq18_Roger.Hecq@UBS.COM30_Fri, 3 Oct 2008 08:36:58 -0400298_us-ascii I pursued this once with V4 or V5. I determined that DB2 would check the columns being 'updated' to see if the value was changed. If none of the columns was changed, then there was no update. You should be able to do the same exercise using a PE Record Trace and a Log analysis tool. [...] 29926 124 42_Re: Performance impact of update statement14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 3 Oct 2008 13:51:54 +0100304_iso-8859-1 HOW DB2 does this may well be proprietary to IBM, but

as DB2 logs updated rows from FIRST changed byte to LAST changed byte, you can surmise that the columns are not checked one by one, rather the whole row images are compared to see what is different between "before" and "after" [...] 30051 414 35_[JOB POSTING] NYC DB2 DBA for Linux12_Kimberly May28_kim.may@THEFILLMOREGROUP.COM30_Fri, 3 Oct 2008 09:24:49 -0400457_us-ascii Happy Friday Listers -





I have a customer in NYC with an immediate need for the DB2 DBA with the skills listed below. If you are interested please email a current resume off-list to me at kim.may@thefillmoregroup.com. Thanks!





Must have:

Five or more year of working experience as DBA with DB2/UDB and/or DB2/LUW in Linux and windows backed up by the corresponding DB2/LUW DBA certification. [...] 30466 91 30_Re: zOS Expensive SET :hostvar0_24_Mohammad_Khan@BCBSIL.COM30_Fri, 3 Oct 2008 08:50:55 -0500547_us-ascii I'm a little puzzled here. Is this SET statement the only sql statement in this package ? If not then how are you getting the timing data for a specific statement ? AFAIK acounting data does not provide that fine granularity, package level data is the most fine it can get. Are you using something like Strobe or performance trace ? If your tool is simply displaying the accounting data you should keep in mind that some of the items displayed are "for that instant" like SQL or object accessed but timing data is cumulative. Khalid [...] 30558 218 24_Re: DB2 V8 for z/OS ZIIP15_Debabrata Ghosh21_the_ghosh@HOTMAIL.COM30_Fri, 3 Oct 2008 10:04:32 -0400562_Windows-1252

Good Morning:

Mostly Stored procedure run under TCB not a SRB, but I see "CALL , COMMIT, result set" can be run run under ZIIP/ZAAP is this because are they executed under a SRB ?

Ghosh

> To: DB2-L@www.idugdb2-l.org> > I don't know people who can make that change. The zIIP story has changed > a lot, and that tool was only part of the story in 2006. The more accurate > information is on the web:> > Stored procedures redirect very little to the zIIP, usually in the 5% to 13% > range, with the exception of DB2 9 [...] 30777 297 50_CA @ IDUG 2008 - The User Day and pre registration14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 3 Oct 2008 15:49:29 +0100206_us-ascii Hi all,





Apologies for the intrusion of a marketing nature, so if you are NOT going to European IDUG in Warsaw later this month, please hit delete NOW!





31075 325 16_Re: Test [ADMIN]16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM30_Fri, 3 Oct 2008 10:49:54 -0400585_iso-8859-1 Please refrain from posting 'test' message like this, and responding to the same.

imagine if everyone would post test messages thus flooding messages into subscribers.

thanks for your understanding



Robert Galambos CIPP/C

Compuware Senior Technical Specialist IBM Certified Database Associate IBM Certified DB2 9 for z/OS Database Administration Certified Information Privacy Professional/Canada robert.galambos@compuware.com



Tel: +1 905 886 7000 Toll Free: +1 800 263 7189 Fax: +1 905 886 7023 Quebec: +1 877-281-1888 [...] 31401 432 39_Re: DPSI Access Confusion - DB2 V8 z/OS13_Terry Purcell18_tpurcel@US.IBM.COM30_Fri, 3 Oct 2008 14:52:59 +0000559_utf-8 Jay,

Glad you've got a solution, and I definitely agree that list prefetch is not always evil.

But here is the part that is most perplexing. Your prior post stated: "One run pulled back 32 rows taking 59 seconds of in-DB2-time with 58 seconds of I/O wait time and 19,331 getpages."

Since there are only 2 WHERE clause predicates in your SQL, and these are both index matching on the DPSI, then 32 rows should qualify from the index. So where do the 19,331 getpages come from? And why does list prefetch make such a difference? [...] 31834 40 36_Can the luw-id on Z/OS be decifered?12_tim malamphy20_timalamphy@YAHOO.COM30_Fri, 3 Oct 2008 08:46:20 -0700359_us-ascii I'm chasing a lock timeout on Z/OS.

I know how to find the address of the machine in the unix/windows format, but can the luw-id on Z/OS be decifered as well? I understand the 1st two positions are netname and machine name, and the very last part is the token. Is the rest of it meaningful (ie Can I find the machine name or IP address)? [...] 31875 467 40_Re: Can the luw-id on Z/OS be decifered?13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Fri, 3 Oct 2008 17:09:15 +0100390_us-ascii Hmmm...





I think you might be out of luck. This is an extract of a slide from a presentation I gave, originally written/given from a lovely man in the States who knows more about the Log than I do German beers - and that's saying summit:





* LUWID(luwid)

* Three parts - LU Network name / LU Instance number / Commit Sequence [...] 32343 27 22_Scheming About Schemas7_Ed Long19_rdhm99a@PRODIGY.NET30_Fri, 3 Oct 2008 09:29:32 -0700412_us-ascii So, I've got this Oracle schema and I've got a DB2/z schema built from a UDB port of the Oracle schema. However, the UDB port was done long ago and may not have been maintained.

I need to do a structural comparison of DZ and O. Structural to me means do they have the same table names, same columns in tables, same primary keys, foreign keys, same indices, triggers, views and STored Procs. [...] 32371 180 40_Re: Can the luw-id on Z/OS be decifered?14_Adrian Collett24_adriancollett@TISCALI.IT30_Fri, 3 Oct 2008 18:31:31 +0200374_ISO-8859-1 Tim, in a moment of calm before i rush off to the last weekend of the Oktoberfest....

you can normally get the IP address from the first part of the LUWID:

Here is an example from our site:

*LUWID=**GA030175.K810*.*0ABD86133005** * Translate the first character according to this schema: G=0, H=1, I=2, J=3, K=4, L=5, M=6, N=7, O=8, P=9 [...] 32552 148 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Fri, 3 Oct 2008 11:44:59 -0500388_us-ascii Hi Terry,

Good to hear from you. Your questions are right on the button. We were still wondering the same thing. Those maintenance fixes sound like they might be what we truly need. I will definitely pass this information on to our system folks and see if we have those applied. In a way I'm hoping not, since those seem to be directly related to what we are seeing. [...] 32701 61 40_Re: Can the luw-id on Z/OS be decifered?14_Adrian Collett24_adriancollett@TISCALI.IT30_Fri, 3 Oct 2008 19:23:12 +0200562_ISO-8859-1 Tim, just reread your msg and now realise you probably knew all that..... sorry, my brain obviously left for the Oktoberfest before the rest of my body...:-)

Prosit!



tim malamphy ha scritto: > I'm chasing a lock timeout on Z/OS. > > I know how to find the address of the machine in the unix/windows format, > but can the luw-id on Z/OS be decifered as well? I understand the 1st two positions are netname and machine name, and the very last part is the token. Is the rest of it meaningful (ie Can I find the machine name or [...] 32763 188 39_Re: DPSI Access Confusion - DB2 V8 z/OS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 3 Oct 2008 13:50:29 -0400558_us-ascii Jay, have you compared the impact of REOPT(ONCE) on access paths? It sounds like you're using REOPT(VARS) or REOPT(ALWAYS). REOPT(ONCE) should provide you the desired access path while avoiding the massive CPU overhead of rebinding the statements in question for every access.

--Phil S.

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Reavill, Jay Sent: Friday, October 03, 2008 12:45 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] DPSI Access Confusion - DB2 V8 z/OS [...] 32952 211 40_Re: Can the luw-id on Z/OS be decifered?12_tim malamphy20_timalamphy@YAHOO.COM30_Fri, 3 Oct 2008 10:54:42 -0700282_us-ascii A timestamp! I got as far as the 2nd character and realized it wasn't going to match any of our IP addresses, so I quit there.

Google didn't turn up anything (useful) so I turned to you listers. It may not be what I wanted, but at least I know what it is, now. [...] 33164 33 73_Z/OS...how do you automagically identify and cancel threads holding locks12_tim malamphy20_timalamphy@YAHOO.COM30_Fri, 3 Oct 2008 11:07:50 -0700327_us-ascii Problem:A developer uses a gui tool and executes a read-only query against a table (OTG and AQT are the tools). Then they go home. The tool doesn't commit or disconnect. The read locks they hold aren't noticed until it collides with an update statement, and things begin to queue up and we get lots of time-outs. [...] 33198 252 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Fri, 3 Oct 2008 13:30:51 -0500367_us-ascii Hi Phil,

Yes, that was actually the first thing we tried thinking exactly as you have. When the results of that were pretty much the same as reopt(none) we decided to give reopt(always) a try. We figured it would cause more CPU usage, but the amount was far greater than anticipated. It went from a few minutes of CPU to around 16 minutes. OUCH! [...] 33451 37 16_More Data Stores10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 3 Oct 2008 18:28:52 +0000566_utf-8 We have mainframje data stores where we keep a certain date range for day to day processes but we keep historical data for legal purposes. Perhaps BI is looking at 4 years but legal needs the data in perpetuity. Similarly there are cases where OLTP processes may need the last year or two but BI processes need four or seven years of data. The current physical designs in both cases, coming from the 90's, split up the newer and older data into seperate databases, data stores etc. Architects, who tend to be more used to the distributed world, also lean [...] 33489 498 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Fri, 3 Oct 2008 13:42:26 -0500553_us-ascii Hi Terry,

We do not have the 04/2008 maintenance applied. The system folks are wondering if there is a specific APAR or PTF that they can reference? I tried searching the APAR site, but no luck so far.

Thanks again, Jay

------------------------------------------------------------- Jay Reavill DBA Fidelity National Information Services, Inc. 11601 Roosevelt Blvd. St. Petersburg, FL. 33716 Office (727) 227-2144 Mobile (727) 215-5794 Jay.Reavill@fnis.com ------------------------------------------------------------- [...] 33988 20 26_Re: Scheming About Schemas10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Fri, 3 Oct 2008 19:16:07 +0000763_utf-8 If you have an Oracle Transparent Gateway you could create a dblink in Oracle to the mainframe tables. Then you could do the compare on the oracle side.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________





The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have [...] 34009 81 26_Re: Scheming About Schemas14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 3 Oct 2008 20:21:56 +0100572_iso-8859-1 Well, I do happen to know of a modelling tool that will allow a compare between two schemas regardless of where they originated

Take a look at ca.com for ERwin Data Modeller

I'm not sure if they have a trial version, but it might be worth checking

But be warned - it is addictive, and once you've seen the Reverse Engineer (bring a schema into ERwin from just about ANY DBMS) and Complete Compare, you'll be hooked. If you need any more information, I can out you in touch with some very knowledgeable people here at CA (who are quite [...] 34091 93 40_Re: Can the luw-id on Z/OS be decifered?10_Roger Hecq18_Roger.Hecq@UBS.COM30_Fri, 3 Oct 2008 15:27:00 -0400281_us-ascii A few years ago, I was having a problem with DDF threads going indoubt, so I wrote a REXX to recover & terminate these threads. Since I had to decipher the IP Addr, the REXX includes the code to do the calculation. If you would like to have it, I can send it to you. [...] 34185 184 77_Re: Z/OS...how do you automagically identify and cancel threads holding locks12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Fri, 3 Oct 2008 15:36:19 -0400333_US-ASCII Tim, We had a similair thing happening with threads like that. I assume you can cancel those threads manually. What we did was set up an exception process within our monitor that goes out and looks for inactive threads and cancels them after a certain amount of time. Don't know if that would work for you or not. Jeff [...] 34370 125 40_Re: Can the luw-id on Z/OS be decifered?13_Laine, Rogers22_rlaine@WHITNEYBANK.COM30_Fri, 3 Oct 2008 14:46:40 -0500630_us-ascii Tim,

This is what I have determined.



195.21.154.213 C3.15.9A.D5



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq Sent: Friday, October 03, 2008 2:27 PM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Can the luw-id on Z/OS be decifered?

A few years ago, I was having a problem with DDF threads going indoubt, so I wrote a REXX to recover & terminate these threads. Since I had to decipher the IP Addr, the REXX includes the code to do the calculation. If you would like to have it, I can send it to you. [...] 34496 77 77_Re: Z/OS...how do you automagically identify and cancel threads holding locks14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 3 Oct 2008 20:53:53 +0100325_iso-8859-1 If you happen to have ANY of the CA DB2 tools, take a look at Thread Terminator - it's part of the Value Pack so you already own it if you have other CA DB2 tools

I think this can detect idle threads, it can certainly be set up to kill all sorts of other problem threads without any human intervention [...] 34574 134 77_Re: Z/OS...how do you automagically identify and cancel threads holding locks12_tim malamphy20_timalamphy@YAHOO.COM30_Fri, 3 Oct 2008 13:00:39 -0700664_us-ascii What monitor are you using? We're using IBM tools including CQM as well as Omegamon. I'm not aware that this capability exists with them.



--- On Fri, 10/3/08, Jeff Frazier wrote:

> From: Jeff Frazier > Subject: Re: [DB2-L] Z/OS...how do you automagically identify and cancel threads holding locks > To: DB2-L@www.idugdb2-l.org > Date: Friday, October 3, 2008, 12:36 PM > Tim, > We had a similair thing happening with threads like that. I > assume you can > cancel those threads manually. What we did was set up an > exception process > within our monitor that [...] 34709 96 20_Re: More Data Stores14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Fri, 3 Oct 2008 15:52:34 -0400320_us-ascii It might matter if you have NPSIs without dates in them -- that'd substantially increase the cardinality of the indexes in question. Also, do you do any queries where date is necessarily omitted? Because your strategy _requires_ date ranges be included in all queries to avoid scanning the old partitions. [...] 34806 135 39_Re: DPSI Access Confusion - DB2 V8 z/OS13_Terry Purcell18_tpurcel@US.IBM.COM30_Fri, 3 Oct 2008 20:10:49 +0000493_utf-8 Jay,

The APAR I referred to from 04/2008 is PK61826.

It doesn't sound like it's a match for your UPDATE issue, although the 04/2007 fix could have been a more likely hit (although you only mentioned you don't have 04/2008 maint).

From your post, the C index is single column (although FIRSTKEYCARD should equal FULLKEYCARD) so it's likely to be much smaller than the A index. Potentially in production index C has higher NLEVELS than A, but not in development. [...] 34942 368 77_Re: Z/OS...how do you automagically identify and cancel threads holding locks12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Fri, 3 Oct 2008 16:16:02 -0400589_US-ASCII We are using TMON/DB2.





tim malamphy Sent by: DB2 Data Base Discussion List 10/03/2008 04:13 PM Please respond to DB2 Database Discussion list at IDUG



To DB2-L@www.idugdb2-l.org cc

Subject Re: [DB2-L] Z/OS...how do you automagically identify and cancel threads holding locks











What monitor are you using? We're using IBM tools including CQM as well as Omegamon. I'm not aware that this capability exists with them. [...] 35311 97 77_Re: Z/OS...how do you automagically identify and cancel threads holding locks0_24_carol.sutfin@REGIONS.COM30_Fri, 3 Oct 2008 15:33:33 -0500374_US-ASCII Tim,

If these are "DDF" threads, you could just run a -STOP DDF MODE(FORCE)

We ran this before the start of our batch cycle at night and then ran a -START DDF in the morning again as scheduled jobs to fix the same problem several years ago.



Carol Sutfin Corporate DBA Regions Financial Corp. (205)261-5214 carol.sutfin@regions.com [...] 35409 227 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Fri, 3 Oct 2008 15:50:29 -0500416_us-ascii Terry,

Thanks for the APAR number. I wasn't having any luck finding it. I'll still pass it on to our system guys even tho it doesn't seem to be a direct hit. And yes, we do have 04/2007 installed.

I'm glad you thought the firstkeycard and fullkeycard not being equal on the C index seemed a bit odd as well. I wasn't sure what to make of that, but was tossing it up to dups or something. [...] 35637 144 39_Re: DPSI Access Confusion - DB2 V8 z/OS13_Terry Purcell18_tpurcel@US.IBM.COM30_Fri, 3 Oct 2008 21:40:50 +0000469_utf-8 Jay,

So here's why page range is not relevant for a partitioning index.

Assume I have table based partitioning on YEAR:

part 1 (limitkey 2006) YEAR C1 2006 1 2006 2

part 2 (limitkey 2007) YEAR C1 2007 2 2007 3

part 3 (limitkey 2008) YEAR C1 2008 1 2008 3

If you create an index on YEAR, then optimizer will consider it a partitioning index (provided it matches the same column sequencing as the table partitioning). [...] 35782 244 40_Re: Can the luw-id on Z/OS be decifered?12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Sat, 4 Oct 2008 01:58:01 +0200646_iso-8859-1

Roger,

this piece of code sounds like it could be save me quite some time on such incidents. I would appreciate very much if you could forward it to me as well?

Thank you in advance and best regards, Peter

________________________ Peter Suhner AXA Tech Switzerland, DB Engineering









> Date: Fri, 3 Oct 2008 15:27:00 -0400 > From: Roger.Hecq@UBS.COM > Subject: Re: [DB2-L] Can the luw-id on Z/OS be decifered? > To: DB2-L@www.idugdb2-l.org > > A few years ago, I was having a problem with DDF threads going indoubt, > so I wrote a REXX to recover & terminate these [...] 36027 211 39_Re: DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Fri, 3 Oct 2008 22:44:07 -0500611_us-ascii Terry,

Thanks for the explanation. I still have some questions, but that's ok.



Since you'd like to take this off-line, please contact me at jay.reavill@fnis.com or feel free to give me a call at either of the numbers below.

I look forward to hearing for you, Jay

------------------------------------------------------------- Jay Reavill DBA Fidelity National Information Services, Inc. 11601 Roosevelt Blvd. St. Petersburg, FL. 33716 Office (727) 227-2144 Mobile (727) 215-5794 Jay.Reavill@fnis.com ------------------------------------------------------------- [...] 36239 67 85_SV: [DB2-L] Z/OS...how do you automagically identify and cancel threads holding locks13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Mon, 6 Oct 2008 02:23:24 +0200408_iso-8859-1 Tim, We had a similar problem long time ago. In our case got collisions between reorg utility and users with open cursors. Nowadays we use zparm IDTHTOIN=3600 which mean that DB2 automatically will terminate inactive DDF threads which been inactive for 3600 seconds.

Med vänliga hälsningar/Kind regards Olle Broström



Olle Broström Databasteknik, DB2 för z/OS IT Service [...] 36307 451 78_SV: [DB2-L] DB2 z/OS v8 - error 00c90101 on tablespace after migration from v713_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Mon, 6 Oct 2008 02:38:20 +0200431_iso-8859-1 Martin, check if apar PK02371/UK01993 is solved on your system. It looks like the abend occurs during execution of repair utility. DSN1COPY with CHECK option does check the physical consistency of the page set but I don't think DSN1COPY check consistency between all pages.



Med vänliga hälsningar, regards,

Olle Broström





Olle Broström

Databasteknik, DB2 för z/OS [...] 36759 62 40_Re: DB2 z/OS UNLOAD Utility and GRAPHICs20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Mon, 6 Oct 2008 14:10:40 +0300628_US-ASCII Adam, Jack,

DSNTIAUL works with SELECT * FROM IP WHERE IP_USER_ID <>'DMUSER' ;

But not with the UNLOAD Utility.

Cuneyt

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jack Campbell > Sent: Friday, September 26, 2008 7:52 PM > To: DB2-L@www.idugdb2-l.org > Subject: Re: [DB2-L] DB2 z/OS UNLOAD Utility and GRAPHICs > > > Adam > > sorry missed the fact u were using UNLOAD, I tried a sample > DSNTIAUL for > > SELECT CAST('XXXXXXXX' AS GRAPHIC(8)) > FROM SYSIBM.SYSDUMMY1 > WITH UR > > an received a ccsid conversion error - [...] 36822 83 40_Re: DB2 z/OS UNLOAD Utility and GRAPHICs20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Mon, 6 Oct 2008 14:11:10 +0300588_US-ASCII It does not work either.

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of jacampbell@ACSLINK.NET.AU > Sent: Saturday, September 27, 2008 1:24 PM > To: DB2-L@www.idugdb2-l.org > Subject: Re: [DB2-L] DB2 z/OS UNLOAD Utility and GRAPHICs > > > USER_ID <> vargraphic('DMUSER') > ? > > James Campbell > > On 26 Sep 2008 at 14:19, Adam Baldwin wrote: > > > Fellow Listers: > > > > A colleague has asked me for help with the following: > > > > UNLOAD TABLESPACE MYDB.MYTS > > PUNCHDDN TEMP_2 > > UNLDDN TEMP_1 > [...] 36906 58 40_Re: DB2 z/OS UNLOAD Utility and GRAPHICs20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Mon, 6 Oct 2008 14:16:02 +0300604_US-ASCII Adam and the Fellow Listers.

I opened a PMR for this issue. As Soon As I have a solution, I'll inform the community as well.

Best Regards, Cuneyt

> -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Adam Baldwin > Sent: Monday, September 29, 2008 2:42 PM > To: DB2-L@www.idugdb2-l.org > Subject: Re: [DB2-L] DB2 z/OS UNLOAD Utility and GRAPHICs > > > Thanks for the suggestions.... the colleague concerned has > gone off on two > weeks holiday so I can't get him to test the vargraphic > option. Will let you > [...] 36965 19 35_Jodi Murawski is out of the office.0_27_Jodi.Murawski@METAVANTE.COM30_Mon, 6 Oct 2008 08:02:07 -0500775_US-ASCII I will be out of the office starting 10/06/2008 and will not return until 10/07/2008.

______________________________________________________________________

* 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 Basic Membership in IDUG, available at no cost, click [...] 36985 277 47_FW: [DB2-L] DPSI Access Confusion - DB2 V8 z/OS12_Reavill, Jay22_Jay.C.Reavill@FNIS.COM30_Mon, 6 Oct 2008 10:43:53 -0500448_us-ascii Terry,

As a follow-up here are some Omegamon stats from two runs of a select version of the update SQL that's giving us an issue. I ran these using literals for the variables which naturally chooses the A index. To get it to choose the C index I appended an empty string to the acct_no to make it not indexable. The first one listed is using the C index while the second is using the A index that we feel it should be using... [...] 37263 123 20_Re: More Data Stores10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Mon, 6 Oct 2008 20:28:56 +0000449_utf-8 Here the queries are based on date over and over again. Last month, quarter, year, four years, seven years. We should actually implement some DPSI's in at least one of the environments.

I'm just not a fan of relocating data when it;s not necessary and I've seen too many cases wheer data is replciated because "it might" be needed someday. especially on the z/OS side replication seems less necessary especially with data sharing. [...] 37387 99 55_Re: Binding DB2 zOS v8 DSNTIAUL against an LUW database10_Fred Edgar18_fredgarx@YAHOO.COM30_Mon, 6 Oct 2008 21:51:15 +0000727_utf-8 We're having the same issue. I came up empty searching the archives. Does anyone have a solution for this? Or confirmation that it will not work?

TIA Fred Edgar

On Fri, 5 Sep 2008 10:44:22 +1000, David Chapman wrote:

>> >Hello, > >We have been running DSNTIAUL on DB2 zOS to unload data from a remote DB2 LUW database on Unix. However, after upgrading to DB2 zOS v8 full function mode, the bind of the remote DSNTIAUL package fails with the following errors: >-------------------------------------------------------------------------- ----------------------------------------------------------- >SQLRABND > SQLCODE = 20 > SQLSTATE = > SQLERRMT = STRDEL DECDEL RELEASE [...] 37487 101 22_Null on select or -81112_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 6 Oct 2008 14:47:28 -0700443_iso-8859-1 I have a query that I don't totally understand what is happening. Maybe someone out there can explain why I'm getting what seem to me to be strange results:

WITH agname (ag_tbname, tbname ) as (

SELECT DISTINCT COALESCE(AG.AG_TBNAME,'XXXXX') as ag_tbname , syscol1.tbname FROM test.XLAT AG , test.syscoldist syscol1 where SYSCOL1.TBNAME = AG.NA_TBNAME and SYSCOL1.TBOWNER = 'PRODZU' AND AG.NA_DBNAME ='AVPDEXC' ) [...] 37589 111 42_New IBM Technotes (FAQ) about DB2 for z/OS9_DB2usa!!!19_db2usa3@HOTMAIL.COM30_Tue, 7 Oct 2008 05:26:46 -0400530_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, October 6th 2008

Here are several new Technotes (FAQ), available on IBM DB2 for z/OS website:

- Does DB2 normally recover a unit of recovery without using an archive log?- Where can I find additional information about DB2 for z/OS?- Attempting to load more data than fits into the data set- What Systen z features does DB2 for z/OS use?- DESCSTAT subsystem parameter [...] 37701 41 30_Re: zOS Expensive SET :hostvar14_Lisa Ouellette27_Lisa.Ouellette@WACHOVIA.COM30_Tue, 7 Oct 2008 12:03:23 +0000354_utf-8 Gwyn, 3 things:

1) If you get info about how to measure the authorization costs, please post on the listserv as I am interested in the same.

2) I agree you have some other issue - I would suspect the monitoring tool data. Our average elapsed time on a statement like this (z9) is 0.00003 seconds and CPU time is 0.00002 seconds. [...] 37743 319 11_Re: Goodbye24_Aurora Emanuela Dellanno24_aurora.dellanno@CFS.COOP30_Tue, 7 Oct 2008 16:35:57 +0100449_iso-8859-1 errr....

talk about countries separated by a common language, I never heard THAT one - though I can guess...

and since when are we PG anyway? ;-)

Aurora Emanuela Dell'Anno Mainframe DB2 DBA

Service Delivery and Infrastructure, Information Systems The Co-operative Financial Services 1st Floor, CIS Building, Miller St., Manchester M60 0AL tel: 0161 903 4760 cell: 07912 163013 e: aurora.dellanno@cfs.coop [...] 38063 25 55_Re: Binding DB2 zOS v8 DSNTIAUL against an LUW database18_Sandra Lakenburger27_sandra.lakenburger@USDA.GOV30_Tue, 7 Oct 2008 17:26:47 +0000286_utf-8 What about coming up with a second version of DSNTIAUL that is built using the v7 source and DBRM? If you want to execute it from LUW, use that version and if running on the frame, use the version that is built as part of the v8 nfm implementation process. Just a thought... [...] 38089 36 36_CTEs in update and delete statements15_Abe F. Kornelis13_abe@BIXOFT.NL30_Tue, 7 Oct 2008 19:36:47 +0200442_iso-8859-1 Hello all,

The syntax for using Common Table Expressions (CTE's) in DB2 V9 is defined only for SELECT and INSERT statements. Some time ago I needed to write a DELETE where a CTE would have come in quite handy, but...

it turns out that syntax for CTE's is not defined for either UPDATE or DELETE, neither in V8 nor in V9. Is there any chance CTE syntax will be added to the syntax for UPDATE / DELETE statements? [...] 38126 177 19_Re: [FLUFF] Goodbye14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Tue, 7 Oct 2008 13:54:46 -0400573_utf-8 Aurora,

Well, I've never seen anyone swear or get real sexually explicit on this list so I'd say it's PG by custom, rather than ordinance.

(BTW, I read this alternative meaning of "broached" or rather "unbroached" from a passage Robert Anson Heinlein's juvenile-Science-Fiction novel "Glory Road.")

--Phil

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Aurora Emanuela Dellanno Sent: Tuesday, October 07, 2008 11:36 AM To: DB2-L@www.idugdb2-l.org Subject: Re: [DB2-L] Goodbye [...] 38304 33 65_CN=Radhakrishnan Gopinath/OU=CHE/O=INeFunds is out of the office.0_36_Radhakrishnan.Gopinath@IN.EFUNDS.COM30_Wed, 8 Oct 2008 01:36:17 +0530663_US-ASCII I will be out of the office starting 10/08/2008 and will not return until 10/13/2008.

I will respond to your message when I return.

Pls send an email to SDC_Offline_Certification group id for any offline settlement assistance.

_____________

The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by [...] 38338 59 34_[Job Alert] - Oracle DBA job in TX13_Nicole Singer33_nicole.singer@THREEPILLARCORP.COM30_Tue, 7 Oct 2008 13:09:09 -0700730_us-ascii One of our major client needs Oracle DBA in TX. This is temp-to-perm job.

Client needs a person with following skills. Please contact me if you are interested and available.

For fast processing send me your resume with following details

Name:

Contact details:

Work authorization:

Hourly pay Rate:

Location: Plano TX Start date: 20th Oct 2008

Application DBA support experience Understand the table structure and data contained therein Evaluate current and future database features for use in applications Evaluate database for suggestions in the areas of RTI and other efficiency improvements System DBA support experience Backup and recovery methods Disaster [...] 38398 111 55_Re: Binding DB2 zOS v8 DSNTIAUL against an LUW database10_Fred Edgar18_fredgarx@YAHOO.COM30_Tue, 7 Oct 2008 13:53:34 -0700447_iso-8859-1 Sandra,

Excellent suggestion! Simple and elegant. My favorite kind. It worked like a charm. Wish I'd thought of it.

Thanks, Fred

--- On Tue, 10/7/08, Sandra Lakenburger wrote:

From: Sandra Lakenburger Subject: Re: [DB2-L] Binding DB2 zOS v8 DSNTIAUL against an LUW database To: DB2-L@www.idugdb2-l.org Date: Tuesday, October 7, 2008, 12:26 PM [...]