>
FROM SYSIBM.SYSDUMMY1
But now I can't, which leads me to believe SPUFI may now be stripping out insignificant blanks
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0)Â 1298 872 148
+44 (0)Â 7505 266 768
www.cogito.co.uk [...]60_4440F5DA00E3F3459BBCB97431B91B6612B6138D69@MAILR004.mail.lan
1060 97 47_Re: Z/OS SQLERRD(5) calculation in syntax error13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 24 May 2010 07:46:19 -0600468_utf-8 Maybe... Seems the max. length of an SQL statement is 2MB. That's a whole lotta love, er, spaces. Maybe if you fell asleep on the spacebar and woke up in, say, September it might be long enough.
Cheers,
Raymond
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: 24 May 2010 14:39
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Z/OS SQLERRD(5) calculation in syntax error [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420CE86100@PHXCCRPRD04.adprod.bmc.com
1158 120 47_Re: Z/OS SQLERRD(5) calculation in syntax error13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Mon, 24 May 2010 10:23:30 -0400441_utf-8 Perhaps, but at 72 bytes per line busting a 2MB statement only takes 28,000 blank lines (and typing r28000 in the prefix area is pretty easy 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
Attend IDUGÂ 2010 - Europe, the premiere event for DB2 professionals.
8-12Â November 2010, Vienna
Learn more at http://www.idug.org [...]60_4440F5DA00E3F3459BBCB97431B91B6612B6138D79@MAILR004.mail.lan
1279 213 47_Re: Z/OS SQLERRD(5) calculation in syntax error14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 24 May 2010 11:39:47 -0400490_iso-8859-1 Phil G...
You have too much time on your hands.
--Phil Sevetson
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Monday, May 24, 2010 10:24 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Z/OS SQLERRD(5) calculation in syntax error
Perhaps, but at 72 bytes per line busting a 2MB statement only takes 28,000 blank lines (and typing r28000 in the prefix area is pretty easy to do!) [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC4697F@MAIL02.fisalan.nycnet
1493 254 47_Re: Z/OS SQLERRD(5) calculation in syntax error13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Mon, 24 May 2010 11:46:05 -0400492_iso-8859-1 I wish
There was a good reason for the test
CAs ISQL has always stripped out insignificant spaces which meant CA could process much "longer" SQL statements than either SPUFI or DSNTEP2
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]60_4440F5DA00E3F3459BBCB97431B91B6612B6138D88@MAILR004.mail.lan
1748 25 0_13_harold ramsey15_hrdb2@YAHOO.COM31_Mon, 24 May 2010 09:06:39 -0700615_us-ascii http://zabixiz.t35.com/
_____________________________________________________________________
* 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! *
_____________________________________________________________________
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!
_____________________________________________________________________ [...]42_112268.8591.qm@web34502.mail.mud.yahoo.com
1774 292 47_Re: Z/OS SQLERRD(5) calculation in syntax error12_Isaac Yassin20_yassini@BEZEQINT.NET31_Mon, 24 May 2010 19:20:06 +0300531_utf-8 Hi
Try with 29128 lines (2*1024*1024/72 ) :-)))
Isaac Yassin
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Monday, May 24, 2010 6:46 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Z/OS SQLERRD(5) calculation in syntax error
I wish
There was a good reason for the test
CAs ISQL has always stripped out insignificant spaces which meant CA could process much "longer" SQL statements than either SPUFI or DSNTEP2 [...]35_002d01cafb5c$f92a4360$eb7eca20$@net
2067 49 19_Generating RUNSTATS13_Mick P Graley16_mgraley2@CSC.COM31_Mon, 24 May 2010 18:03:27 +0100724_UTF-8 Hi All,
I've searched the archives for this but couldn't find anything recent and
relevant.
I'm looking at changing the way we generate our RUNSTATS utilities on large
SAP sub-systems.
I'm was going to use the RTS tables using the STATS* columns of
SYSIBM.TABLESPACESTATS as shown in the Admin Guide:
(((STATSINSERTS+STATSDELETES+STATSUPDATES)*100)/TOTALROWS)>20
or STATSMASSDELETE is non-zero
But there must be other criteria to take into consideration, like no
current stats, no real time stats, null values in the STATS* columns etc.
So I was wondering what other people were doing to take "intelligent"
stats.
Any sample queries would be gratefully received [...]65_OF52C6C518.A064854F-ON8025772D.005983BF-8025772D.005DB31D@csc.com
2117 153 23_Re: Generating RUNSTATS10_Roger Hecq18_Roger.Hecq@UBS.COM31_Mon, 24 May 2010 13:31:22 -0400663_US-ASCII I also like to verify that all indexes and key columns have valid stats.
The age of the stats is totally arbitrary. If the demographics have not
changed, the stats are still valid, no matter how old they are.
"SELECT DISTINCT ",
" CAST(IX.DBNAME AS CHAR(8)) AS DBNAME ",
" ,CAST(TB.TSNAME AS CHAR(8)) AS TSNAME ",
" ,CAST(IX.TBCREATOR AS CHAR(8)) AS OWNER ",
" ,CAST(IX.TBNAME AS CHAR(30)) AS TABLE ",
" ,CAST(KY.COLNAME AS CHAR(30)) AS COLUMN ",
"FROM SYSIBM.SYSINDEXES IX ",
" ,SYSIBM.SYSKEYS KY ",
" ,SYSIBM.SYSTABLES TB ",
" ,SYSIBM.SYSTABLESPACE TS ",
" ,SYSIBM.SYSCOLUMNS CO ",
"WHERE TS.DBNAME = [...]62_1A700EEF49343148A08879B1E3CA5BA614F730F9@NSTMC101PEX1.ubsw.net
2271 91 23_Re: Generating RUNSTATS13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Mon, 24 May 2010 12:53:26 -0500399_UTF-8 Along with the obvious of generating stats where none exist (-1 in
statistics columns), for any statistics generated using RUNSTATS (or
regenerated) for critical queries, I would consider running the query
through Optimization Service Center's (OSC) Statistics Advisor to make
sure the correct RUNSTATS control cards are used to build the optimal
statistics for the query. [...]30_4BFABD16.9020400@attglobal.net
2363 52 0_14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Mon, 24 May 2010 13:59:24 -0400411_us-ascii Harold Ramsey, if you are a real person, you need to explain why you just posted a URL without any explanation or subject line. (I believe this is the first time I've seen autogenerated spam on DB2-L)
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of harold ramsey
Sent: Monday, May 24, 2010 12:07 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46988@MAIL02.fisalan.nycnet
2416 217 23_Re: Generating RUNSTATS10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 24 May 2010 20:06:40 +0200455_UTF-8 Hi
In Admin guida there's a chapter:
"When to reorganize indexes and table spaces"
with some thresholds to consider. See also:
IBM DB2 Statistics Processing RUNSTATS, Real Time Statistics By
Haakon Roberts, SHARE 2008
Real Time Statistics (RTS) Overview and Usage at UBS By
Andrè Goetschy
Real Time Statistics i praktiken By Anders Johansson
Getting Most Out Of Real Time Statistics By Namik
Hrle [...]66_OF5E289D71.85672B34-ONC125772D.0061031D-C125772D.00637C00@cesve.it
2634 60 32_ADMIN Re: [DB2-L] DO NOT respond0_19_galambos@ROGERS.COM31_Mon, 24 May 2010 18:18:45 +0000459_- DO NOT respond to this thread. We will handle this under the usage agreement for DB2-L.
-----------Original Message-----------
From: Sevetson, Phil
To: IDUG DB2-L
ReplyTo: IDUG DB2-L
Date: May 24 14:00:07
Subject: [DB2-L]
Harold Ramsey, if you are a real person, you need to explain why you just posted a URL without any explanation or subject line. (I believe this is the first time I've seen autogenerated spam on DB2-L) [...]104_1643419932-1274725134-cardhu_decombobulator_blackberry.rim.net-604589141-@bda271.bisx.prod.on.blackberry
2695 114 68_some one hi-jack my email and the message at 12:06 to wa not from me13_harold ramsey15_hrdb2@YAHOO.COM31_Mon, 24 May 2010 11:36:19 -0700797_iso-8859-1 Sorry but some hi-jacked my email account and the messags sent at 12:06 today was not me I am working on trying to find this person. Harold Ramsey Dakota Consulting Services Inc. Phone 336-314-5407
_____________________________________________________________________
* 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! *
_____________________________________________________________________
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!
_____________________________________________________________________ [...]43_502092.28569.qm@web34504.mail.mud.yahoo.com
2810 131 23_Re: Generating RUNSTATS13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 24 May 2010 13:16:56 -0600785_Windows-1252 Hmmm... shouldn't [AD] be in the title? ;o)
Cheers,
Raymond
PS: Who is it again you work for, Willie? (double-;o) )
________________________________________
From: IDUG DB2-L [DB2-L@IDUGDB2-L.ORG] On Behalf Of Willie Favero [wfavero@ATTGLOBAL.NET]
Sent: 24 May 2010 18:53
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Generating RUNSTATS
Along with the obvious of generating stats where none exist (-1 in
statistics columns), for any statistics generated using RUNSTATS (or
regenerated) for critical queries, I would consider running the query
through Optimization Service Center's (OSC) Statistics Advisor to make
sure the correct RUNSTATS control cards are used to build the optimal
statistics for the query. [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420C5A3EC8@PHXCCRPRD04.adprod.bmc.com
2942 45 37_I/O problem with IBM DB2 Log Analyzer8_Martin B28_martin.berchtold@ADVERSIO.DE31_Mon, 24 May 2010 15:41:09 -0400394_UTF-8 Dear experts,
For a short time we are running IBM DB Log Analyzer V3.1.
With Version 2.3 we have had no problems, but now we are facing the following.
So we analyzed 44 tables and in total we have approx. 2.8 million movements (insert/update/delete).
When we create a detailed report we see 760.000 I/O, elapsed time 38 minutes and the CPU time is 4 minutes. [...]60_0173967738647679.WA.martin.berchtoldadversio.de@www.idug.org
2988 60 52_Complimentary Business Intelligence Webcasts rom IBM14_Surekha Parekh20_surekha21@UK.IBM.COM31_Mon, 24 May 2010 17:47:13 -0400609_UTF-8 Telecon: IBM looks to System z for its own large scale Business Intelligence Deployment
Broadcast Date: June 1, 2010 at 11:00 a.m. Eastern Daylight Time, 3:00 p.m. UTC
Don't miss Larry Yarter, IBM Chief Architect for Cognos BICC, as he hosts the second teleconference in this series where he will share his teams on-going expertise and methodologies driving the rapid deployment of Cognos 8 BI for Linux on System z within the IBM enterprise. With an internal deployment plan in place, IBM can now share a best practice example for clients - - showing them how to better harness relevant [...]52_1928637492974776.WA.surekha21uk.ibm.com@www.idug.org
3049 129 79_Complimentary DB2 for z/OS and Data Warehousing Seminar - 17th June in Brussels14_Surekha Parekh20_surekha21@UK.IBM.COM31_Mon, 24 May 2010 18:10:06 -0400322_UTF-8 If you live in Belgium - here is a great opportunity to attend local DB2 for z/OS seminar in Brussels and meet
our top speakers including John Campbell, IBM Distinguished Engineer.
This seminar is not only FREE to all attendees but also offers you a chance to catch a glimpse of DB2 10 for z/OS Beta. [...]52_9902153634785458.WA.surekha21uk.ibm.com@www.idug.org
3179 109 35_Re: Can we create a trigger on RTS?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 25 May 2010 11:32:58 +0200551_UTF-8 no you cannot and it is best to create a completely new DB and TS and then
insert from select into it with CURRENT TIMESTAMP. Very fast, very simlpe
and what most places do
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 [...]64_OFC54A194E.650C5711-ONC125772E.0034596E-C125772E.003474EC@seg.de
3289 178 23_Re: Generating RUNSTATS11_Roy Boxwell16_R.Boxwell@SEG.DE31_Tue, 25 May 2010 11:38:06 +0200546_UTF-8 aside from the RTS thresholds you should be aware of the form of the index
(Ie Are the first column(s) temporal, Does the key data change "a lot"
etc.) and you should be aware of the total size of the index as it's
pretty clear that very small indexes do not warrent a RUNSTATS as often as
big ones. So I tend to use a (UPDATES + MIN ROWS) or NEVER RUNSTATTED or
TEMPORAL type of system not forgetting COLGROUPS, KEYCARD, FREQVAL and
HISTOGRAM of course!
/ad In our software we do all this and more of course /ad. [...]64_OF1131AD10.1A64A9DD-ONC125772E.003489EC-C125772E.0034ED71@seg.de
3468 44 63_AUTO: Gab Gambassi is at a customer site (returning 31/05/2010)12_Gab Gambassi20_gabriele@NZ1.IBM.COM31_Tue, 25 May 2010 22:03:35 +1200465_US-ASCII I am out of the office until 31/05/2010.
I am contactable on my mobile 021 484053.
I will do my best to check my mail but I don't guarantee fast response.
For urgent matters please contact:
my collegaue Art Balingit
Phone 09-359-8417
Mobile 021-464-958
Mail balingit@nz1.ibm.com
or my Team Leader, Joshua Joshua
Phone 09-359-8523
Mobile 021-2442673
Mail joshuaj@nz1.ibm.com
Thanks for you patience. [...]69_OFC3A5D2BB.FE1A5280-ONCC25772E.003742AB-CC25772E.003742B5@nz1.ibm.com
3513 35 41_Restarting Utilities - LISTDEF / TEMPLATE14_Gwyn Pritchard27_Gwyn.Pritchard@BARCLAYS.COM31_Tue, 25 May 2010 08:02:41 -0400400_UTF-8 We are converting a lot of our old Utility Jobs to using IBM Listdef with TEMPLATEs to simplify our Utility set up and a recent failure highlighted the fact that despite restarting the Utility using UTPROC=RESTART the jobs always started from the beigining of the step. Some of our Utility Jobs process 20+ objects and so if one of the later objects fail this could prove quite expensive. [...]59_4983169349665058.WA.Gwyn.PritchardBarclays.com@www.idug.org
3549 38 27_Make More Money with DB2 1011_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 25 May 2010 11:24:28 -04000_51_7334146859676432.WA.davedavebeulke.com@www.idug.org
3588 44 27_Make More Money with DB2 1011_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 25 May 2010 11:24:45 -0400682_UTF-8
New on my DB2 Blog...
Make More Money with DB2 10
The 2010 North American IDUG Conference and the IOD EMEA conference the last two weeks have really highlighted all the new functionality and features within DB2 10. The more you hear about these functions and features, the more you realize they are really going to save your company money right away.
Justifying a DB2 upgrade is always necessary for management and for the new DB2 10 it is going to be a lot easier with its many CPU saving features. ...
[...]51_0962283124645419.WA.davedavebeulke.com@www.idug.org
3633 143 23_Re: Generating RUNSTATS13_Mick P Graley16_mgraley2@CSC.COM31_Tue, 25 May 2010 18:03:32 +0100335_UTF-8 Hi All,
Thanks for the answers so far.
I forgot to mention that we can't use any third party products :-(
What I really needed was a query to replace the current query in a REXX
EXEC that generates the utility statements.
So I've gone ahead and "rolled my own".
This is what I've come up with so far:. [...]65_OF108CEF44.CBEA3384-ON8025772E.005D9C30-8025772E.005DB51F@csc.com
3777 452 23_Re: Generating RUNSTATS11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 26 May 2010 07:03:23 +0200501_UTF-8 I would simply add into the second query an emergenecy brake for small
tablespaces like this
AND (
((((FLOAT(STATSINSERTS)
+FLOAT(STATSDELETES)
+FLOAT(STATSUPDATES))*100)/(TOTALROWS+1))>20
AND TOTALROWS > 10000)
OR STATSMASSDELETE > 0
OR STATSLASTTIME IS NULL
OR STATSINSERTS IS NULL
OR STATSDELETES IS NULL
OR STATSUPDATES IS NULL
OR STATSMASSDELETE IS NULL
)
otherwise when 20 rows in a 100 row table change you will get a
runstats... [...]64_OF85BA0A4A.3E0AFDE4-ONC125772F.001B8A53-C125772F.001BC696@seg.de
4230 76 45_Re: Restarting Utilities - LISTDEF / TEMPLATE0_22_DB2information@AOL.COM29_Wed, 26 May 2010 11:17:32 EDT400_US-ASCII Gwyn,
As of yet, it will not do restarts, but we will work on the issue. You
may wish to view _www.recoveryknowledge.com_
(http://www.recoveryknowledge.com)
and browse Listdef Manager for DB2. We store all listdefs, templates, and
groups in DB2 tables for better security, management and automation. We
will be looking at a restart feature in Listdef Manager for DB2. [...]31_4042e.374e6a21.392e958c@aol.com
4307 1235 44_Tampa Bay DB2 Users Group June 2010 Meeting!14_Nelia Gonzalez28_nelia.gonzalez@SYNIVERSE.COM31_Wed, 26 May 2010 11:15:40 -04000_60_1166399980152059.WA.nelia.gonzalezsyniverse.com@www.idug.org
5543 50 12_DB2 UDB V9.714_jack fernicola20_bowling730@YAHOO.COM31_Wed, 26 May 2010 11:26:35 -0700562_iso-8859-1
Has anyone completed the upgrade from DB2/UDB V95 to V97? If so, were there any issues to look out for?
Thanks
Jack
_____________________________________________________________________
* 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! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]43_369464.26075.qm@web51807.mail.re2.yahoo.com
5594 109 30_criteria for generating reorgs11_Tom Moulder16_trexdb2@MAIL.COM31_Wed, 26 May 2010 14:35:21 -0500640_us-ascii Anyone out there have any good recommendations on what thresholds you use to
generate a reorg - based on statistics I assume, but whatever else. I am
just interested in what people are doing.
Tom Moulder
_____________________________________________________________________
* 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! *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]40_00c701cafd0a$96979fe0$c3c6dfa0$@mail.com
5704 145 60_Unemployed z/OS DBAs in NY/NJ/Chicago areas, please email me14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 26 May 2010 15:48:51 -0400328_us-ascii This is particularly to unemployed DBAs in Chicago or NY/NJ, or persons willing to work hourly there.
I have been contacted by a few recruiters looking for good candidates, and am not looking to move at present (just keeping my eyes open, wot?), but am willing to connect them with jobseekers on this list. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC469A5@MAIL02.fisalan.nycnet
5850 225 34_Re: criteria for generating reorgs14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 26 May 2010 15:49:44 -0400582_us-ascii Well, there's always the classic "our batch stream's performance got hammered last night and we haven't changed anything" threshold ;-)
________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Tom Moulder
Sent: Wednesday, May 26, 2010 3:35 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] criteria for generating reorgs
Anyone out there have any good recommendations on what thresholds you use to generate a reorg - based on statistics I assume, but whatever else. I am just interested in what people are doing. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC469A6@MAIL02.fisalan.nycnet
6076 247 34_Re: criteria for generating reorgs18_Satish Srikakulapu26_Satish.Srikakulapu@APS.COM31_Wed, 26 May 2010 13:10:09 -0700308_us-ascii Hi Tom,
We use CA's Database Analyzer to generate our REORGs. For most of the tables, we use IBM defaults. For few tables we have done some changes to the criteria. I have not used it but I guess DSNACCOR stored procedure gives the information regarding when to reorg and what to reorg. [...]55_D42E68651088914B9B327A4EEA8433ACA94B540A@VM251.apsc.com
6324 571 22_Re: IDUG Radio Silence17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 26 May 2010 21:27:51 +0100508_us-ascii EXCUSE ME, is it my fault if the volcano follows me?
although being attractive to a spewing mountain is not every woman's
dream.
sorry, is it not Friday yet?
Thanks.
Aurora
Aurora Emanuela Dell'Anno
CA MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com
http://www.ca.com/
P please don't print this e-mail unless you really need to! [...]56_3D02E8610514C04F991CF832BA154C661276245C@UKSLMS11.ca.com
6896 421 34_Re: criteria for generating reorgs16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Wed, 26 May 2010 16:29:10 -0400379_us-ascii Satish - if you are using the "good old approach" of scheduling reorgs
and other housekeeping processes using PDA stats collection services, I
strongly urge you to look into the r12 RTOS feature where PDA can limit
the scope of objects early in the process based on RTS statistics (like
DSNACCOR/X but with a load of additional sophisticated features). [...]56_0A18F096E689AC43BB2F52DAE5674D600876A51D@USILMS14.ca.com
7318 97 39_FLUFF/OT Re: [DB2-L] IDUG Radio Silence0_19_galambos@ROGERS.COM31_Wed, 26 May 2010 20:31:41 +0000720_- LOL -----------Original Message----------- From: Dell'Anno, Aurora To: IDUG DB2-L ReplyTo: IDUG DB2-L Date: May 26 16:28:40 Subject: Re: [DB2-L] IDUG Radio Silence EXCUSE ME, is it my fault if the volcano follows me? although being attractive to a spewing mountain is not every woman's dream. sorry, is it not Friday yet? Thanks. Aurora Aurora Emanuela Dell'Anno CA MSC Sr. Engineering Services Architect Tel: +44 (0)1753 577 733 Mobile: +44 (0)7768 235 339 Aurora.Dellanno@ca.com http://www.ca.com/ P please don't print this e-mail unless you really need to! ________________________________ From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq Sent: 18 May 2010 [...]105_1708025787-1274905904-cardhu_decombobulator_blackberry.rim.net-1105504061-@bda271.bisx.prod.on.blackberry
7416 121 32_Open Dynamic Cursor Optimization12_George Henke22_henke_george@YAHOO.COM31_Wed, 26 May 2010 17:15:54 -0400461_UTF-8 Incurring a large amount of CPU time opening a dynamic cursor with a UNION ALL as follows. Any ideas on how to optimize it?
EXEC SQL DECLARE NB29_DLR CURSOR
FOR SELECT ACNME AS NAME, ACSTRB, ACCTYB, ACSTEB,
ACCTN AS ENTITYNB, SUBSTR(CHAR(ACTID),6,4),
FLGASL, FLGASR, FLGAOW, FLGAGA,
SUBSTR(CHAR(ACPHN1),2,10),
SUBSTR(CHAR(ACZIPB),1,9), ' ', ' ', ' '
FROM QC20
WHERE
ACNME >= :NB29-ACNME [...]53_7993802429478241.WA.henkegeorgeyahoo.com@www.idug.org
7538 123 36_Re: Open Dynamic Cursor Optimization14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 26 May 2010 17:33:20 -0400508_us-ascii George,
You should try getting rid of that ORDER BY. Even if cursor materialization didn't happen due to the UNION, that ORDER BY together with the UNION would do it.
I suspect that you've got a guaranteed materialization just because it's a UNION against two different tables. All of those LIKE predicates together aren't helping, either. Had you considered doing a DSNUTILB/UNLOAD or a DSNTIAUL of the two tables, with columns appropriately arranged, and sorting them together? [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC469A9@MAIL02.fisalan.nycnet
7662 280 32_[AD] CA DB2 Tools R14 is now GA!17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 26 May 2010 22:43:04 +0100500_iso-8859-1 Hello list,
Many of you attended CA World and will be aware of this already, but for all who are otherwise interested:
"On the heels of announcing a wave of new Mainframe 2.0 deliverables, CA Technologies announce a new release of CA Database Management Solutions for DB2 for z/OS, designed to help customers save time and money by addressing database and application performance issues, simplifying database administration and improving backup and recovery efficiency. [...]56_3D02E8610514C04F991CF832BA154C66127624A0@UKSLMS11.ca.com
7943 30 36_Re: Open Dynamic Cursor Optimization12_George Henke22_henke_george@YAHOO.COM31_Wed, 26 May 2010 18:13:20 -0400348_UTF-8 The predicates in the ORDER BY are indexed with a good cluster ratio and I am doing a Dynamic Select so I was hoping the access path selected would not try to do a sort. But I am thinking the UNION ALL is causing a materialization of a Result Table and this is causing the overhead of a sort and that is why I am incurring the CPU time. [...]53_3639278412234631.WA.henkegeorgeyahoo.com@www.idug.org
7974 27 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions9_Sam Baugh24_sam.baugh@MOTION-IND.COM31_Wed, 26 May 2010 17:47:07 -0400407_UTF-8 So I got back to playing with this and just for fun, I tried "REPAIR SET TABLESPACE dbname.tsname AREORPENDSTAR" and it worked. This doesn't appear to be documented yet, or at least in any .pdf I've downloaded. Works in DB2v9 CM, but not for DB2v8 NFM.
.
No beer for me, but your welcome to give one to Roy for sparking a curiosity. Wouldn't Ol'Roy be a good beer name? Wish it was Friday. [...]55_8095994478346498.WA.sam.baughmotionind.com@www.idug.org
8002 335 64_Re: Unemployed z/OS DBAs in NY/NJ/Chicago areas, please email me16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Wed, 26 May 2010 19:06:18 -0500628_us-ascii Chicago hourly interests me.
Thanks,
Dan
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: Wednesday, May 26, 2010 2:49 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] Unemployed z/OS DBAs in NY/NJ/Chicago areas, please email me
This is particularly to unemployed DBAs in Chicago or NY/NJ, or persons
willing to work hourly there.
I have been contacted by a few recruiters looking for good candidates, and
am not looking to move at present (just keeping my eyes open, wot?), but am
willing to connect them with jobseekers on this list. [...]35_01f801cafd30$6f2e8e20$4d8baa60$@com
8338 55 36_Re: Open Dynamic Cursor Optimization10_Joe Geller21_joerg6666@HOTMAIL.COM31_Wed, 26 May 2010 23:22:16 -0400498_UTF-8 The UNION ALL does not result in materialization and does not require a sort. The problem is that the Order By applies to the union all, not to the individual parts of it. DB2 therefore has to do the complete UNION ALL and then do a sort.
If you are on Version 9, you can have an Order By in a subselect - i.e. each part of the union. If both tables have an index on the order by columns, DB2 may pick an access path the avoids the sort. If there is no sort at all, it will first [...]53_9388913165109352.WA.joerg6666hotmail.com@www.idug.org
8394 310 34_Re: criteria for generating reorgs13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 May 2010 01:52:21 -0600501_us-ascii Ooh! Ooh! I sense an RFE coming on for DASD Manager; another tick box on the selection criteria...
Hmmm. Probably [AD] and [FLUFF] combined. Oops.
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson, Phil
Sent: 26 May 2010 20:50
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] criteria for generating reorgs
Well, there's always the classic "our batch stream's performance got hammered last night and we haven't changed anything" threshold ;-) [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420CF9A286@PHXCCRPRD04.adprod.bmc.com
8705 203 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 27 May 2010 10:52:06 +0200412_ISO-8859-1 I'll take the Ol'Roy beer any time! I think your use of the word "yet" is
very hopefull! I see no chance that IBM will ever "externalize" this nice
little feature to the real word...to wet your appetite there are even more
of these tricks lurking around REPAIR...but one APAR from IBM and they
could all be gone - That's why they are OK for test but never for a
product or a tool! [...]64_OFFFE0CB03.52352002-ONC1257730.00308035-C1257730.0030B74C@seg.de
8909 471 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 May 2010 05:03:43 -0500491_iso-8859-1 Er, AREORPENDSTAR is documented as a TS option for Repair in the V9 Utilities Guide I've got. Might come in handy on occasion - I can think of one right now, but that's another story.
Cheers,
Raymond
PS. I've got the SC18-9855-04 version BTW.
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: 27 May 2010 09:52
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420CF9A465@PHXCCRPRD04.adprod.bmc.com
9381 525 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 May 2010 05:06:58 -0500467_iso-8859-1 Ah - my mistook. AREORPENDSTAR isn't documented; NOAREORPENDSTAR is.
I'll get me coat...
From: Bell, Raymond
Sent: 27 May 2010 11:04
To: DB2-L@IDUGDB2-L.ORG
Subject: RE: [DB2-L] z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions
Er, AREORPENDSTAR is documented as a TS option for Repair in the V9 Utilities Guide I've got. Might come in handy on occasion - I can think of one right now, but that's another story. [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420CF9A46B@PHXCCRPRD04.adprod.bmc.com
9907 234 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 27 May 2010 12:15:48 +0200354_UTF-8 I think you mean the NO version of this at least in mine from 2010-03
there is nothing about the AREOPPENDSTAR.... The setting of the *AREO is,
or at least was until a while ago, an undocumented feature of
REPAIR...there are more...Do I see pizza or beer coming from you Raymond??
I'm still waiting for the DB2 9 posters....<-> ;) [...]64_OF9079AC11.222AB49B-ONC1257730.00382EFA-C1257730.003860F1@seg.de
10142 447 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 27 May 2010 05:23:08 -0500497_utf-8 As we say here, eatin’s cheatin’, so you’ll have to bring your own food, Roy. I’ll see if I can’t smuggle a Schneider Weisse Vat 7 over to Vienna – assuming I’m going this year. My local Supermarkt has started stocking them. Mmmm...
You’re right of course; no documented ‘on’ switches, just the ‘off’ ones. Unless it’s an ‘opaque’ feature – like certain Zparms. Got a bit panto that one; it’s a secret parm. Oh no it isn’t. Oh yes it is, etc. [...]67_78B3EFE2C0C5D74E9E7777D1D669D5420CF9A4A3@PHXCCRPRD04.adprod.bmc.com
10590 241 62_Re: z/OS DB2v9 - Reorg Shrlevel Change for Multiple Partitions11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 27 May 2010 12:41:22 +0200628_UTF-8 Two pints of Ol' Roy please! Air mail will be OK as I hear it doesn't
spoil !!
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
"Bell, Raymond"
Gesendet von: IDUG DB2-L
27.05.2010 12:06
Bitte antworten an
IDUG DB2-L [...]64_OFC8495F8D.392EF121-ONC1257730.003AA4AF-C1257730.003AB897@seg.de
10832 32 35_Page latch contention against CGTTs14_Vidya Attuluri27_vidya.attuluri@MARRIOTT.COM31_Thu, 27 May 2010 07:32:45 -0400723_- Hi
we are on DB2 for z/OS V9 CM.
We have one of the SELECT SQL in a stored procedure involving only CGTTs (Crated Global Temporary Tables) and it waits 35% of elapsed time due to Page latch contention.
why should select on CGTTs wait for page latch?
Regards
Vidya
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]59_6235436983264370.WA.vidya.attulurimarriott.com@www.idug.org
10865 80 36_Re: Open Dynamic Cursor Optimization13_Terry Purcell18_tpurcel@US.IBM.COM31_Thu, 27 May 2010 08:45:56 -0400330_UTF-8 I am in agreement with Joe on using ORDER BY in subselect in V9. Only addition I would make is that you will want to duplicate the ORDER BY and also the FETCH FIRST 100 ROWS in each branch of the UNION ALL - since DB2 does not push those inside the UNION branches today. You also need the final ORDER BY & FETCH FIRST. [...]50_4369944788471362.WA.tpurcelus.ibm.com@www.idug.org
10946 220 34_Re: criteria for generating reorgs10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 27 May 2010 09:03:50 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA61508FAA0@NSTMC101PEX1.ubsw.net
11167 683 22_Re: IDUG Radio Silence10_Roger Hecq18_Roger.Hecq@UBS.COM31_Thu, 27 May 2010 09:07:12 -0400394_us-ascii Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. [...]62_1A700EEF49343148A08879B1E3CA5BA61508FAA5@NSTMC101PEX1.ubsw.net
11851 28 30_DB2 zOS - SYSUTIL and SYSLGRNG12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 27 May 2010 09:39:28 -0400707_UTF-8 Fellow Listers...can anyone remember (to save me trawling through the manuals) in which version(s) SYSUTIL and SYSLGRNG became SYSUTILX and SYSLGRNX? Version 4 spings to mind but I'm not sure if it was the same for both of them.
Thanks in advance.
Cheers, Adam
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]54_7444289786777363.WA.adambaldwines.ibm.com@www.idug.org
11880 173 34_Re: DB2 zOS - SYSUTIL and SYSLGRNG10_Max Scarpa16_mscarpa@CESVE.IT31_Thu, 27 May 2010 15:51:46 +0200670_US-ASCII A quick search in my RedBooks folder found a reference for SYSUTILX in V4
books. In V3 redbooks (actually I've only one) I found no reference.
HTH
Max Scarpa
*********************************************************************************
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org
**********************************************************************************
Adam Baldwin
Sent by: IDUG DB2-L
27/05/2010 15.41
Please respond to
IDUG DB2-L [...]66_OFC5F0213D.DD48D20E-ONC1257730.004BE84C-C1257730.004C25B7@cesve.it
12054 64 34_Re: DB2 zOS - SYSUTIL and SYSLGRNG11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 May 2010 14:54:34 +0100720_UTF-8 Hi Adam
I am fairly certain that they changed in different releases. I found some
old Version 5 documentation that referred to both new names and so it was
certainly before V5.
Regards
Mike Turner
Email: mike.turner@gmx.com
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159
44.
----- Original Message -----
From: "Adam Baldwin"
Newsgroups: bit.listserv.db2-l
To:
Sent: Thursday, May 27, 2010 2:39 PM
Subject: [DB2-L] DB2 zOS - SYSUTIL and SYSLGRNG [...]44_D68FB5165B474327883C2C47C5B01476@ToshibaA300
12119 64 34_Re: DB2 zOS - SYSUTIL and SYSLGRNG11_Mike Turner19_mike.turner@GMX.COM31_Thu, 27 May 2010 15:03:37 +0100756_UTF-8 Hi again Adam
Just found the older manuals on-line. V3 refers to SYSUTILX and SYSLGRNG,
V2.3 refers to SYSUTIL and SYSLGRNG, so I conclude SYSUTIL changed to
SYSUTILX in V3 and SYSLGRNG changed to SYSLGRNX in V4.
Regards
Mike Turner
Email: mike.turner@gmx.com
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159
44.
----- Original Message -----
From: "Adam Baldwin"
Newsgroups: bit.listserv.db2-l
To:
Sent: Thursday, May 27, 2010 2:39 PM
Subject: [DB2-L] DB2 zOS - SYSUTIL and SYSLGRNG [...]44_2FF1D3627FF546588B6AB15EAF2EC060@ToshibaA300
12184 25 34_Re: DB2 zOS - SYSUTIL and SYSLGRNG12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 27 May 2010 09:46:40 -0400530_UTF-8 Forget that!
V3 for SYSUTIL - SYSUTILX and V4 for SYSLGRNG - SYSLGRNX.
Cheers, Adam
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]54_3431709691190076.WA.adambaldwines.ibm.com@www.idug.org
12210 160 37_DB2 V8 z/OS Catalog & Directory Reorg15_Eugene R Renico28_eugene.r.renico@JPMCHASE.COM31_Thu, 27 May 2010 10:11:23 -0400461_us-ascii Hello List,
We are DB2 for z/OS V8 with z/OS 1.9 on some lpars and z/OS 1.11 on others. When I began setting up the cat/dir reorgs for all my data sharing groups I was told that I could shutdown all the DB2's in each group and bring up one in maint mode. Well, now I'm being told this can't be done; DB2 needs to remain up. Whenever I've done reorgs in the past I have always been able to shutdown all DB2's, then restart on in maint mode. [...]75_BAA9B699DFB94E4BACCDFAEB9D18E162370933D1E6@EMARC124VS01.exchad.jpmchase.net
12371 27 42_DB2 zOS - SYSUTIL and SYSLGRNG - Follow up12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 27 May 2010 10:00:12 -0400684_UTF-8 I'm posting this with a changed title as the list is rejecting my attempted replies. Thanks Max and Mike. I've found the info:
V3 SYSUTIL - SYSUTILx and V4 SYSLGRNG - SYSLGRNX.
Let's see if the List Police allow this one.
Cheers, Adam
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]54_2367055405388781.WA.adambaldwines.ibm.com@www.idug.org
12399 168 37_DB2 V8 z/OS Catalog & Directory Reorg15_Eugene R Renico28_eugene.r.renico@JPMCHASE.COM31_Thu, 27 May 2010 10:23:00 -0400509_us-ascii Hello List,
I apologize if you are receiving this twice. I'm not sure if this was sent out so that is why I am sending it again.
We are DB2 for z/OS V8 with z/OS 1.9 on some lpars and z/OS 1.11 on others. When I began setting up the cat/dir reorgs for all my data sharing groups I was told that I could shutdown all the DB2's in each group and bring up one in maint mode. Well, now I'm being told this can't be done; DB2 needs to remain up. Whenever I've done reorgs in the past I [...]75_BAA9B699DFB94E4BACCDFAEB9D18E162370933D241@EMARC124VS01.exchad.jpmchase.net
12568 50 34_Re: criteria for generating reorgs15_Steve Schuering31_steven.schuering@EXELONCORP.COM31_Thu, 27 May 2010 10:30:10 -0400626_UTF-8 We use BMC utilities and we're DB2 v8, mostly NFM. We're starting to look at using RTS but here are our current thresholds. The stats used in evaluating these thresholds are gathered weekly.
Exception Threshold Type
Percent card increase 30 TS
Number of extents per dataset 50 IX/TS
Percent far indirect reference - Percentage of rows (of a tablespace)
that have been relocated more than 16 pages from their original page. 25 TS
Percent leaf pages not in optimal position 10 IX
Percent leaf pages far from previous page 20 IX
Percent clustered 90 IX
Percent space occupied by dropped [...]63_8006395790877016.WA.steven.schueringexeloncorp.com@www.idug.org
12619 47 29_Question on LISTDEF parameter13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM31_Thu, 27 May 2010 10:36:35 -0400505_us-ascii I am using a LISTDEF parameter on a REPAIR OBJECT SET TABLESPACE process.
Anyone have an example they can pass on?
The commands are as follows:
LISTDEF LISTTS INCLUDE TABLESPACE T1861.*
Which works correctly
Then
REPAIR OBJECT SET TABLESPACE LIST LISTTS NOCOPYPEND
INVALID OPERAND 'LISTTS' FOR KEYWORD 'SET'
Then I changed the repair statement:
REPAIR OBJECT
SET TABLESPACE LISTTS NOCOPYPEND
TABLESPACE 'DSNDB04.LISTTS' NOT FOUND [...]75_8902ECFB06C73440874A90F82900BA0E1AE200EFD1@osgood.rdba.remotedbaexperts.com
12667 25 34_Re: DB2 zOS - SYSUTIL and SYSLGRNG12_Adam Baldwin22_adambaldwin@ES.IBM.COM31_Thu, 27 May 2010 09:55:37 -0400653_UTF-8 Hi Max. Thanks for that. I've tried to post a follow up to say that I'd found the info...V3 for SYSUTIL - SYSUTILX and V4 for SYSLGRNG - SYSLGRNGX.... but the message has disappeared in hiperspace....
Cheers, Adam
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]54_5654249577849919.WA.adambaldwines.ibm.com@www.idug.org
12693 51 61_FDRUpstream for backup with 64-bit UDB on Win 2008r2 server ?14_Bruce Lightsey25_Bruce.Lightsey@ITS.MS.GOV31_Thu, 27 May 2010 10:04:36 -0500474_US-ASCII Has anyone been able to get this combination to work yet ? I have a ticket
open with Innovation but thought I'd ask here since there is more DB2
knowledge here.
I use this product on all of my other UDB platforms/versions with no issues
- but I've avoided Windows like the plague. Now have been forced into
setting up a Workgroup edition on Win2008r2 and need to get the "not
completely supported" 64-bit dll to work with my DB2 backup . [...]73_OF15103D3F.B1005784-ON86257730.00516311-86257730.0052D1B2@its.state.ms.us
12745 36 36_Re: Open Dynamic Cursor Optimization12_George Henke22_henke_george@YAHOO.COM31_Thu, 27 May 2010 11:09:45 -0400476_UTF-8 Thank you Terry and Joe for the very helpful advice.
Joe,
I am not quite sure what you mean about getting mixed results. Can you elaborate more?
All,
I am doing iterative open and closes even when the arguments do not change. Since open time is when all this happens, I am thinking of eliminating needless opens and closes, but I suspect there may be some exposure in doing so. The APG recommends doing open and closer cursor each time. [...]53_3967633754456130.WA.henkegeorgeyahoo.com@www.idug.org
12782 122 56_DB2 for z/OS V8 - executing Java program in UDF function12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Thu, 27 May 2010 16:20:49 +0100612_ISO-8859-1 I've been playing with the above and have finally managed to get a test
function running that successfully calls a Java routine. However I can't
seem to find out how to return the results of the Java program to the
invoker of the UDF. The test UDF is as follows -
CREATE FUNCTION H30D.TESTUDF(FRST_NME VARCHAR(20)
,LAST_NME VARCHAR(32)
)
RETURNS VARCHAR(52)
FENCED
READS SQL DATA
COLLID DSNJDBC
LANGUAGE JAVA
EXTERNAL NAME 'H30D.DB2UDF:db2udf.TestUDF.testudf'
WLM ENVIRONMENT JAVAENV
CALLED ON NULL INPUT
PROGRAM TYPE SUB
PARAMETER STYLE JAVA; [...]60_AANLkTinE_MtJawbr_9KZOJ0-O709V9CieQJLRIUz9Zyd@mail.gmail.com
12905 32 36_Re: Open Dynamic Cursor Optimization12_George Henke22_henke_george@YAHOO.COM31_Thu, 27 May 2010 09:42:02 -0400471_UTF-8 Thank you and Joe Geller for your helpful comments.
Unfortunately, I am only running version 8.
I have a question about what Joe means about intermixing rows.
Also, the code does open and close cursor on every iteration even if the arguments do not change.
Since all this overhead occurs at open time, I am thinking of eliminating the needless open and closes. Are there any considerations of which I should be aware in doing this. [...]53_3620915579516747.WA.henkegeorgeyahoo.com@www.idug.org
12938 83 33_Re: Question on LISTDEF parameter13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 27 May 2010 11:34:43 -0400660_iso-8859-1 According to the DB2 9 Utility Guide, REPAIR does not support lists.....
I guess IBM didn't expect people to have mass processes requiring REPAIR
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk
Attend IDUG 2010 - Europe, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Knight
Sent: 27 May 2010 15:37
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Question on LISTDEF parameter [...]60_4440F5DA00E3F3459BBCB97431B91B6612B6138F3C@MAILR004.mail.lan
13022 47 36_Re: Open Dynamic Cursor Optimization10_Joe Geller21_joerg6666@HOTMAIL.COM31_Thu, 27 May 2010 11:52:26 -0400610_UTF-8 George,
What I meant is that if table1 has names 'able', 'baker','fred' etc.
and table2 has 'alex','charlie','david' do you want the result set to be all
of the rows from table1 first, then table2 rows or do you want:
'able','alex','baker','charlie','david','fred'
Assuming you want the latter, then doing it in a Union would have to have
an order by at the end, which in your original would require the complete
set of rows to be read and sorted. With V9, Terry's solution is very elegant -
DB2 will read 100 rows from each table, sort them and then only return 100
[...]53_0476280265934663.WA.joerg6666hotmail.com@www.idug.org
13070 629 60_Re: DB2 for z/OS V8 - executing Java program in UDF function16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 27 May 2010 10:59:11 -0500501_iso-8859-1 Select H30D.TESTUDF(CAST(‘123’ AS VARCHAR(20),CAST(NULL AS VARCHAR(32)) from
sysibm.sysdummy1;
The “123” in the main is for DB2 independent testing.
You could set up a second UDF with a path to main
(maybe'H30D.DB2UDF:db2udf.TestUDF' not sure) . I’ve never tried that myself.
Dan
Daniel L Luksetich
IBM Information Champion
IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS [...]35_00a301cafdb5$8cef95a0$a6cec0e0$@com
13700 28 45_Querying tables from different DB2 subsystems13_Sekhar Mekala23_SEKHAR.MEKALA@GMAIL.COM31_Thu, 27 May 2010 10:09:33 -0400640_UTF-8 Hi All,
If you have any steps to join tables from two different subsystems (residing in same and different mainframes/LPARS) please share with me.
Thanks in advance!!
Best regards,
Sekhar
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]55_8120434651396056.WA.SEKHAR.MEKALAGMAIL.COM@www.idug.org
13729 238 60_Re: DB2 for z/OS V8 - executing Java program in UDF function10_Todd Burch17_toddburch@MAC.COM31_Thu, 27 May 2010 11:08:51 -0500349_ISO-8859-1 Hi Jim. I've created several UDFs, but not a Java UDF yet.
I have some questions.
You have a main in your UDF, but your CREATE says SUB. Seems wrong.
Also, your MAIN returns VOID.
Is your MAIN just a driver you are using to test your UDF, while the
public static method testudf is being called by DB2? [...]44_39004FA1-75B7-4904-A65E-46533AB1AD8B@mac.com
13968 96 60_Re: DB2 for z/OS V8 - executing Java program in UDF function12_Jim McAlpine22_jim.mcalpine@GMAIL.COM31_Thu, 27 May 2010 17:24:49 +0100459_windows-1252 On Thu, May 27, 2010 at 4:59 PM, Daniel Luksetich wrote:
> Select H30D.TESTUDF(CAST(‘123’ AS VARCHAR(20),CAST(NULL AS VARCHAR(32))
> from sysibm.sysdummy1;
>
>
>
> The “123” in the main is for DB2 independent testing.
>
>
>
> You could set up a second UDF with a path to main
> (maybe'H30D.DB2UDF:db2udf.TestUDF' not sure) . I’ve never tried that myself.
>
>
>
> Dan
> [...]60_AANLkTikB5l4T1eAJKVBI98MY8fVqeVKD4rn7Cfxb26th@mail.gmail.com
14065 110 33_Re: Question on LISTDEF parameter13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM31_Thu, 27 May 2010 12:39:09 -0400540_us-ascii Yep you're right found it shortly after further review.
Well I was using nocopypend parameter.
Thanks
Bob Knight
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Phil Grainger
Sent: Thursday, May 27, 2010 11:35 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Question on LISTDEF parameter
According to the DB2 9 Utility Guide, REPAIR does not support lists.....
I guess IBM didn't expect people to have mass processes requiring REPAIR [...]75_8902ECFB06C73440874A90F82900BA0E1AE200F002@osgood.rdba.remotedbaexperts.com
14176 419 60_Re: DB2 for z/OS V8 - executing Java program in UDF function16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 27 May 2010 11:41:06 -0500390_us-ascii Sure is!
Eliminate the input parameters and keep the output parameter. Then move the
logic into the testudf (note it's the method associated with the UDF)
method. Then you are good to go.
If you still don't understand (or maybe I don't) then let's take this off
the listserv and I'll give you an example..but not until next week (clients
come first) [...]35_00ce01cafdbb$69ada5e0$3d08f1a0$@com
14596 264 60_DB2 LUW Eweek Article on new DB2 Sybase Migration Technology12_Phil Gunning19_pkgunning@GMAIL.COM31_Thu, 27 May 2010 12:48:19 -0400936_us-ascii http://www.eweek.com/c/a/Database/IBM-DB2-Gets-Sybase-Migration-Technology-4
09544/?kc=EWKNLDAT05272010STR1
Also, this goes hand in hand with SAP announcement of plans to purchase
Sybase last week.
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(at)gts1consulting.com
IBM Business Partner
IBM Authorized Reseller
This message (including attachments, header and footer
details) contains confidential information intended for a specific
individual or individuals and purpose(s), and is protected by law. If you
are not the intended recipient, you should delete this message and any
attachments.Any disclosure, copying, or distribution of this [...]35_005801cafdbc$6a0d83b0$3e288b10$@com
14861 945 71_Re: [SPAM] Re: DB2 for z/OS V8 - executing Java program in UDF function16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 27 May 2010 12:40:43 -0500433_iso-8859-1 Todd,
The main is for testing before making it a UDF, and that’s all. It allows
you to try the program out as a stand alone program. I believe all Java UDFs
are SUBs.
You’re UDF examples are good, but might require CAST expressions to avoid a
-440.
Cheers,
Dan
Daniel L Luksetich
IBM Information Champion
IBM Certified Database Administrator - DB2 9 for z/OS [...]35_00f001cafdc3$bc011cc0$34035640$@com
15807 166 22_Re: IDUG Radio Silence10_Mark Labby21_mlabby@AESSUCCESS.ORG31_Thu, 27 May 2010 13:49:01 -04000_67_OF846A5E0A.8907ADEA-ON85257730.0061A6A3-85257730.0061DD8D@pheaa.org
15974 66 49_Re: Querying tables from different DB2 subsystems15_Abe F. Kornelis13_abe@BIXOFT.NL31_Thu, 27 May 2010 21:13:07 +0200701_UTF-8 Sekhar,
To my knowledge this still(!) cannot be done.
I have always copied remote tables to local when
needing to join over subsystem or DataSharing Group
boundaries.
The alternative is to write a program that can connect
to multiple DB2 environments, then open multiple cursors
and join in the application (Stage 3 processing, alas)
Success!
Abe Kornelis.
DB2 Consultant.
===========
----- Original Message -----
From: "Sekhar Mekala"
Newsgroups: bit.listserv.db2-l
To:
Sent: Thursday, May 27, 2010 4:09 PM
Subject: [DB2-L] Querying tables from different DB2 subsystems [...]47_D40659B66A2B42D082E0DE6F27E44E75@your10f19f641c
16041 62 49_Re: Querying tables from different DB2 subsystems0_18_IWANT2BEME@AOL.COM29_Thu, 27 May 2010 17:49:23 EDT722_US-ASCII Perhaps develop stored procedures on 2 LPARS and used the CONNECT /
DISCONNECT commands to call the one on the alternate LPAR. Nesting stored
procedures has restrictions such as when COMMIT may be performed. Could pull the
data into a temp table and do the join.
Jacquie
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]31_9ac8b.3a424315.393042e3@aol.com
16104 57 49_Re: Querying tables from different DB2 subsystems16_Lavoie, Frederic29_Frederic.Lavoie@CRA-ARC.GC.CA31_Fri, 28 May 2010 08:20:39 -0400606_iso-8859-1 Hi,
Personally I use Microsoft Access with DB2 Client/Connect to join tables between subsystems. It's more for personal use, or reporting...
Frédéric Lavoie
Database
613-954-9958
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sekhar Mekala
Sent: May 27, 2010 10:10 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Querying tables from different DB2 subsystems
Hi All,
If you have any steps to join tables from two different subsystems (residing in same and different mainframes/LPARS) please share with me. [...]62_7A2589420A0D884C8EDE008CF8077E63ED8D4BA4@SD01CFMV0001.PROD.NET
16162 27 49_Re: Querying tables from different DB2 subsystems10_Joe Geller21_joerg6666@HOTMAIL.COM31_Fri, 28 May 2010 10:37:36 -0400679_UTF-8 You can also use Information Integrator (or whatever IBM is calling it these days).
It runs with DB2 UDB and can access data not only from DB2, but also Oracle,
SQLServer, XML, Excel etc and join data between these different sources.
Joe
_____________________________________________________________________
* 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
_____________________________________________________________________ [...]53_6841427852635470.WA.joerg6666hotmail.com@www.idug.org
16190 105 49_Re: Querying tables from different DB2 subsystems4_Rods19_custodior@GMAIL.COM31_Fri, 28 May 2010 12:45:35 -0300639_ISO-8859-1 What if you create an ALIAS for the remote table?
On Fri, May 28, 2010 at 11:37 AM, Joe Geller wrote:
> You can also use Information Integrator (or whatever IBM is calling it
> these days).
> It runs with DB2 UDB and can access data not only from DB2, but also
> Oracle,
> SQLServer, XML, Excel etc and join data between these different sources.
>
> Joe
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> * If you are going to attend only [...]60_AANLkTikUVVwVyodMw3uCeOXSd5Pjkehi_yItu-BVKnfS@mail.gmail.com
16296 378 49_Re: Querying tables from different DB2 subsystems13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 28 May 2010 12:00:28 -0400412_us-ascii DRDA does not (yet?) support distributed statements - which is what this is
You can have a distributed unit of work (so you could select from both tables and do the join programmatically) but you can't get DB2 to do it for you
Phil Grainger
Cogito Ltd.
phil.grainger@cogito.co.uk
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk [...]60_4440F5DA00E3F3459BBCB97431B91B6612B61DF7B8@MAILR004.mail.lan
16675 51 34_What DB2 10 information is public?12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 28 May 2010 17:10:20 -0400382_UTF-8 At the last couple of conferences, quite a few presentations on DB2 10 expanded the information
which is publicly available. Dave Beulke's paper is just about ready to publish. Since not everyone
goes to conferences, we loaded the presentations on our web to make them more accessible.
We have an overview with the webcast and answered questions on the web too. [...]51_7544847424836368.WA.millerrlus.ibm.com@www.idug.org