1 IDUGDB2-L.ORG /home/listserv/home/db2-l May 2010, week 1
2 238 25_Re: Why do a DB2 Upgrade?10_Max Scarpa16_mscarpa@CESVE.IT30_Sat, 1 May 2010 11:04:00 +0200412_US-ASCII In this last period I talked with many non-IT people and medium-level
managers, and they all think their data come from the heavens for some
sort of miracle. They think their credit card process is a small computer
'somewhere' doing all the work and the only thing needed is the plastic
card. That's the level of perception for non IT managers. And from beans
counter point of view. [...]66_OF6A25DEF4.A246D67D-ONC1257716.00232D4B-C1257716.0031C7B2@cesve.it
241 59 47_Same application use DB2 and ORACEL cocurrently13_Laila hosaini19_lshosaini@YAHOO.COM30_Sat, 1 May 2010 03:35:26 -0700635_iso-8859-1 Hi Dear Listers
Is there any way ,the same application connect and use 2 databases ,one on DB2 V7 on Z/OS and the other database is Oracel 9G on windows?
Thanks In Advance,
L.S.H
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]43_677746.15115.qm@web50303.mail.re2.yahoo.com
301 97 55_Re: Getting Ready for DB2 10: The Competitive Advantage12_Roger Miller19_millerrl@US.IBM.COM30_Sat, 1 May 2010 13:39:25 -0400625_UTF-8 Pricing is complex. Consistency across a company at this size and scale has not been possible. For example, some products have monthly licenses, while others have you purchase the license and service. There are a dozen or so variations on the license charges that you did not mention, with reduced numbers of charge units for later versions of hardware, specialty engines, and options for newer applications which allow customers to reduce software charges. One of the most troubling problems for customers is growing CPU time, where the price of every product in the stack increases. Reducing the CPU time should [...]51_5411780589384822.WA.millerrlus.ibm.com@www.idug.org
399 148 51_Re: Same application use DB2 and ORACEL cocurrently15_Leon Katsnelson15_leon@CA.IBM.COM30_Sat, 1 May 2010 16:08:15 -0400669_US-ASCII Any application can connect to multiple databases at the same time
(including DB2 for z/OS and Oracle). The question is: "how do you want to
work with these databases once connected?". There are 3 common cases:
1. application establishes two separate connections and works with the two
databases independently
2. application establishes two separate connections that may participate
in a single distributed transaction where data in both data sources is
updated with full integrity
3. application establishes a single connection to a federated database and
works with it as if it was a single database (e.g. SELECT ... FROM T1, [...]68_OF32335A0C.A7D7CDCD-ON85257716.006DDFF4-85257716.006E9F03@ca.ibm.com
548 506 25_Re: Why do a DB2 Upgrade?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM30_Sun, 2 May 2010 00:39:38 -0700571_us-ascii Myron, I'm sorry to hear of the problems you have experienced. As I've already said in this thread, we have a single machine at 630 Mips, and the total cost of parallel sysplex was far less than half a million. I really do not see why people think they need a lot of horsepower to run parallel sysplex. We incurred no expense for training or extra manpower, nor any additional software costs. We are now able to upgrade everything without outages to CICS and DB2, which is all the business cares about. Yes, there will be the occasional Power-On-Reset, but [...]64_927FE8790B320742927BCF9ADF7AE48A18F2B42FB7@PDXMAIL.pacificorp.us
1055 543 55_Same application(s) use DB2 and ORACLE concurrently ...12_PUSCH Othmar27_Othmar.Pusch@IT-AUSTRIA.COM30_Mon, 3 May 2010 08:52:42 +0200498_us-ascii Hi Laila (alias " L.S.H. ") & Leon, dear all !
We run ORACLE's "Oracle Transparent GateWay for z/OS (OTGW: SQL/DB-10.2.0.3.92 and OSDI/NET-1.2.1.4)" on our Mainframe's to solve your question for our dec. oracle-programmers. We use & used OTGW (DB2: V6-V7-V8-V9), z/OS: 1.8 - 1.9 - 1.10 - 1.11 and ORACLE: V8 - V9 - V10) with (nearly) no problems. Okay, some ptf's (mainframe) & patch-set's (oracle-servers) we've had to apply long time ago in the area of a global UTF-Support. [...]63_81BB61A7ECD28B40BC5E4701B999860896351E2169@A2XMXS1V1.A2X.LAN.AT
1599 143 55_Re: Getting Ready for DB2 10: The Competitive Advantage10_Max Scarpa16_mscarpa@CESVE.IT30_Mon, 3 May 2010 11:42:16 +0200538_US-ASCII > If DB2 10 can reduce CPU time by 5%, then a lot of customers will pay
less for
> all products running on the same LPAR.
Hi Roger, could you explain it better ? For small shops (with few DB2
subsystems, 2-4 for instance) 5% in CPU reduction for DB2 could be a too
little gain to justify a jump to DB2 10 (I was told it's not 'V10' any
more...) comparing savings with the cost of new software. I say it as in 2
occasions, after a deep system-wide tuning, we were able to reduce CPU
consuption of [...]66_OF07A7D066.A5C3AA52-ONC1257718.00251B2F-C1257718.00354E07@cesve.it
1743 106 39_[v9 z/OS] Utilities Redbook New Edition14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Mon, 3 May 2010 11:15:42 -0400634_us-ascii Revised version of the DB2 Utilities Redbook, for those who aren't on the redbook mailing list:
DB2 9 for z/OS: Using the Utilities Suite (http://www.redbooks.ibm.com/abstracts/sg246289.html?Open)
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC468D1@MAIL02.fisalan.nycnet
1850 180 25_Re: Why do a DB2 Upgrade?10_Mark Labby21_mlabby@AESSUCCESS.ORG30_Mon, 3 May 2010 12:12:58 -04000_67_OF11ADAEA2.5CF76CDA-ON85257718.00572766-85257718.0059134C@pheaa.org
2031 39 51_Row_number function as replacement for MAX subquery12_Myron Miller22_myronwmiller@YAHOO.COM30_Mon, 3 May 2010 13:47:34 -0700528_us-ascii _____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L44_605399.56209.qm@web112118.mail.gq1.yahoo.com
2071 35 22_v8 CM and v9 migration10_Teldb2kals22_teldb2kals@TELSTRA.COM30_Mon, 3 May 2010 19:24:24 -0400470_UTF-8 Hi,
We are currently planning our migration (finally) to v8 NFM.
We are also looking at the possibility of upgrading to v9 CM as well as part of the same project, to avoid duplicated testing and to fasttrack v9 implementation. What are your thoughts on doing the two close to each other ? We haven't yet gone into details of what is involved in v9 migration, hence, not yet sure about the effort involved, and release incompatibilities with v8. [...]54_7632297666906988.WA.teldb2kalstelstra.com@www.idug.org
2107 42 34_Maximizing Your DB2 10 Upgrade ROI11_Dave Beulke19_dave@DAVEBEULKE.COM30_Mon, 3 May 2010 21:01:36 -04000_51_2430492897083477.WA.davedavebeulke.com@www.idug.org
2150 331 175_The DB2Night Show Episode #18 - "The TOP TEN DB2 LUW Mistakes", with special guest Klaas Brant, IBM DB2 Gold Consultant & Founder, KBCE - Friday 7 May 2010 at 11:00 am USA CDT13_David Chapman24_David.Chapman@IAG.COM.AU30_Tue, 4 May 2010 16:57:04 +1000785_us-ascii _______________________________________________________________________________________
Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________
Hello DB2 List,
The DB2Night Show Episode #18 - "The TOP TEN DB2 LUW Mistakes", with special guest Klaas Brant, IBM DB2 Gold Consultant & Founder, KBCE
The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. This live event will run approximately 60 minutes and participants will have the opportunity to pose questions to the presenter. Capacity is limited, so register early. [...]43_DB2-L%201005040257142669.02F7@IDUGDB2-L.ORG
2482 233 26_Re: v8 CM and v9 migration11_Roy Boxwell16_R.Boxwell@SEG.DE30_Tue, 4 May 2010 10:27:01 +0200470_ISO-8859-1 hmmm....the V8 NF migration hurt a lot more than the V9 NF migration so I
would: Back up my access paths - go to NF - make sure all is 100% ok -
REBIND the world - check the access paths - get it all working again and
then repeat for 9 I don't think 9 is any near as worrying as V8 was so
there is hope but as I said to someone the other day
"there is light at the end of the tunnel - you just have to hope it's not
an oncoming train!" [...]64_OF887E2ED9.CED4261B-ONC1257719.002E21A7-C1257719.002E6B67@seg.de
2716 187 26_Re: v8 CM and v9 migration12_Isaac Yassin20_yassini@BEZEQINT.NET30_Tue, 4 May 2010 11:47:37 +0300418_iso-8859-1 Hi,
Or, you can go from V7 to 9 while jumping on V8.
Works great if you plan it correctly, and you can run in
parallel if you have the DASD for that.
and, YES, I know it's not the recomended path :-)
Isaac Yassin
IBM Information Champion
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS V8 & 9
IDUG Israel RUG
IDUG EU-CPC 2010 [...]42_20100504114737.FZK00362@mas22.bezeqint.net
2904 162 26_Re: v8 CM and v9 migration10_Max Scarpa16_mscarpa@CESVE.IT30_Tue, 4 May 2010 13:04:19 +0200407_US-ASCII I agree , V7 ->V8 migration was (by far) scaring as there were a lot of
things involved other than 3 steps migration for DB2 (UNICODE 'in primis',
to be activated in z/OS). As introduced a lot of new features people felt
this migration as critical and error prone.
I never migrated to V9, but from what I heard one of main issues was
access path changes but have a look at: [...]66_OF122C438A.D4C4FEFA-ONC1257719.002EF4C2-C1257719.003CD136@cesve.it
3067 164 55_Re: Row_number function as replacement for MAX subquery10_Roger Hecq18_Roger.Hecq@UBS.COM30_Tue, 4 May 2010 07:51:09 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA614BE48A1@NSTMC101PEX1.ubsw.net
3232 118 55_Re: Row_number function as replacement for MAX subquery12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 4 May 2010 05:48:22 -0700469_us-ascii I screwed up. I got a new computer (Windows 7) and hit a wrong button. New email system that I am not used to.
I do humbly apologize to everyone.
Sorry.
Myron
________________________________
From: Roger Hecq
To: DB2-L@IDUGDB2-L.ORG
Sent: Tue, May 4, 2010 7:51:09 AM
Subject: Re: [DB2-L] Row_number function as replacement for MAX subquery
What
is the question? Your e-mail was textless. [...]44_953318.99603.qm@web112120.mail.gq1.yahoo.com
3351 30 13_JDBC Settings10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Tue, 4 May 2010 12:29:38 -0400447_UTF-8 HI All,
I'm looking for a good place to see how the jdbc and dynamic sql bind options get set. I noticed our versions of SYSLN* are very iold with keep dynamic(no) and degree(1). While the jdbc sql calls do say they are using that package the statements are still cached and use Degree(Any). So I'm trying to see which parms are the trumps in the configuration. There's one app that will greatly benefit to switch to Reopt(once) [...]53_2658754633840725.WA.jorghluekebcbsmn.com@www.idug.org
3382 39 10_Bill Kehoe13_Dan Luksetich18_danl@DB2EXPERT.COM30_Tue, 4 May 2010 09:37:54 -0700363_UTF-8 A friend and fellow DBA died too early this past weekend. Bill Kehoe was a
good man dedicated to the hard work of DB2 DBA, and I'll always keep what
I
learned from working with him with me as I will also his friendship. If
you
knew Bill, or just want to leave a kind word to the family of a fellow
DBA,
please sign his guest book. [...]42_5c0a875002d40f237a046331cdc7f581@127.0.0.1
3422 258 9_IFCID 31816_Daniel Luksetich18_danl@DB2EXPERT.COM30_Tue, 4 May 2010 19:02:18 -0500422_us-ascii Has anyone benchmark tested the performance impact of starting IFCID 318?
I've used this in several customer sites and have not noticed a performance
impact. However, I have not performed a controlled study. I know the
performance redbook says 5-6%, but it quite honestly looks like an
advertisement.
Thanks in advance!
Dan
Daniel L Luksetich
IBM Information Champion [...]35_037401caebe6$3cff9a30$b6fece90$@com
3681 130 38_DB2 Z/OS : Load Replace or Load Resume12_Sangram Shee22_shee.sangram@GMAIL.COM30_Wed, 5 May 2010 01:36:11 -0400445_ISO-8859-1 We're working on loading a few tables and debating if we should be using
LOAD RESUME YES or LOAD REPLACE to get a better performance.
Will appreciate the comments from the esteemed group.
Following are the details.
** Loading tables of various sizes ranging from 100 rows to 65M.
** The input load files will have nearly same number of rows as is currently
existing in the corresponding tables. [...]62_h2od5d6436f1005042236u944c9937je812dafaa7f3ea37@mail.gmail.com
3812 272 13_Re: IFCID 31812_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Wed, 5 May 2010 07:51:42 +0200363_Windows-1252 Dan,
we never really cared about the impact in detail. It's obvious that there is a performance impact. As a result, we only activate it when dynamic statement cache analysis really is required. But for those situations, it is unvoidable to switch it on - so from our point of view there was little reason to investigate in the impact... [...]43_SNT107-W181F1767AC53CBD6C484DEF1F40@phx.gbl
4085 570 24_Re: [SPAM] Re: IFCID 31816_Daniel Luksetich18_danl@DB2EXPERT.COM30_Wed, 5 May 2010 06:59:38 -0500661_us-ascii Peter,
Can you quantify "obvious"? I set 316,318 and have not noticed an impact.
Thanks
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Suhner
Sent: Wednesday, May 05, 2010 12:52 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] Re: IFCID 318
Dan,
we never really cared about the impact in detail. It's obvious that there is
a performance impact. As a result, we only activate it when dynamic
statement cache analysis really is required. But for those situations, it is
unvoidable to switch it on - so from our point of view there was little
reason to investigate in the impact... [...]35_03c501caec4a$7280a910$5781fb30$@com
4656 257 22_[AD] db2expert updates16_Daniel Luksetich18_danl@DB2EXPERT.COM30_Wed, 5 May 2010 07:05:39 -0500369_us-ascii Hello,
I have finally begun making updates to the db2expert.com site. The events
page is updated, and there are two new links to recent magazine articles on
the links page. More updates, including downloads, coming soon. All content
(including 35 or so podcasts) is completely free to all! Make sure to say hi
at IDUG NA if you see me! [...]35_03db01caec4b$4ed0f5a0$ec72e0e0$@com
4914 464 42_Re: DB2 Z/OS : Load Replace or Load Resume13_Bright, Randy20_Randy_Bright@BMC.COM30_Wed, 5 May 2010 07:32:20 -0500595_us-ascii Are you using IBM Utilities or do you have a third party product?
Since you will be loading almost as many rows as already exist in the tablespace, if you have LoadPlus for DB2 from BMC Software, LOAD RESUME YES with INDEX BUILD would be more efficient. LoadPlus will append the new data to the existing tablespace while unloading existing keys from indexes, merging them with the new keys using BMCSORT, and rebuilding the indexes. This leaves your indexes reorganized when you are done. If your input data is not in clustering key sequence, you can also specify ORDER YES [...]69_476996CBBE9AF14285E09E63C370072A13F7DDB4AC@PHXCCRPRD01.adprod.bmc.com
5379 304 42_Re: DB2 Z/OS : Load Replace or Load Resume10_Roger Hecq18_Roger.Hecq@UBS.COM30_Wed, 5 May 2010 08:39:19 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA614BE4C1C@NSTMC101PEX1.ubsw.net
5684 31 28_Error reported from DSN1CHKR15_Mehdi Hejazifar18_mhej2004@YAHOO.COM30_Wed, 5 May 2010 08:57:03 -0400820_UTF-8 Hi,
I ran DSN1CHKR utility (on DB2 v9 for z/OS) and saw this output:
DSN1828I OBID = 001 IS INVALID FOR THIS DATABASE.
What can I do to repair it?
Parameters for DSN1CHKR are :
PARM='MAP=RID(00000201,06,00000B01,06)
Thanks
Mehdi
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________ [...]50_8781923647245613.WA.mhej2004yahoo.com@www.idug.org
5716 280 50_AW: [DB2-L] DB2 Z/OS : Load Replace or Load Resume35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM30_Wed, 5 May 2010 15:17:02 +0200535_iso-8859-1 Hi
AFAIK, Load RESUME will place the data rows always at the end of the tablespace and indexes always in sorted order, leaving the tablespace always in bad clusterratio (if the keys aren't all ascending and greater than those already in the table) and the index with many page splits.
So if you unload your existing data, sort both datsets and merge them together and then reload, can be better. But mind you, if there are more than one table in that tablespace, load replace will empty the other tables. [...]43_DB2-L%201005050917141116.0377@IDUGDB2-L.ORG
5997 43 54_Re: AW: [DB2-L] DB2 Z/OS : Load Replace or Load Resume10_Joe Geller21_joerg6666@HOTMAIL.COM30_Wed, 5 May 2010 09:43:44 -0400494_UTF-8 Load Resume will place the rows at the end unless you also you specify Sharelevel Change. In that case,
Load works like Inserts and will try to place the rows in clustering order.
Joe
AFAIK, Load RESUME will place the data rows always at the end of the tablespace and indexes always in sorted order, leaving the tablespace always in bad clusterratio (if the keys aren't all ascending and greater than those already in the table) and the index with many page splits. [...]53_0227658740122652.WA.joerg6666hotmail.com@www.idug.org
6041 25 17_Re: JDBC Settings10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM30_Wed, 5 May 2010 09:50:46 -0400751_UTF-8 Well I have found out that there are special collections for Reopt(Once) and Reopt(Any), NULLIDR1 and NULLIDRA.
Now I just need to find the java method that sets runtime degree in JDBC.
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________ [...]53_8762287818185184.WA.jorghluekebcbsmn.com@www.idug.org
6067 433 42_Re: DB2 Z/OS : Load Replace or Load Resume12_Isaac Yassin20_yassini@BEZEQINT.NET30_Wed, 5 May 2010 16:55:09 +0300388_utf-8 Hi,
On top of other answers – "load replace" does a delete & define of the VSAM space on the DASD (both tablespace and indexspace).
LOAD REPLACE REUSE does not do it as it just changes the "hi-use-rba" to zero. Time difference depends on number of files involved.
If you have 1 table only in your tablespace you can use load replace without any problem. [...]35_00fb01caec5a$93665750$ba3305f0$@net
6501 349 24_Re: [SPAM] Re: IFCID 3187_Ed Long19_rdhm99a@PRODIGY.NET30_Wed, 5 May 2010 07:31:48 -0700448_utf-8 I did a very large benchmark in Poughkeepsie about a year ago with 316->318 active. The incremental cost was negligible; the explain statement cache data we collected was priceless.
Edward Long
--- On Wed, 5/5/10, Daniel Luksetich wrote:
From: Daniel Luksetich
Subject: Re: [DB2-L] [SPAM] Re: IFCID 318
To: DB2-L@IDUGDB2-L.ORG
Date: Wednesday, May 5, 2010, 7:59 AM [...]43_891492.35900.qm@web80202.mail.mud.yahoo.com
6851 143 54_Re: AW: [DB2-L] DB2 Z/OS : Load Replace or Load Resume10_Max Scarpa16_mscarpa@CESVE.IT30_Wed, 5 May 2010 16:50:37 +0200431_US-ASCII In the past I faced similar problem from programmers and as said in
previous posts it depends.
I did some tests and they showed if you have a relative low number of
rows LOAD RESUME + later REORG (if needed) is the best choice, considering
n.o of indexes, the time you have for LOAD data with concurrent SQL
programs, the chance to use SET NOCOPYPEND or if you need IC using LOAD
LOG NO etc.... [...]66_OFDFFADA3E.27E81294-ONC125771A.004A57C4-C125771A.00518945@cesve.it
6995 37 42_Re: DB2 Z/OS : Load Replace or Load Resume13_Jack Campbell23_jackrcampbell@YAHOO.COM30_Wed, 5 May 2010 12:44:17 -0400472_UTF-8 Also don't forget about
1) the compression dictionary - if you intend to load very large tables.
Will the existing compression dictionary match to the new data, or will you see a degredation in compression when the new data is loaded. If you need to rebuild the compression dictionary LOAD REPLACE
2) If partitioned tablespaces, you may benefit from parallel loads - break data by limitkey, sort, load (RESUME or REPLACE at the part level) [...]55_6819916809214417.WA.jackrcampbellyahoo.com@www.idug.org
7033 31 19_MQT and OUTER JOINS10_John Lantz20_john.p.lantz@FRB.GOV30_Wed, 5 May 2010 12:54:59 -0400384_UTF-8 Can you create a MQT on z/OS that is based on a SELECt with OUTER JOIN's? I saw some comments (I think it was pertaining to LUW) that you can as long as it's not REFRESH IMMEDIATE. I'm trying to define mine as REFRESH DEFERRED. The error soming back just says:
DSNT408I SQLCODE = -20058, ERROR: THE FULLSELECT SPECIFIED FOR MATERIALIZED
QUERY TABLE .... IS NOT VALID [...]52_9804205932866679.WA.john.p.lantzfrb.gov@www.idug.org
7065 58 23_Re: MQT and OUTER JOINS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 5 May 2010 12:59:15 -0400638_us-ascii John,
What an interesting question! Can we see a suitably anonymized version of your construct so that the tinkers among us can play with it?
-Phil Sevetson
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Lantz
Sent: Wednesday, May 05, 2010 12:55 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] MQT and OUTER JOINS
Can you create a MQT on z/OS that is based on a SELECt with OUTER JOIN's? I saw some comments (I think it was pertaining to LUW) that you can as long as it's not REFRESH IMMEDIATE. I'm trying to define mine as REFRESH DEFERRED. The error [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC468F2@MAIL02.fisalan.nycnet
7124 261 50_DB2 for Z/Os V9 NFM Implicitly Defined Tablespaces15_Schade, Jeffrey15_JSchade@ISO.COM30_Wed, 5 May 2010 13:09:42 -0400626_US-ASCII We have DDL generated by a script for a 3rd party application which uses
implicitly defined tablespaces (I.e. Create Table with no TS name given)
and we have been experiencing deadlocks at a row level. When we take the
DDL from the script and add CREATE TABLESPACE the application runs
without deadlocks. We are trying to determine what is different between
the tablespace definitions and the only thing we have identified is that
the implicit tablespaces are partitioned with 1 partition while the ones
we created are non partitioned. We would like to use the script for this
product to [...]59_DB138660B6B01D458B16F9205C0C26340AA4A8B7@ISOEMAILP3.iso.com
7386 138 23_Re: MQT and OUTER JOINS11_Suresh Sane21_data_arch@HOTMAIL.COM30_Wed, 5 May 2010 12:35:35 -0500607_iso-8859-1 John,
I recall this being related to ENABLE QUERY OPTIMIZATION - you cannot have outer joins in that case.
Thx
Suresh
> Date: Wed, 5 May 2010 12:54:59 -0400
> From: john.p.lantz@FRB.GOV
> Subject: [DB2-L] MQT and OUTER JOINS
> To: DB2-L@IDUGDB2-L.ORG
>
> Can you create a MQT on z/OS that is based on a SELECt with OUTER JOIN's? I saw some comments (I think it was pertaining to LUW) that you can as long as it's not REFRESH IMMEDIATE. I'm trying to define mine as REFRESH DEFERRED. The error soming back just says:
> DSNT408I SQLCODE = -20058, [...]43_COL104-W359BC482C1641B7798409A98F40@phx.gbl
7525 66 55_Re: Getting Ready for DB2 10: The Competitive Advantage12_Roger Miller19_millerrl@US.IBM.COM30_Wed, 5 May 2010 12:59:01 -0400551_UTF-8 Small customers and large customers who still have a little room in the CPU may not need to move fast, but customers who have the alternative of upgrading their processor or moving to the next DB2 will probably move quickly. DB2 10 is still in beta, so the information is preliminary, but very good so far. The reaction I've been getting from customers is the strongest of any version since Version 2 Release 1. We have large and small customers working on the beta. I did a webcast with Dave Beulke a couple of weeks ago, and more than 50 [...]51_0780989816128644.WA.millerrlus.ibm.com@www.idug.org
7592 59 23_Re: MQT and OUTER JOINS10_John Lantz20_john.p.lantz@FRB.GOV30_Wed, 5 May 2010 15:42:19 -0400844_UTF-8 This is what I'm trying...
CREATE TABLE NARI.CUV_SUPV_RESP_DIST_FRS_1STSELECT_MQT
( ID_RSSD_TOP , DT_START , DT_END , D_DT_START ,
D_DT_END , ID_RSSD_TOP_AFTER , ID_RSSD_TOP_BEFORE ) AS
( SELECT T1.ID_RSSD_TOP , T1.DT_START , T1.DT_END ,
T1.D_DT_START , T1.D_DT_END , TAFTER.ID_RSSD_TOP AS
ID_RSSD_TOP_AFTER , TBEFORE.ID_RSSD_TOP AS ID_RSSD_TOP_BEFORE
FROM NARI.CUV_TOP_HOLDER T1
LEFT OUTER JOIN NARI.CUV_TOP_HOLDER TAFTER
ON T1.ID_RSSD_TOP = TAFTER.ID_RSSD AND T1.CTRL_FLG = 'Y'
AND TAFTER.CTRL_FLG = 'Y' AND T1.D_DT_END < TAFTER.D_DT_START
LEFT OUTER JOIN NARI.CUV_TOP_HOLDER TBEFORE
ON T1.ID_RSSD_TOP = TBEFORE.ID_RSSD
AND T1.CTRL_FLG = 'Y' AND TBEFORE.CTRL_FLG = 'Y'
AND T1.D_DT_START > TBEFORE.D_DT_END
WHERE T1.HOLDER_TYPE = 'REG' AND T1.CTRL_FLG = 'Y'
) DATA INITIALLY DEFERRED [...]52_6649980097792523.WA.john.p.lantzfrb.gov@www.idug.org
7652 74 23_Re: MQT and OUTER JOINS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 5 May 2010 16:01:22 -0400466_us-ascii Wait -- the question I should have asked first: Does this work as SQL when you run it in a processor or a program? (If performance is bad, consider restricting the return set by adding a predicate for a single T1.ID_RSSD_TOP)
-ps
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Lantz
Sent: Wednesday, May 05, 2010 3:42 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] MQT and OUTER JOINS [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC468FC@MAIL02.fisalan.nycnet
7727 599 24_Re: [SPAM] Re: IFCID 31812_Peter Suhner24_peter_suhner@HOTMAIL.COM30_Wed, 5 May 2010 22:52:20 +0200372_Windows-1252 Dan,
not "obvious" in the sense of "noticeable" (limited linguistical accuracy of a non-native speaking individual).
What I meant was that - even though we never cared about measuring this - additional resources are used up to
gather, store, format the requested information. That's why we only turn it on if we intend to analyze this data. [...]42_SNT107-W6C72C1C30325287C75D2FF1F40@phx.gbl
8327 93 38_[z/OS] V8.0 Lock escalation on Select?3_Ray22_ray_in_doubt@YAHOO.COM30_Wed, 5 May 2010 13:53:09 -0700592_iso-8859-1 Dear Listers,
I am aware of Lock Escalation but getting it at a TS level for a SELECT statement
is making me ponder ...why? I can understand for an UPDATE statement but
on a SELECT.....
Issue:
Multiple jobs (partitioned jobs) get timed out on a -904 while trying to execute
the same DECLARE CURSOR statement that has a SELECT Clause with
Cursor Stability?
Error Message Received:
UNAVAILABLE RESOURCE. REASON 00C200FA, TYPE OF RESOURCE
00000200, AND RESOURCE NAME ARROP9DB.ARROP901.
SQLSTATE = 57011 SQLSTATE RETURN CODE [...]43_177715.71732.qm@web45508.mail.sp1.yahoo.com
8421 49 23_Re: MQT and OUTER JOINS12_Pete Woodman20_pete.woodman@EDS.COM30_Wed, 5 May 2010 17:11:20 -0400669_UTF-8 I don't think it's allowed. See description of Fullselect under the CREATE TABLE statement in the SQL Reference ...
When a materialized query table is defined with ENABLE QUERY OPTIMIZATION specified, the following additional restrictions apply:
The fullselect must be a subselect.
The subselect cannot contain
- An ORDER BY clause
- A FETCH FIRST clause
- A nested table expression or view that requires temporary materialization
- A join using the INNER JOIN syntax
- An outer join
- A special register
- A scalar fullselect
- A row change timestamp column
- Any predicates that include subqueries.
- [...]52_9496930709047149.WA.pete.woodmaneds.com@www.idug.org
8471 132 42_Re: [z/OS] V8.0 Lock escalation on Select?0_24_hhuang@DCCSH.ICBC.COM.CN30_Thu, 6 May 2010 08:54:01 +0800439_GB2312 Ray,
REASON 00C200FA means DB2 is unable to get an open lock to perform
OPEN/CLOSE on the page set. Please check:
Is the page set HSM migrated?
Is your IRLM address space service class set correctly?
And last, for a very busy muiltiple jobs burst, especially with high
CPU usage, it is possible that some lock contentions happened to
prevent the normal locks obtaining process that lead to a -904. [...]69_OFF47AAFE5.92F6DA27-ON4825771B.000418C7-4825771B.0004F1C5@icbc.com.cn
8604 56 42_Re: [z/OS] V8.0 Lock escalation on Select?10_Joe Geller21_joerg6666@HOTMAIL.COM30_Wed, 5 May 2010 22:14:32 -0400433_UTF-8 00C200FA is not a lock escalation, it is a timeout opening the dataset. One
cause is if the tablespace dataset was migrated by HSM and it took longer
than the timeout limit to recall it. (this is from the Codes manual).
Joe
I am aware of Lock Escalation but getting it at a TS level for a SELECT statement
is making me ponder ...why? I can understand for an UPDATE statement but
on a SELECT..... [...]53_1459839910828972.WA.joerg6666hotmail.com@www.idug.org
8661 139 54_Re: DB2 for Z/Os V9 NFM Implicitly Defined Tablespaces0_24_hhuang@DCCSH.ICBC.COM.CN30_Thu, 6 May 2010 11:24:19 +0800720_GB2312 Jeffrey,
For DB2 9, the implicitly created tablespace is a UTS with LOCKSIZE ROW.
And for the default LOCKSIZE ANY, DB2 chooses PAGE.
I think this is the point.
"Schade, Jeffrey"
·¢¼þÈË: IDUG DB2-L
2010-05-06 01:09
Çë´ð¸´ ¸ø
IDUG DB2-L
ÊÕ¼þÈË
DB2-L@IDUGDB2-L.ORG
³ËÍ
Ö÷Ìâ
[DB2-L] DB2 for Z/Os V9 NFM Implicitly Defined Tablespaces
We have DDL generated by a script for a 3rd party application which uses
implicitly defined tablespaces (I.e. Create Table with no TS name given)
and we have been experiencing deadlocks at a row level. When we take the
DDL from the script and [...]69_OF3027073D.AC83C088-ON4825771B.0012460B-4825771B.0012B582@icbc.com.cn
8801 172 23_Re: MQT and OUTER JOINS28_Balachandran Chandrasekaran119_balaccha@IN.IBM.COM30_Thu, 6 May 2010 11:26:24 +0530553_US-ASCII Hi John,
No. The fullselect on the MQT can not have OUTER join as far as I know.
Under Chapter 10 in the IBM redbook SG24-6079-00, you can find other
considerations depending upon your QUERY OPTIMiZATION option
(ENABLE/DISABLE).
Hope this helps.
Regards, Bala.
John Lantz
Sent by: IDUG DB2-L
05/05/2010 22:24
Please respond to
IDUG DB2-L
To
DB2-L@IDUGDB2-L.ORG
cc
Subject
[DB2-L] MQT and OUTER JOINS [...]68_OF3FB73AC0.38BBF3D4-ON6525771B.0020151A-6525771B.00205D06@in.ibm.com
8974 102 48_[DB2 z/OS V8] MQT hiding itself from optimizer !6_Bala C21_bala.db2dba@GMAIL.COM30_Thu, 6 May 2010 12:52:39 +0530588_ISO-8859-1 Dear list,
Not very long ago, there was a discussion on a MQT not picked by the
optimizer. I am facing similar situation/challenge. As far as I know, I am
doing everything as required.
1. MQT is refreshed (using REFRESH as well as using the LOAD, it was USER
MANAGED).
2. STATS done on base tables and MQT. Dynamic cache invalidated too.
3. ZPARM for REFRESH AGE and MQT types set to ANY and ALL respectively.
4. The SQL is dynamic (on SPUFI and in future would run dynamically from
JDBC). I have cachedyn turned on (if that matters).
5. [...]62_n2k35e6ff9a1005060022o7c477009nff515bbfae20c4da@mail.gmail.com
9077 74 52_Re: [DB2 z/OS V8] MQT hiding itself from optimizer !10_Joe Geller21_joerg6666@HOTMAIL.COM30_Thu, 6 May 2010 09:02:03 -0400571_UTF-8 Dear list,
Not very long ago, there was a discussion on a MQT not picked by the optimizer. I am facing similar situation/challenge. As far as I know, I am doing everything as required.
1. MQT is refreshed (using REFRESH as well as using the LOAD, it was USER MANAGED).
2. STATS done on base tables and MQT. Dynamic cache invalidated too.
3. ZPARM for REFRESH AGE and MQT types set to ANY and ALL respectively.
4. The SQL is dynamic (on SPUFI and in future would run dynamically from JDBC). I have cachedyn turned on (if that matters).
5. [...]53_0328111217264376.WA.joerg6666hotmail.com@www.idug.org
9152 32 23_Re: MQT and OUTER JOINS10_John Lantz20_john.p.lantz@FRB.GOV30_Thu, 6 May 2010 14:02:01 -0400543_UTF-8 As several pointed out, you can't have OUTER when a MQT is defined with ENABLE QUERY OPTIMIZATION. I was able to create it by specifying DISABLE QUERY OPTIMIZATION. This MQT will actually be part of a view. I'm not really sure if it's hurting anything by not allowing QUERY OPTIMIZATION. The manual says you can till query the MQT directly - which is what I'm doing in the view. My guess is that I'm OK since people/applications will only be querying the view that contains the MQT. Nobody will be querying the MQT's by themselves. [...]52_7015059043539680.WA.john.p.lantzfrb.gov@www.idug.org
9185 61 23_Re: MQT and OUTER JOINS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Thu, 6 May 2010 14:06:56 -0400523_us-ascii John,
ENABLE QUERY OPTIMIZATION allows people who are writing queries with the same logic as the MQT (or with a superset of that), to automatically use the MQT in their access path instead of re-extracting the data from the objects named in their query. (Does that help?)
--ps
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of John Lantz
Sent: Thursday, May 06, 2010 2:02 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] MQT and OUTER JOINS [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46912@MAIL02.fisalan.nycnet
9247 248 52_Re: [DB2 z/OS V8] MQT hiding itself from optimizer !6_Bala C21_bala.db2dba@GMAIL.COM30_Fri, 7 May 2010 11:29:06 +0530581_ISO-8859-1 Hi Joe, Thanks for your response.
*We need a little more information. What access path does the Explain show?
*
The access paths for the joins is inner Join and all the indexes on the
tables are picked.
* Does it do the joins in the order TAB1, TAB2, TAB3, TAB4? *Yes.
*What is the time estimate in DSN_STATEMNT_TABLE? And, what is the time
estimate
if you explicitly join the MQT to TAB4?
*
The statement cost of the SQL remains same with or without the MQT. i.e if I
join the MQT and the TAB4, the access path and [...]62_q2m35e6ff9a1005062259if3479864q95f35a178cb81415@mail.gmail.com
9496 206 42_Re: DB2 Z/OS : Load Replace or Load Resume6_Bala C21_bala.db2dba@GMAIL.COM30_Fri, 7 May 2010 14:29:11 +0530363_ISO-8859-1 If I were you, I would go for an approach that the table demands. I was once
asked to implement one approach/PROC for all tables in the system in case of
REORG and for over an year, we are spending time optimizing DASD, elapsed
time etc.. And, DB2 versions with which enhancements or changes in behavior
come in the way Utilities run. [...]62_z2h35e6ff9a1005070159t45d94e00gb08570e4075b8767@mail.gmail.com
9703 62 19_DB2 Sample Question12_Ian Reynolds28_ireynold@FIRSTDATACORP.CO.UK30_Fri, 7 May 2010 06:58:46 -0400398_UTF-8 There is some confusion as to whether the correct answer has been given to a question for the 702 Sample Exam in DB2 for z/OS Version 8 DBA Certification Guide by Susan Lawson.
Question 57 asks:
A DBA wants to check the health of the DB2 catalog or directory. WHich of the following utilities should the DBA not use?
a) REPAIR
b) REPORT
c) DSN1COPY
d) DSN1CHKR [...]60_9899915676691362.WA.ireynoldfirstdatacorp.co.uk@www.idug.org
9766 31 32_z/OS DB2 9: U0046 in online LOAD13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Fri, 7 May 2010 07:40:42 -0400489_UTF-8 Can anyone explain to me why a LOAD SHRLEVEL CHANGE abend with U0046?
The scenario was that the LOAD job was cancelled and the utility was stopped in the RELOAD phase (I beleive RELOAD is the only phase in an online load).
The utility was terminated and the job was rerun. This second time all rows from the input file was discarded because they were duplicates (DSNU1117I), after that all rows was discarded, the job abend with: Module ICEXPUB offset X'286': Abend U0046 [...]57_5389208234346171.WA.olle.brostromswedbank.se@www.idug.org
9798 43 36_Re: z/OS DB2 9: U0046 in online LOAD15_Lizette Koehler23_starsoul@MINDSPRING.COM30_Fri, 7 May 2010 07:56:38 -0400586_UTF-8 Are you seeing any messages in the ICE section of the LOAD?
Sometimes there are supporting messages from SORT that might help determine the cause.
Lizette
>
> Can anyone explain to me why a LOAD SHRLEVEL CHANGE abend with U0046?
> The scenario was that the LOAD job was cancelled and the utility was
> stopped in the RELOAD phase (I beleive RELOAD is the only phase in an
> online load).
> The utility was terminated and the job was rerun. This second time all
> rows from the input file was discarded because they were duplicates
> [...]35_01ae01caeddc$598a62f0$0c9f28d0$@com
9842 37 36_Re: z/OS DB2 9: U0046 in online LOAD11_Enrico Haak21_eh@INSOFT-SOFTWARE.DE30_Fri, 7 May 2010 08:07:59 -0400354_UTF-8 Hi Olle,
this is a DFSORT "problem".
To avoid this error, increase the number of work data sets available for DFSORT to dynamically allocate. The maximum number of work data sets available is controlled in the DYNALLOC parameter of ICEMAC.
If the above circumvention does not correct the error then add the following DD : [...]52_9172628178295065.WA.ehinsoftsoftware.de@www.idug.org
9880 88 23_Re: DB2 Sample Question14_Fazio, Richard21_RFAZIO@TRANSUNION.COM30_Fri, 7 May 2010 07:19:05 -0500497_us-ascii REPAIR DBD TEST/DIAGNOSE provide great clues to the soundness of a DBD.
faz
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@idugdb2-l.org] On Behalf Of Ian Reynolds
Sent: Friday, May 07, 2010 5:59 AM
To: DB2-L@idugdb2-l.org
Subject: [DB2-L] DB2 Sample Question
There is some confusion as to whether the correct answer has been given
to a question for the 702 Sample Exam in DB2 for z/OS Version 8 DBA
Certification Guide by Susan Lawson. [...]70_0CF187805D9C924DAFF59A877104C36A046387B5@CHI4EVS04.corp.transunion.com
9969 105 36_Re: z/OS DB2 9: U0046 in online LOAD10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 7 May 2010 14:38:17 +0200462_US-ASCII Usually it's 'SORT CAPACITY EXCEEDED'. Try to find 'EXCEEDED' in your
sysout, there'd be a section where all SORT parametersallocations are
described (and giving you some hints so).
It depends on your sort defaut allocation which is very often not too big.
If you're in V9 SORT utilization changed a bit.
Max Scarpa
'IDUG et amo. Quare id faciam fortasse requiris. Nescio, sed fieri sentio,
et gaudenda est Vienna' [...]66_OF940A476F.E5FBF5CD-ONC125771C.0044A1CE-C125771C.00456B8E@cesve.it
10075 25 10_Threadsafe16_Alison Pelletier28_alisonpelletier@DISCOVER.COM30_Fri, 7 May 2010 09:06:03 -0400347_UTF-8 Hello. I am posting this for our CICS team. They have run benchmark on quasirent vs. threadsafe DB2 heavy transactions. The CICS PA rept. shows greatly reduced TCB switching, but the user CPU for the transaction is not reduced. Can anyone respond with their threadsafe savings? Are we looking at the right number to compare? Thank you. [...]60_8228226330346424.WA.alisonpelletierdiscover.com@www.idug.org
10101 59 44_SV: [DB2-L] z/OS DB2 9: U0046 in online LOAD13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE30_Fri, 7 May 2010 15:29:35 +0200508_utf-8 Hello,
What are DB2 trying to sort?
My LOAD statements looks like:
LOAD DATA RESUME YES SHRLEVEL CHANGE
INTO TABLE DB2C.CDPTKTN7 WHEN(240:242)='001'
(KONTONR POSITION(3) CHAR(19),
REP_KEY POSITION(243) CHAR(24))
I didn't include the SORTDEVT keyword because I thought SORT was not invoked in an online load, we also use UTSORTAL YES to let DB2 allocate the sortworks datasets. IF DB2 invoke sort I think SORTDEVT must be specified? There are no sort messages written. [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A99832@FSPAS01EV011.fspa.myntet.se
10161 113 14_Re: Threadsafe10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 7 May 2010 16:17:17 +0200410_US-ASCII Hi
Actually I worked (for a very very short time) to (try to) implement
threadsafe so I haven't any number.
From what I remember it's not an easy task: CPU savings are maximized
when when no non-Threadsafe commands are issued between the first SQL
command and the last, for instance. There are some other things to check
but I don't remeber them well. See for instance: [...]66_OF3BDB05E6.D74B8E73-ONC125771C.004CFBDC-C125771C.004E7BC1@cesve.it
10275 124 64_[AD] Apply to become an IDUG Mentor...and earn a big discount !!10_Max Scarpa16_mscarpa@CESVE.IT30_Fri, 7 May 2010 16:25:33 +0200374_US-ASCII Esteemed DB2 professionals
IDUG gives you the great opportunity to gain an 80% discount for a
first-time attendee collegue in year 2010. How ? By applying to become an
IDUG Mentor !
All DB2 professionals who have attended at least five IDUG conferences in
the last 10 years are eligible to obtain 'IDUG Mentor' status and
recognition [...]66_OF31E3DDC5.1BDED27F-ONC125771C.0046E824-C125771C.004F3D97@cesve.it
10400 197 81_AD: Buffer Pool Tool and Performance Wizard Demo, new Web browser based Interface35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Fri, 7 May 2010 11:03:02 -0400476_utf-8 Wednesday and Thursday, Booth 200, at IDUG/Tampa.
We are not planning demos for the initial Tuesday evening opening, because of the expected large traffic volumes.
Demos are not on any fixed timetable, come up and ask - they require only a few minutes of your time.
We will be giving demonstrations of both the current workstation code with the Performance Wizard,
and the new Java enabled Web browser replacement available later this year. [...]46_5031170BB0A8438BB6C29D4DD9F54F8D@DellNotebook3
10598 90 14_Re: Threadsafe19_Humphris,Richard P.25_RichardP.Humphris@CNA.COM30_Fri, 7 May 2010 10:33:46 -0500432_us-ascii Hi Alison,
Perhaps the cpu savings were in the overhead of doing the tcb switching. And perhaps the CICS/system overhead wasn't charged to the transaction so just looking at the CICS AP rept may not be showing the full story.
Btw, are you seeing any substantial savings in elapsed time for the transaction? That should also happen if you switch to thread safe and are seeing a big drop in TCB switching. [...]60_10B866DA3B4BC44FA723C2C315C8FE6F01661BF844@E2K7CLSTA.cna.com
10689 136 58_z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions9_Sam Baugh24_Sam.Baugh@MOTION-IND.COM30_Fri, 7 May 2010 16:48:52 -0500599_us-ascii We are in process of converting to DB2v9, and I have a question concerning REORG of multiple partitions using SHRLEVEL CHANGE. Will it be best to REORG all partitions that need reorganization as single REORG step, instead of just one partition at a time?
I was just experimenting and have not researched performance (yet), but was curious what some others are doing with these. I am aware that I will have to have plenty of disk space to handle all of the shadow datasets, especially since DB2v9 will create shadows for all partitions being reorged, and all related indexes. We [...]73_1A20EF32A1A1CA44B36AF5D6920397221645FB04E5@MIHQPEXMB1.corp.motion-ind.com
10826 225 42_Re: DB2 Z/OS : Load Replace or Load Resume12_Sangram Shee22_shee.sangram@GMAIL.COM30_Fri, 7 May 2010 22:28:13 -0400580_ISO-8859-1 Thank you all for your respones.
On Fri, May 7, 2010 at 4:59 AM, Bala C wrote:
> If I were you, I would go for an approach that the table demands. I was
> once asked to implement one approach/PROC for all tables in the system in
> case of REORG and for over an year, we are spending time optimizing DASD,
> elapsed time etc.. And, DB2 versions with which enhancements or changes in
> behavior come in the way Utilities run.
>
> Apart from all the valuable replies, you should also check how these tables
[...]62_n2od5d6436f1005071928ua3ae443aidd6efdecb64ed719@mail.gmail.com
11052 168 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions0_24_hhuang@DCCSH.ICBC.COM.CN30_Sat, 8 May 2010 11:59:16 +0800652_GB2312 Sam,
In DB2 9, the recommended rules of REORG is, run REORG agianst as MANY
partitions as possible
in one job step, or against the whole tablespace.
DB2 9 introduces REORG partition parallelism as a piece of performance
improvements.
If without NPI, you can still run multiple concurrent REORG PART jobs, as
well as one REORG
job against multiple partitions. The diffenrence is, with your original
"one partition at a
time" fashion, you can definitely control the parallelism by number of
jobs as you wish, and
you can also spread the REORG workloads cross multiple DB2 members, take
advantage of [...]69_OF7CA62C6A.EED68EAE-ON4825771D.00105CC5-4825771D.0015E7CF@icbc.com.cn