1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l February 2010, week 3
2 764 38_Re: IMMEDWRITE YES Performance problem16_Robert Catterall21_rfcatterall@GMAIL.COM31_Sun, 14 Feb 2010 23:01:21 -0500404_windows-1252 The dire warnings concerning the CPU and response time impact of binding with IMMEDWRITE(YES) are relevant if the programs for which you'll use this option drive a lot of updates of GBP-dependent pages before committing. If a program inserts or updates a small number of rows before committing, the performance impact of binding the program with IMMEDWRITE(YES) should not be so large. [...]
767 64 56_DB2 V8.. UTSORTAL and dynamic sort work file allocations9_DB2DBAzOS21_bala.db2dba@GMAIL.COM31_Mon, 15 Feb 2010 17:28:26 +0530482_ISO-8859-1 Dear list,
We are DB2 v8 z/OS 1.7. System already has the Sysmod UK33692 that enables DB2 reorg utility to use RTS if available.
I have not enabled RTS yet. My zparms have UTSORTAL=NO (and IGNSORTN=N). So, DB2 must demand explicit coding of sort work files on the JCL, as I understand. But, that is not happening. DB2 dynamically allocates the sort work files and what's more confusing is, DB2 allocates SORTWKnn datasets as well as SWnnWKmm datasets. [...]
832 112 60_Re: DB2 V8.. UTSORTAL and dynamic sort work file allocations9_DB2DBAzOS21_bala.db2dba@GMAIL.COM31_Mon, 15 Feb 2010 18:42:40 +0530577_ISO-8859-1 More..
I turned UTSORTAL=YES and found SWnnWKmm is not getting recognized. Looks like I need to applyUK47841. PK87579 seems to deal about this.
But, the problem I am facing and stated in original mail (below) seems to "not working as expected". Has anyone faced or known of a fix ?
*This new behaviour is activated by setting system parameter UTSORTAL=YES. Sort work data sets will then only be allocated by the utility when no SORTNUM option is specified on the utility statement AND if no sort work data sets were specified in the JCL. * [...]
945 204 18_DB2 V9 / WTX ERROR12_Asamoto, Roy25_Roy.Asamoto@STATEAUTO.COM31_Mon, 15 Feb 2010 13:39:48 -0500534_us-ascii Hi,
This is probably not a DB2 issue, but I was hoping someone here may have come across this problem before, or maybe can point me to a WTX user group (I googled for it and found one in Sweden, but unfortunately I don't speak the language).
We are having several problems trying to use DB2 with IBM's WTX on our test machine. We are on DB2 V9 NF with RSU0909. The WTX is V8.2. We are using the ODBC connection, as the doc recommends this over the DB2 connection. We use the resource map of the WTX to set [...]
1150 403 60_Re: DB2 V8.. UTSORTAL and dynamic sort work file allocations16_Christian Michel18_cmichel@DE.IBM.COM31_Mon, 15 Feb 2010 20:17:05 +0100437_ISO-8859-1 Ok, I'll try to address your confusion.
First of all, DFSORT will attempt to dynamically allocate sort work data sets if no hard coded sort work data sets are found for the job. It will do so, even if you didn't specify a SORTDEVT parameter in the DB2 utility, as long as the DFSORT installation option value for DYNALLOC has a device type specified. The value in SORTDEVT can be used to override the device type. [...]
1554 754 60_Re: DB2 V8.. UTSORTAL and dynamic sort work file allocations12_Weaver, Rick19_Rick_Weaver@BMC.COM31_Mon, 15 Feb 2010 14:12:02 -0700334_iso-8859-1 Normally I would not respond to this type of message, but I could not resist.
20+ years ago the BMC utilities enabled parallel processing without all the 'however' and 'in this case' considerations. Our approach was: give us the data and point us to the table and we will figure it all out. [...]
2309 331 60_Re: DB2 V8.. UTSORTAL and dynamic sort work file allocations15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Mon, 15 Feb 2010 14:12:45 -0700351_us-ascii Normally I would not respond to this type of message, but I could not resist. Sorry.
Well over 10 years ago the BMC utilities enabled parallel processing without all the 'however' and 'in this case' considerations. Our approach was: give us the data and/or point us to the table and we will figure it all out. [...]
2641 33 40_Allocating more 32K Work pagesets for V95_Steve21_malutjuta@HOTMAIL.COM31_Tue, 16 Feb 2010 02:57:11 +0000345_UTF-8 Esteemed list,
We'd like to Allocate more 32K Work pagesets for our z/OS Db2 V9 systems and would like to ask if it's a good or bad idea to just allocate more 32K work spaces to the work database larger than those already there? Is sort work distributed evenly across the work spaces or does Db2 look for the 'emptiest' space? [...]
2675 80 44_Re: Allocating more 32K Work pagesets for V913_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Tue, 16 Feb 2010 09:02:08 -0000319_utf-8 Steve
A couple of things
1. If you are using DGTTs or static scrollable cursors then take a look at PK70060 to determine whether you want secondary allocations on your work files or not (this APAR changes the way DB2 utilises work file page sets depending on whether they have SECQTY 0 or not) [...]
2756 777 46_AW: [DB2-L] IMMEDWRITE YES Performance problem35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 16 Feb 2010 10:47:11 +0100481_iso-8859-1 Hi
May I add one further information. I recently raised a requirement, that I can specify the IMMEDWRITE-Option on a plan-level, which should override the package-option. The reason for this is, that all our packages could be executed in all our plans (and some are executed everywhere and nobody knows, which packages are used in which plans) and some of our plans suffer from not seeing some updates. So we want to change this option only for these plans. [...]
3534 181 16_Reorg on Catalog14_Galeos Antonis19_AGaleos@EUROBANK.GR31_Tue, 16 Feb 2010 12:53:53 +0200374_us-ascii Hi All !
We would like to reorganize all catalog tables during weekend without disturbing our online access (internet banking).
No batch jobs will be running. No maintenance will take place on DB2, during this time.
Do you have in mind any considerations that we may have?
Btw, we are talking of DB2 V8 for z/OS 1.9 [...]
3716 1155 46_SV: [DB2-L] IMMEDWRITE YES Performance problem13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Tue, 16 Feb 2010 12:12:00 +0100419_iso-8859-1 Currently DB2 works as follows: If you do NOT specify IMMEDWRITE on the package bind, then DB2 choose to use the IMMEDWRITE option specified in the plan bind. I Think this what you ask for? Regards, Olle
Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Walter Janißen Skickat: den 16 februari 2010 10:47 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: [DB2-L] AW: [DB2-L] IMMEDWRITE YES Performance problem [...]
4872 1120 46_AW: [DB2-L] IMMEDWRITE YES Performance problem35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 16 Feb 2010 13:00:00 +0100499_iso-8859-1 Ole
You cannot not specify IMMEDWRITE, because there is always the default NO, which will take effect, if you bind a plan with IMMEDWRITE(YES).
BTW: There is an option, where you can specify nothing and in that case, the plan option takes effect. This option is RELEASE.
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com [...]
5993 224 20_Re: Reorg on Catalog13_Palko, George16_gpalko@OPERS.ORG31_Tue, 16 Feb 2010 13:07:49 +0000581_us-ascii Hi Galeos,
Review V8.1 upgrade job DSNTIJNE. I used the V8.1 upgrade job as a template for creating the catalog reorg jobs. The job uses shadow datasets (fast switching) so there is minimal impact on production. Prior to reorganizing production, one bit of advice... practice, practice, practice and ensure that you have recovery plans at hand. I've been reorganizing the catalog for over 15 years. However, it was not until V8.1 that I've become very comfortable with the process. The ability to use fast switch on the catalog really makes a huge difference. [...]
6218 1396 46_SV: [DB2-L] IMMEDWRITE YES Performance problem13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Tue, 16 Feb 2010 15:21:39 +0100440_iso-8859-1 Hi Walter, I see what you mean. According to the Command Reference Manual for DB2 9 it should be possible to override the default IMMEDWRITE(NO) DB2 seems to ignore IMMEDWRITE(YES) when specified in BIND PLAN?! Regards, Olle
Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Walter Janißen Skickat: den 16 februari 2010 13:00 Till: DB2-L@WWW.IDUGDB2-L.ORG Ämne: [DB2-L] AW: [DB2-L] IMMEDWRITE YES Performance problem [...]
7615 1354 46_AW: [DB2-L] IMMEDWRITE YES Performance problem35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Tue, 16 Feb 2010 16:07:01 +0100624_iso-8859-1 Hi Ole
Yes, most of the bind plan parameters take effect, when you bind with the member-clause, but I think, this is no longer possible in DB2 X.
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf
mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrates: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 [...]
8970 441 66_Re: DB2 V9 nigration: INVALID OPERAND 'COPYDDN' FOR KEYWORD 'COPY'14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 10:09:59 -0500555_us-ascii Kevin,
One thing to look for here is invalid characters in cols. 1-72, in your SYSIN, after CPY01 and before COPYDDN - I recently had an occurrence of this error which was caused by that.
[F P'.' 1 72] is the TSO Edit command to look for that.
--Phil Sevetson
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Clark, Kevin Sent: Friday, February 12, 2010 7:31 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V9 nigration: INVALID OPERAND 'COPYDDN' FOR KEYWORD 'COPY' [...]
9412 115 44_Re: Allocating more 32K Work pagesets for V99_Jeff Kram24_Jeff.Kram@WELLSFARGO.COM31_Tue, 16 Feb 2010 09:41:06 -0600430_us-ascii I'd also mention that IBM recommends SMS stogroup defined workfiles, but be aware even with SECQTY 0 these objects can take additional datasets (up to 32 2GB) so plan accordingly. If you are using user defined (vcat) datasets, 2GB for 4K datasets is 2913 cylinders. 2GB for 32K datasets is 2979 cylinders. If you don't use that value for 32K files, it will not use any additional parts (A002,A003, etc) for that TS. [...]
9528 114 12_ADMIN update16_Galambos, Robert29_Robert.Galambos@COMPUWARE.COM31_Tue, 16 Feb 2010 10:55:16 -0500593_utf-8 Due to an issue with the software, we were unable to upgrade the listserv software this past weekend.
So we 'fell back' to the original version
I will update you when we have more info
Le contenu de ce courriel s'adresse au destinataire seulement. Il contient de l'information pouvant être confidentielle. Vous ne devez ni le copier ni l'utiliser ni le divulguer à qui que ce soit à moins que vous soyez le destinataire ou une personne désignée autorisée. Si vous le receviez par erreur, veuillez nous aviser immédiatement et le détruire. [...]
9643 370 36_AD: DB2 Tech Talk with Phil Grainger13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Tue, 16 Feb 2010 16:14:48 -0000356_us-ascii Hi all
It's been noticed that since my "change in circumstances" I managed to get through the whole of 2009 without giving a webcast
Well, it seems that people still remember the DB2 9 webcast series I did with CA and IDUG in 2008 (yes, it WAS that long ago) so I felt it was time for a relaunch [...]
10014 458 40_Re: AD: DB2 Tech Talk with Phil Grainger14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 12:10:44 -0500434_us-ascii Hey, Phil, where's the signup link?
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Tuesday, February 16, 2010 11:15 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] AD: DB2 Tech Talk with Phil Grainger
Hi all
It's been noticed that since my "change in circumstances" I managed to get through the whole of 2009 without giving a webcast [...]
10473 641 40_Re: AD: DB2 Tech Talk with Phil Grainger13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Tue, 16 Feb 2010 17:36:10 -0000643_us-ascii Apologies
When I said "We're still finalising content" - I also meant we're finalising sign up details as well
Watch this space
Phil Grainger
Grainger Database Solutions Ltd
phil@graingerdatabasesolutions.com
+44 (0)1298 872148
+44 (0)7505 266768
www.graingerdatabasesolutions.com
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: 16 February 2010 17:11 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AD: DB2 Tech Talk with Phil Grainger [...]
11115 140 24_AW: [DB2-L] ADMIN update12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 16 Feb 2010 19:19:45 +0100599_iso-8859-1 Shit happens ;-))
Just kidding, dear Robert ;-))
Regards, Georg ---------------------------------------------------------------------- In nature, nothing is ever right. Therefore, if everything is going right ... something is wrong.
-----Ursprüngliche Nachricht----- Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Galambos, Robert Gesendet: Dienstag, 16. Februar 2010 16:55 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: [DB2-L] ADMIN update
Due to an issue with the software, we were unable to upgrade the listserv software this past weekend. [...]
11256 34 28_New DB2 9 for z/OS Utilities11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 16 Feb 2010 18:21:57 +0000
11291 98 22_APAR Signup or search?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 13:58:29 -0500607_us-ascii Okay, time I 'fessed up. I don't know how to search for, or receive notification on, DB2 APARs (or any other kind, for that matter). Where does one go to search APARs and PTFs? --Phil
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * Your only source for independent, unbiased, and trusted DB2 information. * ** The best DB2 technical sessions in the world ** NEW - IBM hands-on labs -> no additional charge _____________________________________________________________________ [...]
11390 189 40_AW: [DB2-L] New DB2 9 for z/OS Utilities12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 16 Feb 2010 20:06:33 +0100362_us-ascii Dave
I know you and we met at several IDUG conferences since 1991. Please so kind and send me your presentation "Performance features in DB2 V9 for z/OS".
I realy need this for the "argumenting future of OUR upcoming data base system" in my company. I need arguments for IBM, for DB2 z/OS-............................................ [...]
11580 560 44_Re: AW: [DB2-L] New DB2 9 for z/OS Utilities16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 16 Feb 2010 14:15:41 -0500410_iso-8859-1 Georg - just click on the link from the original email where Beulke mentioned this presentation. It will take you straight there (I just browsed through the slides - interesting stuff)
Steen Rasmussen CA
Sr Engineering Services Architect
IBM Certified Database Associate - DB2 9 Fundamentals
IBM Certified Database Administrator - DB2 9 DBA for z/OS [...]
12141 120 26_Re: APAR Signup or search?15_Andrew Lawinger17_alawinger@MMM.COM31_Tue, 16 Feb 2010 13:12:25 -0600563_ISO-8859-1 https://www14.software.ibm.com/webapp/ShopzSeries/ShopzSeries.jsp
sign up for a shop Zseries userid and start with that..
Andy
From: "Sevetson, Phil" To: DB2-L@WWW.IDUGDB2-L.ORG Date: 02/16/2010 12:58 PM Subject: APAR Signup or search? Sent by: IDUG DB2-L
Okay, time I ?fessed up. I don?t know how to search for, or receive notification on, DB2 APARs (or any other kind, for that matter). Where does one go to search APARs and PTFs? --Phil [...]
12262 452 26_Re: APAR Signup or search?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 15:04:33 -0500421_us-ascii It made me register my userid and customer number, told me I was unauthenticated, and said it was going to try to get my status changed to "submitter".
I don't think this is taking me where I want to go. I'm a DBA with no sysprog/installadmin role, and I don't need to be downloading fixes; I need to be able to _view_ them and have an intelligent opinion on whether my _sysprogs_ should apply them. [...]
12715 181 34_APAR/Support Info for DB2 for z/OS12_Phil Gunning19_pkgunning@GMAIL.COM31_Tue, 16 Feb 2010 15:33:32 -05001006_us-ascii Phil, go here, click on support in lower left hand corner, and then search. Phil
http://www-01.ibm.com/software/data/db2/zos/
Phil Gunning
IBM Information Champion
The World Leader in DB2 LUW Consulting
Database Industry Analyst
DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support
Direct +1.610.451.5801
Toll Free 888.241.1070
pgunning@gts1softwaredot.com
IBM Business Partner
IBM Authorized Reseller
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * Your only source for independent, unbiased, and trusted DB2 information. * ** The best DB2 technical sessions in the world ** NEW - IBM hands-on labs -> no additional charge _____________________________________________________________________ [...]
12897 515 26_Re: APAR Signup or search?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 15:36:23 -0500468_us-ascii Okay, I found it - the IBMLink SIS (Service Information Search) appears to be what I was looking for. Interestingly (frustratingly), its date-range search is not Y2K-intelligent. I've filed feedback. Maybe someone's listening.
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: Tuesday, February 16, 2010 3:05 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] APAR Signup or search? [...]
13413 412 38_Re: APAR/Support Info for DB2 for z/OS13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Tue, 16 Feb 2010 20:52:24 -0000690_us-ascii Or if you are just looking for the odd APAR, go here -> http://www-01.ibm.com/software/support/toolbar/index.html?ibmsst=ibmTbMenu
And download the IBM Toolbar for Internet Explorer - I use it all the time
Phil Grainger
Grainger Database Solutions Ltd
phil@graingerdatabasesolutions.com
+44 (0)1298 872148
+44 (0)7505 266768
www.graingerdatabasesolutions.com
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Gunning Sent: 16 February 2010 20:34 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] APAR/Support Info for DB2 for z/OS [...]
13826 282 38_Re: APAR/Support Info for DB2 for z/OS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 15:55:15 -0500412_us-ascii Thanks. I've probably flooded my inbox - the subscriptions look pretty good.
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Gunning Sent: Tuesday, February 16, 2010 3:34 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] APAR/Support Info for DB2 for z/OS
Phil, go here, click on support in lower left hand corner, and then search. Phil [...]
14109 505 38_Re: APAR/Support Info for DB2 for z/OS14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 16 Feb 2010 16:04:58 -0500423_us-ascii Can't - I'm not a local administrator on my machine. Thanks for the thought, though. Wonder if they've got a single-point-of-access via a webpage that does similar stuff.
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Tuesday, February 16, 2010 3:52 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] APAR/Support Info for DB2 for z/OS [...]
14615 52 20_[LUW] sortheap issue12_Pierre Evans19_pevansdba@YAHOO.COM31_Tue, 16 Feb 2010 23:01:20 +0000319_UTF-8 Greetings listers, Has anyone come across this : Sortheap fills up for a query, then the database core dumps and hangs up, leaving existing queries in limbo and not allowing new connections. Meanwhile the (very large) tempspace remains untouched at 0% used.
This is db2 9.5/dpf on aix 6 with 7 nodes. [...]
14668 101 44_Re: AW: [DB2-L] New DB2 9 for z/OS Utilities13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Tue, 16 Feb 2010 18:23:42 -0600567_ISO-8859-1 Drop me a private e-mail... we do have what I consider some good arguments FOR DB2 for z/OS.
Willie
Peter, Georg wrote: > Dave > > I know you and we met at several IDUG conferences since 1991. Please so > kind and send me your presentation *"Performance features in DB2 V9 for > z/OS". * > ** > *I realy need this for the "argumenting future of OUR upcoming data base > system" in my company. I need arguments for IBM, for DB2 > z/OS-............................................ * > ** > *If I have do not really good arguments, bye bye [...]
14770 42 44_Re: Allocating more 32K Work pagesets for V95_Steve21_malutjuta@HOTMAIL.COM31_Wed, 17 Feb 2010 03:12:18 +0000491_UTF-8 Thanks guys,
We should be right without catering for DGTTs or scrollable cursurs, and our past practice is VCAT defined, so that info will be handy.
Could I ask a further question - if we currently have 5 x 300Cyl work datasets could we just allocate a further 5 X 600Cyl work datasets (i.e. ending up with different sizes in the same subsytem) or would it be definitely better to delete the original 5 and allocate the same amount of space evenly - as10 x 450 Cyl? [...]
14813 41 41_db2 luw(v9.5) - REORG & index page splits13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Wed, 17 Feb 2010 03:48:18 +0000532_UTF-8 I am looking at ways to improve our REORG process, and limit the selection to just tables/indexes that are disorganized. In checking out the REORGCHK statistics I noticed there does not appear to be a "formula" that reports index pages splits...........
I did notice some columns in SYSCAT.INDEXES that might suggest index page split stats.......but I am not clear on exactly which statistics would tell me the percentage of pages split or something similar to leafnear / leaffar which I am used to from db2 z/os. [...]
14855 341 38_Re: AW: [DB2-L] SQL PROBLEM. Need help8_duam lee20_duam_lee@HOTMAIL.COM31_Wed, 17 Feb 2010 05:27:34 +0000642_utf-8
Hi Joe,David ,Walterand James,
Thank you very much for this help. I tried all , but some different resukt set. Not sure where i am missing.
This is not working function wise. I am trying my best or I am blockhead now. Not able to acomplish this.
With Thanks Duam.
> Date: Fri, 12 Feb 2010 15:08:46 +0000 > From: joerg6666@HOTMAIL.COM > Subject: Re: [DB2-L] AW: [DB2-L] SQL PROBLEM. Need help > To: DB2-L@WWW.IDUGDB2-L.ORG > > Of course I have a typo below. The Where clause to correlate the scalar > fullselect should come before the group by, not after. [...]
15197 657 38_Re: APAR/Support Info for DB2 for z/OS13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 17 Feb 2010 08:52:08 -0000856_us-ascii When I do an APAR search through the toolbar, it uses http://www-111.ibm.com/search/SupportSearchWeb/SupportSearch?action=search&i bmsst=defSrchEnt&pageCode=MPS&productKey=&clearAll=false&brand=
Phil Grainger
Grainger Database Solutions Ltd
phil@graingerdatabasesolutions.com
+44 (0)1298 872148
+44 (0)7505 266768
www.graingerdatabasesolutions.com
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: 16 February 2010 21:05 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] APAR/Support Info for DB2 for z/OS
Can't - I'm not a local administrator on my machine. Thanks for the thought, though. Wonder if they've got a single-point-of-access via a webpage that does similar stuff. [...]
15855 237 38_Re: APAR/Support Info for DB2 for z/OS11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 17 Feb 2010 10:31:24 +0100554_UTF-8 weird...I use this one
http://www14.software.ibm.com/webapp/set2/psearch/search?domain=sysz
isnt it odd that IBM have about fifteen different ways of getting at the APAR database???
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
16093 847 38_Re: APAR/Support Info for DB2 for z/OS13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 17 Feb 2010 10:53:23 -0000633_utf-8 It’d be odder if they had 15 different databases!
But that would be crazy – no one duplicates data to store it in more than one place do they ;)
Phil Grainger
Grainger Database Solutions Ltd
phil@graingerdatabasesolutions.com
+44 (0)1298 872148
+44 (0)7505 266768
www.graingerdatabasesolutions.com
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: 17 February 2010 09:31 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] APAR/Support Info for DB2 for z/OS [...]
16941 377 38_Re: APAR/Support Info for DB2 for z/OS11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 17 Feb 2010 12:37:22 +0100444_UTF-8 just wait a year or two and after they have swallowed Oracle and MySQL the world could look pretty different!!! Of course we will all be running Windows 11 on z/OS with 8088 processors and 5.25inch disks.....
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...]
17319 61 45_Re: db2 luw(v9.5) - REORG & index page splits12_Phil Gunning19_pkgunning@GMAIL.COM31_Wed, 17 Feb 2010 08:44:27 -0500581_UTF-8 Use db2pd - -tcbstats to see index splits and go from there. PG
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jack Campbell Sent: Tuesday, February 16, 2010 10:48 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] db2 luw(v9.5) - REORG & index page splits
I am looking at ways to improve our REORG process, and limit the selection to just tables/indexes that are disorganized. In checking out the REORGCHK statistics I noticed there does not appear to be a "formula" that reports index pages splits........... [...]
17381 93 44_Re: AW: [DB2-L] New DB2 9 for z/OS Utilities10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 17 Feb 2010 16:02:33 +0100458_US-ASCII Hi
I hope I'm wrong but, from my previous experiences, they have already decided. For example in a previous company IBM made a very high discount for HW/SW but it wasn't enough (but at the very end the new platform was by far more expensive with respect to MF).
Linux (free OS - I'm testing CentOS and it's nice) sounds nice to every manager, and ORACLE is always cheaper than DB2. The only hope is to have Linux and DB2 for LUW. [...]
17475 38 20_Re: Reorg on Catalog11_Dave Harvey17_db2dave@GMAIL.COM31_Wed, 17 Feb 2010 15:09:29 +0000454_UTF-8 Galeos,
The advice of George is exactly what we did but one small warning. The IDCAMS commands to create the 'shadow' clusters uses the "MODEL" option on the basis presumably that if the I.... is good enough then the J.... must also be.
However, in certain cases e.g. SPT01, the REORG can fail because the 'shadow' is too small. This can be remedied by recoding the IDCAMS "MODEL" clauses by using "CYLINDER...." specifications. [...]
17514 170 24_Re: [LUW] sortheap issue23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Wed, 17 Feb 2010 09:28:27 -0600772_ISO-8859-1 Supposedly this problem was fixed in Fix Pack 5?
http://www-01.ibm.com/support/docview.wss?uid=swg1LI74256
Jeffrey Benner IBM Certified Advanced Database Administrator DB2 9 for Linux, UNIX and Windows eBenner.com Consulting http://www.ebenner.com +001 312 520 0090 http://www.linkedin.com/in/ebenner
On Tue, Feb 16, 2010 at 17:01, Pierre Evans wrote:
> Greetings listers, > Has anyone come across this : Sortheap fills up for a query, then the > database core dumps and hangs up, leaving existing queries in limbo and > not allowing new connections. Meanwhile the (very large) tempspace > remains untouched at 0% used. > > This is db2 9.5/dpf on aix 6 with [...]
17685 288 82_Re: [DB2 for z/OS] Is there a limitation as to the # of rows to discard with reorg14_Larry Kintisch17_LKint@VERIZON.NET31_Wed, 17 Feb 2010 11:50:42 -0500588_iso-8859-1 Hi Rudiger,
I have a few alternative suggestions that you may have already tried.
(1) Run the job with about 1/100 th the number of items in the IN clause [about 10,000 instead of a million]. The last time I learned about the internals of the IN predicate, the system builds a sorted temporary "IN table", eliminates duplicates and builds a "sparse index" of say the starting value, 25 percentile value, 50th & 75th percentiles of values in the sorted "IN table". Then for each table row presented to the IN predicate, there is a search first of that sparse [...]
17974 60 28_DB2 Connect install question15_Mark Hardenburg30_mark.hardenburg@PRUDENTIAL.COM31_Wed, 17 Feb 2010 12:48:17 -0500574_US-ASCII Hi, My work group was recently given some responsibilities on overseas servers. The servers have DB2 Connect Server V7.1 installed. We would like to install DB2 Connect Server V7.1 on a test server and migrate it to V9.5 or V9.7 to perfect our migration technique. When we try to install DB2 Connect it asks for an account and password. We fill in an account and put 14 characters (the maximum that will fit) in the password field. We get an error that the password is too short. Naturally, we don't have an extended service agreement with IBM for DB2 Connect [...]
18035 143 32_Re: DB2 Connect install question12_Byron Pierce27_byron.pierce@PRUDENTIAL.COM31_Wed, 17 Feb 2010 14:15:58 -0500372_US-ASCII Hi Mark,
I haven't seen this with DB2 Connect, but I have with other v7 installs (i.e. Admin/Runtime Client)... I think all I did was make sure the password conformed to Windows name standards. Typically I assign a password that matches the day/date (helps me remember) - for example, for an install today, I would assign a password of 'Wednesday17'. [...]
18179 134 32_Re: DB2 Connect install question15_Bernie O'Connor26_berniedb2loconnor@LIVE.COM31_Wed, 17 Feb 2010 14:24:27 -0500737_iso-8859-1
Hi, Mark.
I think we used userid db2admin password db2admin for the initial installation on a lab machine just to verify a release. That might work for you....
All the best,
Bernie
Date: Wed, 17 Feb 2010 12:48:17 -0500 From: mark.hardenburg@PRUDENTIAL.COM Subject: [DB2-L] DB2 Connect install question To: DB2-L@WWW.IDUGDB2-L.ORG
Hi, My work group was recently given some responsibilities on overseas servers. The servers have DB2 Connect Server V7.1 installed. We would like to install DB2 Connect Server V7.1 on a test server and migrate it to V9.5 or V9.7 to perfect our migration technique. When we try to install DB2 [...]
18314 125 20_DB2 Connect Packages10_Irwin, Jim19_Jim.Irwin@OA.MO.GOV31_Wed, 17 Feb 2010 13:55:09 -0600342_us-ascii Anyone have a way of telling if DB2 Connect packages from an old release are still being used? We are in the process of cleaning up our old packages and were just going to revoke access to them and see who squealed but our customer has asked if there was a way to identify any clients still using them before we do the revokes. [...]
18440 178 83_DB2 LUW Oracle in for Tougher Sailing against IBM (re: Sun deal) -- Link to article12_Phil Gunning19_pkgunning@GMAIL.COM31_Wed, 17 Feb 2010 15:07:06 -05001100_US-ASCII Article cites migrations from Sun to IBM AIX and HP.
http://itknowledgeexchange.techtarget.com/eye-on-oracle/sailing-against-ibm- may-be-rougher-for-larry-than-the-america%E2%80%99s-cup/?track=NL-786&ad=750 267&asrc=EM_NLN_10897314&uid=2145371
Phil Gunning
IBM Information Champion
The World Leader in DB2 LUW Consulting
Database Industry Analyst
DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support
Direct +1.610.451.5801
Toll Free 888.241.1070
pgunning@gts1software.com
IBM Business Partner
IBM Authorized Reseller
This message (including
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * * If you are going to attend only one conference this year, this is it! * ** DB2 certification -> no additional charge ** Meet fellow DB2 users and leading DB2 consultants _____________________________________________________________________ [...]
18619 227 24_Re: DB2 Connect Packages10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 17 Feb 2010 15:20:43 -0500382_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. [...]
18847 99 87_Re: DB2 LUW Oracle in for Tougher Sailing against IBM (re: Sun deal) -- Link to article7_Ed Long19_rdhm99a@PRODIGY.NET31_Wed, 17 Feb 2010 12:26:44 -0800840_iso-8859-1 Any guy who recognizes the value of a sail boat that can do 21 knots in 7 knots of wind has to be considered a serious competitor regardless of the venue. Edward Long ________________________________ From: Phil Gunning To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wed, February 17, 2010 3:07:06 PM Subject: [DB2-L] DB2 LUW Oracle in for Tougher Sailing against IBM (re: Sun deal) -- Link to article Article cites migrations from Sun to IBM AIX and HP. http://itknowledgeexchange.techtarget.com/eye-on-oracle/sailing-against-ibm-may-be-rougher-for-larry-than-the-america%E2%80%99s-cup/?track=NL-786&ad=750267&asrc=EM_NLN_10897314&uid=2145371 Phil Gunning IBM Information Champion The World Leader in DB2 LUW Consulting Database Industry Analyst DB2 Consulting, Books on DB2, Outsourcing, Education, Remote Support [...]
18947 99 24_Re: DB2 Connect Packages0_24_carol.sutfin@REGIONS.COM31_Wed, 17 Feb 2010 14:43:10 -0600377_US-ASCII Jim,
If you are worried about trying to get the packages back if you FREE them There is a way to save them.
You can BIND a copy of the package with a different collection name using the COPY option in the BIND. FREE the original package from the NULLID collection and if no one calls then you can free the copy after the appropriate amount of time. [...]
19047 148 24_Re: DB2 Connect Packages16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 17 Feb 2010 16:09:22 -0500534_US-ASCII Carol has a very good point - another idea you might consider is to do a REBIND using the DISABLE parameter.
Steen Rasmussen CA Sr Engineering Services Architect IBM Certified Database Associate - DB2 9 Fundamentals IBM Certified Database Administrator - DB2 9 DBA for z/OS
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of carol.sutfin@REGIONS.COM Sent: Wednesday, February 17, 2010 2:43 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Connect Packages [...]
19196 92 60_Baltimore Washington DB2 Users' Group - March 9 2010 Meeting14_Henry L Nalven25_henry.nalven@MARRIOTT.COM31_Wed, 17 Feb 2010 21:54:47 +0000608_UTF-8 HEADS UP!!! SHERYL LARSEN IS COMING TO TOWN!!
The Baltimore/Washington DB2 Users' Group Special DB2 Course Offering - Wednesday, March 10, 2010 presenting...
SHERYL LARSEN International DB2 Consultant
BWDB2UG z/OS Educational Session
Sheryl Larsen
When?
Wednesday, March 10, 2010, 9:00AM-3:30PM (registration at 8:30)
Where?
Sheraton Columbia Hotel, 10207 Wincopin Circle, Columbia, MD (410) 730-3900
Meeting Fees: Pre-registered: $250 (lunch and break refreshments included) At-the-door: $350 (lunch and break refreshments included) [...]
19289 41 19_ANSI SQL Validation9_Ray Houle32_raynald.houle@SCD.DESJARDINS.COM31_Wed, 17 Feb 2010 20:12:09 +0000383_UTF-8 Can someone direct me to a site (or other source) where I can validate whether SQL respects ANSI standard (and is portable).
For example the following statement is valid with DB2 AIX, a SELECT of an UPDATE of a SELECT...
SELECT * FROM NEW TABLE (UPDATE (SELECT * FROM DB2D095.EMPLOYEE WHERE FIRSTNME LIKE 'R%' FETCH FIRST 3 ROWS ONLY) SET BONUS = BONUS + 1) [...]
19331 297 174_Upcoming IDUG Webcast - "The DB2Night Show Episode #12 - "The Wild Wonderful World of DB2 Resources", Scott Hayes, DBI Software" - Friday 12 February 2010 at 10:00 am USA CST13_David Chapman24_David.Chapman@IAG.COM.AU31_Thu, 18 Feb 2010 09:38:31 +1100774_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 #13 - 19 Feb 2010 - "DB2 Compression Updates with Paul Z!", Scott Hayes, DBI Software
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. [...]
19629 413 87_Re: DB2 LUW Oracle in for Tougher Sailing against IBM (re: Sun deal) -- Link to article12_Phil Gunning19_pkgunning@GMAIL.COM31_Wed, 17 Feb 2010 17:49:46 -0500426_US-ASCII Since I make my living, and have for a long time on DB2 LUW, my opinion is that DB2 LUW, has for a long time been a rock solid database, it just does not get the press it deserves. The enhancements in 9.7 make it an extremely capable database, and with the addition of pureScale as a combat multiplier, quite capable of kicking Oracle's butt. Now I don't know much about sailing.just DB2 and tanks..Cheers, Phil [...]
20043 94 88_CLARIFICATION OF DATE!! Baltimore/Washington DB2 Users' Group Meeting MARCH 10, 2010!!!!14_Henry L Nalven25_henry.nalven@MARRIOTT.COM31_Wed, 17 Feb 2010 22:01:58 +0000609_UTF-8 HEADS UP!!! SHERYL LARSEN IS COMING TO TOWN!!
The Baltimore/Washington DB2 Users' Group Special DB2 Course Offering - Wednesday, March 10, 2010 presenting...
SHERYL LARSEN International DB2 Consultant
BWDB2UG z/OS Educational Session
Sheryl Larsen
When?
Wednesday, March 10, 2010, 9:00AM-3:30PM (registration at 8:30)
Where?
Sheraton Columbia Hotel, 10207 Wincopin Circle, Columbia, MD (410) 730- 3900
Meeting Fees: Pre-registered: $250 (lunch and break refreshments included) At-the-door: $350 (lunch and break refreshments included) [...]
20138 294 81_Re: DB2 LUW Oracle in for Tougher Sailing against IBM -> DB2 support quality poll12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Thu, 18 Feb 2010 01:03:25 +0100606_Windows-1252
Yesterday, an Oracle guy in my team has put it like this: "Ok, Larry has got the Auld Cup back - good! Now I sincerely(!!) hope that he will eventually(!!!) invest his money in improving(!!!!) Oracle product support(!!!!!) instead of yachting!"
And, hey, this is *not* Friday. Each time my mates are in touch with Oracle support, I realize I'm far better off being on the DB2 side of the team. With IBM support, I've seldomly encountered issues like late, stupid and useless answers or endless waiting for fixes. Or am I just lucky? [...]
20433 73 45_Re: db2 luw(v9.5) - REORG & index page splits12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 17 Feb 2010 16:17:41 -0800669_us-ascii Jack-
Have you looked at the REORGCHK command? There are several formulas evaluated, and it puts a little * under the column with the formula/guidline it exceeded, showing which tables to reorg. It will evaluate the whole database if you want.
timm
--- On Tue, 2/16/10, Jack Campbell wrote:
> From: Jack Campbell > Subject: [DB2-L] db2 luw(v9.5) - REORG & index page splits > To: DB2-L@WWW.IDUGDB2-L.ORG > Date: Tuesday, February 16, 2010, 7:48 PM > I am looking at ways to improve our > REORG process, and limit the selection > to just tables/indexes that are disorganized. [...]
20507 135 32_Re: DB2 Connect install question12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 17 Feb 2010 16:23:16 -0800349_iso-8859-1 On windoze, If you already have an id with admin rights, try that one. You are using an id with domain admin rights to install, aren't you? If unix, you'll need root to install, and then you should be able to use whatever matches your current naming/password convention on that machine. And you could use an existing id/pwd to test. [...]
20643 268 15_DB2 Reorg Issue9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Thu, 18 Feb 2010 10:10:58 +0530415_us-ascii My reorg Job has abended. The error is
REORG1 DSNUPROC UTIL1 - ABEND=S04E U0000 REASON=00E40009
I seem to have lost all data in the tables. I do not have Image Copies.
LISTDEF LIST1 INCLUDE TABLESPACE FCRMRBL2.* REORG TABLESPACE LIST LIST1 UNLOAD CONTINUE
LISTDEF DSN8LDEF INCLUDE TABLESPACES DATABASE DSN8D91A
Is there any way to get back the data? [...]
20912 138 19_Re: DB2 ReOrg Issue11_Nick Cianci19_ncianci@AU1.IBM.COM31_Thu, 18 Feb 2010 16:24:50 +1100607_ISO-8859-1 Srinivas, the default REORG is SHRLEVEL NONE (unless you specified SHRLEVEL REFERENCE or CHANGE) deletes and redefines the datasets after the unload phase.
It depends what utility phase you failed in. If you failed after RELOAD phase completed, you can restart or terminate and REBUILD the indices.
On the other hand if you failed in the RELOAD phase (where the VSAM DataSet has been DEL/DEF'd) things are more interesting: -o- So long as you still have the Unload DataSet, you can restart the ReOrg, once you've corrected the issue; or you can use the file in a Load job. -o- [...]
21051 258 24_Re: DB2 Connect Packages12_tim malamphy20_timalamphy@YAHOO.COM31_Wed, 17 Feb 2010 21:29:02 -0800507_iso-8859-1
Ask them. The clients could always be requested to execute the db2level or db2licm command, and if it returned an old version to call tech support for an upgrade. At some point, one of the next upgrades is going to break the client from connecting anyway, whether you clean up the packages or not. My experience is that they'll still work for 2, maybe 3 future versions. May as well get everyone up to current now. Don't forget to save those connection definitions when you upgrade. [...]
21310 239 19_Re: DB2 ReOrg Issue9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Thu, 18 Feb 2010 11:18:54 +0530796_iso-8859-1 Looks like the Job has failed in the reload phase.
How can I find the Unloaded Dataset? Whatever datasets show up in the JESYSMSG , I am unable to find.
This is my REORG JCL :
//REORG1 JOB REGION=0M,CLASS=A, // NOTIFY=&SYSUID,MSGLEVEL=(1,1),MSGCLASS=X,TIME=1440 //JOBLIB DD DISP=SHR, // DSN=DSN910.SDSNLOAD //RSTAT JCLLIB ORDER=(SYS1.PROCLIB) //UTIL1 EXEC DSNUPROC,SYSTEM=DB9G,UID='RORGG1',UTPROC='' //SYSLISTD DD DISP=SHR,DSN=DB9G.DSN8.LISTDEF //SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR //SORTOUT DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE) //DSNTRACE DD SYSOUT=* //SYSUT1 DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE) //SYSREC DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE) //SYSIN DD * LISTDEF LIST1 INCLUDE TABLESPACE FCRMRBL2.* REORG TABLESPACE LIST LIST1 UNLOAD CONTINUE [...]
21550 479 36_Re: DB2 Tech Talk with Phil Grainger13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 18 Feb 2010 09:11:59 -0000503_US-ASCII As promised, the registration links for the DB2 Tech Talk webcast are now live
They are being hosted by Helios & Matheson Software (http://www.hmnasoftware.com/) and you can register at http://www.hmnasoftware.com/events/db2-tech-talk-with-phil-grainger/
These are scheduled to suit a North American audience, but I am also working with Cogito to see if we can bring DB2 Tech Talk to a more acceptable European time as well - watch this space [...]
22030 167 19_Re: DB2 ReOrg Issue14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 18 Feb 2010 21:08:19 +1100474_ISO-8859-1 Unfortunately you wrote the unload data to a temporary data (DD=SYSREC) - which was deleted at step termination. For future reference, you should have DISP=(NEW,CATLG,CATLG) (and a DSN=) on the SYSREC. If you are happy with the REORG you can delete the dataset - after taking an image copy
How long ago was the tablespace created? Do you still have all archive logs since then? It is possible that the storage people made a backup of the disk volume. [...]
22198 170 27_AW: [DB2-L] DB2 ReOrg Issue35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 18 Feb 2010 11:10:41 +0100431_iso-8859-1 Nick
May I add one comment on this? You cannot always use the unload file from a reorg for the load utility. e.g. if a reorg rebalance fails. But I don't know all situations, where you can and where you can't.
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com [...]
22369 233 27_AW: [DB2-L] DB2 ReOrg Issue35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 18 Feb 2010 11:20:57 +0100514_iso-8859-1 There is no unload-dataset, because you didn't specify a name for it:
//SYSREC DD UNIT=SYSDA,SPACE=(CYL,(100,100),RLSE)
With the above specification you use a temporal file, which is lost after the job terminates. And is this case it doesn't matter, if the job ends normal or abnormal.
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com [...]
22603 225 19_Re: DB2 ReOrg Issue13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Thu, 18 Feb 2010 11:19:41 -0000362_iso-8859-1 IF you still have ALL of the archive logs since the table was created you can do a RECOVER TORBA (pick an RBA immediately prior to when the reorg started)
DB2 will use the point that the table was created as a "point of consistency" (DB2 knows the table was empty when it was created) and do a logapply from that point to your recover RBA [...]
22829 129 19_Re: DB2 ReOrg Issue17_Kniaz, Benjamin X26_benjamin.x.kniaz@CHASE.COM31_Thu, 18 Feb 2010 08:08:50 -0500432_iso-8859-1 A bunch of IF's coming here, but:
1) If you created the tablespace such a short period of time ago that you have ALL the logs. 2) If you have a log tool (BMC/CA/IBM......) 3) If you know how to use it, or if you have help.
You could then regenerate all the SQL that was applied to the object, and re-execute that..... Not that I think this to be a likely scenario, but it is not completely impossible. [...]
22959 23 13_VIEW question20_Bruzdzinski, Michael27_Michael.Bruzdzinski@SSA.GOV31_Thu, 18 Feb 2010 10:05:26 -0500478_UTF-8 It has been my experience never to create a VIEW as a SELECT * FROM base table. We have an application group that is requesting that we create VIEWS like this on a database.
Since we use Top Secret for DB2 to restrict access to the databases based on profiles, I am not sure why it would be done for security reasons as all the applications and users have access to all the tables and all the columns. There is no reason not to access the base table directly. [...]
22983 76 19_Re: DB2 ReOrg Issue9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK31_Thu, 18 Feb 2010 15:13:13 +0000306_iso-8859-1
You could try running a REPORT RECOVERY for the tablespace to see what DB2 thinks your recovery scenarios are. Even if you have all the arclogs since creation, any LOAD LOG NO in the past would limit recovery unless you still have the input data. Hopefully this is a test system!?! [...]
23060 351 32_Re: DB2 Connect install question15_McCardle, Corey23_CMcCardle@AMERISURE.COM31_Thu, 18 Feb 2010 10:18:27 -0500479_us-ascii There should be a local windows Id that was used for installation. V7 had restrictions for ID and passwords; they had to be 8 characters or less. The Id's password may be changed for the installation account in windows, but make sure that it is not more than 8 characters. Also, depending on the server's security policy, windows may require the password to be more than 8 characters. It also sounds like you don't have right to install on the server. Good luck... [...]
23412 29 24_Re: [LUW] sortheap issue12_Pierre Evans19_pevansdba@YAHOO.COM31_Thu, 18 Feb 2010 15:17:30 +0000648_UTF-8 Thanks, That seems to describe our incident exactly.
Regards Pierre
_____________________________________________________________________
* 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 How can you expand your staff or do succession planning in this economy? Mentoring is a proven, economical, way to train the next generation of DB2 Users! _____________________________________________________________________ [...]
23442 281 22_Changes to plan_tables13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 18 Feb 2010 11:10:23 -0500409_us-ascii Hi all
I was researching some Explain stuff for Cogito this morning when I stumbled across something I didn't know before
DB2 has been very kind in supporting just about every format of plan_table since the year dot (I still see V3 and V4 plan_tables being used). Of course, you only get information in the columns that are there - so older plan_tables aren't really that much use [...]
23724 59 23_DSNTEP2 and COMMIT -92514_Paul Peplinski24_paul.peplinski@WPSIC.COM31_Thu, 18 Feb 2010 16:55:31 +0000323_UTF-8 I have a batch job that runs DSNTEP2 as two of its steps. The SQL is a SELECT followed by a COMMIT. This has run fine in all of our subsystems for years. However starting yesterday the production job fails on one of my subsystems. I can run the same SQL in the same DB2 in my own batch DSNTEP2 and it runs fine. [...]
23784 93 27_Re: DSNTEP2 and COMMIT -92513_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 18 Feb 2010 12:11:33 -0500585_utf-8 Hello Paul,
Have you customized DSNTEP2 at your shop? If not, I'd try opening a problem with IBM. While DSNTEP2 is a sample program distributed by DB2, lots and lots of shops depend on it. I'd give the PMR process a go.
Dave
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...]
23878 260 27_Re: DSNTEP2 and COMMIT -92515_Andrew Lawinger17_alawinger@MMM.COM31_Thu, 18 Feb 2010 11:18:51 -0600596_US-ASCII Check your steplib or if the linklist has been changed. It is possible you picked up a different version of DSNTEP2.
From: "Seibert, Dave" To: DB2-L@WWW.IDUGDB2-L.ORG Date: 02/18/2010 11:12 AM Subject: Re: DSNTEP2 and COMMIT -925 Sent by: IDUG DB2-L
Hello Paul,
Have you customized DSNTEP2 at your shop? If not, I'd try opening a problem with IBM. While DSNTEP2 is a sample program distributed by DB2, lots and lots of shops depend on it. I'd give the PMR process a go. [...]
24139 148 17_Re: VIEW question12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Thu, 18 Feb 2010 18:29:14 +0100523_iso-8859-1
Michael,
at our site, we only use VIEWs where this makes sense to cover specific business requirements (i.e. simplify reporting by hiding JOINs and UNIONs in a VIEW).
I would *never* recommend creating a VIEW as a "SELECT * FROM ", not even for security reasons. It just can't see the clue in doing this because you don't get anything other out of this than with direct table access: Same columns, same column names, same result sets, same possibilities for access definitions. [...]
24288 53 17_Re: VIEW question13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Thu, 18 Feb 2010 17:35:39 +0000380_UTF-8 Michael
I am not big on creating such "base table" views. They just add additional maintenance......
Having said that I have been at more than one client which uses this std.... Some reasons include:
1) Can insulate application against base table name changes (*common for vendor package where "table_name_xxx" where xxx is version number is used) [...]
24342 81 17_Re: VIEW question12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 18 Feb 2010 09:37:48 -0800546_us-ascii My current client is doing this select * from base table view stuff. They have a home built security tool that assigns all access on the views rather than the base tables.
One very major problem we ran into on this was that some JAVA tools and some BI tools as part of their build process, need access to the primary keys and indexes and base table columns information And it's not a simple process to get that information using a view rather than from the base table. So we had to point these app's to the base table rather [...]
24424 114 32_Re: DB2 Connect install question13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Thu, 18 Feb 2010 10:39:03 -0700556_ISO-8859-1 I know this doesn't answer the original question, but I am pretty confident that you can't upgrade from V7.1 to V9.5 directly.
I think it would probably be a lot easier (i.e. less work) to just install V9.7 along side the existing V7.1 instance, and then manually catalog all of your data sources from the DB2 7.1 db2 connect instance (i.e. capture the output from LIST NODE DIRECTORY, LIST DCS DIRECTORY and LIST DATABASE DIRECTORY in your DB2 7.1 instance, and then use these to create the identical entries for the 9.7 instance). [...]
24539 400 17_Re: VIEW question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 18 Feb 2010 12:50:17 -0500420_us-ascii In the deep, dark past of DB2, SELECT * FROM tablename was sometimes used in coding. If you ALTER a _table_ by adding columns, the results (list of columns) returned by a SELECT * will be different after such a change. However, if you are SELECTing against a "Base View" (This is the term of art for these select-all-rows-and-columns views) against that ALTERed table, no change in the column list occurs. [...]
24940 123 39_Denormalization Paper (Ancient History)14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 18 Feb 2010 12:55:35 -0500605_us-ascii Does anyone remember a user group presentation (made, at least, at New England DB2 Users' Group) in the '90s about the reasons for denormalizing a design? It was made by a Candle technical programmer, I think, a woman, and broke the types of denormalization down into seven groups, each of which had an explicit justification. I remember that "horizontal partitioning" was one of the seven types; splitting the attributes of an entity type into multiple tables to reduce the number of pages retrieved for queries which only used some of the columns. I remember thinking at the time that the [...]
25064 481 17_Re: VIEW question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 18 Feb 2010 13:31:55 -0500339_us-ascii Self correction. IF you add columns to a table with ALTER, and you don't REBIND the programs calling the table with SELECT *, they won't pick up the new columns. However, the next REBIND will cause the bind to fail at the SELECT * INTO statement because of a shortage of host variables to hold the data from the new columns. [...]
25546 525 26_Re: Changes to plan_tables12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 18 Feb 2010 12:40:14 -0600398_us-ascii Phil;
In the early days of V8 if you accidently created a UNICODE plan table, the explain would work but the data was unusable. In V9 you could use either UNICODE or EBCIDIC and in V10 they are apparently going UNICODE all the way.
To do comparisons you need to be able to switch between the two states and the various number of columns. [...]
26072 61 43_Re: Denormalization Paper (Ancient History)12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 18 Feb 2010 14:05:05 -0500
26134 58 17_Re: VIEW question13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 18 Feb 2010 14:31:29 -0500471_utf-8 For those of us "of a certain age" these base table views were all the rage (once)
The idea being that you created your table with a suffix "version" number and created a view WITHOUT the version number
Then, when you needed to make a change to the table (which ALWAYS meant a drop/recreate) you'd create a brand new table, incrementing the version number. Then, in your down time (which we seemed to have a lot more of in those days too) you'd: [...]
26193 260 43_Re: Denormalization Paper (Ancient History)35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 18 Feb 2010 14:44:19 -0500465_iso-8859-1 Peggy V..... Vehman? Don't remember how to spell her last name.
Joel Goldstein Responsive Systems IBM Gold Consultant Buffer Pool Tool for DB2, the worldwide industry standard Performance software that works...... Predicts IO Rate !! Predicts Group Buffer Pool performance too www.responsivesystems.com
Buffer Pool Tool for DB2 on www.LinkedIn.com Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1 [...]
26454 332 26_Re: Changes to plan_tables8_Bob Kota23_bob.kota@EXELONCORP.COM31_Thu, 18 Feb 2010 13:51:06 -0600558_us-ascii Hi Donna,
Maybe I'm misinterpreting this post but is Phil saying that we need to drop and recreate our plan_tables with explicit UNICODE?
Thanks.
Bob Kota Information Technology - Database Services CHQ 46/NE/010/01 Office: (312)-394-7523 Pager: (877)-396-9345
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Thursday, February 18, 2010 10:10 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Changes to plan_tables [...]
26787 633 17_Re: VIEW question13_George Toolan19_gtoolan@COMCAST.NET31_Thu, 18 Feb 2010 14:51:56 -0500572_US-ASCII In the deep, dark past it was the recommendation (I believe from IBM) to create SELECT * views for all tables to insulate applications from table changes that did not require a base table drop. You just created additional views as tables grew. Old programs that didn't need the new columns would not have to do anything. This was nice in theory but just turned out to be a waste of effort. Almost every shop required programs that accessed tables being changed to update the code and include the columns anyway. Since all the work of creating SELECT * views [...]
27421 113 17_Re: VIEW question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 18 Feb 2010 14:54:40 -0500395_iso-8859-1 ...and since you can rename tables now, this is no longer a consideration...
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger Sent: Thursday, February 18, 2010 2:31 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] VIEW question
For those of us "of a certain age" these base table views were all the rage (once) [...]
27535 194 17_Re: VIEW question11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 18 Feb 2010 21:10:43 +0100381_ISO-8859-1 when I was young and thin an IBMer told me the following, very tall, tale "You must have a view for every table so that when DB2 tries to find the view/table it finds the view first and this is quicker than looking for a view - not finding it - and then looking for a table" I did not believe it then and I still do not believe it but it fits with your question!!! [...]
27730 34 27_Re: DSNTEP2 and COMMIT -92514_Paul Peplinski24_paul.peplinski@WPSIC.COM31_Thu, 18 Feb 2010 20:24:09 +0000384_UTF-8 I could open an incident with IBM but I question how far it would get. We have a solution (removing the commit), inconsistent results (it works for my job but not for a CA7 job), and a sudden appearance with no system changes. I suspect this could go away with the weekend IPL (some old APARs on -925 suggest storage related issues). Could security be involved in any way? [...]
27765 452 17_Re: VIEW question13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 18 Feb 2010 15:48:23 -0500449_iso-8859-1 Again, from a long time ago, I remember benchmarking a select from a table and one from a view of the table
Guess what - the view was repeatedly more efficient!!!
OK, this WAS DB2 V1.2 and instrumentation wasn't what it is now, but I still remember being somewhat confused Phil Grainger Cogito Ltd. phil.grainger@cogito.co.uk +44 (0) 1298 872 148 +44 (0) 7505 266 768 www.cogito.co.uk [...]
28218 576 26_Re: Changes to plan_tables13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 18 Feb 2010 15:49:20 -0500485_us-ascii Not quite - you'll have to read the APAR to figure out whether the REXX execs that are supplied as part of this PTF will do what you need them to do Phil Grainger Cogito Ltd. phil.grainger@cogito.co.uk +44 (0) 1298 872 148 +44 (0) 7505 266 768 www.cogito.co.uk
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bob Kota Sent: 18 February 2010 19:51 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Changes to plan_tables [...]
28795 587 27_Re: DSNTEP2 and COMMIT -92513_Joehlin, Gary26_Gary.Joehlin@SALLIEMAE.COM31_Thu, 18 Feb 2010 16:40:41 -0500470_us-ascii Paul,
One thing you may want to try, check to see if the correct DB2 stub was linked with it at your shop.
Gary
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andrew Lawinger Sent: Thursday, February 18, 2010 12:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DSNTEP2 and COMMIT -925
Check your steplib or if the linklist has been changed. It is possible you picked up a different version of DSNTEP2. [...]
29383 622 17_Re: VIEW question14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 18 Feb 2010 16:44:10 -0500375_iso-8859-1 That's actually kinda scary. Hope it's not true in the present.
I could do a rough timing - select the current timestamp; select from the table; select the current timestamp again. But I don't even know how to get Mainview to produce an accounting trace from my userID, so precise timing would be hard. Hmm. Maybe I should be thinking more about this. [...]
30006 667 54_Re: VIEW question -> How to best cope with old habits?12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Thu, 18 Feb 2010 23:13:01 +0100650_Windows-1252
Its' amazing to - once again - read of the various possible reasons for programmer's habits. Thanks for sharing your experience!
Actually it was only a few weeks ago when - during a meeting about new features of V9 - one of our developers asked the DB Team whether we had any information on outdated recommendations and techniques that meanwhile are deprecated or replaced by new functionality. Things like generating key values with "SELECT MAX() + 1" plus the respective descending index on that column, which has become obsolete with identity columns and sequences. [...]
30674 312 35_Correlate page set with tablespace?13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Thu, 18 Feb 2010 15:27:00 -0800390_us-ascii First of all, we don't have any kind of tool to analyze DB2 logs, and we're not going to get one.
That said, I have a process in which I choose a random time period and do a DSN1LOGP summary on it, then audit the resulting dataset to see if certain people performed any updates that they shouldn't have. My problem is that sometimes the summary records look like this: [...]
30987 53 35_LUW cleaning up logs after box swap11_Jeff Garmon15_jgarmon@UGA.EDU31_Fri, 19 Feb 2010 01:21:40 +0000578_UTF-8 We're planning a migration from v9.5 32bit to v9.7 64bit by doing a box swap; take an offline database backup on 9.5 and restore it on the 9.7 instance/box, this seems to work fine.
However; the existing 9.5 instance has a bunch of active logs that have not been archived (long story - found where the userexit has been failing for a while) and those logs are not needed on the new box/instance. On the 9.7 instance we're setting the LOGARCHMETH1 to DISK: /my/archive (instead of userexit) and have issued the PRUNE LOG prior command on the new instance based [...]
31041 87 39_Re: LUW cleaning up logs after box swap7_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 18 Feb 2010 17:39:06 -0800470_us-ascii How about Export / import?
Edward Long
________________________________ From: Jeff Garmon To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, February 18, 2010 8:21:40 PM Subject: [DB2-L] LUW cleaning up logs after box swap
We're planning a migration from v9.5 32bit to v9.7 64bit by doing a box swap; take an offline database backup on 9.5 and restore it on the 9.7 instance/box, this seems to work fine. [...]
31129 78 39_Re: LUW cleaning up logs after box swap12_Phil Gunning19_pkgunning@GMAIL.COM31_Thu, 18 Feb 2010 20:45:50 -0500434_UTF-8 Your offline backup and restore should work. You shouldn't need any logs from the old database. I'd like to see the error messages you are getting, send us the appropriate db2diag.log entries. You are correct in your settings for LOGARCHMETH1, forget about the user exit, it has long been obe, except in extreme cases. You could also try the restore of the offline backup and start out with a new recovery history file. pg [...]
31208 336 19_Re: DB2 ReOrg Issue9_SrinivasG21_SRINIVASG@INFOSYS.COM31_Fri, 19 Feb 2010 08:29:49 +0530440_iso-8859-1 Hi,
Thanks to all who replied. Before I could try RECOVER TORBA , I started doing a Dump Recovery. But henceforth I will use REORG with SHARELEVEL REFERENCE.
Thanks,
Srinivas G
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Kniaz, Benjamin X Sent: Thursday, February 18, 2010 6:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 ReOrg Issue [...]
31545 62 39_Re: LUW cleaning up logs after box swap12_tim malamphy20_timalamphy@YAHOO.COM31_Thu, 18 Feb 2010 19:50:07 -0800542_iso-8859-1 Jeff- I just did this twice this week. What makes you think you need the old logs of the old instance? I can't think of any reason to worry about them unless you want to fall back to a point in time before the offline backup was taken. After the restore of the new database on the new box from a full offline backup, you'll issue a "rollforward db dbname complete" command. You don't need the old logs for the rollforward, or ever again on the new instance. The new instance will start using it's own logs. Worry about making [...]
31608 206 17_Re: VIEW question11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 19 Feb 2010 07:46:29 +0100403_UTF-8 OMG! Perhaps it was true....it was 1.3 in my case and the IBMer was wearing a crummy tie with a cheap suit.........I will do a quick test on 9 and post the results....
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...]
31815 389 39_AW: Correlate page set with tablespace?35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Fri, 19 Feb 2010 09:49:11 +0100718_iso-8859-1 Cathy
I would say, for the directory that's impossible, because nothing of the directory is stored in the catalog. But for the others: Aren't the hex-values shown equal to HEX(DBID) or HEX(PSID) respectively in SYSIBM.SYSTABLESPACE or SYSINDEXES?
Mit freundlichen Grüßen Walter Janißen
ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf mailto:walter.janissen@itergo.com
Vorsitzender des Aufsichtsrates: Jürgen Vetter Geschäftsführung: Dr. Bettina Anders (Vorsitzende), Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön. Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996 [...]
32205 702 17_Re: VIEW question13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 19 Feb 2010 06:58:03 -0500500_iso-8859-1 Don't forget on todays big machines with much speedier DB2s you might need to test with a few thousand (millions?) select statements to get a measureable difference Phil Grainger Cogito Ltd. phil.grainger@cogito.co.uk +44 (0) 1298 872 148 +44 (0) 7505 266 768 www.cogito.co.uk
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil Sent: 18 February 2010 21:44 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] VIEW question [...]
32908 618 39_Re: Correlate page set with tablespace?13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 19 Feb 2010 07:03:53 -0500498_iso-8859-1 The internal IDs of the catalog and directory pagesets are fixed (so far as I remember) - after all, they are created before/during installation when there isn't a catalog to catalog things into, so they have to be
They are all documented in the diagnosis manual
The IDs of all the user pagesets are indeed in the catalog Phil Grainger Cogito Ltd. phil.grainger@cogito.co.uk +44 (0) 1298 872 148 +44 (0) 7505 266 768 www.cogito.co.uk [...]
33527 287 17_Re: VIEW question11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 19 Feb 2010 13:58:31 +0100357_UTF-8 Now that is interesting......in four out of five tests on a "quiet" machine with DB2 9 the VIEW was faster in elasped time but only by 0.01 seconds for 1000 selects so really 0.00001 seconds and I would actually write that off as well with the margin of error. Perhaps someone out there with a better monitor and more time could have a go at it? [...]
33815 221 39_Re: LUW cleaning up logs after box swap11_Jeff Garmon15_jgarmon@UGA.EDU31_Fri, 19 Feb 2010 13:10:29 +0000622_UTF-8 The backup/restore works fine and agree I don't need the logs, but the new instance keeps trying to archive them.
This is a snapshot of the db2diag entries for the restore on the new instance:
2010-02-19-06.11.49.071066-300 E68256510E492 LEVEL: Info PID : 18267 TID : 47706751887680PROC : db2sysc INSTANCE: db2inst9 NODE : 000 DB : ANIMALS APPHDL : 0-8 APPID: *LOCAL.db2inst9.100219111149 AUTHID : DB2INST9 EDUID : 21 EDUNAME: db2agent (ANIMALS) FUNCTION: DB2 UDB, database utilities, sqluxGetDegreeParallelism, probe:509 DATA #1 : Autonomic backup/restore - using parallelism = 2. [...]
34037 293 39_Re: LUW cleaning up logs after box swap11_Jeff Garmon15_jgarmon@UGA.EDU31_Fri, 19 Feb 2010 14:11:35 +0000491_UTF-8 I've issued a prune history command for 20100131, stopped/started the instance, and it's still trying to archive logs created on the previous instance from November:
2010-02-19-08.58.34.722598-300 I10431158E365 LEVEL: Warning PID : 24552 TID : 47401775655232PROC : db2sysc INSTANCE: db2inst9 NODE : 000 EDUID : 22 EDUNAME: db2logmgr (ANIMALS) FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3108 MESSAGE : Started archive for log file S0610174.LOG. [...]
34331 57 17_Re: VIEW question14_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Fri, 19 Feb 2010 09:26:08 -0500548_utf-8 Hold it, hold it.
Tests like have lots of hidden costs. You must be extremely careful and aware of various factors such as concurrent system load, dynamic statement caching, buffer pool page residency, etc.
For example, you execute a SELECT against the table in SPUFI. DB2 issues an OPEN against the pageset (because maybe it's closed), and reads data into the buffer pools (causing physical I/O). All that takes elapsed time and CPU. You then execute a SELECT against the View ... and are Astonished! that it runs faster! [...]
34389 281 17_Re: VIEW question11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 19 Feb 2010 15:57:48 +0100466_ISO-8859-1 absolutely thats what I said in my mail "margin of error" - I was doing 1000 and 10000 selects by the way...
Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de
Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert [...]
34671 268 39_Re: Correlate page set with tablespace?14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Fri, 19 Feb 2010 08:15:22 -0700491_UTF-8 While the OBID's and DBID of the catalog (DSNDB06) are indeed pre-defined, the catalog does indeed provide this self-describing data in SYSIBM.SYSTABLESPACE and SYSIBM.SYSINDEXES tables.. While the directory (DSNDB01) also contains pre-defined constants, however, since the directory objects cannot be accessed via DML statements, these objects are not described in the catalog, but the data is described in the Diagnosis Guide and Reference and could easily be put into a table. [...]
34940 398 39_Re: LUW cleaning up logs after box swap12_Phil Gunning19_pkgunning@GMAIL.COM31_Fri, 19 Feb 2010 12:34:30 -0500289_UTF-8 I'd like to see all the output of get db cfg , then this part of a db snapshot,
Node number = 0 File number of first active log = 310719 File number of last active log = 310748 File number of current active log = 310728 File number of log being archived = Not applicable [...]
35339 41 25_Db2Connect V9.5 FP5 OLEDB12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Fri, 19 Feb 2010 18:31:12 +0000382_UTF-8 Installed DB2C V9.5 Unlimited Edition and now the OLEDB test from Config Assistant doesn't work anymore. All other tests work. Have FP3a and all works fine.
Just get message: Connection failed..... IBM says getting: The -3023 error on the OLEDB connection test indicates that it is not receiving a valid database name from the application. They are still looking. [...]
35381 598 26_Re: APAR Signup or search?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 19 Feb 2010 13:35:52 -0500311_us-ascii Okay, I need to say this publicly. I was wrong, the SIS search webpage _is_ Y2K-aware. I was using it slightly wrong - it's got a place where you have to not only fill out a value, but also check a box. If you don't fill out the box, the date filter doesn't get applied to the return set. My bad. [...]
35980 597 43_Re: Denormalization Paper (Ancient History)13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 19 Feb 2010 14:02:41 -0500427_utf-8 I think it was Peggy Vieman or Viehman.
I believe she worked for IBM after they et Candle.
I bet Roger or Ed Woods remember.
Dave
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Joel Goldstein - Responsive Systems Sent: Thursday, February 18, 2010 2:44 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Denormalization Paper (Ancient History) [...]
36578 364 43_Re: Denormalization Paper (Ancient History)13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 19 Feb 2010 11:55:43 -0800376_utf-8 This was in a DB2-L post dated 11/9/2001:
Peggy Viehman - Peggy is a Technical Specialist with the database tools development group at IBM Silicon Valley Lab. She has worked with DB2 UDB for z/OS for over fifteen years. She has been a programmer, DBA, and DBA Manager. Peggy has taught advanced DB2 performance classes and published many technical articles. [...]
36943 42 45_Re: db2 luw(v9.5) - REORG & index page splits9_Andy Hunt21_andy_hunt@YAHOO.CO.UK31_Sat, 20 Feb 2010 10:14:02 +0000657_UTF-8 I'm being a bit pedantic here but.....
To get just the table statistics: db2pd -d -tcbstats
To get the index statistics (including the page split info) db2pd -d -tcbstats index
To get both table and index statistics (including page split info) db2pd -d -tcbstats all
It's also worth noting that these statistics are maintained in database memory so everytime you shutdown/startup db2 or deactivate/activate a database, then the counters start again. Therefore the page split metrics may not always truly reflect the actual index page splits of your data. A reorg of the index does reset the [...]
36986 30 35_DB2 failed and restart failed RESTP13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Sat, 20 Feb 2010 17:15:39 +0100399_utf-8 A runaway application got a DB2 test system to fail. We restarted the DB2 but after some time it failed again with a message that the next recover RBA is not in any active or archived log dataset.
I no have 2 tablespaces with index in RESTP status. One of them do not have any imagecopy, who explains that the restore failed I guess. I still have on member in the DS-group active. [...]
37017 44 39_Re: DB2 failed and restart failed RESTP10_Max Scarpa16_mscarpa@CESVE.IT31_Sat, 20 Feb 2010 18:10:15 +0100549_US-ASCII Aren't you able to start it even in MAINT option ? Did you try to start with DEFERRED objects (or DEFER ALL) ZPARM ?
Max Scarpa
_____________________________________________________________________
* 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 _____________________________________________________________________ [...]
37062 368 47_SV: [DB2-L] DB2 failed and restart failed RESTP13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Sat, 20 Feb 2010 20:31:18 +0100612_iso-8859-1 I was able to restart DB2 but it went down after around 45 minutes with the message:
14.18.03 STC02120 DSNR047I -DB2B DSNRBMON POSTPONED ABORT BACKOUT 457 457 PROCESSING LOG RECORD AT RBA 00736C345000 TO RBA 007276C000D7 14.18.17 STC02120 DSNJ113E -DB2B DSNJR003 RBA '007369560000' NOT IN 458 458 ANY ACTIVE OR ARCHIVE LOG DATA SET. CONNECTION-ID=DB2B, 458 CORRELATION-ID=003.RCRSC 02, MEMBER-ID=1 14.18.19 STC02120 IEF450I DB2BMSTR DB2BMSTR - ABEND=S04F U0000 REASON=00000000 707 TIME=14.18.19 14.18.19 STC02120 IEF352I ADDRESS SPACE UNAVAILABLE 14.18.19 STC02120 ÅHASP395 DB2BMSTR ENDED [...]
37431 264 17_Re: VIEW question12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 20 Feb 2010 12:52:21 -0800637_utf-8 You will get a rebind automatically (if zparm autorebind set on) or the program will be marked inop - with autorebind being done. ________________________________ From: "Sevetson, Phil" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, February 18, 2010 1:31:55 PM Subject: Re: [DB2-L] VIEW question Self correction. IF you add columns to a table with ALTER, and you don’t REBIND the programs calling the table with SELECT *, they won’t pick up the new columns. However, the next REBIND will cause the bind to fail at the SELECT * INTO statement because of a shortage of host variables to hold the data from [...]
37696 227 17_Re: VIEW question12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 20 Feb 2010 12:54:14 -0800636_utf-8 Understand that the view is marked as forced regeneration and if the program uses the view, it MUST be rebound, either automatically or manually before it can be used. At least this true with V9 NFM. I'm sure of that, having been burned by that several times. ________________________________ From: "Sevetson, Phil" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, February 18, 2010 12:50:17 PM Subject: Re: [DB2-L] VIEW question In the deep, dark past of DB2, SELECT * FROM tablename was sometimes used in coding. If you ALTER a _table_ by adding columns, the results (list of columns) returned by a SELECT * [...]
37924 182 47_SV: [DB2-L] DB2 failed and restart failed RESTP13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Sun, 21 Feb 2010 14:26:41 +0100393_us-ascii Deferred did not solve the problem. I had to do a could start. And after conferring with the developer a started the objects with ACCESS(FORCE) and dummy load of the tables. All this in MAINT an with install DBA user. The problem started with a delete of 40 mill rows, that was cancelled from the server side after some of the archive log needed for the backout already deleted. [...]