1 IDUGDB2-L.ORG /home/listserv/home/db2-l July 2010, week 1
2 23 64_Auto Reply: DB2-L Digest - 30 Jun 2010 to 1 Jul 2010 (#2010-176)0_27_scott.a.saunders@ORACLE.COM31_Wed, 30 Jun 2010 22:01:44 -0700390_utf-8 From June 21 - 25, I will be on vacation. There will be a delay in responding to your email. For emergencies, I am available by cell phone at 727-687-1566 or contact my manager Pete Vammino at pete.vammino@oracle.com.<br><br>_____________________________________________________________________<br>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA * [...]44_6f54fa60-7f09-4133-8522-5d9ea883a38f@default
26 77 95_AUTO: Leon Katsnelson/Toronto/IBM is out of the office until 03/04/2002. (returning 2010/07/05)15_Leon Katsnelson15_leon@CA.IBM.COM30_Thu, 1 Jul 2010 04:08:58 -0400395_US-ASCII I am out of the office until 2010/07/05.<br><br>This is a long weekend in Canada and I will be out of the office. I will<br>reply to your email when I return. In case of emergency, please call my on<br>my cell phone.<br><br>Note: This is an automated response to your message "[DB2-L] Auto Reply:<br>DB2-L Digest - 30 Jun 2010 to 1 Jul 2010 (#2010-176)" sent on 7/1/10<br>1:01:44. [...]68_OF56E91454.30403395-ON85257753.002CC46C-85257753.002CC46C@ca.ibm.com
104 89 87_=?GB2312?Q?=B4=F0=B8=B4=3A_?=Re: [DB2-L] translate the stmt column of syspackstmt table12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN30_Thu, 1 Jul 2010 16:48:51 +0800696_GB2312 Thanks for all replied. It really helps. Thanks again.<br><br>Jeremy<br><br>"Walters, Paul"<br><Paul.A.Walters@SAL<br>LIEMAE.COM><br>发件人： IDUG DB2-L<br><DB2-L@IDUGDB2-L.OR 收件人：<br>G> DB2-L@IDUGDB2-L.ORG<br>抄送：<br><br>2010-06-30 21:26 主题：<br>请答复 给 IDUG Re: [DB2-L] translate the stmt column of syspackstmt table<br>DB2-L<br><br>Check out this technote from IBM<br>http://www-01.ibm.com/support/docview.wss?uid=swg21203359<br><br>-----Original Message-----<br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jeremy Huang<br>Sent: Wednesday, June 30, 2010 5:30 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: [DB2-L] translate the stmt column of syspackstmt table [...]69_OF23E608B3.644AB1A3-ON48257753.003059DE-48257753.00306BB2@icbc.com.cn
194 73 29_Re: How to shrink tablespaces12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 1 Jul 2010 11:35:50 +0100576_ISO-8859-1 On Thu, Jul 1, 2010 at 3:11 AM, Ray Lopez <raylopez@us.ibm.com> wrote:<br><br>> I agree with Phil. Since your goal is to use DB2 tables instead of<br>> temporary sequential files for the purposes of checkpoint and<br>> restartability, it sounds like you want to start with an empty table each<br>> time. Using DB2 LOAD/REPLACE with input of either a DUMMY dataset or just<br>> an empty one, will quickly and easily set you back to empty and recover the<br>> space. I have used this technique many many times.<br>><br>><br>So what happens if there are multiple [...]60_AANLkTinYtzeQUx3_2Kenzn_PK-ts-HDZ2Wf82kwcXv2y@mail.gmail.com
268 327 29_Re: How to shrink tablespaces13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 1 Jul 2010 05:38:48 -0500693_us-ascii Depends whose Load utility you use but yes, with IBM Load a replace of one table will wipe the entire tablespace.<br><br>You'll only do it once...<br><br>Cheers,<br><br>Raymond<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine<br>Sent: 01 July 2010 11:36<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] How to shrink tablespaces<br><br>On Thu, Jul 1, 2010 at 3:11 AM, Ray Lopez <raylopez@us.ibm.com<mailto:raylopez@us.ibm.com>> wrote:<br>I agree with Phil. Since your goal is to use DB2 tables instead of temporary sequential files for the purposes of checkpoint and restartability, it sounds like you want to start with an empty table each time. [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FB36FFB@PHXCCRPRD04.adprod.bmc.com
596 60 50_Re: translate the stmt column of syspackstmt table11_Diana Nagel16_dknagel@BEPC.COM30_Thu, 1 Jul 2010 07:27:45 -0500535_us-ascii This works ....pretty good.<br><br>SELECT NAME, STMTNO, STMTNOI,<br>CAST(CAST(STMT AS VARCHAR(3500) CCSID 1208) AS VARCHAR(3500) CCSID<br>EBCDIC)<br>FROM SYSIBM.SYSPACKSTMT<br>WHERE NAME = 'TIGPPAAA'<br>AND NOT (STMTNO=0 AND SEQNO=0 AND SECTNO=0)<br>ORDER BY STMTNO, STMTNOI;<br><br>-----Original Message-----<br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jeremy Huang<br>Sent: Wednesday, June 30, 2010 4:30 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: [DB2-L] translate the stmt column of syspackstmt table [...]56_6454C4DAE553484980557DB37B06BC030CD8C749@HDQ153.bepc.net
657 99 31_Re: Nested loop vs. Hybrid join10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 1 Jul 2010 14:34:07 +0200369_US-ASCII Hi<br><br>I saw in the past similar cases and most of them were caused by small<br>pools (usually RID/SORT) and/or less powerful LPAR (less CPU power) but a<br>number of them were caused by missing indexes<br><br>and/or different definitions of objects (some of them a little 'hidden'<br>as lock size, package bind parameters , etc.) in test environment. [...]66_OFA8ECB9BE.B7FDA761-ONC1257753.0042162B-C1257753.00450336@cesve.it
757 55 19_A question on REORG23_Parvathavardhini Kannan21_pkannan@INAUTIX.CO.IN30_Thu, 1 Jul 2010 18:19:05 +0530404_US-ASCII Esteemed Listers,<br><br>We have DB2 V8 on zOS.<br><br>We have a table that has 100 Million rows, we will be adding a million row<br>to the table by a IBM load job scheduled daily.<br>At the end of the week we will purge around 4 - 5 million rows from the<br>table based on specific criteria.<br>As of now we use IBM REORG with Discards to accomplish the weekly purge of<br>5 million rows. [...]71_OFA87F4395.C648428F-ON65257753.0044B8F3-65257753.004669B3@inautix.co.in
813 95 29_Re: How to shrink tablespaces12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 1 Jul 2010 14:04:15 +0100420_windows-1252 On Thu, Jul 1, 2010 at 11:38 AM, Bell, Raymond <Raymond_Bell@bmc.com> wrote:<br><br>> Depends whose Load utility you use but yes, with IBM Load a replace of<br>> one table will wipe the entire tablespace.<br>><br>><br>><br>> You抣l only do it once...<br>><br>><br>><br>> Cheers,<br>><br>><br>><br>><br>><br>> Raymond<br>><br>><br>><br>So are there any non-IBM utilities that can "shrink" a tablespace. [...]60_AANLkTikVc31n7gxQX1npQH3GEXgEvIDOAASePHclQyLl@mail.gmail.com
909 674 58_Re: Free Tools for DB2 Help You Improve Access Performance13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 1 Jul 2010 09:12:13 -0400599_us-ascii I'm thinking that this may be another symptom of IBMs worries about anti-trust issues<br><br>If they are GIVING AWAY software that others are charging for, it could be viewed as anti-competitive - which is why they started charging for the utilities all those years ago<br><br>Oh, and as well as CA Detector (rather than CA Insight)/BMC Apptune there are other (perhaps better value?) options out there for SQL tuning :)<br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk> [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E819C5@MAILR004.mail.lan
1584 24 21_(OT) Happy canada day0_19_galambos@ROGERS.COM30_Thu, 1 Jul 2010 13:20:45 +0000410_- For all those canadians who live in igloos 6 months of the year ;-)<br><br>Have a happy and safe canada day<br>"Age is an issue of mind over matter. If you don't mind, it doesn't matter." ~ Mark Twain<br>mobile email sent from my blackberry.<br><br>_____________________________________________________________________<br>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA * [...]105_1173863755-1277990446-cardhu_decombobulator_blackberry.rim.net-1959084496-@bda274.bisx.prod.on.blackberry
1609 26 21_ADMIN subject headers0_19_galambos@ROGERS.COM30_Thu, 1 Jul 2010 13:26:20 +0000309_- I noticed that many of you, when you start a new thread, fail to indicate on the subject line what type of DB2 is the question/statement on (I.E. LUW, z/OS)<br><br>I encourage you to do so, as then the right answer for the right platform will be easier to be obtained from the experts in the platform. [...]103_657219024-1277990781-cardhu_decombobulator_blackberry.rim.net-880089126-@bda274.bisx.prod.on.blackberry
1636 88 23_Re: A question on REORG13_David Simpson22_dsimpson@THEMISINC.COM30_Thu, 1 Jul 2010 09:35:56 -0400329_us-ascii Only way I can think of would be to come up with a partitioning strategy<br>that places the "new" rows in different partitions from the "old" ones.<br>You could then reorg only the partitions with the "new" rows and use a<br>discard. There is no way (other than partitioning) to reorganize only<br>part of a table. [...]67_7EC87535E7581C4C890F5BF1CF9A9374029B7053@THEMISSRV1.themisinc.local
1725 43 29_Re: How to shrink tablespaces10_Joe Geller21_joerg6666@HOTMAIL.COM30_Thu, 1 Jul 2010 09:53:12 -0400527_UTF-8 Jim,<br>Are you saying that you do have multiple tables in the tablespace? I hope it is a segmented<br>tablespace, not a simple one. Why don't you want to do a reorg? If you have multiple<br>tables and only one of them has had the rows deleted and needs to be shrunk, then reorg is<br>your best option. The pages of the different tables are intermixed in the tablespace (only one<br>table per segment, but the segments are intermixed), so to actually reclaim space from the<br>VSAM file, the reorg would do the job. [...]53_4243193871195941.WA.joerg6666hotmail.com@www.idug.org
1769 95 39_AW: [DB2-L] Nested loop vs. Hybrid join35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 1 Jul 2010 15:57:04 +0200825_iso-8859-1 Hi<br><br>Sometimes I have seen that due to maintenance. Have both systems the same maintenance level?<br><br>Mit freundlichen Gren<br>Walter Jani遝n<br><br>ITERGO Informationstechnologie GmbH<br>Anwendungsentwicklung<br>Laufzeitarchitektur<br>Victoriaplatz 2<br>40477 D黶seldorf<br>mailto:walter.janissen@itergo.com<br><br>Vorsitzender des Aufsichtsrates: J黵gen Vetter<br>Gesch鋐tsf黨rung: Dr. Bettina Anders (Vorsitzende),<br>Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Sch鰊.<br>Sitz: D黶seldorf, Handelsregister: Amtsgericht D黶seldorf, HRB 37996<br><br>-----Urspr黱gliche Nachricht-----<br>Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Le Hoang<br>Gesendet: Mittwoch, 30. Juni 2010 18:54<br>An: DB2-L@IDUGDB2-L.ORG<br>Betreff: [DB2-L] Nested loop vs. Hybrid join [...]43_DB2-L%201007010957156241.1CD3@IDUGDB2-L.ORG
1865 315 29_Re: How to shrink tablespaces14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 1 Jul 2010 10:07:40 -0400391_us-ascii Jim,<br>If you need to regularly reclaim space on one table and your tablespace contains many tables... move the table to its own tablespace and do the LOAD/REPLACE. Otherwise, you have to DELETE FROM target-table and then REORG, or REORG with DISCARD FROM TABLE target-table.<br><br>IBM has no way to release unused space from a VSAM dataset, so you're otherwise out of luck. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46A88@MAIL02.fisalan.nycnet
2181 103 31_AW: [DB2-L] A question on REORG35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 1 Jul 2010 16:12:05 +0200508_iso-8859-1 Hi<br><br>That's not possible with the standard reorg utility and I don't know, if there is any vendor available, which has this functionality.<br><br>But is your tablespace partitioned? If yes, may be you can reorg discard only one partition and hopefully there are no NPIs.<br><br>Mit freundlichen Gren<br>Walter Jani遝n<br><br>ITERGO Informationstechnologie GmbH<br>Anwendungsentwicklung<br>Laufzeitarchitektur<br>Victoriaplatz 2<br>40477 D黶seldorf<br>mailto:walter.janissen@itergo.com [...]43_DB2-L%201007011012146953.1CD6@IDUGDB2-L.ORG
2285 69 40_AW: [DB2-L] Ascending/Descending Indexes35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Thu, 1 Jul 2010 16:15:15 +0200332_iso-8859-1 It depends, because to read indexes backwards if more expensive than reading them forward. So I have seen cases, where the optimizer decided to do a sort instead of reading backwards.<br><br>DB2 can read pages backwards, but in between a page, the rids must also be read backwards, which I think is the cost factor. [...]43_DB2-L%201007011015267000.1CDA@IDUGDB2-L.ORG
2355 68 25_Re: (OT) Happy canada day12_Martin Hubel17_Martin@MHUBEL.COM30_Thu, 1 Jul 2010 10:29:09 -04000_43_DB2-L%201007011029279105.1CDC@IDUGDB2-L.ORG
2424 395 29_Re: How to shrink tablespaces13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 1 Jul 2010 10:26:42 -0500616_us-ascii Jim,<br><br>You're talking about lowering a cluster's HURBA, right? And getting rid of any extents allocated that aren't really needed with the volume of data actually present, yes? That's only really possible if you delete/define the cluster again and repopulate it somehow. And a reorg utility worth its salt will do that for you, with minimum impact to concurrent users, keeping the data you want and junking the rest. There's nowt wrong with doing a table-based Load Replace (to clear the data in that table) and reorganising the table to reclaim extents. Or unloading the 'other' data and doing a [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FBB2798@PHXCCRPRD04.adprod.bmc.com
2820 582 29_Re: How to shrink tablespaces13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 1 Jul 2010 11:40:55 -0400503_us-ascii I've been trying to restrain myself, and apologies if the original poster explained the reasoning (I must have missed it)<br><br>BUT, I can't help myself any longer<br><br>WHY DO YOU CARE?<br><br>It's "only" DASD<br><br>Unless we're talking hundreds (or thousands, or tens of thousands) of cylinders, why not just ignore it?<br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk> [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E819DE@MAILR004.mail.lan
3403 108 29_Re: How to shrink tablespaces12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 1 Jul 2010 16:50:47 +0100543_windows-1252 On Thu, Jul 1, 2010 at 4:40 PM, Phil Grainger <phil.grainger@cogito.co.uk>wrote:<br><br>> I抳e been trying to restrain myself, and apologies if the original poster<br>> explained the reasoning (I must have missed it)<br>><br>><br>><br>> BUT, I can抰 help myself any longer<br>><br>><br>><br>> WHY DO YOU CARE?<br>><br>><br>><br>> It抯 搊nly� DASD<br>><br>><br>><br>> Unless we抮e talking hundreds (or thousands, or tens of thousands) of<br>> cylinders, why not just ignore it?<br>><br>> *Phil Grainger*<br>> Cogito Ltd.<br>> [...]60_AANLkTilNjeb8AsJOMVHQsj43ParuW_QNoHybYALMlh_-@mail.gmail.com
3512 388 29_Re: How to shrink tablespaces13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Thu, 1 Jul 2010 11:14:29 -0500475_us-ascii Ah. The 'O' word. So I guess you're talking non-mainframe here, where space management is an entirely different kettle of fish. Containers, hundreds of TBs per TS, etc. Diff'rent strokes for diff'rent folks I guess. Dunno what the tin-pot space management options are - it's been awhile since I played in that world. Maybe some more recently experienced UDB folk can chip in but I suspect if that's all the Land of O folk can offer, well, best leave it to 'em. [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FBB2848@PHXCCRPRD04.adprod.bmc.com
3901 67 29_Re: How to shrink tablespaces14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Thu, 1 Jul 2010 12:21:16 -0400413_UTF-8 As a famous US senator, Evert Dirkson, who served for the same state as our current President said:<br>"A billion dollars here a billion dollars there, then eventually you are talking about a lot of money"<br><br>I think the real problem, here is choosing a technology (A DBMS) that is designed for storage and retrieval of data long term with integrity and recoverability for a short term repository. [...]56_0369975571205814.WA.ibmsysproggeeksites.com@www.idug.org
3969 130 29_Re: How to shrink tablespaces10_Max Scarpa16_mscarpa@CESVE.IT30_Thu, 1 Jul 2010 18:23:52 +0200572_UTF-8 I'm following this thread but (probably I missed something as well) I'm<br>not able to understand if the problems is extents consolidation,<br>reclaiming space (pages) not used any more (after deletes, rows move as<br>overflow etc) or something else (compaction ? compression ?).<br><br>As I don't know ORACLE, can anyone say if I use ALTER statement/command to<br>reclaim space how long the process lasts (immediate ? x minutes ?) ? I<br>asked it because in Informix (if I remember well) there's a similar<br>command to consolidate 'chunks' not used any more. [...]66_OF6B3CCBC7.FB2FA14F-ONC1257753.0058100F-C1257753.005A0BDE@cesve.it
4100 102 29_Re: How to shrink tablespaces12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Thu, 1 Jul 2010 17:29:05 +0100589_windows-1252 On Thu, Jul 1, 2010 at 5:14 PM, Bell, Raymond <Raymond_Bell@bmc.com> wrote:<br><br>> Ah. The 慜� word. So I guess you抮e talking non-mainframe here, where<br>> space management is an entirely different kettle of fish. Containers,<br>> hundreds of TBs per TS, etc. Diff抮ent strokes for diff抮ent folks I<br>> guess. Dunno what the tin-pot space management options are � it抯 been<br>> awhile since I played in that world. Maybe some more recently experienced<br>> UDB folk can chip in but I suspect if that抯 all the Land of O folk can<br>> offer, well, best leave it to [...]60_AANLkTinwmSDLwmrrZGtArSNj8a1m4kophA4dSKI3kSB5@mail.gmail.com
4203 24 29_Re: How to shrink tablespaces11_Ted MacNEIL18_eamacneil@YAHOO.CA30_Thu, 1 Jul 2010 17:14:34 +0000444_Windows-1252 >I care because Oracle can do it and I get爐old it's爋ne of the reasons why Oracle is better than DB2.<br><br>So, Oracle is 'better' because, at pennies, a gigabyte, you might save a dime?<br><br>-<br>I'm a SuperHero with neither powers, nor motivation!<br>SHAZAM!<br><br>_____________________________________________________________________<br>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA * [...]103_592210864-1278004456-cardhu_decombobulator_blackberry.rim.net-836011750-@bda026.bisx.prod.on.blackberry
4228 70 35_Re: Tablespace in STOP PENDING Mode0_22_DB2information@AOL.COM28_Thu, 1 Jul 2010 13:30:24 EDT772_US-ASCII Prakash,<br>You may wish to view _www.recoveryknowledge.com_<br>(http://www.recoveryknowledge.com) and look at Health Check Interface for DB2 to view all pending<br>modes and automate the commands.<br><br>Ed.<br>Recovery Knowledge<br>The DB2 Recovery Automation People<br>_www.recoveryknowledge.com_ (http://www.recoveryknowledge.com)<br><br>_____________________________________________________________________<br>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *<br><br>* Your only source for independent, unbiased, and trusted DB2 information. *<br>** The best DB2 technical sessions in the world<br>** NEW - IBM hands-on labs -> no additional charge<br>_____________________________________________________________________ [...]31_32e00.4d79f502.395e2ab0@aol.com
4299 25 19_DB2 z/OS encryption10_Gregg Hess28_gregg.hess@ERIEINSURANCE.COM30_Thu, 1 Jul 2010 13:49:07 -0400472_UTF-8 Does anyone have experience with the Integrated Cryptographic Services Facility (ICSF) from IBM. It is on our list of a possible option for data encryption. I would be interested in your comments on the implementation, procedures and/or anything else you might think is important about this product.<br><br>_____________________________________________________________________<br>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA * [...]60_2933548474297548.WA.gregg.hesserieinsurance.com@www.idug.org
4325 28 31_Re: Nested loop vs. Hybrid join13_Terry Purcell18_tpurcel@US.IBM.COM30_Thu, 1 Jul 2010 14:00:25 -0400367_UTF-8 As has been highlighted, optimizer takes CPU speed, BP sizes, RID pool etc into consideration in addition to statistics. Since hybrid join (HBJ) uses the RID pool, this is a significant influencing factor. Optimizer will not choose a HBJ if it is estimated to exceed a percentage of the RID pool - thus a smaller RID pool is more likely to disqualify HBJ. [...]50_0038670570500022.WA.tpurcelus.ibm.com@www.idug.org
4354 614 29_Re: How to shrink tablespaces12_Isaac Yassin20_yassini@BEZEQINT.NET30_Thu, 1 Jul 2010 21:14:44 +0300469_UTF-8 Hi<br><br>Oracle does 鈥渋nsert鈥� + 鈥渄elete鈥� when it is shrinking a table.<br><br>If you want that 鈥�.<br><br>As for 鈥渢runcate鈥� 鈥� it does not release the space. However, next time you run the program it鈥檒l allocate the space again.<br><br>So 鈥� you may save space between runs.<br><br>In order for 鈥渓oad/replace鈥� to free space you need the primary allocation to be really small and then you鈥檒l pay at run time for allocating scondaries. [...]35_001d01cb1949$4813b830$d83b2890$@net
4969 42 29_Re: How to shrink tablespaces10_Joe Geller21_joerg6666@HOTMAIL.COM30_Thu, 1 Jul 2010 13:49:46 -0400552_UTF-8 I think it may go back to what is a tablespace in the two environments. As someone said,<br>in the LUW world tablespaces or containers are a very different animal than in DB2 z/OS<br>and having multiple tables in one of those is not a problem. In the z/OS world, it is<br>vastly better to have one table per tablespace. Yes, it is more work to maintain your<br>product that way. And many Peoplesoft etc. shops have the headache of splitting<br>apart the delivered DDL so that each table has it's own tablespace. But that is the<br>way to go [...]53_8204409063713789.WA.joerg6666hotmail.com@www.idug.org
5012 259 61_Re: Question on character representation when doing an insert13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM30_Thu, 1 Jul 2010 14:28:51 -0400695_us-ascii I want to thank all for the input for this issue it is as always greatly appreciated.<br>They were to execute a specific package to get this represented correctly.<br>All is now well.<br><br>Thanks<br><br>Bob Knight<br><br>________________________________<br><br>[http://www.idug.org/images/stories/IDUG_Corporate/idug_corp_728.gif]<http://www.idug.org><br><br>[http://www.idug.org/images/stories/IDUG_Corporate/idug_conf_728.gif]<http://www.idug.org/db2-conferences-list.html><br><br>________________________________<br><br>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.<http://www.idug.org/register> [...]75_8902ECFB06C73440874A90F82900BA0E1B4E2289F8@osgood.rdba.remotedbaexperts.com
5272 50 44_Re: AW: [DB2-L] Ascending/Descending Indexes13_Terry Purcell18_tpurcel@US.IBM.COM30_Thu, 1 Jul 2010 15:56:33 -0400596_UTF-8 Walter and all.......a couple of useless pieces of information for you on this topic.<br><br>One reason as to why reading an index in reverse has a minor performance penalty compared with reading forwards is prefetch I/O performance - related to the control unit prestaging into the cache. Regardless of DB2's usage of prefetch, if the control unit detects sequential access, then it prestages into the DASD cache, and DB2 prefetch then benefits because it then finds those pages in the DASD cache. The behavior on DB2 is the same whether the pages are found in the DASD cache or they [...]50_2450779363902128.WA.tpurcelus.ibm.com@www.idug.org
5323 126 14_Question on RI13_Jose de Bedos20_josedebedos@LIVE.COM30_Thu, 1 Jul 2010 17:50:11 -0300323_Windows-1252 Hi list,<br><br>I have 2 tables, say T1 and T2. T1 has primary key with columns a and b, and T2 has a foreign key to T1, and an index (for a primary key) with columns a, b and c.<br><br>I couldn't resolve from the manuals if this index serves for a delete on table T1 with cascade on T2, because it says: [...]43_BLU130-W15C109459BD384BFDA80E2CECD0@phx.gbl
5450 366 29_Re: How to shrink tablespaces13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Thu, 1 Jul 2010 17:43:06 -0400599_us-ascii OK, but DB2 doesn't NEED you to do this at all., so why is Oracle better?<br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk><br><br>Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.<br>8-12 November 2010, Vienna<br>Learn more at http://www.idug.org<http://www.idug.org/><br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim McAlpine<br>Sent: 01 July 2010 16:51<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] How to shrink tablespaces [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81A0F@MAILR004.mail.lan
5817 70 18_Re: Question on RI14_Larry Kintisch17_LKint@VERIZON.NET30_Thu, 1 Jul 2010 18:30:31 -0400537_us-ascii Hi Jose,<br>In both DB2 z/OS and DB2 LUW for at least the past 10 years DB2 is<br>smart enough to use an {A,B,C} index to check RI for parent key {A,B}<br>deletes. I think the reference you quoted could be clarified as<br>"..must lead with the identical columns in the same order..." . You<br>cannot use {A,C,B} or {C,A,B} or {B,A,C} to support RI checking for {A,B}.<br>If I'm wrong about any of the DB2 implementations will someone<br>please correct me.<br>Hope this helps. Larry Kintisch Pres. ABLE Information Services [...]40_0L4W00AK8H720GP0@vms173011.mailsrvcs.net
5888 560 61_Re: Question on character representation when doing an insert12_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Fri, 2 Jul 2010 01:23:43 +0200467_Windows-1252 Bob, Aurora,<br><br>just to avoid any misdirection: IMHO this is outside the topic of Unicode, but a mere codepage problem. Using a local codepage for our mainframes (Swiss CP500), we had seen comparable problems long before anyone talked of Unicode, inside and outside of DB2.<br><br>Four specific EBCDIC codes were particularly difficult at our site, as they displayed differently: [, ], � and !. Plus, of course, some of the German Umlauts, etc. [...]43_SNT107-W5384E0996783223BA05A3EF1CD0@phx.gbl
6449 457 60_What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM30_Thu, 1 Jul 2010 20:22:09 -0500460_ISO-8859-1 Hello everyone,<br><br>We have a situation involving a significant performance discrepancy<br>between executing the same SQL via distributed vs. local. We would<br>expect to see some degradation in distributed, but this is out of the<br>norm. It happens primarily when the system is busy. However, the<br>system is not being pushed to its limits (no where near the MIP limit)<br>and the WLM service class it's running under is very aggressive. [...]62_C90B7164E1511D499B3C479D8B7CEC9202128954@CMBFISLTC07.FNFIS.COM
6907 402 57_Re: DB2-LUW: Block-based bufferpools and automatic tuning16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU30_Fri, 2 Jul 2010 09:51:09 +0800365_us-ascii Hi Sally,<br><br>Welcome to the 'z/OS to LUW' club! There are things you will love about LUW and things you will hate, but you'll survive :)<br><br>The block-based portion of the buffer pool is fixed and is not subject to any of the automatic tuning.<br>In your example the block size of 800 will stay the same even if your BP grows to 100,000 pages. [...]73_8522673947DF7D4394C962BFE441CD2705099DDF24@PER-EXCHMBX.win2k.iinet.net.au
7310 164 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V89_Mike Bell21_mbell11a1@VERIZON.NET30_Thu, 1 Jul 2010 21:09:06 -0500568_us-ascii The first thing to check is the WLM specification that DB2DIST is running<br>under. In DB2 processing, the sync IO is driven by the calling address<br>space and async IO (prefetch) is issued under DBM1.<br><br>You have 3 starting questions<br>1. buffer pool hits? - if DIST isn't finding the data in the bufferpool, it<br>is time to talk the nice people at DB2 support.<br>2. sync IO wait - if they are different between local and DIST, you have a<br>major problem - remember that local in say SPUFI is only a powerfull as your<br>TSOID which usually on [...]43_F3D48E98743841D39C4F973D61D7EFDD@mikelaptop
7475 254 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM30_Thu, 1 Jul 2010 21:39:13 -0500597_us-ascii Thanks Mike.<br><br>We're actually working directly with one of our system guys who manages<br>WLM in trouble shooting this so we've got a pretty good handle on how<br>things are setup.<br><br>All of the DB2 address spaces are running at the appropriate WLM service<br>class level (one of the highest on the machine).<br><br>For the local test we temporarily assigned my TSO id to the same low<br>level service class as we used for the distributed test which was also<br>done under my id connecting from one subsystem to another. So we made<br>the playing field as equal as possible [...]62_C90B7164E1511D499B3C479D8B7CEC9202128960@CMBFISLTC07.FNFIS.COM
7730 156 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V814_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Thu, 1 Jul 2010 22:47:17 -0400578_UTF-8 Is auto screen refresh on for Omegamon?<br>If so what is the refresh interval.<br><br>Where are you seeing these percentage numbers ... some sort of graphical delay display?<br>If so check the dexan short interval collection period.<br><br>Best wishes<br>Avram Friedman<br><br>PS I am the father of Omegamon DB2 but never involved in product development, support or marketing.<br>Introduced the then Candle VP of technology (Marty Sprinzen) to DB2 before first customer ship and with Jim Woodhill higered the first Omegamon DB2 developer who was Jan Hankoweski (sp?) [...]56_6416027339857367.WA.ibmsysproggeeksites.com@www.idug.org
7887 209 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM30_Thu, 1 Jul 2010 21:53:44 -0500876_us-ascii Thanks Avram.<br><br>I'll have to check on the Omegamon settings. However, we were viewing<br>these time counters interactively thru the active thread Omegamon<br>interface and hitting ENTER to manually refresh the screen.<br><br>Thanks again,<br>Jay<br><br>-------------------------------------------------------------<br>Jay Reavill<br>DBA<br>Fidelity National Information Services, Inc.<br>11601 Roosevelt Blvd.<br>St. Petersburg, FL. 33716<br>Office (727) 227-2144<br>Mobile (727) 215-5794<br>jay.c.reavill@fnisglobal.com<br>-------------------------------------------------------------<br><br>-----Original Message-----<br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Avram<br>Friedman<br>Sent: Thursday, July 01, 2010 10:47 PM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] What is DB2DIST doing?!?! - A Performance Quandary<br>- z\OS V8 [...]62_C90B7164E1511D499B3C479D8B7CEC9202128967@CMBFISLTC07.FNFIS.COM
8097 1561 72_FW: [DB2-L] What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM30_Thu, 1 Jul 2010 22:18:30 -0500418_ISO-8859-1 In case this helps, here are the service classes that we have DB2<br>assigned to. I realize you don't know what's behind them, but at least<br>you can see that they are all at the same level except for IRLM which is<br>slightly higher.<br><br>JOBNAME ,PROCSTEP,STEPNAME,JOB# ,C,-CPU%-,ECPU% ,CPU-TIME ,ECPU TIME<br>SRVCLASS<br><br>DB2AMSTR IEFPROC DB2AMSTR 10482 0.80 0.79 62184.36 62184.36<br>STC160C [...]62_C90B7164E1511D499B3C479D8B7CEC9202128970@CMBFISLTC07.FNFIS.COM
9659 83 36_Antwort: [DB2-L] DB2 z/OS encryption17_Michael Klaeschen34_Michael.Klaeschen@DEUTSCHERRING.DE30_Fri, 2 Jul 2010 07:14:04 +0200615_US-ASCII Since a couple of weeks we are implementing various products to implement<br>secure sign on. Most of them deal with cryptography, so ICSF is one major<br>aspect of it. From my experiences, there are just very few people to<br>expect answers from regarding questions on ICSF in specific. And there is<br>quite a lot of misunderstanding on cryptography and related topics like<br>SSL/TLS around. But IBM manuals as well as common literature about System<br>SSL, RACF, HTTP, LDAP etc is very comprehensive. So when you just read in<br>the books you should be very comfortable. However, I cannot say this [...]74_OFC15C72E2.C3AEEE6E-ONC1257754.0019F530-C1257754.001CCC7C@deutscherring.de
9743 293 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V814_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Fri, 2 Jul 2010 03:02:57 -0400538_UTF-8 If you are viewing by hitting enter that would rule out the auto update interval.<br>It does not rule out the DEXAN interval.<br><br>Your original question was. Why did the data reset every 20 seconds?<br>I don't think WLM would be the cause of the reset;<br><br>You also never answered my question about just. What is it that you are looking at?<br>Is it a little graphical display that shows the percentage of time for a monitored workload in various stages like?<br>Using CPU<br>Waiting CPU<br>SYNC I/O<br>PRE FETCH<br>etc. [...]56_1919889397393941.WA.ibmsysproggeeksites.com@www.idug.org
10037 79 29_Re: How to shrink tablespaces12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Fri, 2 Jul 2010 09:31:42 +0100397_ISO-8859-1 On Thu, Jul 1, 2010 at 6:14 PM, Ted MacNEIL <eamacneil@yahoo.ca> wrote:<br><br>> >I care because Oracle can do it and I get told it's one of the reasons why<br>> Oracle is better than DB2.<br>><br>> So, Oracle is 'better' because, at pennies, a gigabyte, you might save a<br>> dime?<br>><br>> -<br>> I'm a SuperHero with neither powers, nor motivation!<br>> SHAZAM!<br>><br>><br>> [...]60_AANLkTinHtLzAjSmgaSXh9gIirOmOB9vFLTAZ6tqId6OM@mail.gmail.com
10117 223 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V811_Mike Turner19_mike.turner@GMX.COM30_Fri, 2 Jul 2010 10:12:35 +0100679_iso-8859-1 The WLM specification for DB2DIST only affects work performed under a DB2<br>service task. the user work (SQL) runs under separate goals defined for the<br>Enclave.<br><br>For work originating from the network, the DB2 Distributed Database Facility<br>(DDF) address space creates a WLM independent Enclave. Classification rules<br>defined for subsystem type DDF are used to assign the work to a Service<br>Class. You must define Classification Rules with Subsystem Type DDF and at<br>least assign an overall default Service Class. Otherwise all DDF work is<br>assigned to Service Class SYSOTHER, which will not provide good performance.<br>You will probably want [...]44_6435F6C17594434490100EF329C7C55A@ToshibaA300
10341 122 32_Delay reporting and many threads14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Fri, 2 Jul 2010 05:54:00 -0400589_UTF-8 A diffrent poster asked a question about why usage percents seemed to favor, in his case doing I/O for DIST threads.<br>like<br>05 percent using cpu<br>25 percent sync i/o<br>70 percent other<br><br>The answer to this question is not DB2, It lies in the nature of doing delay monitoring for many requestors.<br><br>In a computer system there are a very limited number of requestors that can use CPU concurrently. In a single engine machine that number is 1 (one)<br>There is a small number of requestions that can do I/O concurrently, limited by the number of paths to devices, [...]56_3177373205851393.WA.ibmsysproggeeksites.com@www.idug.org
10464 55 25_Re: (OT) Happy canada day16_Lavoie, Frederic29_Frederic.Lavoie@CRA-ARC.GC.CA30_Fri, 2 Jul 2010 07:48:44 -0400581_iso-8859-1 Thank you Robert<br><br>I hope you've enjoyed it!<br><br>Fr閐閞ic Lavoie<br>Database<br>613-954-9958<br><br>-----Original Message-----<br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of galambos@ROGERS.COM<br>Sent: July 1, 2010 9:21 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: [DB2-L] (OT) Happy canada day<br><br>For all those canadians who live in igloos 6 months of the year ;-)<br><br>Have a happy and safe canada day<br>"Age is an issue of mind over matter. If you don't mind, it doesn't matter." ~ Mark Twain<br>mobile email sent from my blackberry. [...]62_7A2589420A0D884C8EDE008CF8077E63F0D8B52C@SD01CFMV0001.PROD.NET
10520 2045 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM30_Fri, 2 Jul 2010 06:57:24 -0500548_ISO-8859-1 I'm not sure what DEXAN is? Where would I look for that?<br><br>As for what we're looking at, I'll give explaining it another shot...<br><br>It's the Omegamon classic interface panel for active threads. It looks<br>like this...<br><br>Waits Count Total Current<br><br>--------------------------------- ---------- ------------<br>------------<br><br>Synchronous I/O Wait 0 00:00:00.000<br>00:00:00.000<br><br>Asynchronous Read I/O Wait 0 00:00:00.000<br>00:00:00.000<br><br>Asynchronous Write I/O Wait 0 00:00:00.000<br>00:00:00.000 [...]62_C90B7164E1511D499B3C479D8B7CEC92021289BE@CMBFISLTC07.FNFIS.COM
12566 313 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V812_Reavill, Jay27_Jay.C.Reavill@FISGLOBAL.COM30_Fri, 2 Jul 2010 07:03:57 -0500335_us-ascii Thanks Mike.<br><br>Yes, we have DDF service classes defined down to the userid level so<br>that we can give appropriate goals for the different types of work<br>coming in. In production we have these goals set very aggressively for<br>the work in question. For our test we purposely set a new service class<br>very low. [...]62_C90B7164E1511D499B3C479D8B7CEC92021289C4@CMBFISLTC07.FNFIS.COM
12880 175 33_db2 v 8 on z/OS - EOS announced ?14_Steurs Patrick21_Patrick.Steurs@NBB.BE30_Fri, 2 Jul 2010 14:26:05 +0200695_us-ascii Hi,<br><br>We are now on db2 v8.1 on z/Os v1.10<br><br>I would like to now when the "end of service" will be. Is this yet<br>announced ?<br><br>Can someone help me ?<br><br>greetings,<br><br>Patrick Steurs<br>Dba - Database Services<br><br>IT Infrastructure<br><br>National Bank of Belgium - Eurosystem<br><br>Office : +32 (0)2 221 53 84<br><br>-----------------------------------------<br>Visit our website! http://www.nbb.be<br><br>"DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and [...]60_96DBEA7143ECD048905FAAA27D86E0D212E406D6@VSRBEX10.LANPRD.NBB
13056 59 25_Re: (OT) Happy canada day14_Larry Kintisch17_LKint@VERIZON.NET30_Fri, 2 Jul 2010 08:36:03 -0400571_us-ascii Bonjour!<br>I'll be visiting Quebec with my wife and grown sons for a few days<br>during the Mont Tremblant International Blues Festival that starts<br>next weekend.<br><br>I loved my earlier visits to Canada. Imagine my surprise when I<br>left the airport in my Canadian rental car and discovered the legal<br>speed limit was 80! At least you didn't keep the Brits' penchant for<br>driving on the wrong side. And the beauty of the land: while hiking<br>in the Cape Breton Highlands National Park we watched a moose<br>knee-deep in water just 75 feet away. [...]40_0L4X00DM5KCBF1J0@vms173013.mailsrvcs.net
13116 377 37_Re: db2 v 8 on z/OS - EOS announced ?20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM30_Fri, 2 Jul 2010 15:37:47 +0300543_US-ASCII EOS date is not announced yet.<br><br>Please check the link below.<br><br>http://www.ibm.com/software/data/db2/zos/support/plc/<br><br>Regards, Cuneyt<br><br>-----Original Message-----<br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steurs<br>Patrick<br>Sent: Friday, July 02, 2010 3:26 PM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: [DB2-L] db2 v 8 on z/OS - EOS announced ?<br><br>Hi,<br><br>We are now on db2 v8.1 on z/Os v1.10<br><br>I would like to now when the "end of service" will be. Is this yet<br>announced ? [...]42_01bb01cb19e3$60f54b30$3664ac09@cuneytgoksu
13494 179 37_Re: db2 v 8 on z/OS - EOS announced ?13_arlen stovall22_arlenstovall@GMAIL.COM30_Fri, 2 Jul 2010 08:51:47 -0400628_ISO-8859-1 Patrick, check out this URL for EOS.<br><br>http://www-01.ibm.com/software/data/support/lifecycle/<br><br>On Fri, Jul 2, 2010 at 8:26 AM, Steurs Patrick <Patrick.Steurs@nbb.be>wrote:<br><br>> Hi,<br>><br>><br>><br>> We are now on db2 v8.1 on z/Os v1.10<br>><br>><br>><br>> I would like to now when the "end of service" will be. Is this yet<br>> announced ?<br>><br>> Can someone help me ?<br>><br>><br>><br>> greetings,<br>><br>><br>><br>> *Patrick Steurs*<br>> Dba - Database Services<br>><br>> IT Infrastructure<br>><br>> *N*ational* B*ank of* B*elgium - *Eurosystem*<br>><br>> Office : +32 (0)2 221 53 84<br> [...]60_AANLkTikE38GBnGcEAJsVVT_dCO7DnoCR6P6wIgIC3eek@mail.gmail.com
13674 96 76_Re: FW: [DB2-L] What is DB2DIST doing?!?! - A Performance Quandary - z\OS V810_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 2 Jul 2010 15:01:48 +0200408_US-ASCII What 'very aggressive' means' ? Is it a velocity or response time goal (I<br>think it's velocity for distributed transactions, resp. time for local and<br>with local I presume TSO/SPUFI) ? It happened in test<br><br>environment some time, in a CPU with WLC when machine become capped (after<br>we went over the limit in4-hour mean) . Do You have I/O priority enabled<br>? do you have periods ? [...]66_OFDD853A0D.D387E9FB-ONC1257754.0046C49A-C1257754.0047904A@cesve.it
13771 55 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V810_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 2 Jul 2010 15:07:21 +0200479_US-ASCII In Mainview we had a screen to see which service class was attributed to<br>transactions. Do you have a OMEGAMON screen with dist transactions names<br>and showing their service class ? Some transactions (DDF and VTAM if I<br>remember well) have different ways of classification.<br><br>HTH<br><br>Max Scarpa<br><br>_____________________________________________________________________<br>* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU * [...]66_OF91DBC385.E09F0B09-ONC1257754.0047C921-C1257754.00481251@cesve.it
13827 53 31_Re: Nested loop vs. Hybrid join13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Fri, 2 Jul 2010 10:39:59 -0400360_utf-8 Actually, this does raise a question that's been bothering me for a while<br><br>It IS possible to create a table space in a buffer pool that doesn't exist (yet), or even alter a buffer pool to have a zero size even though there are objects living in it<br><br>You can still EXPLAIN statements that reference buffer pools with a zero size, so ..... [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81A6E@MAILR004.mail.lan
13881 693 64_Re: What is DB2DIST doing?!?! - A Performance Quandary - z\OS V814_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Fri, 2 Jul 2010 11:24:51 -0400395_UTF-8 The cut and pasted display helps alot.<br>The problem is not with a delay monitor.<br><br>The displayed data is from a performance trace interval.<br>The length of an interval is usually set in ZPARM STATTIME<br><br>You are not experiencing an expire of STATTIME. This ZPARM is coded in minutes (15 minute default) and your data is clearing every 20 seconds according to your report. [...]56_4740175938892455.WA.ibmsysproggeeksites.com@www.idug.org
14575 31 34_Re: Soundex used in a create index16_Deborah Grunwald19_DGrunwald@GLHEC.ORG30_Fri, 2 Jul 2010 11:41:05 -0400483_UTF-8 I had this exact problem, and was happy to see the syntax to create the index here on listserv.<br><br>However, DB2 never used the index...when I ran an explain in OSC I saw why....DB2 did this behind the scenes.<br><br>CAST(CAST(INTERNAL SCLF(PROD.RSDEMO.LAST_NM)AS CHAR(4)) AS CHAR(4)) = CAST(CAST(INTERNAL SCLF('HALL') AS CHAR(4)) AS CHAR(4))<br><br>I don't think I am going to recommend we use the SOUNDEX function until I can figure out how to get an index to match. [...]51_8252441666074534.WA.DGrunwaldglhec.org@www.idug.org
14607 105 34_Re: Soundex used in a create index10_Mark Stone17_mastone@GMAIL.COM30_Fri, 2 Jul 2010 08:59:42 -0700729_ISO-8859-1 This is working well for me using an index with the<br>definition CAST(SOUNDEX(REG_MASTER.LAST_NAME) AS CHAR(4) CCSID EBCDIC) ASC<br><br>WHERE CAST(SOUNDEX(REG_MASTER.LAST_NAME) AS CHAR(4) CCSID EBCDIC) =<br>CAST(SOUNDEX(?) AS CHAR(4) CCSID EBCDIC)<br><br>On Fri, Jul 2, 2010 at 8:41 AM, Deborah Grunwald <DGrunwald@glhec.org>wrote:<br><br>> I had this exact problem, and was happy to see the syntax to create the<br>> index here on listserv.<br>><br>> However, DB2 never used the index...when I ran an explain in OSC I saw<br>> why....DB2 did this behind the scenes.<br>><br>> CAST(CAST(INTERNAL SCLF(PROD.RSDEMO.LAST_NM)AS CHAR(4)) AS CHAR(4)) =<br>> CAST(CAST(INTERNAL SCLF('HALL') AS CHAR(4)) AS CHAR(4))<br> [...]60_AANLkTikosYWApq2Shrz6EggwLdmg6ycpR5TLXRfDLBar@mail.gmail.com
14713 106 31_Re: Nested loop vs. Hybrid join13_Mick P Graley16_mgraley2@CSC.COM30_Fri, 2 Jul 2010 17:17:01 +0100558_UTF-8 Hi Phil,<br><br>This is interesting as I thought as of V8 that at least some of the buffer<br>pools had a minimum size for an alter buffer pool command. We have standard<br>jobs with all the alter buffer pool commands for a sub-system in each one,<br>so when we change the number of buffers for a pool we just alter and submit<br>the standard job even though most of the pools aren't really changing. In<br>development we had some very small buffer pools for some ISV products and<br>the alter buffer pool jobs started to fail because the size of [...]65_OFA9878DA8.B5447BC2-ON80257754.0058CCE0-80257754.00597327@csc.com
14820 85 38_Luke R Tetreault is out of the office.6_Luke T28_Luke_R_Tetreault@KEYBANK.COM30_Fri, 2 Jul 2010 14:18:07 -0400724_us-ascii I will be out of the office starting 07/02/2010 and will not return until<br>07/06/2010.<br></pre><br><br>This communication may contain privileged and/or confidential information. It<br>is intended solely for the use of the addressee. If you are not the intended<br>recipient, you are strictly prohibited from disclosing, copying, distributing<br>or using any of this information. If you received this communication in error,<br>please contact the sender immediately and destroy the material in its entirety,<br>whether electronic or hard copy. This communication may contain nonpublic personal<br>information about consumers subject to the restrictions of the<br>Gramm-Leach-Bliley Act. You may not directly [...]69_OF85C7ED74.A9CC83E4-ON85257754.00648930-85257754.00648930@keybank.com
14906 33 31_Re: Nested loop vs. Hybrid join13_Terry Purcell18_tpurcel@US.IBM.COM30_Fri, 2 Jul 2010 15:31:06 -0400280_UTF-8 Hi Phil,<br><br>One main area where BP size comes into play is when we are revisiting pages - such as repeat access to the inner table of a join. Optimizer uses the size of the BP here, and thus VPSIZE(0) will not benefit from any revisiting. There is no default size. [...]50_4908027340780724.WA.tpurcelus.ibm.com@www.idug.org
14940 62 29_Re: DB2 LUW Disaster Recovery14_Larry Kintisch17_LKint@VERIZON.NET30_Fri, 2 Jul 2010 16:08:01 -0400728_us-ascii Hi Bob,<br>I am not an LUW trained person.<br>I don't know if you found something on LUW recovery since two<br>weeks ago when this was posted. Check out these two Redbooks. Larry Kintisch<br><br>Up and Running with DB2 on Linux<br><br>http://www.redbooks.ibm.com/abstracts/sg246899.html<br><br>High Availability and Disaster Recovery Options for DB2 on Linux,<br>UNIX, and Windows<br><br>http://www.redbooks.ibm.com/abstracts/sg247363.html<br><br>At 05:50 PM 6/13/2010, you wrote:<br>>Am new to LUW (AIX), previous experience with Z/OS. Need to set up<br>>disaster recovery at alternate site for a partitioned DB. Will use<br>>backup and recover. Is there a redbook that describes the process<br>>using an offline [...]40_0L4Y00MK359MOSE0@vms173001.mailsrvcs.net
15003 25 63_Auto Reply: DB2-L Digest - 2 Jul 2010 to 3 Jul 2010 (#2010-178)13_Chris Economy28_christine.economy@ORACLE.COM30_Fri, 2 Jul 2010 22:00:58 -0700714_utf-8 This is an auto-replied message. I am out of the office.<br>I will respond to your E-mail when I return.<br>Thank you.<br><br>_____________________________________________________________________<br>* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *<br><br>* Your only source for independent, unbiased, and trusted DB2 information. *<br>_____________________________________________________________________<br>http://www.IDUG.org/mentor<br>How can you expand your staff or do succession planning in this economy?<br>Mentoring is a proven, economical, way to train the next generation of DB2 Users!<br>_____________________________________________________________________ [...]44_2b15fb37-a3f0-4865-9bfe-0c1ce5f1826b@default
15029 54 32_Re: Ascending/Descending Indexes14_Peter Vanroose17_pvanroose@ABIS.BE30_Sun, 4 Jul 2010 14:29:32 -0400395_UTF-8 George,<br><br>Be especially careful when the index to be deleted is the cluster index!<br>Even if it's just "a" clustering index, access paths may degrade:<br>Running backward through the forward index will "de facto" be in TS cluster sequence, but the access path will be marked "dynamic prefetch", so it will depend on subtle runtime statistics whether prefetch will be applied... [...]49_4327217765925328.WA.pvanrooseabis.be@www.idug.org
15084 118 30_A DB2 virtual storage question15_Smartcurl Zhang19_smartcurl@GMAIL.COM30_Mon, 5 Jul 2010 11:04:34 +0800458_ISO-8859-1 *Hi,guys*<br>*<br>*<br>*I gota strange phenomenon.I have a two way data sharing DB2 system,but the<br>first one occupied much more virtual storage than second one.It's confuse<br>for me ,a DB2 newbie. Any explain will be appreciated.thanks a lot.*<br>*followed are RMF report:*<br><br>*first one:*<br>Samples: 120 System: BP1G Date: 07/05/10 Time: 10.24.00 Range: 120<br>Sec<br><br>Service -- Frame Occup.-- - Active Frames - AUX PGIN MemObj [...]60_AANLkTilr08VjL3vacT3N8DxbWsVvatJVm58J_IsJ1F99@mail.gmail.com
15203 100 18_Re: Question on RI13_Sushanta Dash30_Sushanta.K.Dash@WELLSFARGO.COM30_Sun, 4 Jul 2010 23:13:53 -0500505_us-ascii Addition:<br><br>The foreign keys and primary keys do not need to match in terms of null attribute and default values.<br>Foreign key and primary key attributes must be of same data type and length apart from same order.<br>Hope this helps.<br><br>With Thanks<br>Sushanta Dash<br>-----Original Message-----<br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Larry Kintisch<br>Sent: Thursday, July 01, 2010 5:31 PM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] Question on RI [...]73_4524A782FDBC234089E413906B58E722320102EEB2@MSGCMSV21026.ent.wfb.bank.corp
15304 46 80_AUTO: Mitchell L. Lovins/IRM/DST/US is out of the office. (returning 07/12/2010)15_Mitchell Lovins23_MLLovins@DSTSYSTEMS.COM30_Mon, 5 Jul 2010 00:15:51 -0500341_US-ASCII I am out of the office until 07/12/2010.<br><br>I will respond to your message when I return.<br>If you need assitance with a project contact the secondary dba or send an<br>email to db support.<br><br>Note: This is an automated response to your message "DB2-L Digest - 4 Jul<br>2010 to 5 Jul 2010 (#2010-180)" sent on 7/5/10. [...]72_OF61E5C94D.B3F116EF-ON86257757.001CEAD0-86257757.001CEAD0@dstsystems.com
15351 181 76_AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE30_Mon, 5 Jul 2010 07:47:17 +0200378_iso-8859-1 Phil,<br><br>Even at the risk of having missed the obvious here,<br>What exactly do you mean by "doesn't exist"?<br>Could it be that you referred to a not-yet-activated bufferpool as a non-existing one?<br><br>I haven't tried the ALTER BP yet (my friends, the system programmers, would not be pleased, I guess), but I tried the CREATE TABLESPACE, and it failed: [...]69_31CF7002A1132E40BD5AD816A179C1588CDDB5FC85@VMX00100.lan.huk-coburg.de
15533 41 50_Determining CPU cost for a job that does DB2 stuff15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM30_Mon, 5 Jul 2010 16:35:09 +0300214_us-ascii The TIMEUSED results do not match what SMF records.<br><br>IWMEQTME requires supervisor state.<br><br>Is there a way for an application to determine the CPU cost of a section of<br>code that does DB2 work?42_vjn336t4k7fqsjto0euipg9dbvost4hug1@4ax.com
15575 87 62_AW: [DB2-L] Determining CPU cost for a job that does DB2 stuff35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Mon, 5 Jul 2010 17:04:16 +0200281_iso-8859-1 Hi<br><br>I also use this instruction just before and after I run the SQL-statement. I am happy with the times I get back. O.k. they do not match with the time our monitor tells me, but I think, I can compare my results with other results, I got using this method. [...]43_DB2-L%201007051104267722.1E4C@IDUGDB2-L.ORG
15663 97 54_Re: Determining CPU cost for a job that does DB2 stuff9_Mike Bell21_mbell11a1@VERIZON.NET30_Mon, 5 Jul 2010 10:04:08 -0500581_US-ASCII Depends -<br><br>if it is in a CICS transaction, the general answer is no - there is just too<br>much shifting and tcb switching even with threadsafe. Threadsafe reduces<br>the tcb switching by a large amount but still too much other function going<br>on.<br><br>If it is IMS and the MPP/BMP region is stable, then timeused will account<br>for all the cpu time on the TCB between two code points. NOTE - SMF101 only<br>starts accumulating CPU with the FIRST SQL statement. There can be a fairly<br>substantial amount of CPU btween the initial IMS GU to the IOPCB and [...]43_B89EEE64128E406681E6AA01957CAB05@mikelaptop
15761 259 30_Next DB2 Tech Talk from Cogito13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Mon, 5 Jul 2010 11:05:53 -0400497_us-ascii It could happen to YOU - scary disaster stories, lessons learned, NOTHING is impossible<br><br>Thursday, July 8th, 2010 12:00 PM - 1:30 PM EDT<br><br>There are many presentations on recovering computer systems from disasters and the need for planning BUT what exactly ARE these disasters?<br><br>This presentation will give you the opportunity to learn from other people's misfortunes that NOTHING is impossible, and even the most improbable events can (and will) happen to someone. [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81AEF@MAILR004.mail.lan
16021 393 34_Re: Next DB2 Tech Talk from Cogito13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Mon, 5 Jul 2010 11:04:28 -0500488_us-ascii [AD]!!! ;o)<br><br>You accepting impartial ISV attendees? I promise not to heckle, as what goes around etc.<br><br>Cheers,<br><br>Raymond<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger<br>Sent: 05 July 2010 16:06<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: [DB2-L] Next DB2 Tech Talk from Cogito<br><br>It could happen to YOU - scary disaster stories, lessons learned, NOTHING is impossible<br><br>Thursday, July 8th, 2010 12:00 PM - 1:30 PM EDT [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FC69E5D@PHXCCRPRD04.adprod.bmc.com
16415 563 39_Re: [AD] Next DB2 Tech Talk from Cogito13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Mon, 5 Jul 2010 12:09:20 -0400444_us-ascii Guilty as charged M'lud - [AD] duly added :)<br><br>In general we'll accept anyone on our calls - even other ISVs!<br><br>However, we do have to reserve the right to give priority to customers, users and consultants if available "seating space" becomes an issue<br><br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk> [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81B33@MAILR004.mail.lan
16979 223 80_Re: AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Mon, 5 Jul 2010 12:17:45 -0400391_iso-8859-1 Hmm<br><br>I need to revisit my test - I thought I'd done just what you did!<br><br>On the other hand I may have altered a buffer pool to have a zero size - in which case a zero size buffer pool is NOT the same as one that's NEVER been used<br><br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0)�1298 872 148<br>+44 (0)�7505 266 768<br>www.cogito.co.uk [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81BAA@MAILR004.mail.lan
17203 41 46_IBM DB2 Unload Control Deck Creation (3 lines)13_Jason Beetham28_Jason.Beetham@SUNCORP.COM.AU30_Mon, 5 Jul 2010 21:50:39 -0400396_UTF-8 Hi,<br><br>In DB2 v9 for z/os, when an IBM unload is performed, and a Control deck produced, DB2 spits out the lines:<br>"INTO TABLE"<br>"CREATOR".<br>"TABLENAME"<br>over 3 lines....<br><br>In DB2 v8 for z/os, this was on one line, such as:<br>"INTO TABLE "CREATOR"."TABLENAME"<br><br>This is causing probems for some of our jobs that change the CREATOR between environment refreshes. [...]60_7945180838814773.WA.Jason.Beethamsuncorp.com.au@www.idug.org
17245 235 50_Re: IBM DB2 Unload Control Deck Creation (3 lines)11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 6 Jul 2010 06:57:42 +0200777_ISO-8859-1 in my V8, 9 and 10 systems I always get that format!! Looks like:<br><br>INTO TABLE<br>"SVNXTEST".<br>"SVNXT003"<br><br>Roy Boxwell<br>SOFTWARE ENGINEERING GMBH<br>-Product Development-<br>Robert-Stolz-Stra遝 5<br>40470 D黶seldorf/Germany<br>Tel. +49 (0)211 96149-675<br>Fax +49 (0)211 96149-32<br>Email: R.Boxwell@seg.de<br>http://www.seg.de<br><br>Software Engineering GmbH<br>Amtsgericht D黶seldorf, HRB 37894<br>Gesch鋐tsf黨rung: Siegfried F黵st, Gerhard Schubert<br><br>Jason Beetham <Jason.Beetham@SUNCORP.COM.AU><br>Gesendet von: IDUG DB2-L <DB2-L@IDUGDB2-L.ORG><br>06.07.2010 03:50<br>Bitte antworten an<br>IDUG DB2-L <DB2-L@IDUGDB2-L.ORG><br><br>An<br>DB2-L@IDUGDB2-L.ORG<br>Kopie<br><br>Thema<br>[DB2-L] IBM DB2 Unload Control Deck Creation (3 lines) [...]64_OF65AFF572.751096AF-ONC1257758.001A943D-C1257758.001B41BA@seg.de
17481 35 34_Admin Tool support DB2 Release 9154_Anil21_alisha_kale@YAHOO.COM30_Tue, 6 Jul 2010 04:14:12 -0400515_UTF-8 Hi All !<br><br>I noticed that the IBM's db2 admin tool (DB2 Administration 7.2.0) does not provide the "Table Space Drop Impact" with DB2 Release 915.<br>From the "Drop Table Space" menu, you would get the following message "This function does not support DB2 Release 915" if you ask for the Drop Impact.<br>So two questions to my fellow DBAs:<br>1. Any idea if the IBM's DB2 Admin Tool support DB2 Version 9 ? If not,<br>2. Any documentation on what all is not supported by the Admin Tool for DB2 V9 ? [...]52_7012636064287877.WA.alishakaleyahoo.com@www.idug.org
17517 94 31_count of a materialised cursor?16_Laurens Zwanepol31_lbn.zwanepol@BELASTINGDIENST.NL30_Tue, 6 Jul 2010 10:28:36 +0200426_us-ascii Hello,<br><br>Does anyone know how i can see what the count is of a materialised cursor<br>during executiontime ?<br><br>For example:<br>Program A starts with a cursor (a multiple join on sevaral tables). Then<br>the cursor will be fetched and each fetch will be processed.<br>While monitoring the thread I would like to know how many rows there are<br>in het cursor, so i can estimate the time the job wil run. [...]76_OF8B580390.E71BE9EF-ONC1257758.002DBD03-C1257758.002E93DE@belastingdienst.nl
17612 294 35_Re: count of a materialised cursor?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 6 Jul 2010 10:41:27 +0200523_ISO-8859-1 I would try using the SQLCA field SQLERRD(3) type INTEGER<br><br>Contains the number of rows that qualified to be deleted, inserted, or<br>updated after a DELETE, INSERT, UPDATE, or MERGE statement.<br><br>For the OPEN of a cursor for a SELECT with a data change statement or for<br>a SELECT INTO, SQLERRD(3) contains the number of rows affected<br>by the embedded data change statement. The value is 0 if the SQL<br>statement fails, indicating that all changes made in executing the<br>statement canceled. [...]64_OFB2CEBDDE.14DDF5C0-ONC1257758.002FA64B-C1257758.002FBE08@seg.de
17907 218 35_Re: count of a materialised cursor?11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 6 Jul 2010 10:48:49 +0200652_ISO-8859-1 nope . I just tested it - Unless you have some sort of Update intent it<br>alwasy has 00000000<br><br>Roy Boxwell<br>SOFTWARE ENGINEERING GMBH<br>-Product Development-<br>Robert-Stolz-Stra遝 5<br>40470 D黶seldorf/Germany<br>Tel. +49 (0)211 96149-675<br>Fax +49 (0)211 96149-32<br>Email: R.Boxwell@seg.de<br>http://www.seg.de<br><br>Software Engineering GmbH<br>Amtsgericht D黶seldorf, HRB 37894<br>Gesch鋐tsf黨rung: Siegfried F黵st, Gerhard Schubert<br><br>Laurens Zwanepol <lbn.zwanepol@BELASTINGDIENST.NL><br>Gesendet von: IDUG DB2-L <DB2-L@IDUGDB2-L.ORG><br>06.07.2010 10:28<br>Bitte antworten an<br>IDUG DB2-L <DB2-L@IDUGDB2-L.ORG> [...]64_OF53E3BB83.B5301757-ONC1257758.00306100-C1257758.00306AB6@seg.de
18126 89 49_Betr: Re: [DB2-L] count of a materialised cursor?16_Laurens Zwanepol31_lbn.zwanepol@BELASTINGDIENST.NL30_Tue, 6 Jul 2010 11:26:59 +0200472_us-ascii Roy,<br><br>Thanks for the reply,<br><br>But, it is not my intention to receive the number within my program. I am<br>looking for a way to retrieve the number when the program is running. With<br>some kind of trace/ifcid.<br>Within our company OMEGAMON is used to monitor DB2-traffic<br><br>Mvgr,<br><br>Laurens Zwanepol<br><br>Belastingdienst/Centrum voor Infrastructuur en Exploitatie (B/CIE)<br>Unit infrastructuur/Team infrastructuur DBMS Mainframe CICS [...]76_OFA7EC78E9.545DBF6B-ONC1257758.0033DA01-C1257758.0033EC05@belastingdienst.nl
18216 42 63_AUTO: Gab Gambassi is at a customer site (returning 12/07/2010)12_Gab Gambassi20_gabriele@NZ1.IBM.COM30_Tue, 6 Jul 2010 22:04:25 +1200465_US-ASCII I am out of the office until 12/07/2010.<br><br>I am contactable on my mobile 021 484053.<br>I will do my best to check my mail but I don't guarantee fast response.<br><br>For urgent matters please contact:<br>my collegaue Art Balingit<br>Phone 09-359-8417<br>Mobile 021-464-958<br>Mail balingit@nz1.ibm.com<br><br>or my Team Leader, Joshua Joshua<br>Phone 09-359-8523<br>Mobile 021-2442673<br>Mail joshuaj@nz1.ibm.com<br><br>Thanks for you patience. [...]69_OF551F30CF.D7067D62-ONCC257758.00375660-CC257758.00375662@nz1.ibm.com
18259 224 80_Re: AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Tue, 6 Jul 2010 06:33:03 -0400508_iso-8859-1 OK, I've retried my tests (under DB2 9) and things make a little more sense now<br><br>1. As Rudiger points out, you CAN'T create a table space in a buffer pool with a zero size (apologies for the confusion)<br>2. BUT You CAN alter a buffer pool to a zero size EVEN IF there are table spaces defined to use it<br>3. Interestingly, if you DO alter a buffer pool to VPSIZE(0) and then try to DROP a table space using that buffer pool, the drop fails with a -904 on the unavailable buffer pool! [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81ECD@MAILR004.mail.lan
18484 38 46_DB2 v8 z/OS SYSREC for SYSPKAGE reorg and GTTs11_Steven Lamb24_steven.lamb@BARCLAYS.COM30_Tue, 6 Jul 2010 06:40:14 -0400536_UTF-8 Dear Listers,<br><br>When performing a REORG SHRLEVEL CHANGE on the SYSPKAGE tablespace, what effect do GTTs in SYSPKAGE have on the size of the Unload dataset used by the reorg?<br><br>I've seen a Craig Mullins formula for estimating the size of the SYSREC dataset as (longrow * numrows) bytes, with an SQL statement for determining the value for longrow (the length of the longest row in the tablespace).<br>When I run the SQL it gives me a value of 32,691 for the SYSPKAGE longrow, due to a GTT and 1,505,895 for numrows. [...]56_8904546808665980.WA.steven.lambbarclays.com@www.idug.org
18523 51 46_SV: [DB2-L] Admin Tool support DB2 Release 91513_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Tue, 6 Jul 2010 13:09:53 +0200507_utf-8 Hi Anil,<br>It looks like you are some behind on service for Admin Tool.<br>DB2 Admin tool now support almost all features in DB2 9 if you have applied all service available.<br>DB2 release 915 means that the current DB2 system is in New function Mode in Version 9.<br>DB2 release 910 means that the current DB2 system is in Compatibility Mode in Version 9.<br>The only not yet supported feature in DB2 9 I know about is:<br>APAR PM13392 - Implicit Objects OC Support for XML and LOBS, MIG only. [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A998F6@FSPAS01EV011.fspa.myntet.se
18575 39 50_Re: DB2 v8 z/OS SYSREC for SYSPKAGE reorg and GTTs14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 6 Jul 2010 21:11:30 +1000604_US-ASCII None, there's nothing actually there. It's just a logical construct as the catalog design<br>requires a table to be defined in a tablespace.<br><br>When a CGTT is actually used all data is stored in the WORKFILE database (eg DSNDB07).<br><br>James Campbell<br><br>On 6 Jul 2010 at 6:40, Steven Lamb wrote:<br><br>> Dear Listers,<br>><br>> When performing a REORG SHRLEVEL CHANGE on the SYSPKAGE tablespace, what effect do GTTs in SYSPKAGE have on the size of the Unload dataset used by the reorg?<br>><br>> I've seen a Craig Mullins formula for estimating the size of the SYSREC dataset as [...]49_4C339C02.31396.80BA75CC@jacampbell.acslink.net.au
18615 220 61_AW: [DB2-L] Betr: Re: [DB2-L] count of a materialised cursor?35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Tue, 6 Jul 2010 13:41:15 +0200559_iso-8859-1 Hi<br><br>AFAIK, there is no way of getting this information. When I try to estimate how long the program will take, I look for the number of getpages. How many getpages has the application already acquired and how many it might keep doing.<br><br>Btw: the number of rows a cursor will fetch is useless, if you don't know the access path. Imagine, the cursor will fetch only 2 rows, the first row is the stored in the very first data page and the second, which qualifies is storred in the very last page and access is using a tablespace scan. [...]43_DB2-L%201007060741249053.1EE6@IDUGDB2-L.ORG
18836 22 50_Re: DB2 v8 z/OS SYSREC for SYSPKAGE reorg and GTTs11_Steven Lamb24_steven.lamb@BARCLAYS.COM30_Tue, 6 Jul 2010 08:54:07 -0400533_UTF-8 Thanks James. That'll reduce the space needed - the biggest real table has a row length of 4,056 bytes.<br><br>_____________________________________________________________________<br>* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *<br><br>* Your only source for independent, unbiased, and trusted DB2 information. *<br>** The best DB2 technical sessions in the world<br>** NEW - IBM hands-on labs -> no additional charge<br>_____________________________________________________________________ [...]56_4719480722889810.WA.steven.lambbarclays.com@www.idug.org
18859 77 33_DB2 for z/OS - convert Oracle DDL12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Tue, 6 Jul 2010 15:47:02 +0100751_ISO-8859-1 Below is some Oracle ddl -<br><br>WHENEVER sqlerror CONTINUE;<br><br>DROP TABLE "PAPERCLIP_FOLDERS" cascade constraints;<br><br>WHENEVER sqlerror EXIT failure;<br><br>CREATE TABLE "PAPERCLIP_FOLDERS"<br>(<br>DOCUMENT_TYPE VARCHAR2(255) not null,<br>FOLDER_NAME VARCHAR2(90) not null,<br>FOLDER_ID NUMBER not null,<br>PARENT_FOLDER_ID NUMBER<br>);<br><br>ALTER TABLE PAPERCLIP_FOLDERS ADD CONSTRAINT PAPERCLIP_FOLDERS_PK PRIMARY<br>KEY (DOCUMENT_TYPE, FOLDER_ID) ENABLE;<br><br>what is the easiest way to accomplish what the ALTER statement requests<br>above.<br><br>Jim McAlpine<br><br>_____________________________________________________________________<br>* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU * [...]60_AANLkTikNPmCphi5ijKBjX3ANcUHU58mYMj_nUiwsnIet@mail.gmail.com
18937 100 37_Re: DB2 for z/OS - convert Oracle DDL7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 6 Jul 2010 08:21:11 -0700456_iso-8859-1 You can put the primary key definition in the table definition.<br>Until you build a primary unique index to enforce it however, the primary key is little more than a comment.<br><br>Edward Long<br><br>--- On Tue, 7/6/10, Jim McAlpine <jim.mcalpine@GMAIL.COM> wrote:<br><br>From: Jim McAlpine <jim.mcalpine@GMAIL.COM><br>Subject: [DB2-L] DB2 for z/OS - convert Oracle DDL<br>To: DB2-L@IDUGDB2-L.ORG<br>Date: Tuesday, July 6, 2010, 10:47 AM [...]43_755069.27681.qm@web80201.mail.mud.yahoo.com
19038 137 37_Re: DB2 for z/OS - convert Oracle DDL12_Jim McAlpine22_jim.mcalpine@GMAIL.COM30_Tue, 6 Jul 2010 16:21:11 +0100723_ISO-8859-1 On Tue, Jul 6, 2010 at 3:47 PM, Jim McAlpine <jim.mcalpine@gmail.com> wrote:<br><br>> Below is some Oracle ddl -<br>><br>> WHENEVER sqlerror CONTINUE;<br>><br>> DROP TABLE "PAPERCLIP_FOLDERS" cascade constraints;<br>><br>> WHENEVER sqlerror EXIT failure;<br>><br>> CREATE TABLE "PAPERCLIP_FOLDERS"<br>> (<br>> DOCUMENT_TYPE VARCHAR2(255) not null,<br>> FOLDER_NAME VARCHAR2(90) not null,<br>> FOLDER_ID NUMBER not null,<br>> PARENT_FOLDER_ID NUMBER<br>> );<br>><br>> ALTER TABLE PAPERCLIP_FOLDERS ADD CONSTRAINT PAPERCLIP_FOLDERS_PK PRIMARY<br>> KEY (DOCUMENT_TYPE, FOLDER_ID) ENABLE;<br>><br>> what is the easiest way to accomplish what the ALTER statement requests<br>> above.<br>><br>> Jim McAlpine<br>> [...]60_AANLkTiltK_fqVITBYGMNyc_jikibBQjZuAAssTCUt6LW@mail.gmail.com
19176 306 31_Count of a materialised cursor?12_Tony Andrews22_tandrews@THEMISINC.COM30_Tue, 6 Jul 2010 11:39:15 -0400399_us-ascii If you know for sure that the data is being materialized at open time,<br>then you could set it up as<br><br>a scrollable cursor.<br><br>'DECLARE C1 INSENSITIVE SCROLL CURSOR FOR<br><br>SELECT ....'<br><br>'OPEN C1'<br><br>Scrollable cursors will always materialize the data into a work file in<br>your temp database. The program can them execute<br><br>'FETCH INSENSIIVE LAST FROM C1 [...]67_7EC87535E7581C4C890F5BF1CF9A9374029B7181@THEMISSRV1.themisinc.local
19483 423 37_Re: DB2 for z/OS - convert Oracle DDL13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Tue, 6 Jul 2010 11:39:36 -0400643_us-ascii Unless you are running DB2 9, then implicit object creation will also create the constraining unique index for you<br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk><br><br>Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.<br>8-12 November 2010, Vienna<br>Learn more at http://www.idug.org<http://www.idug.org/><br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Ed Long<br>Sent: 06 July 2010 16:21<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] DB2 for z/OS - convert Oracle DDL [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81F11@MAILR004.mail.lan
19907 58 32_z/OS-Data Studio SP Deploy error16_Natalie Faulkner25_natalie.w.faulkner@WV.GOV30_Tue, 6 Jul 2010 11:40:43 -0400675_UTF-8 Gang,<br><br>We are currently v8 on z/OS<br><br>One of our developers is attempting to use Data Studio, for the first time, to develop SQL stored procs...<br>They select the DEPLOY option and receive the following error...<br><br>DSNTPSMP CONSOLE 1 *** DB2 V8R1 SQL Procedure Processor DSNTPSMP 1.21 (PK59011 2008-04-04)<br>DSNTPSMP CONSOLE 2 ** INFORMATION ** Action was requested against a routine using an unqualified name of SYSPTST1<br>DSNTPSMP CONSOLE 3 ** The calling application process has set the CURRENT SCHEMA special register to SYDBTST1<br>DSNTPSMP CONSOLE 4 ** The qualified routine name is "SYDBTST1"."SYSPTST1"<br>DSNTPSMP CONSOLE 5 ** This could [...]57_7283524498008226.WA.natalie.w.faulknerwv.gov@www.idug.org
19966 164 36_Re: z/OS-Data Studio SP Deploy error7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 6 Jul 2010 09:53:51 -0700628_iso-8859-1 Hi Natalie.<br>Consider the following:<br>1: There is an IBM supplied sample job that exercises DSNTPSMP. Find it and run it in your shop; don't bother with Data Studio until you prove that the sample jobs for DSNTPSMP work.<br>2: DSNTPSMP has been the subject of a lot of maintenance; make sure your current. 2008 sounds a little behind in maintenance.<br>3: DSNTPSMP does not dynamically allocate the load library. So, we make multiple copies of it in multiple application environments so we can have multiple DBRM and load libraries. Essentially we create a separate DSNTPSMP application environment for each [...]43_826994.89871.qm@web80204.mail.mud.yahoo.com
20131 388 31_HOTDUG meeting on July 13, 20108_Lo, Mary17_Mary.Lo@TGSLC.ORG30_Tue, 6 Jul 2010 11:55:20 -0500403_us-ascii Our 3rd quarterly meeting will be held on Tuesday, July 13, 2010 at IBM (11501 Burnett Road, Building 904). This will be a DB2 for z/OS system performance planning workshop, covering tuning DB2 subsystem, DB2 Connect and monitoring. As always, attendance is free of charge.<br><br>Please RSVP to mary.lo@tgslc.org<mailto:mary.lo@tgslc.org><br><br>Hope to see you.<br>------- Agenda ------ [...]65_550D0016ABCA22488AD932ACA94AC56831CAFAFA6C@CORPEXCHMBOX.tgslc.org
20520 236 37_Re: DB2 for z/OS - convert Oracle DDL7_Ed Long19_rdhm99a@PRODIGY.NET30_Tue, 6 Jul 2010 09:57:10 -0700369_iso-8859-1 The extra info suggests another feature.<br>If the primary key constraint is defined in the create table then DB2 allows you to define it first, that is without the primary index. Obviously you must eventually define the index.� If you try to do the primary key after the create table, the unique index must already exist before you can define the key. [...]43_665980.92171.qm@web80204.mail.mud.yahoo.com
20757 44 23_Re: A question on REORG9_Ray Lopez19_raylopez@US.IBM.COM30_Tue, 6 Jul 2010 13:19:06 -0400331_UTF-8 Vardhini, I agree with David. Unless you are unloading a single partition, there is no way to unload/discard from a subset of the table and ignore the rest.<br><br>If you need to minimize the down time on the table, try using an Online Reorg (uses a mapping table and swaps out the underlying VSAMs at the last minute). [...]51_8754969402818760.WA.raylopezus.ibm.com@www.idug.org
20802 39 31_What DB2 Tools Do You Use Most?11_Dave Beulke19_dave@DAVEBEULKE.COM30_Tue, 6 Jul 2010 13:43:26 -04000_51_3816338725886704.WA.davedavebeulke.com@www.idug.org
20842 31 35_Re: What DB2 Tools Do You Use Most?14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM30_Tue, 6 Jul 2010 14:59:25 -0400353_UTF-8 The "tool" I use the most is the -DIS command.<br><br>For those who might consider this odd please keep in mind that the most used IBM program for any function is IEFBR14.<br><br>Best wishes<br>Avram Friedman<br><br>PS A diffrent classic answer to a question like "What is your most important monitoring tool"?<br>The telephone of course!!!! [...]56_0336597039380497.WA.ibmsysproggeeksites.com@www.idug.org
20874 42 32_Re: Ascending/Descending Indexes11_Rick Butler17_ricodba@YAHOO.COM30_Tue, 6 Jul 2010 17:12:26 -0400670_UTF-8 Hello George and Peter and Willie, this is fyi, the following info should also be considered when removing any index.<br><br>It's from section 5.4.8 Considerations on RTS usage in the "DB2 9 for z/OS: Using the Utilities Suite" Redbook<br><br>Updated last April , it is available here:<br><br>http://www.redbooks.ibm.com/abstracts/sg246289.html?Open<br><br>Note: The existence of an index has the opportunity for RUNSTATS to collect statistics (by default) that would not have been collected otherwise. Dropping an index that appears to not have been used for a long time may affect the access path of dynamic SQL. Although there is no apparent dependency on [...]49_5148899857293561.WA.ricodbayahoo.com@www.idug.org
20917 46 32_Re: Ascending/Descending Indexes13_Terry Purcell18_tpurcel@US.IBM.COM30_Tue, 6 Jul 2010 21:20:15 -0400492_UTF-8 Rick is right for pointing out the implications of dropping an index on the statistics. However, this original discussion was around dropping DESC indexes given that ASC indexes can be read in reverse in V8. If we limit the discussion to that scope, then we are talking about indexes that are duplicates - except for their ASC/DESC column sequence. Thus, these indexes will cause RUNSTATS to collect the same statistics and thus dropping the DESC index will not affect statistics. [...]50_1986844955875116.WA.tpurcelus.ibm.com@www.idug.org
20964 344 37_Re: DB2 for z/OS - convert Oracle DDL12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 6 Jul 2010 18:33:20 -0700651_us-ascii But remember, except for certain Java functions and RI, you really don't have to define a primary key for a DB2 table. It's not required.<br><br>________________________________<br>From: Ed Long <rdhm99a@PRODIGY.NET><br>To: DB2-L@IDUGDB2-L.ORG<br>Sent: Tue, July 6, 2010 12:57:10 PM<br>Subject: Re: [DB2-L] DB2 for z/OS - convert Oracle DDL<br><br>The extra info suggests another feature.<br>If the primary key constraint is defined in the create table then DB2 allows you to define it first, that is without the primary index. Obviously you must eventually define the index. If you try to do the primary key after the create table, the [...]43_706992.5214.qm@web112113.mail.gq1.yahoo.com
21309 56 23_Re: A question on REORG13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 7 Jul 2010 03:04:13 -0500366_utf-8 <Generic_ISV_plug> Actually there's at least one way of selectively putting back only some of the original rows in a tablespace when you reorg it. There are more reorg utilities out there than the one that comes in the DB2 box, you know... </Generic_ISV_plug><br><br>Any more and it becomes [AD] so, as I pulled Phil up for it earlier, I'll stop now. ;o) [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FCDB934@PHXCCRPRD04.adprod.bmc.com
21366 118 26_AW: [DB2-L] Question on RI35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Wed, 7 Jul 2010 10:14:49 +0200569_iso-8859-1 Hi Larry<br><br>We recently paid the price of having wrong indexes in a RI-relationship. The key of the parent table was (A,B,C,D,E,F) and the index of the dependant table starts with (A,B,C,E,D,F). We saw very high numbers of getpages for the bufferpool containing only tablespaces during a delete cascade.<br><br>A long, long time ago, I attended a course about RI, and if I recall it correctly, the check data utiltiy requires an index, which matches exactly the index of the primary index of the parent key. I don't know, if that is still the case. [...]43_DB2-L%201007070414588426.1F09@IDUGDB2-L.ORG
21485 98 80_Re: AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Jul 2010 12:28:30 +0200334_US-ASCII Step 2) was tested in the past (I think it was an article by Craig<br>Mullins) and yes you can alter a BP to 0 even if there are tablespace<br>defined AND data in that BP. It was observed that storage wasn't released<br>immediately but buffers were released after a while, with data pages<br>active for a while as well. [...]66_OFEAA323EA.76FA733E-ONC1257759.003199C3-C1257759.00398A0F@cesve.it
21584 244 74_[Slightly OT] - IBM Hot Water-Cooled Supercomputer Goes Live at ETH Zurich13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Wed, 7 Jul 2010 06:54:40 -0400653_us-ascii Sorry for the slight OT, but I couldn't help be amused by this<br><br>They do say What goes around comes around<br><br>The article (at http://www-03.ibm.com/press/us/en/pressrelease/32049.wss) says<br><br>"IBM has delivered a first-of-a-kind hot water-cooled supercomputer to the Swiss Federal Institute of Technology Zurich (ETH Zurich), marking a new era in energy-aware computing. The innovative system, dubbed Aquasar, consumes up to 40 percent less energy than a comparable air-cooled machine. Through the direct use of waste heat to provide warmth to university buildings, Aquasar's carbon footprint is reduced by up to 85 percent." [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81F63@MAILR004.mail.lan
21829 394 80_Re: AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Wed, 7 Jul 2010 06:59:51 -0400536_us-ascii Actually, no I didn't<br><br>I was somewhat surprised that a DROP needs a table space to be in an active buffer pool - I guess DB2 was trying to flush any data before throwing it away (but why?)<br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk><br><br>Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.<br>8-12 November 2010, Vienna<br>Learn more at http://www.idug.org<http://www.idug.org/> [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81F65@MAILR004.mail.lan
22224 33 35_z/OS SQL Stored Procedure statement10_Teldb2kals22_teldb2kals@TELSTRA.COM30_Wed, 7 Jul 2010 07:20:33 -0400306_UTF-8 Hi,<br><br>DB2 z/OS. What is the best way of extracting the text of a SQL stored procedure, in order to copy to another database, or just provide someone a copy of the procedure ?<br><br>I have done it in the past using the client Development Center, but is there an easier way on z/OS itself ? [...]54_3978347825743091.WA.teldb2kalstelstra.com@www.idug.org
22258 232 78_Re: [Slightly OT] - IBM Hot Water-Cooled Supercomputer Goes Live at ETH Zurich14_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Wed, 7 Jul 2010 06:32:20 -0500482_utf-8 But Phil, if you had 400 independent servers chuncking away, you could heat your building AND roast hot dogs.<br><br>You gotta define your priorities... Be more efficient...or best use of your inefficient computers heat.<br><br>IBM messed up by making all those "energy reduction"/Green improvements. Just think if we still had those 3380 controllers pushing a petabyte of disk. You could not only heat your building, you could melt glass. Who wouldn't want a free kiln! [...]70_0CF187805D9C924DAFF59A877104C36A0491BE57@CHI4EVS04.corp.transunion.com
22491 117 88_AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)12_Peter, Georg15_G.Peter@DZBW.DE30_Wed, 7 Jul 2010 13:32:41 +0200558_US-ASCII >>>> Max Scarpa (DB2 for ENIAC sysprog) <<<<<br><br>Hi Max, don't worry.<br><br>We giant reptiles of the old school could not possibly understand the<br>modern technology.......<br><br>SCNR ;-))<br><br>Best regards,<br>Georg H. Peter<br><br>Abonnieren Sie unseren Infobrief und erfahren Sie regelm溥ig die neuesten Nachrichten 黚er unsere L鰏ungen, aktuellen Projekte und Entwicklungen. Melden sie sich an mit diesem Link http://www.datenzentrale.de/Info-Brief<br>_______________________________________________________________________________ [...]58_99E58D5AC1BE6D4CBF492AF72B942C3D061ADCE9@dzstus010.dzbw.de
22609 46 41_Searching STMT in SYSPACKSTMT table in v810_Teldb2kals22_teldb2kals@TELSTRA.COM30_Wed, 7 Jul 2010 07:41:05 -0400539_UTF-8 Hi,<br><br>I am doing a basic search on SYSPACKSTMT to identify programs using Temp tables.<br><br>Select * from SYSIBM.SYSPACKSTMT where STMT like '%GLOBAL%';<br><br>This doesn't return me the rows I am expecting.<br><br>So I tried<br><br>Select * from SYSIBM.SYSPACKSTMT WHERE CAST(STMT AS VARCHAR(3500) CCSID EBCDIC) LIKE '%GLOBAL%';<br><br>This works.<br><br>But is there a way of moving the CAST function to the right hand side of the predicate so that the query would not have to convert STMT for every row in the table ? [...]54_8862857671836279.WA.teldb2kalstelstra.com@www.idug.org
22656 217 36_DB2 Z/Os Version 9 Spatial Functions15_Schade, Jeffrey15_JSchade@ISO.COM30_Wed, 7 Jul 2010 08:54:24 -0400588_us-ascii We have installed the spatial extender on our DB2 Z/os Version 9<br>software and now the Developers are asking for assistance in using these<br>functions/stored procedures. We have been unable to find any clear<br>documentation on how to add spatial column data to new or existing<br>tables and are trying our best to figure it out by executing the stored<br>procedures and correcting errors as we go along. Can anyone point me to<br>documentation for the use of the spatial functions and possibly a set of<br>steps necessary to design and implement this for a given table. [...]59_DB138660B6B01D458B16F9205C0C263418476143@ISOEMAILP3.iso.com
22874 98 53_AW: [DB2-L] Searching STMT in SYSPACKSTMT table in v835_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Wed, 7 Jul 2010 15:06:09 +0200391_iso-8859-1 Hi Kals<br><br>When I tried your statement, I didn't get a row returned. I must use:<br><br>CAST(CAST(STMT AS VARCHAR(3500) CCSID 1208)AS VARCHAR(3500) CCSID EBCDIC)<br><br>I don't know, why it's working for you but nor for me. Are you a UNICODE shop?<br><br>But what about:<br><br>Select * from SYSIBM.SYSPACKSTMT WHERE STMT LIKE CAST('%GLOBAL%' AS VARCHAR(8) CCSID 1208) ; [...]43_DB2-L%201007070906409767.1F2C@IDUGDB2-L.ORG
22973 225 40_Re: DB2 Z/Os Version 9 Spatial Functions11_Roy Boxwell16_R.Boxwell@SEG.DE30_Wed, 7 Jul 2010 15:10:19 +0200654_ISO-8859-1 I thoughte the docu was pretty good myself! Even tells you that REORG dies<br>when you have spatial indexes....<br><br>Roy Boxwell<br>SOFTWARE ENGINEERING GMBH<br>-Product Development-<br>Robert-Stolz-Stra遝 5<br>40470 D黶seldorf/Germany<br>Tel. +49 (0)211 96149-675<br>Fax +49 (0)211 96149-32<br>Email: R.Boxwell@seg.de<br>http://www.seg.de<br><br>Software Engineering GmbH<br>Amtsgericht D黶seldorf, HRB 37894<br>Gesch鋐tsf黨rung: Siegfried F黵st, Gerhard Schubert<br><br>"Schade, Jeffrey" <JSchade@ISO.COM><br>Gesendet von: IDUG DB2-L <DB2-L@IDUGDB2-L.ORG><br>07.07.2010 14:54<br>Bitte antworten an<br>IDUG DB2-L <DB2-L@IDUGDB2-L.ORG> [...]64_OF0AD7C420.7BEE3A7D-ONC1257759.00484FE8-C1257759.00485B27@seg.de
23199 263 92_Re: AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)15_Gaston, Raymond17_GastonRay@ORU.COM30_Wed, 7 Jul 2010 09:17:02 -0400472_iso-8859-1 _.-_<br>__.' o "-.<br>___----"" ,,,'<br>__--" _-_ ^^;<br>____--"" ___ _-\" `""<br>..--===-------""" "( __-" ,}<br>.'_/"\<br>.'/" `\`._<br>"-_c " '<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg<br>Sent: Wednesday, July 07, 2010 7:33 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: [DB2-L] AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)<br><br>>>>> Max Scarpa (DB2 for ENIAC sysprog) <<<< [...]63_0A69F3794ED5F842B1A75AEF1D59EC430E8E2F1F@exchmbir.conedison.net
23463 586 40_Re: DB2 Z/Os Version 9 Spatial Functions15_Schade, Jeffrey15_JSchade@ISO.COM30_Wed, 7 Jul 2010 09:35:01 -0400592_iso-8859-1 Roy,<br><br>Which document are you referring too? I guess I have not found it.<br><br>Jeff<br><br>_________________________________________<br>Jeffrey Schade<br><br>Systems Consultant, Technology Engineering<br><br>ISO<br>545 Washington Boulevard<br>Jersey City, NJ 07310<br>Voice: (201) 469-3738<br><br>FAX: (201) 748-1500<br>jschade@iso.com <mailto:jschade@iso.com><br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell<br>Sent: Wednesday, July 07, 2010 9:10 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] DB2 Z/Os Version 9 Spatial Functions [...]59_DB138660B6B01D458B16F9205C0C263418476147@ISOEMAILP3.iso.com
24050 78 39_Re: z/OS SQL Stored Procedure statement12_Myron Miller22_myronwmiller@YAHOO.COM30_Wed, 7 Jul 2010 06:39:40 -0700492_us-ascii Why not Development Center or Data Studio? That's the easiest way that I know.<br><br>________________________________<br>From: Teldb2kals <teldb2kals@TELSTRA.COM><br>To: DB2-L@IDUGDB2-L.ORG<br>Sent: Wed, July 7, 2010 7:20:33 AM<br>Subject: [DB2-L] z/OS SQL Stored Procedure statement<br><br>Hi,<br><br>DB2 z/OS. What is the best way of extracting the text of a SQL stored procedure,<br>in order to copy to another database, or just provide someone a copy of the<br>procedure ? [...]44_776150.33843.qm@web112112.mail.gq1.yahoo.com
24129 656 34_Re: A DB2 virtual storage question12_Kirk Hampton37_kirk.hampton@ENERGYFUTUREHOLDINGS.COM30_Tue, 6 Jul 2010 16:17:05 -0500600_us-ascii We don't do datasharing here, but I have attended the workshop a few<br>years ago. If I am not mistaken, the two subsystems in your datasharing<br>group have separate ZPARMS, correct? So they can have different size<br>parms for EDMPOOL, etc. Also each subsystem can easily have different<br>number of buffers in their bufferpools, do a -DIS BPOOL(*) on each and<br>compare. And if the traffic to them is not balanced, then the busier<br>one will have more storage consumed to support active threads, I<br>believe. So I would expect it would be nearly impossible for both of<br>them to [...]71_31F1BA84DF370A4C825F6E64D59DAF54043EB525@MDCTXUEXCL01N4.corptxu.txu.com
24786 132 88_Re: [FLUFF] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 7 Jul 2010 15:57:22 +0200383_UTF-8 Bellissimo ! (very nice !). If in Vienna, at IDUG EMEA, you'll see<br>someone like this Dino but with a beard and a towel.....well It'll be me<br>!!<br><br>We Giant reptiles could not possibly understand modern technology....but<br>if modern technology means Windoze I'm happy to be a Dino.....<br><br>Thank you all so much for making me laugh, today I really need it.... [...]66_OFE5A65A80.8CA06C39-ONC1257759.0049AC0F-C1257759.004CAA20@cesve.it
24919 775 40_Re: DB2 Z/Os Version 9 Spatial Functions15_Schade, Jeffrey15_JSchade@ISO.COM30_Wed, 7 Jul 2010 10:05:49 -0400644_iso-8859-1 Never mind Roy, I found the 'Spatial Support Users Guide' for Z/os. I guess I wasn't looking hard enough before.<br><br>Thanks<br><br>Jeff<br><br>_________________________________________<br>Jeffrey Schade<br><br>Systems Consultant, Technology Engineering<br><br>ISO<br>545 Washington Boulevard<br>Jersey City, NJ 07310<br>Voice: (201) 469-3738<br><br>FAX: (201) 748-1500<br>jschade@iso.com <mailto:jschade@iso.com><br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Schade, Jeffrey<br>Sent: Wednesday, July 07, 2010 9:35 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] DB2 Z/Os Version 9 Spatial Functions [...]59_DB138660B6B01D458B16F9205C0C263418476149@ISOEMAILP3.iso.com
25695 367 92_Re: AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 7 Jul 2010 09:12:38 -0500495_iso-8859-1 Ray, you have too much time on your hands...<br><br>Cheers,<br><br>Raymond<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gaston, Raymond<br>Sent: 07 July 2010 14:17<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)<br><br>_.-_<br>__.' o "-.<br>___----"" ,,,'<br>__--" _-_ ^^;<br>____--"" ___ _-\" `""<br>..--===-------""" "( __-" ,}<br>.'_/"\<br>.'/" `\`._<br>"-_c " ' [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FCDBFF4@PHXCCRPRD04.adprod.bmc.com
26063 552 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Wed, 7 Jul 2010 10:14:11 -0400610_iso-8859-1 But it IS good<br><br>Phil Grainger<br>Cogito Ltd.<br>phil.grainger@cogito.co.uk<br>+44 (0) 1298 872 148<br>+44 (0) 7505 266 768<br>www.cogito.co.uk<blocked::http://www.cogito.co.uk><br><br>Attend IDUG 2010 - EMEA, the premiere event for DB2 professionals.<br>8-12 November 2010, Vienna<br>Learn more at http://www.idug.org<http://www.idug.org/><br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond<br>Sent: 07 July 2010 15:13<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join) [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E81FA4@MAILR004.mail.lan
26616 302 92_Re: AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)15_Gaston, Raymond17_GastonRay@ORU.COM30_Wed, 7 Jul 2010 10:23:43 -0400645_us-ascii It's the HEAT here in NY J<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bell, Raymond<br>Sent: Wednesday, July 07, 2010 10:13 AM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated<br>BP (Was: Nested loop vs. Hybrid join)<br><br>Ray, you have too much time on your hands...<br><br>Cheers,<br><br>Raymond<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Gaston,<br>Raymond<br>Sent: 07 July 2010 14:17<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] AW: [DB2-L] AW: [DB2-L] Create TS In Not Activated<br>BP (Was: Nested loop vs. Hybrid join) [...]63_0A69F3794ED5F842B1A75AEF1D59EC430E8E3151@exchmbir.conedison.net
26919 137 39_Re: z/OS SQL Stored Procedure statement10_Mark Stone17_mastone@GMAIL.COM30_Wed, 7 Jul 2010 07:49:37 -0700655_ISO-8859-1 It's also very easy to extract the SQL using Visual Studio.<br><br>On Wed, Jul 7, 2010 at 6:39 AM, Myron Miller <myronwmiller@yahoo.com> wrote:<br><br>> Why not Development Center or Data Studio? That's the easiest way that I<br>> know.<br>><br>> ------------------------------<br>> *From:* Teldb2kals <teldb2kals@TELSTRA.COM><br>> *To:* DB2-L@IDUGDB2-L.ORG<br>> *Sent:* Wed, July 7, 2010 7:20:33 AM<br>> *Subject:* [DB2-L] z/OS SQL Stored Procedure statement<br>><br>> Hi,<br>><br>> DB2 z/OS. What is the best way of extracting the text of a SQL stored<br>> procedure, in order to copy to another database, or just provide someone a<br>> [...]60_AANLkTikqB19q2SM-zII6MGqZt1TC_InZE3lzNmA1MNNO@mail.gmail.com
27057 59 39_Re: z/OS SQL Stored Procedure statement14_Michael Kalena27_Michael.Kalena@JPMORGAN.COM30_Wed, 7 Jul 2010 10:58:47 -0400366_utf-8 We're v8 and do it a few ways:<br><br>1. in the SP that does the build, there's a SQLCSRC DD that has a PDS for the Generated C Source. Each member starts off with the Create Procedure statement so you can get the source there.<br><br>2. In the Catalog, SYSIBM.SYSROUTINES_SRC contains the source too, so you can select it from there (column CREATESTMT). [...]75_276F3617E5150C48A5795557E7E68D200237EFBE68@EMASC201VS01.exchad.jpmchase.net
27117 181 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Wed, 7 Jul 2010 11:23:07 -04000_67_OF1E9364E2.6E540AB0-ON85257759.0053CEA1-85257759.00548106@pheaa.org
27299 768 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)13_Bell, Raymond20_Raymond_Bell@BMC.COM30_Wed, 7 Jul 2010 10:32:29 -0500468_iso-8859-1 Good idea, Mark. Might put in an abstract on it. Oh, wait; the date for submissions has closed. Oh well, next year.<br><br>And not to be outdone:<br><br>. .<br>/ `. .' \<br>.---. < > < > .---.<br>| \ \ - ~ ~ - / / |<br>~-..-~ ~-..-~<br>\~~~\.' `./~~~/<br>\__/ \__/<br>/ .- . \<br>_._ _.- .-~ ~-. / } \/~~~/<br>_.-'q }~ / } { ; \__/<br>{'__, / ( / { / `. ,~~| . .<br>`''''='~~-.__( /_ | /- _ `..-' \\ //<br>/ \ =/ ~~--~~{ ./| ~-. `-..__\\_//_.-'<br>{ \ [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420FCDC107@PHXCCRPRD04.adprod.bmc.com
28068 455 76_Re: [FLUFF] Create TS In Not Activated BP (Was: Nested loop vs. Hybrid join)10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Wed, 7 Jul 2010 11:46:47 -04000_67_OFF55EDAD6.A4E3D462-ON85257759.0055D57D-85257759.0056ABC7@pheaa.org
28524 43 15_MERGE Statement12_Cuneyt Goksu22_cuneyt.goksu@GMAIL.COM30_Wed, 7 Jul 2010 19:34:45 +0300421_ISO-8859-1 Hello DB2 Community,<br><br>MERGE INTO T1 A USING(SELECT C1,C2 FROM T2) B<br>ON (A.C1 = B.C1)<br>WHEN NOT MATCHED THEN<br>INSERT .........<br>WHEN MATCHED THEN<br>UPDATE .........<br><br>The order of NOT MATCHED and MATCHED is not important in the syntax.<br>But let's say the result set from the SELECT ... contains many dup<br>keys which means MERGE will hit MATCHED condition more than NOT<br>MATCHED. [...]60_AANLkTikXIusgac6gPjIPjcuIaoJsvdtdlLsF53korK5M@mail.gmail.com
28568 55 19_Re: MERGE Statement10_Joe Geller21_joerg6666@HOTMAIL.COM30_Wed, 7 Jul 2010 13:33:27 -0400545_UTF-8 I would have to think that if it matters, it matters a whole lot less than in<br>the programmatic approach. With the old solution, if you do the "wrong"<br>one first, you have to issue another SQL statement, with repositioning<br>on the index. With Merge, I would assume that DB2 finds the correct spot<br>in the index and then proceeds to either update or insert. I would also<br>guess that it doesn't matter which order you code it in because the<br>process of finding the right spot in the index should be essentially the<br>same. [...]53_4955011565156778.WA.joerg6666hotmail.com@www.idug.org
28624 69 19_Re: MERGE Statement13_Phil Grainger26_phil.grainger@COGITO.CO.UK30_Wed, 7 Jul 2010 13:40:31 -0400370_us-ascii As it happens I've been playing with merge this week<br><br>If uou explain one you'll see there is some sort of "join" going on<br><br>So perhaps what's going on internally is not as obvious as we think!<br><br>Phil G<br><br>Sent from my iPod, so typing is even worse than usual<br><br>On 7 Jul 2010, at 17:35, Cuneyt Goksu <cuneyt.goksu@GMAIL.COM> wrote: [...]49_0A3EF21A-B9C6-45A7-A8C0-453614205F3C@cogito.co.uk
28694 149 59_[z/OS][Job Posting] Harrisburg, PA wants DB2 DBA consultant14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 7 Jul 2010 14:03:36 -0400507_us-ascii Location: Harrisburg, PA - about 90 mi. NW of Philadelphia, PA, USA<br>Duration: 6 months starting 7/12/10<br>Rate: $70/hr 1099<br>Skills: Senior DB2 DBA, heavy z/OS and some Parallel Sysplex, some AIX desired, large environment experience needed (terabytes)<br><br>Email me offline and I'll connect you with the headhunter.<br>-ps<br><br>_____________________________________________________________________<br>* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA * [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46AB5@MAIL02.fisalan.nycnet
28844 128 63_Re: [z/OS][Job Posting] Harrisburg, PA wants DB2 DBA consultant10_John Bolas18_BolasJ@COMCAST.NET30_Wed, 7 Jul 2010 18:15:06 +0000499_utf-8 Please send me the name and email for the headhunter.聽 Thanks!<br><br>----- Original Message -----<br>From: "Phil Sevetson" <PSevetson@FISA.NYC.GOV><br>To: DB2-L@IDUGDB2-L.ORG<br>Sent: Wednesday, July 7, 2010 2:03:36 PM GMT -05:00 US/Canada Eastern<br>Subject: [DB2-L] [z/OS][Job Posting] Harrisburg, PA wants DB2 DBA consultant<br><br>Location:聽聽聽聽聽聽聽聽聽 Harrisburg, PA 鈥� about 90 mi. NW of Philadelphia, PA, USA<br><br>Duration:聽聽聽聽聽聽聽聽聽聽 6 months starting 7/12/10 [...]82_134234143.1409781278526506131.JavaMail.root@sz0018a.emeryville.ca.mail.comcast.net
28973 210 63_Re: [z/OS][Job Posting] Harrisburg, PA wants DB2 DBA consultant15_Gaston, Raymond17_GastonRay@ORU.COM30_Wed, 7 Jul 2010 14:21:25 -0400634_utf-8 Oops! You failed first test鈥�. 鈥淓mail me offline鈥︹��<br><br>From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Bolas<br>Sent: Wednesday, July 07, 2010 2:15 PM<br>To: DB2-L@IDUGDB2-L.ORG<br>Subject: Re: [DB2-L] [z/OS][Job Posting] Harrisburg, PA wants DB2 DBA consultant<br><br>Please send me the name and email for the headhunter. Thanks!<br><br>----- Original Message -----<br>From: "Phil Sevetson" <PSevetson@FISA.NYC.GOV><br>To: DB2-L@IDUGDB2-L.ORG<br>Sent: Wednesday, July 7, 2010 2:03:36 PM GMT -05:00 US/Canada Eastern<br>Subject: [DB2-L] [z/OS][Job Posting] Harrisburg, PA wants DB2 DBA consultant [...]63_0A69F3794ED5F842B1A75AEF1D59EC430E8E3642@exchmbir.conedison.net
29184 66 19_Re: MERGE Statement10_Joe Geller21_joerg6666@HOTMAIL.COM30_Wed, 7 Jul 2010 15:59:08 -0400359_UTF-8 From the DB2 9 for z/OS Technical Overview (SG24-7330):<br><br>'MERGE is equivalent to performing 鈥渟ource table鈥� left outer join 鈥渢arget table鈥�.'<br><br>That is the join you see in the Explain.<br><br>Joe<br><br>As it happens I've been playing with merge this week<br><br>If uou explain one you'll see there is some sort of "join" going on [...]53_6925886312979532.WA.joerg6666hotmail.com@www.idug.org
29251 520 34_Re: A DB2 virtual storage question10_Dave Barry14_dbarry@UPS.COM30_Wed, 7 Jul 2010 18:38:15 -0400343_us-ascii Bear in mind that a working set is comprised of virtual storage frames backed by REAL storage. Real storage is a resource pool managed by the operating system. Less-recently used virtual storage pages on a busy LPAR are more likely to be "stolen" (written over) or paged out (if they were changed) than on a lightly used system. [...]66_CF6886BAE7BCA244B2593F11C2FD1B58017F8E7789@njrarsvr3bf7.us.ups.com