1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2009, week 2
2 457 27_Performance Control for DB235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Mon, 7 Dec 2009 19:59:17 -0500623_iso-8859-1 It may only be Monday, but what the heck... Friday is never far away.
Was doing some general websurfing, and saw something about DB2 performance on the JC Whitney website - they're auto parts. Or course I clicked to see what it was... snake oil. Like chrome muffler bearings.
Performance Control & Engine Management for your Aston Martin DB2. Check out our great selection of Performance Control & Engine Management products for your Aston Martin DB2. We offer an industry leading ONE YEAR GUARANTEE so that you can Shop with confidence! Select from the products or categories below to narrow [...]
460 375 39_Re: DB2 LUW V8.2 Tablespace Unavailable16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU30_Tue, 8 Dec 2009 09:19:16 +0800495_us-ascii Jeff,
You can try LIST HISTORY ALL SINCE , or check in the db2diag.log file for info/error messages. Perhaps you can add LIST TABLESPACES SHOW DETAIL and LIST UTILITIES (or db2pd -uti) to the start/end of the weekly backup job as well?
Cheers
Greg
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Schade, Jeffrey Sent: Monday, 7 December 2009 9:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 LUW V8.2 Tablespace Unavailable [...]
836 337 39_Re: [Fluff] Performance Control for DB211_Nick Cianci19_ncianci@AU1.IBM.COM30_Tue, 8 Dec 2009 12:20:57 +1100475_ISO-8859-1 Oh what the heck ... are they running a "v8"? ... :-)
Regards, Nick CIANCI
Database Administration IBM Australia ~ Canberra Phone: +61 2) 6212-1214 (Barton) Mobile: 0408- 64 06 01 MailTo:ncianci@au1.ibm.com
Joel Goldstein - Responsive Systems To cc Sent by: IDUG DB2-L Subject [...]
1174 66 74_DB2 z/OS V8 NFM - is Inline Statistics supported on some Catalog index es?13_David S Waugh16_dsw-dba@JUNO.COM28_Tue, 8 Dec 2009 03:27:39 GMT469_windows-1252 Anybody else notice this?
In the sections of the DB2 V8 Utility Guide under REORG INDEX and REBUILD INDEX, for the STATISTICS parameter it specifically says:
"You cannot collect inline statistics for indexes on the catalog and directory tables."
Yet, it appears you CAN collect inline statistics for indexes on some of the Catalog tables. The only ones disallowed are the indexes for the tables in SYSDBASE, SYSSTATS and SYSHIST. [...]
1241 93 31_Re: AW: [DB2-L] RTRIM weirdness13_Terry Purcell18_tpurcel@US.IBM.COM30_Tue, 8 Dec 2009 03:42:49 +0000478_utf-8 I am somewhat of a "glass half full" person - so I would say "Wow, look how the optimizer rewrites this stage 2 predicate RTRIM(col) LIKE to an indexable predicate!!".
It's true that this enhancement only extends to LIKE.
I can understand why someone would code the RTRIM with the LIKE, because then they don't have to worry about the trailing blanks and use wildcards for the literal/host var side. Not sure why you would use the RTRIM for the equals. [...]
1335 891 39_Re: [Fluff] Performance Control for DB235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM30_Mon, 7 Dec 2009 23:08:38 -0500397_iso-8859-1 Hi Nick,
6 actually a dual overhead cam straight 6... so we can't say V6
Regards, Joel
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 Performance Wizard is in Beta.. ..www.responsivesystems.com [...]
2227 300 169_Upcoming IDUG Webcast - "The DB2Night Show Episode #8 - Managing the DB2 LUW Workload Manager - Scott Hayes, DBI Software" - Friday, 11 December 2009 at 10:00 am USA CST13_David Chapman24_David.Chapman@IAG.COM.AU30_Tue, 8 Dec 2009 18:32:27 +1100773_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 #8 - 11 Dec 2009 - Managing the DB2 LUW Workload Manager - 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. [...]
2528 58 42_Re: z/OS V8 and V9 SQL Optimizer weirdness14_Peter Vanroose17_pvanroose@ABIS.BE30_Tue, 8 Dec 2009 09:05:58 +0000355_UTF-8 On Mon, 7 Dec 2009 10:33:32 +0100, Walter Janißen wrote: I was surprized, that the query with the RTRIM-function and LIKE returned a row at all.
This should not surprise: since version 8, the column DBNAME changed from CHAR(8) to VARCHAR(24), hence no blanks are *needed* anymore at the end of the DB name. [...]
2587 105 39_Re: DB2 LUW V8.2 Tablespace Unavailable16_Frederik Engelen25_engelenfrederik@GMAIL.COM30_Tue, 8 Dec 2009 10:19:58 +0100595_ISO-8859-1 You can also try db2pdcfg to get more information what's happening when this specific error occurs.
Kind regards,
Frederik
On Tue, Dec 8, 2009 at 2:19 AM, Gregory Palgrave wrote: > > Jeff, > > > > You can try LIST HISTORY ALL SINCE , or check in the db2diag.log file for info/error messages. > > Perhaps you can add LIST TABLESPACES SHOW DETAIL and LIST UTILITIES (or db2pd -uti) to the start/end of the weekly backup job as well? > > > > Cheers > > > > Greg > > > > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On [...]
2693 37 62_Re: AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?12_Norbert Wolf21_norbert.wolf@DATEV.DE30_Tue, 8 Dec 2009 09:37:56 +0000400_utf-8 HI,
of course the solution with UNION will be the end of the performance problem. And if the vendor will change the SQL it will work fine. Really? I think the solution must happen inside the DB2 optimizer, because this is a common sql problem. The optimizer must be able to transform the SQL ( under the cover to a UNION solution) and then we can call him an intelligent optimizer. [...]
2731 33 31_Re: Performance Control for DB29_Sam Orner21_sorner@AESSUCCESS.ORG30_Tue, 8 Dec 2009 07:06:07 -0500489_us-ascii JC Whitney. Now there's a blast from the past! They were a cost saver for many a replacement part for my '74 Camaro and subsequent '76 Monte Carlo!
(Embedded image moved to file: pic09881.jpg) ============================================================================= This message contains privileged and confidential information intended for the above addressees only. If you receive this message in error please delete or destroy this message and/or attachments. [...]
2765 38 41_DB2 9.5 UDB on LUW index reorg monitoring28_=?UTF-8?B?R2VvcmcgVGhvbcOp?=18_georg.thome@IEE.LU30_Tue, 8 Dec 2009 13:16:16 +0100337_UTF-8 Hi all, since Sunday we have now a reorganisation of table indexes running. After the data-TS has been changed to large this is the first run, though it means the RID are converted to large. Is there a way to monitor the progress? The snapshots for Tablereorgs or the db2pd ... -reorgs doesn't show anything. Thanks for help. Georg
2804 600 39_Re: DB2 LUW V8.2 Tablespace Unavailable15_Schade, Jeffrey15_JSchade@ISO.COM30_Tue, 8 Dec 2009 08:09:21 -0500512_us-ascii Hi Greg,
The LIST HISTORY worked perfectly and I identified the other task that was causing the issue. I also took your suggestion for adding the LIST TABLESPACES and LIST UTILITIES commands to my backup script.
Thank You
Jeff
_________________________________________ Jeffrey Schade
Systems Consultant, Technology Engineering
ISO 545 Washington Boulevard Jersey City, NJ 07310 Voice: (201) 469-3738 [...]
3405 926 31_Re: Performance Control for DB213_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Tue, 8 Dec 2009 08:33:29 -0500451_utf-8 I get google news alerts and every once in awhile, one of these pops up.
Thanks for sharing Joel
Dave
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Joel Goldstein - Responsive Systems Sent: Monday, December 07, 2009 7:59 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Performance Control for DB2
It may only be Monday, but what the heck... Friday is never far away. [...]
4332 457 37_Re: Performance Control for DB2 Fluff12_Jeff Frazier31_Jeffrey.Frazier@WENDYSARBYS.COM30_Tue, 8 Dec 2009 08:39:16 -0500489_US-ASCII Does it come with an 'OOOGA Horn'. How Cool!!
"Seibert, Dave" Sent by: IDUG DB2-L 12/08/2009 08:34 AM Please respond to IDUG DB2-L
To DB2-L@WWW.IDUGDB2-L.ORG cc
Subject Re: [DB2-L] Performance Control for DB2
I get google news alerts and every once in awhile, one of these pops up. Thanks for sharing Joel [...]
4790 679 45_AW: [DB2-L] Performance Control for DB2 Fluff12_Peter, Georg15_G.Peter@DZBW.DE30_Tue, 8 Dec 2009 15:03:41 +0100897_iso-8859-1 Joel,
it seems Aston Martin really had a car with DB2 in the name:
1950-1953 Aston Martin DB2 1953-1957 Aston Martin DB2/4 ....
(found under www.myvrl.com/group/astonmartinownersgroup)
So do we really have do rewrite the history of DB2 ??????
SCNR ;-)
Any info that anybody can provide on the above would be greatly appreciated.
With kind regards - mit freundlichen Gruessen, G e o r g H . P e t e r ------------------------------------------------------------------- Datenzentrale Baden-Wuerttemberg Development and Product Support (E3) Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, Europe e:mail G.Peter@dzbw.de Phone 0049-711-8108-271 PC-Fax 004971189696071 Internet (only in german language):http://www.dzbw.de ---------------------------------------------------------------------- P [...]
5470 199 52_ADVERTISEMENT: DB2 on z/OS - DBA Positions Available19_Claypool, Alisa@EDD25_Alisa.Claypool@EDD.CA.GOV30_Tue, 8 Dec 2009 06:27:07 -0800632_us-ascii The State of California Employment Development Department, located in Sacramento, has 4 openings for a DB2 on z/OS DBA. If you are interested, please contact me directly.
Alisa Claypool
(916) 654-8984
alisa.claypool@edd.ca.gov
________________________________
IDUG - The Worldwide DB2 User Community!
The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. [...]
5670 30 41_Build2 Phase for REORG SHRLEVEL Reference14_Daniel Nardini22_djnardinidb2@GMAIL.COM30_Tue, 8 Dec 2009 14:30:03 +0000511_utf-8 In all the V9 presentations and documents, it is stated that the BUILD2 step is eliminated for a online REORG (SHRLEVEL CHANGE). Is the BUILD2 step also eliminated for the REORG with SHRLVEL REFERENCE.? A shadow copy of the NPI is built with SHRLEVEL REFERENCE but no LOGAPPLY step is required. We can not find any explicit statement regarding the elimination of the BUILD2 step of a REORG SHRLEVEL REFERENCE although it would seem logical that it would work the same way as a REORG SHRLEVEL CHANGE. [...]
5701 991 37_Re: Performance Control for DB2 Fluff13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Tue, 8 Dec 2009 10:10:26 -0500490_us-ascii Here's another:
Last week, I was walking through one of the floors here that are pretty much entrenched in Oracle DB work and I noticed the word DB2 on a white board in a cubicle. I got excited for a moment and thought, as the Corporate DB2 bigot here, I needed to go find out what's going on and encourage it. But then I noticed the rest of the diagram where it showed connections to and from DB1, DB2, & DB3 and I realized these were entity diagrams. [...]
6693 261 37_Re: Performance Control for DB2 Fluff14_Lyon, Lockwood20_Lockwood.Lyon@53.COM30_Tue, 8 Dec 2009 10:57:04 -0500502_us-ascii This shows the craftiness of IBM Marketing.
After all, I doubt that when walking through the floor where everyone is entrenched in DB2 that you'd see diagrams labelled ORACLE1, ORACLE2, etc.
Lock Lyon Fifth Third Bancorp
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Seibert, Dave Sent: Tuesday, December 08, 2009 10:10 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Performance Control for DB2 Fluff [...]
6955 38 12_DB2 V8 & OSC11_Bob Markway21_bob.markway@OA.MO.GOV30_Tue, 8 Dec 2009 16:00:09 +0000302_utf-8 We had delayed installing the Optimization Service Center on V8 since we were planning to upgrade to V9 in the near future, but due to budget constraints, we are not performing the upgrade this fiscal year. So we are considering installing OSC on V8 since it has been "back-leveled" for V8. [...]
6994 503 37_Re: Performance Control for DB2 Fluff12_Kirk Hampton37_kirk.hampton@ENERGYFUTUREHOLDINGS.COM30_Tue, 8 Dec 2009 10:48:55 -0600596_us-ascii We once had a client who was balking at converting their in-house written application to DB2, because it had been prototyped in dBase III, and they did not want to take a step backwards.
J Kirk Hampton
Sr. Specialist - Mainframe
HCL Technologies America
Mesquite Data Center
972-216-3119
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Lyon, Lockwood Sent: Tuesday, December 08, 2009 9:57 AM To: Hampton, Kirk Subject: Re: Performance Control for DB2 Fluff [...]
7498 51 16_Re: DB2 V8 & OSC10_Todd Burch17_toddburch@MAC.COM30_Tue, 8 Dec 2009 11:11:54 -0600555_US-ASCII Hi Bob.
The very short answer from a support standpoint:
OSC for V8 won't extract V9 stats. OSC (Service SQL) is the wave of the future.
Todd DB2 Tech Support
On Dec 8, 2009, at 10:00 AM, Bob Markway wrote:
We had delayed installing the Optimization Service Center on V8 since we were planning to upgrade to V9 in the near future, but due to budget constraints, we are not performing the upgrade this fiscal year. So we are considering installing OSC on V8 since it has been "back-leveled" for V8. [...]
7550 69 45_Re: DB2 9.5 UDB on LUW index reorg monitoring12_Phil Gunning19_pkgunning@GMAIL.COM30_Tue, 8 Dec 2009 12:40:23 -0500655_utf-8 Unfortunately, db2pd -d -reorgs does not show index reorgs, neither does the snaptab_reorg convenience view at this time. PG
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Georg Thomé Sent: Tuesday, December 08, 2009 7:16 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 9.5 UDB on LUW index reorg monitoring
Hi all, since Sunday we have now a reorganisation of table indexes running. After the data-TS has been changed to large this is the first run, though it means the RID are converted to large. Is there a way to monitor the progress? The snapshots for Tablereorgs or the db2pd ... [...]
7620 65 16_Re: DB2 V8 & OSC10_Todd Burch17_toddburch@MAC.COM30_Tue, 8 Dec 2009 12:42:19 -0600395_US-ASCII Well, I need to correct myself. (Thanks Terry for the correction!)
VE does not collect DB2 V9 stats. I knew this, just wrote the wrong thing.
And my comment about the future wave... as of today - it is correct. ;)
Todd
On Dec 8, 2009, at 11:11 AM, Todd Burch wrote:
Hi Bob.
The very short answer from a support standpoint: [...]
7686 196 45_Re: DB2 9.5 UDB on LUW index reorg monitoring13_Mark Horrocks22_agentlease@HOTMAIL.COM30_Tue, 8 Dec 2009 20:44:42 +0000738_iso-8859-1
Hi,
My suggestion is to check O/S and ensure activity is happening at the disk/memory/cpu also check filesystem usage if being used for underlying tablespace containers.
Many Thanks,
mark Horrocks.
> Date: Tue, 8 Dec 2009 12:40:23 -0500 > From: pkgunning@GMAIL.COM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: [DB2-L] DB2 9.5 UDB on LUW index reorg monitoring > > Unfortunately, db2pd -d -reorgs does not show index reorgs, neither does the snaptab_reorg convenience view at this time. PG > > -----Original Message----- > From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Georg Thomé > Sent: Tuesday, December 08, 2009 7:16 AM > [...]
7883 390 45_Re: DB2 9.5 UDB on LUW index reorg monitoring13_Crissy, Frank28_Frank_N_Crissy@HOMEDEPOT.COM30_Wed, 9 Dec 2009 06:35:37 -0600430_iso-8859-1 You can also look in the dbdiag.log file and you will see index rebuild stanza's. This is helpful if you have a table that has a lot of indexes on it you can see how many have been reorg'd.
Nick
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Horrocks Sent: Tuesday, December 08, 2009 2:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 9.5 UDB on LUW index reorg monitoring [...]
8274 174 30_DB2 z/os v8 partitioned tables13_Hodgin, Scott19_shodgin@SCFBINS.COM30_Wed, 9 Dec 2009 08:02:36 -0600459_us-ascii I was playing around with converting my index controlled partitioned tables to be table controlled. It appears that you can only have ONE unique partitioned index, but many non-unique partitioned indexes. I've looked through the Sql Reference, but can't find anything in black and white saying this is a restriction, but when I try to create a second unique partitioned index on a table, I get an error about multiple or conflicting parameters. [...]
8449 396 37_Re: Performance Control for DB2 Fluff13_David S Waugh16_dsw-dba@JUNO.COM28_Wed, 9 Dec 2009 01:44:37 GMT871_windows-1252 Wouldn't they be labeled URK-1, URK-2, URK-3...???
You know, as in Urkel (I mean Oracle!!)
David
Please note: message attached
From: "Lyon, Lockwood" To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Performance Control for DB2 Fluff Date: Tue, 8 Dec 2009 10:57:04 -0500
____________________________________________________________ Nutrition Improve your career health. Click now to study nutrition! http://thirdpartyoffers.juno.com/TGL2131/c?cp=_2dEHRRzmveEdY_v9HTNYQAAJz2i9R_j_Y5ef6WP9NxqQ2yLAAYAAAAAAAAAAAAAAAAAAADNAAAAAAAAAAAAAAAAAAASQwAAAAA=
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...]
8846 145 46_Re: Identity Column Restart and need for reorg12_Myron Miller22_myronwmiller@YAHOO.COM30_Tue, 8 Dec 2009 15:39:58 -0800706_iso-8859-1 Suresh brings up an excellent point. Autorebinds can be real issues. If you have an active package, the probability that the autorebind will fail approaches unity. Need to manually rebind everything and check all binds as they finish. The issue will be contention on the system resource SYSIBM.SYSDATABASE. I see anytime I manually rebind more than about 10-20 packages consecutively, resource failures on this. And of course, I've seen tons of autorebind failures. Not pretty. Myron ________________________________ From: Suresh Sane To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Mon, December 7, 2009 2:19:10 PM Subject: Re: [DB2-L] Identity Column Restart and need for reorg [...]
8992 142 45_Re: Build2 Phase for REORG SHRLEVEL Reference16_Robert Catterall21_rfcatterall@GMAIL.COM30_Tue, 8 Dec 2009 23:10:52 -0500644_ISO-8859-1 There is no BUILD2 phase for a partition-level online REORG, whether executed with SHRLEVEL CHANGE or SHRLEVEL REFERENCE. As for an explicit statement to this effect, you can check the DB2 for z/OS V9 Utility Guide and Reference (available in PDF or HTML form at http://www-01.ibm.com/support/docview.wss?rs=64&uid=swg27011656#manuals -- click on the "Manuals" link). In the section on REORG TABLESPACE, under the heading "Execution phases of REORG TABLESPACE," you'll see that there is no reference to a BUILD2 phase. There would be such a reference if there were a BUILD2 phase for a REORG TABLESPACE PART SHRLEVEL REFERENCE. [...]
9135 195 45_Re: DB2 9.5 UDB on LUW index reorg monitoring30_=?ISO-8859-1?Q?Georg_Thom=E9?=18_georg.thome@IEE.LU30_Wed, 9 Dec 2009 13:44:04 +0100
9331 427 45_Re: DB2 9.5 UDB on LUW index reorg monitoring12_Phil Gunning22_pgunning@GUNNINGTS.COM30_Tue, 8 Dec 2009 17:50:09 -0500471_iso-8859-1 Georg, after re-reading your post, you could do an application snapshot and look at details regarding db2reorg application. You should see changes in rows read and rows written, and bufferpool activity, as well as cpu time. PG
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Horrocks Sent: Tuesday, December 08, 2009 3:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 9.5 UDB on LUW index reorg monitoring [...]
9759 32 21_DB2 System Programmer13_Tim Schneider22_tschneider3@CFL.RR.COM30_Wed, 9 Dec 2009 17:19:05 +0000403_utf-8 All,
Many of you that read this Listserv know and have worked with me. On Friday 12/11 IBM is ending my contract. Any of you who know of an organization that might need the sevices of a mainframe DB2 system programmer, please pass them my email tschneider3@cfl.rr.com and I will forward my resume. Best regards and look forward to working with Y'all again in the future. Tim Schneider [...]
9792 354 45_Re: Build2 Phase for REORG SHRLEVEL Reference14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 9 Dec 2009 12:28:01 -0500420_us-ascii Robert, Thanks for the note about logapply on PART SHRLEVEL REFERENCE. I hadn't thought through the implications of the V9 changes for that. --Phil Sevetson
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Catterall Sent: Tuesday, December 08, 2009 11:11 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Build2 Phase for REORG SHRLEVEL Reference [...]
10147 373 34_Re: DB2 z/os v8 partitioned tables13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM30_Wed, 9 Dec 2009 13:24:29 -0500727_utf-8 Hi Scott,
Can you post the DDL for both Create Index statements? And can you note the partitioning columns?
Dave
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Hodgin, Scott Sent: Wednesday, December 09, 2009 9:03 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 z/os v8 partitioned tables
I was playing around with converting my index controlled partitioned tables to be table controlled. It appears that you can only have ONE unique partitioned index, but many non-unique partitioned indexes. I've looked through the Sql Reference, but can't find anything in black and white saying this is a restriction, but when I try to [...]
10521 299 34_Re: DB2 z/os v8 partitioned tables14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 9 Dec 2009 13:33:33 -0500425_us-ascii Scott, When IBM created Table partitioning, one unique index had to be permitted as the original, partitioning index on index-partitioned tablespaces was potentially unique. But DPSI's are not permitted to be unique. You seem to have kind of slid into the "legacy-accommodation gap," here. DB2 appears to be permitting your index to exist in the same way it permits legacy unique partitioning indexes to exist. [...]
10821 120 27_XML DB2 9 DASD space issues13_Palko, George16_gpalko@OPERS.ORG30_Wed, 9 Dec 2009 19:53:59 +0000612_us-ascii Hi List,
We recently completed a pilot project that involved converting a BLOB which was 6.1 volumes in size to XML. After the conversion, the XML table expanded to 25.1 volumes. We then did a reorg to reclaim space; afterwards the XML table was about 7.7 volumes in size. We would like to convert other BLOBS. However, we are having issues with DASD. We have multiple BLOBS that exceed well over 100 volumes. To convert these in production and development we would need to support additional volumes for the conversion, such as sorting and mirroring which would necessitate 1500 additional [...]
10942 94 31_Re: XML DB2 9 DASD space issues9_Mike Bell21_mbell11a1@VERIZON.NET30_Wed, 9 Dec 2009 14:28:34 -0600473_US-ASCII I think your question is
If I build a table in a compressed tablespace can I get compression even I haven't done a load or reorg.
The answer as I understand it is NO.
What you can do is convert some percentage of the data and then run the reorg to establish the compression dictionary. This would require your convert process to be smart enough to restart with the last processed blob. Good programming practice but not everyone does it. [...]
11037 36 47_OSC use of subsystem connections in db2 connect10_Rich Janni23_rich.janni@WAKEFERN.COM30_Wed, 9 Dec 2009 21:01:57 +0000439_utf-8 I'm having a problem with the OSC version 1.1 fixpack 7. I uninstalled the old version and installed the new. It did not automatically see the subsystems that I have in DB2 connect. The old version did. And yet when I try to add one, it won't let me use an existiing subsystem alias name because it can see it. I have to install on 100 clients, and I don't want to add the subsystems to each one. Anyone else have this problem? [...]
11074 60 51_Re: OSC use of subsystem connections in db2 connect13_David Simpson22_dsimpson@THEMISINC.COM30_Wed, 9 Dec 2009 16:21:13 -0500632_utf-8 I also have this problem with fixpack 7. I believe it is a bug.
-----Original Message----- From: Rich Janni Sent: Wednesday, December 09, 2009 4:19 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] OSC use of subsystem connections in db2 connect
I'm having a problem with the OSC version 1.1 fixpack 7. I uninstalled the old version and installed the new. It did not automatically see the subsystems that I have in DB2 connect. The old version did. And yet when I try to add one, it won't let me use an existiing subsystem alias name because it can see it. [...]
11135 67 51_Re: OSC use of subsystem connections in db2 connect14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV30_Wed, 9 Dec 2009 16:26:25 -0500283_us-ascii Rich, I, at least, can't help ya -- they don't let me install diddly on the machine I have, has to be done by a domain admin. I'd recommend hitting Glenn up to do an ETR to get a fast response, as I haven't seen a lot of discussion of configuring the OSC on this list. [...]
11203 106 34_Re: DB2 z/os v8 partitioned tables14_Peter Vanroose17_pvanroose@ABIS.BE31_Thu, 10 Dec 2009 09:03:35 +0000346_UTF-8 Only a partitioning, partitioned index can have the UNIQUE qualifier. Suppose your table's first column is the partitioning column. Creating a unique index on that column makes that index partitioning, hence it is allowed to be partitioned. Creating a unique index on the second column alone requires that index to be non-partitioned. [...]
11310 64 12_QMF Question9_DB2DBAzOS21_bala.db2dba@GMAIL.COM31_Thu, 10 Dec 2009 15:37:53 +0530331_ISO-8859-1 Hello List,
QMF v8, DB2 v8 - z/Os 1.7 is the environment.
Is there a say in QMF to check if the returned rows is zero ? I want to distinguish the situation where the Query returns empty table. We have got few QMf PROCs written for generating reports and sometimes, they send out empty reports/emails. [...]
11375 157 34_Re: DB2 z/os v8 partitioned tables14_Peter Backlund21_BacklundDB2@TELIA.COM31_Thu, 10 Dec 2009 11:27:32 +0100
11533 223 34_Re: DB2 z/os v8 partitioned tables12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 10 Dec 2009 09:42:49 -0800419_us-ascii So if I interpret what is being said by both Peter's, is that if I partition by one set of keys (and that key according to the documentation doesn't have to be unique), I can't have any other UNIQUE indexes on that table, period, end of discussion.
So if I have customer sales information and partition by date and region, I can't have another index that is unique by sold partno, and salesman ID. [...]
11757 258 34_Re: DB2 z/os v8 partitioned tables14_Peter Backlund21_BacklundDB2@TELIA.COM31_Thu, 10 Dec 2009 19:24:15 +0100
12016 379 34_Re: DB2 z/os v8 partitioned tables12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 10 Dec 2009 10:50:18 -0800540_us-ascii Thanks Peter, As I mentioned, I was totally confused here about the issues. I probably missed the statement that said it was about DPSI's.
Myron
________________________________ From: Peter Backlund To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, December 10, 2009 1:24:15 PM Subject: Re: [DB2-L] DB2 z/os v8 partitioned tables
Myron,
my note was only describing DPSIs - which can be unique only if the partitioning key is part of the DPSI some way or other. [...]
12396 173 16_Re: QMF Question13_Vaughan, Mike26_Vaughan.Mike@PRINCIPAL.COM31_Thu, 10 Dec 2009 13:16:11 -0600645_us-ascii Take a look at the global variable DSQAO_NUM_FETCHED - it should give you what you are looking for.
________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of DB2DBAzOS Sent: Thursday, December 10, 2009 4:08 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] QMF Question
Hello List,
QMF v8, DB2 v8 - z/Os 1.7 is the environment.
Is there a say in QMF to check if the returned rows is zero ? I want to distinguish the situation where the Query returns empty table. We have got few QMf PROCs written for generating reports and sometimes, they send out empty reports/emails. [...]
12570 39 16_Re: DB2 V8 & OSC10_Rich Janni23_rich.janni@WAKEFERN.COM31_Thu, 10 Dec 2009 19:18:40 +0000409_utf-8 Bob, We have been using OSC V1.1 fixpack 6 in V8 for 6 months. The access plan graph (visual explain) part of it is enhanced. In addition, you can generate actual runstat statements, that may contain the colgroup options, on an sql statement or an entire DBRM. We have had a number of cases where we simply ran those runstats, rusulting in an optimum access path, and greatly improved performance. [...]
12610 600 47_SQL Stored procedure - Case statement behaviour12_Rosina Porco17_rporco@UOTTAWA.CA31_Thu, 10 Dec 2009 15:03:25 -0500400_iso-8859-1 Hi
We are a DB2 V8 Z/OS
I am trying to understand the behavior of a simple DB2 SQL store procedure
With 2 main statements
CASE/ WHEN / END CASE
And INSERT into
If the CASE statement is first the INSERT does not get executed . the SP ends successfully
If however
The first statement is the INSERT [...]
13211 107 16_Re: DB2 V8 & OSC12_Myron Miller22_myronwmiller@YAHOO.COM31_Thu, 10 Dec 2009 13:14:15 -0800545_iso-8859-1 I've been using 1.1.7 for some time now with no issues at all. It came up with all of my subsystems when I updgraded from 1.1.6. There's also a section in the performance manual about OSC as well, Pat Bossman did two presentations (Part I and Part II) PDFs, that are exceptional in learning how to use it. To be honest, you really don't need a class. I've got a number of programmers that have converted from Visual Explain to OSC with no problems and very minimal training to the product.. I have a few issues with it. But it's [...]
13319 110 51_Re: SQL Stored procedure - Case statement behaviour14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 10 Dec 2009 23:02:47 -0500302_iso-8859-1 Hi Rosina,
The INSERT statement you showed us is not correct: it has three target columns and 5 VALUES expressions.
Aside from that, assignment can't be done to an IN parameter: make it INOUT. I would have used: IF (att1 = ' ' and att3 <> ' ' ) then set att1 = ‘P’; ENDIF [...]
13430 62 51_Re: SQL Stored procedure - Case statement behaviour10_Todd Burch17_toddburch@MAC.COM31_Thu, 10 Dec 2009 23:31:35 -0600395_WINDOWS-1252 An assignment can be done to an IN parameter - perfectly legal.
Confusing perhaps to the reader, but legal nonetheless.
Rosina, I don't see any error handlers in that SQL Procedure. You should use them. When you get to V9, if you aren't already, every stinking statement is an "SQL" statement, and can set an SQLSTATE and has the potential to throw an exception. [...]
13493 195 16_Re: QMF Question9_DB2DBAzOS21_bala.db2dba@GMAIL.COM31_Fri, 11 Dec 2009 14:11:13 +0530632_ISO-8859-1 Thank you, Mike. I wrote a Rexx procedure to process using this global variable.
On Fri, Dec 11, 2009 at 12:46 AM, Vaughan, Mike wrote:
> Take a look at the global variable DSQAO_NUM_FETCHED - it should give you > what you are looking for. > > ------------------------------ > *From:* IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] *On Behalf Of *DB2DBAzOS > *Sent:* Thursday, December 10, 2009 4:08 AM > *To:* DB2-L@WWW.IDUGDB2-L.ORG > *Subject:* [DB2-L] QMF Question > > Hello List, > > QMF v8, DB2 v8 - z/Os 1.7 is the environment. > > Is there a say in QMF to check if [...]
13689 39 30_LASTUSED in SYSINDEXSPACESTATS34_Andreas Sp=?UTF-8?Q?=C3=A5ngberg?=19_spangberg@GMAIL.COM31_Fri, 11 Dec 2009 11:50:49 +0000443_UTF-8 Hello,
I'm trying to determine index usage partly by looking at the LASTUSED column of the RTS table SYSIBM.SYSINDEXSPACESTATS. I'm on V9.1 for z/OS.
The SQL Reference manual (SC18-9854) states that the default value is '0001-01-01' which would mean that index haven't been used if I understand it correctly.
But what does NULL for the column mean? I can't seem to find any information about it in the manuals. [...]
13729 864 49_Re: AW: [DB2-L] Performance Control for DB2 Fluff12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Fri, 11 Dec 2009 13:52:08 +0100568_Windows-1252
...and of course James Bond would use DB2 if he needed a database to save the world once again. Maybe IBM should talk to EON Productions about product placement for the next 007 movie (before URKL does)...
Here's what the "other" DB2 looks like: http://wheels.blogs.nytimes.com/2009/10/30/1950-aston-martin-sells-for-record-900000/. Check out the plate, saying "VMF 64" -> these guys ran a "Virtual MainFrame, 64bit" version back in the fifties already! Famous thingy, won several racings. Also didn't come for free, though... [...]
14594 26 34_Re: LASTUSED in SYSINDEXSPACESTATS10_Rich Janni23_rich.janni@WAKEFERN.COM31_Fri, 11 Dec 2009 12:54:08 +0000654_utf-8 Andreas, Try reorging the object to initialize it in real time stats. That's the way it works in V8 for other columns that are null. Rich
_____________________________________________________________________
* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________
http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups, there is probably one near you! Regional User Groups are your local connection to the Worldwide DB2 User Community _____________________________________________________________________ [...]
14621 918 49_Re: AW: [DB2-L] Performance Control for DB2 Fluff9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK31_Fri, 11 Dec 2009 13:39:23 +0000588_Windows-1252
Yes, we wouldn't want URKL in the Bond movies, they've already had their 15 minutes of fame in the Matrix trilogy
Date: Fri, 11 Dec 2009 13:52:08 +0100 From: peter_suhner@HOTMAIL.COM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AW: [DB2-L] Performance Control for DB2 Fluff
...and of course James Bond would use DB2 if he needed a database to save the world once again. Maybe IBM should talk to EON Productions about product placement for the next 007 movie (before URKL does)... [...]
15540 809 49_Re: AW: [DB2-L] Performance Control for DB2 Fluff14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Fri, 11 Dec 2009 14:55:38 +0100491_iso-8859-1 I thougth they run on Zion Mainframes....
greetings,
Patrick Steurs
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Chris Tee Sent: vrijdag 11 december 2009 14:39 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] AW: [DB2-L] Performance Control for DB2 Fluff
Yes, we wouldn't want URKL in the Bond movies, they've already had their 15 minutes of fame in the Matrix trilogy [...]
16350 364 45_Re: Build2 Phase for REORG SHRLEVEL Reference12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Fri, 11 Dec 2009 08:29:05 -0700545_us-ascii Hi Robert- we recently had experience with a reorg shrlevel NONE that applies logs during the build2 phase as well. I can't figure that one out- since there are NO updates to the table but IBM says its because the index structure can change and that's why they need to log. Just something to keep in mind if you have a large table that requires option reference or none. We basically blew out the space for the logs in our test system a couple of times because we were reorging a table that contained 800 million rows- and we were [...]
16715 37 34_Re: LASTUSED in SYSINDEXSPACESTATS18_GRESHAM, DEBORAH B18_DGRESHAM@SCANA.COM31_Fri, 11 Dec 2009 12:41:09 -0500432_utf-8 We noticed the same thing and opened a question with IBM. The resolution was that they are going to update the documentation to say the default is NULL instead of '0001-01-01.' Thanks, Deborah
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Andreas Spångberg Sent: Friday, December 11, 2009 6:51 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] LASTUSED in SYSINDEXSPACESTATS [...]
16753 40 11_DB2 V9 z/os12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Fri, 11 Dec 2009 18:32:42 +0000324_utf-8 Migrating to V9 fairly soon, are there any gotchas to be aware of (what, if anything bit your shop)?
.....Heard from somewhere, that that the memory management of V9 is not as forgiving as V8, anyone know what that means? Ie. may get hard crashes if not careful with memory alocations, more so than in V8. [...]
16794 53 28_PDM diagram from DB2 catalog10_Art McEwen21_art.mcewen@ONTARIO.CA31_Fri, 11 Dec 2009 20:44:33 +0000457_utf-8 Hi,
Any recommendations out there for tools that will draw physical data model diagrams directly from the contents of the DB2 Catalog?
We're trying to close a gap between our architects logical view of our data and the physical reality.
Thanks,
Art McEwen
Sr. Database Administrator Service Delivery & Management Br. Health Services Cluster 5th flr, 49 Place d'Armes Kingston ON K7L 5J3 [...]
16848 354 45_Re: Build2 Phase for REORG SHRLEVEL Reference16_Robert Catterall21_rfcatterall@GMAIL.COM31_Fri, 11 Dec 2009 15:53:50 -0500509_windows-1252 Hey, Shery.
Were you doing a partition-level REORG or a REORG of the entire tablespace? I ask because you refer to "reorging a table that contained 800 million rows" and "there are NO updates to the table." Both of these phrases suggest that the entire tablespace was reorganized; however, you also refer to "build2," and that suggests a partition-level REORG. Perhaps you did a partition-level REORG for every partition in the tablespace, maybe via several jobs running in parallel? [...]
17203 88 15_Re: DB2 V9 z/os13_Phil Grainger30_philg@PHILGRAINGER.DEMON.CO.UK31_Fri, 11 Dec 2009 21:26:29 -0000298_us-ascii One BIG gotcha that I've seen far too many people fall foul of concerns the new algorithm to choose which work file table spaces DB2 uses
In DB2 V8 (and prior) Db2 will ALWAYS sort in 4K work files unless the sort record won't fit, which causes DB2 to switch to 32K work files [...]
17292 78 32_Re: PDM diagram from DB2 catalog14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 11 Dec 2009 16:29:35 -0500409_us-ascii ERWin. Not cheap, but good.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Art McEwen Sent: Friday, December 11, 2009 3:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] PDM diagram from DB2 catalog
Hi,
Any recommendations out there for tools that will draw physical data model diagrams directly from the contents of the DB2 Catalog? [...]
17371 36 15_Re: DB2 V9 z/os14_Barbara Koenen33_barbara.koenen.bgit@STATEFARM.COM31_Fri, 11 Dec 2009 14:30:20 -0700379_UTF-8 Watch your workfile allocations. It uses tons more 32K.
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bob Jeandron Sent: Friday, December 11, 2009 12:33 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2 V9 z/os
Migrating to V9 fairly soon, are there any gotchas to be aware of (what, if anything bit your shop)? [...]
17408 146 15_Re: DB2 V9 z/os13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Fri, 11 Dec 2009 16:17:20 -0600637_UTF-8 If you have storage concerns, the good news, IMHO, is that DB2 9 has automatic warning message to alert of some storage conditions. I blogged it last week...
**http://it.toolbox.com/blogs/db2zos/look-out-youre-running-out-of-storage-35437
Willie
Bob Jeandron wrote: > Migrating to V9 fairly soon, are there any gotchas to be aware of (what, if > anything bit your shop)? > > .....Heard from somewhere, that that the memory management of V9 is not as > forgiving as V8, anyone know what that means? Ie. may get hard crashes if > not careful with memory alocations, more so than in V8. > > Also, found that [...]
17555 47 15_Re: DB2 V9 z/os13_David Simpson22_dsimpson@THEMISINC.COM31_Fri, 11 Dec 2009 17:28:11 -0500671_UTF-8 Also pay attention to the APARs associated with re-ordered row format.
Build2 phase elimination may also require changes to your reorg schedule.
______________________________________________________________________________ David Simpson | Senior Technical Advisor | Themis Education 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 | dsimpson@themisinc.com For more information about Themis, visit www.themisinc.com
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Barbara Koenen Sent: Friday, December 11, 2009 3:30 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 V9 z/os [...]
17603 121 15_Re: DB2 V9 z/os7_Ed Long19_rdhm99a@PRODIGY.NET31_Fri, 11 Dec 2009 15:01:56 -0800639_iso-8859-1 One other caution I would offer is to be very aware of access path changes. In our recent user group meetings here in sunny New England several attendees noted they had seen - to them signficant - changes, not all for the better. I remember the ancient, (1980), proverb, one aw crap wipes out all previous atta boys. Same applies here, if one access path regresses in a material way, no one will remember that 10 improved. Moral of story, test access paths and performance across the v8 v9 boundary. Edward Long ________________________________ From: Willie Favero To: DB2-L@WWW.IDUGDB2-L.ORG Sent: [...]
17725 548 45_Re: Build2 Phase for REORG SHRLEVEL Reference12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Fri, 11 Dec 2009 16:51:09 -0700575_us-ascii HI Robert, I was running a reorg on the whole tablespace since we were changing the table partitioning from an integer to a date driven design. Our systems programmer opened up an ETR with IBM early November and I will include part of their explanation. I was surprised we were logging due to the restriction on the tablespace and the fact that the reorg specified log no and a copy was included. I guess the logging was in the BUILD/SORTBLD phase- not specifically BUILD2. (my mistake on terminology). I was not running with DISCARD, but the indexes were in a [...]
18274 69 15_Re: DB2 V9 z/os13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 11 Dec 2009 16:09:08 -0800390_utf-8 I kept increasing my 32K work space until it was the same size as the 4K space. We had a few headaches with RRF because we use CA's PFR to recover from prod to test, and if one is in RRF and the other isn't, recover won't work. We also found a lot more stuff being put into advisory reorg status from simple things like adding columns. We reorg a lot more often than we used to. [...]
18344 79 32_Re: PDM diagram from DB2 catalog12_Phil Gunning19_pkgunning@GMAIL.COM31_Fri, 11 Dec 2009 20:36:36 -0500484_utf-8 Any modeling tool should be able to reverse engineer what you have. I like ER/Studio from Embarcadero. PG
-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Art McEwen Sent: Friday, December 11, 2009 3:45 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] PDM diagram from DB2 catalog
Hi,
Any recommendations out there for tools that will draw physical data model diagrams directly from the contents of the DB2 Catalog? [...]
18424 52 52_AUTO: MARK STANKIEWICZ/PLI/PNX is out of the office.0_30_MARK.STANKIEWICZ@PHOENIXWM.COM31_Fri, 11 Dec 2009 20:18:30 -0500410_US-ASCII I will be out of the office starting 01/02/2007 and will not return until 01/01/2010.
Please note that I will not be reading incoming mail at this address.
Please address mail to Mark.Stankiewicz@eds.com
Thank you.
Note: This is an automated response to your message "DB2-L Digest - 15 Feb 2009 to 17 Feb 2009 (#2009-53)" sent on 2/16/2009 8:00:05 PM. [...]
18477 105 15_Re: DB2 V9 z/os6_Leslie21_db2dba@BTINTERNET.COM31_Sat, 12 Dec 2009 21:48:27 -0000490_us-ascii Hi Bob
- sort works config is super important (have apar / doc on this if you need it) - ensure 32K files have cisize of 32k too, get ready for 7 and 8 messages in mstr, and the odd 84, also convert to segmented as soon as poss using the supplied rexx (segsize 16 default), we have seen a quirk where all use of the tempdb ends up on one vsam file and a stop/start of the db is needed to free it up. As we have not taken dumps yet (long story) when we see this we have [...]
18583 428 45_Re: Build2 Phase for REORG SHRLEVEL Reference16_Robert Catterall21_rfcatterall@GMAIL.COM31_Sat, 12 Dec 2009 17:35:37 -0500587_ISO-8859-1 Thanks for the clarification, Shery.
Robert
On Fri, Dec 11, 2009 at 6:51 PM, Hepp Shery C wrote:
> HI Robert, I was running a reorg on the whole tablespace since we were > changing the table partitioning from an integer to a date driven design. Our > systems programmer opened up an ETR with IBM early November and I will > include part of their explanation. I was surprised we were logging due to > the restriction on the tablespace and the fact that the reorg specified log > no and a copy was included. I guess the [...]
19012 184 15_Re: DB2 V9 z/os16_Robert Catterall21_rfcatterall@GMAIL.COM31_Sat, 12 Dec 2009 18:25:18 -0500561_ISO-8859-1 Do you use SQL procedures (stored procedures for which the source is included in the CREATE PROCEDURE statement)? If so, be aware of a change in the way that DB2 V9 treats an unqualified variable or parameter name in such a procedure, versus the way this is handled in a DB2 V8 environment (a parameter name can be qualified with the associated stored procedure name, a variable can be qualified with the label of the compound statement in which it was declared, and a column name can be qualified by the name of the table of which its a part). [...]
19197 428 57_Re: Approximate "break-even" when using Rowset processing15_Coleman, Troy L19_Troy.Coleman@CA.COM31_Sat, 12 Dec 2009 23:24:25 -0500461_UTF-8 Hi David,
Sorry I’m just getting back to DB2-L.
This is a great idea. Have you verified this?
Is the SQLCODE set to the highest SQLCODE?
The reason I ask is if you get several different errors but the last row processed is successful what do you get?
Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025 [...]
19626 590 57_Re: Approximate "break-even" when using Rowset processing13_David Simpson22_dsimpson@THEMISINC.COM31_Sun, 13 Dec 2009 14:26:22 -0500300_iso-8859-1 For multi-row fetch you get a +345 SQLCODE which essentially says "one or more errors may have occurred... have a nice day"
For a non atomic multi-row insert you get a -253 if some of the rows fail or a -254 if all of them fail. Again, no details just "an error has occurred". [...]
20217 602 57_Re: Approximate "break-even" when using Rowset processing12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Sun, 13 Dec 2009 21:31:40 +0100434_Windows-1252
David, Troy,
looks like a typo: The SQLCODE for multi-row fetch actually is +354.
And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered.
Regards, Peter
Date: Sun, 13 Dec 2009 14:26:22 -0500 From: dsimpson@THEMISINC.COM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing [...]
20820 1246 57_Re: Approximate "break-even" when using Rowset processing13_David Simpson22_dsimpson@THEMISINC.COM31_Sun, 13 Dec 2009 17:46:52 -0500523_iso-8859-1 Indeed... I cannot type. +354 it is.
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter Suhner Sent: Sunday, December 13, 2009 2:32 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
David, Troy,
looks like a typo: The SQLCODE for multi-row fetch actually is +354.
And - yes - that's the way we do it. Only call DIAGNOSTICS when one of the respective SQLCODEs were encountered. [...]
22067 1259 49_Re: AW: [DB2-L] Performance Control for DB2 Fluff15_Coleman, Troy L19_Troy.Coleman@CA.COM31_Sun, 13 Dec 2009 19:51:12 -0500790_iso-8859-1 I feature the 1950 Aston Martin DB2 car on my web site www.troylcoleman.com with a note about the power and performance just as DB2 for z/OS has. J
Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg Sent: Tuesday, December 08, 2009 8:04 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] AW: [DB2-L] Performance Control for DB2 Fluff [...]
23327 1451 57_Re: Approximate "break-even" when using Rowset processing15_Coleman, Troy L19_Troy.Coleman@CA.COM31_Sun, 13 Dec 2009 20:04:50 -0500740_iso-8859-1 Thanks. I love this list.
Troy Coleman
CA, Inc.
Principal Product Manager
Office: +1630 505 6025
Mobile: +1224 343 0073
Blog: http://ibmsystemsmag.blogs.com/db2utor/
Twitter: http://twitter.com/DB2utor
Twitter: http://twitter.com/troycoleman
Linkedin: http://www.linkedin.com/in/troylcoleman
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of David Simpson Sent: Sunday, December 13, 2009 4:47 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Approximate "break-even" when using Rowset processing
Indeed... I cannot type. +354 it is. [...]
24779 186 51_Re: SQL Stored procedure - Case statement behaviour11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Dec 2009 10:16:01 +0100516_UTF-8 I think its the "end case" perhaps that second "case" is causing some "confusion" - Just remove the case and correct the insert...hopefully all will be well!
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 [...]
24966 205 16_DB2mag Fall 200910_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 14 Dec 2009 04:55:56 -0500543_iso-8859-1
Hi DB2 user,
Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com
Last update on Monday, December 14th 2009
Fall 2009 issue of DB2mag is available on line:
- DB2mag Fall 2009 (digital version: interactive PDF) - DB2mag Summer 2009 (digital version: interactive PDF)
- DB2mag Archives (Winter 1996-Spring 2009)
Note: DB2mag became IBMDatabasemag and then IBM Data Management magazine. [...]
25172 43 38_DSNTIAUL to DSNUTILB unload conversion4_amit25_amit.chaudhari@IN.IBM.COM31_Mon, 14 Dec 2009 11:20:50 +0000615_utf-8 Hi
while converting existing DSNTIAUL Unload to DSNUTILB unload utility, I am getting VB output file from DSNUTILB instead of FB which was in DSNTIAUL, also Null indicators are in front of the field, too,instead of after, in case you have nullable fields.
Due to this the downstream jobs failed because of record format change, i tried to change the record format & Null indicator position after the field using Fileaid/Syncsort, But i am getting spaces X'40' in DSNUTILB unload file instaed of Null X'00' which are in DSNTIAUL unload file for empty fields while looking the both the files [...]
25216 152 51_Re: SQL Stored procedure - Case statement behaviour12_Rosina Porco17_rporco@UOTTAWA.CA31_Mon, 14 Dec 2009 09:36:28 -0500388_iso-8859-1 Thanks. I realize I could code an IF statement . I guess my exam was poor .
Basically, I really would like to understand why DB2 ends the processes after the CASE WHEN END CASE and does not continue to execute the rest of the code .
Similarly , SP with multiple CASE /when/ end case statement only the first one is executed and the SP completes successfully [...]
25369 39 21_DB2 z/os V9 workfiles12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Mon, 14 Dec 2009 14:53:01 +0000407_utf-8 Trying to get a handle on what to do for V9 workfiles. It seems from a previous post (thanks) that at least additional 32K spaces are required/strongly suggested for V9.1. What is the recommended procedure for converting to the new WorkFile Tbsps? Is it recommended to convert? Drop old 4k and 32k tbsps and delete user defined files then recreate using the new Stogroup defined tbsps (DSNTWFG)? [...]
25409 239 25_Re: DB2 z/os V9 workfiles11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 14 Dec 2009 16:31:05 +0100328_ISO-8859-1 lots of good stuff in the archives and you will need two ts's with extents allowed. The last ROT I heard was "flip the numbers". Allocate as many 32k's as you had 4Ks but at 8x the space. reduce the 4ks in size and qty - monitor usage and "adjust where needed"...better than "it depends" but not a lot better!!! [...]
25649 88 40_BMC Aptune SQL statement DEtail analysis8_duam lee20_duam_lee@HOTMAIL.COM31_Mon, 14 Dec 2009 18:17:08 +0000515_ks_c_5601-1987
Hi All,
Can any one tell me the Other wait time in BMC aptune's sql statement detail analysis? I mean what could be the causes for this. When I check the details of SQLs which are taking how much time in I get for most of the sqls the other wait time as more. The example below is an exception. But I am interested to know whether this is other db2 wait time or other wait time. What could be the causes for this? Thanks to all of you for valuable inputs. [...]
25738 166 44_Re: BMC Aptune SQL statement DEtail analysis16_Robert Catterall21_rfcatterall@GMAIL.COM31_Mon, 14 Dec 2009 14:02:36 -0500504_TIS-620 Assuming that the "other wait time" figure is obtained from fields in DB2 for z/OS accounting trace class 3 records, it could include time spent waiting for DB2 to perform various services for the application process, such as preformatting space in a data set (for insert processing), extending a data set (also for insert processing), commit processing, and deallocation processing. If you're running DB2 in data sharing mode, it could also include wait time due to global lock contention. [...]