1 IDUGDB2-L.ORG /home/listserv/home/db2-l June 2010, week 4
2 116 63_Re: Checking for use of reserved words in existing applications14_Peter Schwarcz23_schwarcz@BIGPOND.NET.AU31_Tue, 22 Jun 2010 14:58:23 +1000399_us-ascii The last time I did this sort of exercise, I was in the middle of a DB2 V8
upgrade. I grabbed a set of reserved words out or the latest DB2 manuals.
The easiest way to extract the words seemed to be via the on-line manuals
where you can cut and paste, mind you when I started hunting around there
are different lists of reserved words available from the IBM documentation. [...]38_003701cb11c7$910c2f10$b3248d30$@net.au
119 23 65_Auto Reply: DB2-L Digest - 19 Jun 2010 to 22 Jun 2010 (#2010-168)0_27_scott.a.saunders@ORACLE.COM31_Mon, 21 Jun 2010 22:09:02 -0700390_utf-8 From June 21 - 25, I will be visiting a customer or in transit. There will be a delay in responding to your email. I am available by cell phone at 727-687-1566 or contact my manager Pete Vammino at pete.vammino@oracle.com.
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU * [...]44_58763e74-c6d2-4e2c-b48b-3aecb10a28fa@default
143 82 81_Re: Is there a way to define a function that only certain parameters can be null?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 22 Jun 2010 18:53:18 +1000463_US-ASCII The only "slight" catch is that the specificname occurs after the parameters -
so you need to have code to identify when your program parameters are
function parameters and when they are the additional SQLSTATE or later
parameters.
It might be easier to write a glue routine, that takes the 2 parameter form of
the function and programatically calls the 3 parameter form with the third
parameter dummied out (ie a null value). [...]49_4C21069E.13002.3822DB06@jacampbell.acslink.net.au
226 135 81_Re: Is there a way to define a function that only certain parameters can be null?13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 22 Jun 2010 08:50:02 -0400329_iso-8859-1 I was under the impression that the whole point of DB2s function implementation was that you COULD create the "same" function with different parameter specifications?
So in this case, you'd create two functions WITH THE SAME NAME (and let DB2 pick a unique specific name) - one with 2 parms and one with 3 [...]60_4440F5DA00E3F3459BBCB97431B91B6612B84D2C8E@MAILR004.mail.lan
362 24 81_Re: Is there a way to define a function that only certain parameters can be null?2_RL31_ranga.lingala@MORGANSTANLEY.COM31_Tue, 22 Jun 2010 09:47:31 -0400585_UTF-8 You will have issues managing ddl for function in case you need drop and recreate individual function, I suggest use custom specific name as you do for SPs.
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]63_9468222893614519.WA.ranga.lingalamorganstanley.com@www.idug.org
387 132 81_Re: Is there a way to define a function that only certain parameters can be null?10_Todd Burch17_toddburch@MAC.COM31_Tue, 22 Jun 2010 10:27:29 -0500331_US-ASCII James is correct - this aspect of the User Defined Function parameter
list definition is a pain. The fixed stuff should be prior to the
varying stuff, or, there should be a count of varying items so you can
easily get to the fixed stuff after the varying items. Doesn't
really make overloading very easy. [...]44_D7BBB925-A485-4422-BBB9-EAF7D5889167@mac.com
520 87 35_DB2 9 z/OS CM...+ 100% DB2 CPU time15_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Tue, 22 Jun 2010 17:54:32 +0200296_ISO-8859-1 Dear all ,
Just migrated to V9CM and did some performance test between V8 NFM and V9 CM
and on our test application , we get the double in DB2 CPU time for V9.
All other figures are comparable , so how can we explain this cpu spike ? :
1:31 for V9 vs 48s for V8 . [...]60_AANLkTinPvyMqCKQsqeztO6IsA8TJEPPP5j4xq3DTcZ4i@mail.gmail.com
608 345 39_Re: DB2 9 z/OS CM...+ 100% DB2 CPU time16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Tue, 22 Jun 2010 11:05:12 -0500443_us-ascii What are the getpage counts between the two tests?
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of nguyen duc tuan
Sent: Tuesday, June 22, 2010 10:55 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] DB2 9 z/OS CM...+ 100% DB2 CPU time
Dear all ,
Just migrated to V9CM and did some performance test between V8 NFM and V9 CM
and on our test application , we get the double in DB2 CPU time for V9. [...]35_01ea01cb1224$b37457a0$1a5d06e0$@com
954 119 52_Re: Can z/os USS web page display & update DB2 data?11_Hunter Cobb22_hhc@TRAINERSFRIEND.COM31_Tue, 22 Jun 2010 10:48:29 -0600473_UTF-8 Max,
Within z/OS UNIX (included with your z/OS system) there is
a free HTTP server (included with your z/OS system)
that does a fine job of serving web pages. To deliver DB2
data in this context requires that you write a CGI program --
typically in COBOL, PL/I, REXX, C/C++, or Assembler.
Let me suggest that you take a look at some of our
free papers along this line (no cost, no sign up, no registration);
the place to start is [...]35_4C20E95D.9080002@trainersfriend.com
1074 52 20_DBM1 slower shutdown12_Roy Reynolds17_royr@BERKELEY.EDU31_Tue, 22 Jun 2010 13:31:36 -0400416_UTF-8 What would cause DBM1 to run a long time during shutdown?
Several months ago we started keeping our DB2 V8 NFM data sharing group up for a month at a time. Before that DBM1 would shutdown very soon after all the other started tasks for DB2. This happens for our Prod DS group, but isn't as slow for our QA and Dev DS groups. There are two members in each group.
DSMAX is 44K datasets for all these. [...]49_8058133022075624.WA.royrberkeley.edu@www.idug.org
1127 72 24_Re: DBM1 slower shutdown14_Lyon, Lockwood20_Lockwood.Lyon@53.COM31_Tue, 22 Jun 2010 13:58:19 -0400342_utf-8 Roy,
Thoughts.
In the past I've experienced DBM1 "slow" shutdowns due to a large number of physical closes of open datasets. I believe that dataset close also triggers writes of some SMF records, as well as logging. Another possibility (depends upon your shutdown procedure) is waits due to logging or log archiving. [...]72_4C9F8982DB3D124695B6B4621DA3700C2B257F@SCCINDCE2K301-B.dm0001.info53.com
1200 157 24_Re: DBM1 slower shutdown14_Wayne Driscoll18_wdrisco@US.IBM.COM31_Tue, 22 Jun 2010 13:00:13 -0500647_UTF-8 Look at the SMF setting DDCONS. If DDCONS=YES is set (or defaulted to)
SMF will perform much more processing due to the number of DD allocations
that come and go over a month of DB2 open/close activity.
From the console:
D SMF,O
===============================================
Wayne Driscoll
OMEGAMON DB2 L3 Support/Development
wdrisco(AT)us.ibm.com
===============================================
From:
Roy Reynolds
To:
DB2-L@IDUGDB2-L.ORG
Date:
06/22/2010 12:48 PM
Subject:
[DB2-L] DBM1 slower shutdown
Sent by:
IDUG DB2-L [...]68_OFA04AC3AE.533DB1F8-ON8625774A.0062AB7F-8625774A.0062E56C@us.ibm.com
1358 138 24_Re: DBM1 slower shutdown16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Tue, 22 Jun 2010 13:04:44 -0500648_utf-8 If you leave your online monitor active during the shutdown you can actually watch the open dataset count decrease. If that is happening you know it's datasets. If not, then you need to look elsewhere.
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
IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database Administration for UNIX, Windows, and OS/2
IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development
IBM Certified Advanced Technical Expert - DB2 Data Replication [...]35_023f01cb1235$653a7e50$2faf7af0$@com
1497 258 33_[AD] YLA and BMC Webcast Tomorrow16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Tue, 22 Jun 2010 13:09:02 -0500610_us-ascii On indexing.
http://www.bmc.com/events/webinar/Exploiting-Indexes-and-Influencing-DB2-on-
zOS-Usage.html
Cheers,
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
IBM Certified Solutions Expert - DB2 Universal Database V7.1 Database
Administration for UNIX, Windows, and OS/2
IBM Certified Solutions Expert - DB2 UDB V7.1 Family Application Development
IBM Certified Advanced Technical Expert - DB2 Data Replication [...]35_024301cb1235$fee331a0$fca994e0$@com
1756 26 24_Re: DBM1 slower shutdown12_Roy Reynolds17_royr@BERKELEY.EDU31_Tue, 22 Jun 2010 15:10:44 -0400435_UTF-8 Thanks for the quick replies. Our DDCONS=NO for SMF so, at the very least, I'll follow Dan's advice next time to watch our monitor show DS close rate. I'm still open to hearing how others have dealt with this situation. Please keep the ideas coming.
Thanks,
Roy
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU * [...]49_0938269102236364.WA.royrberkeley.edu@www.idug.org
1783 42 33_FIFO vs LRU for DSNDB07 sortworks13_Case, Missy J24_Missy.Case@FIRSTDATA.COM31_Tue, 22 Jun 2010 14:21:35 -0500379_utf-8 Long ago, in a faraway place...sorry, it's only Tuesday.
We were looking at our bufferpools and thresholds. The question came up about using FIFO for our sortwork pools. I used to have them set to FIFO, but changed it back to LRU. The trouble is I can't recall why I changed it back. I'm quite sure it was based on something I read somewhere, most likely here. [...]64_3C8EB6E657433F439F22BB22A069206802663FF5@WFDROMPXMASRV03.1DC.COM
1826 53 24_Re: DBM1 slower shutdown12_Isaac Yassin20_yassini@BEZEQINT.NET31_Tue, 22 Jun 2010 22:26:25 +0300652_utf-8 As strange as it sounds DDCON=NO is faster than YES
Watch the open datasets.
What's your PCLOSEN / PCLOSET ?
How many oped DS do you allow ?
Isaac Yassin
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Reynolds
Sent: Tuesday, June 22, 2010 10:11 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DBM1 slower shutdown
Thanks for the quick replies. Our DDCONS=NO for SMF so, at the very least, I'll follow Dan's advice next time to watch our monitor show DS close rate. I'm still open to hearing how others have dealt with this situation. Please keep the ideas [...]35_000601cb1240$cdfda470$69f8ed50$@net
1880 69 24_Re: DBM1 slower shutdown16_Daniel Luksetich18_danl@DB2EXPERT.COM31_Tue, 22 Jun 2010 15:16:11 -0500502_utf-8 If the monitor is not up I think the DBM1 makes a JES system message on deallocation, IGD104I. You can watch for those pop up during the slow shutdown time.
Then, if confirmed what to do about it. You could reduce the number of datasets, reduce the number of open datasets, give DB2 its own usercat if it doesn't already have one of its own, make more members and use system affinities to split work and thus open fewer datasets per member, buy faster machines, or just live with it. [...]35_026001cb1247$c2ead920$48c08b60$@com
1950 29 24_Re: DBM1 slower shutdown11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Tue, 22 Jun 2010 20:18:38 +0000687_- >As strange as it sounds DDCON=NO is faster than YES
It's DDCONS, and it isn't strange.
YES does some manipulation.
NO just dumps with no processing.
It's been a known performance hit for decades.
-
Too busy driving to stop for gas!
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]105_1111395442-1277237913-cardhu_decombobulator_blackberry.rim.net-1736365711-@bda026.bisx.prod.on.blackberry
1980 22 24_Re: DBM1 slower shutdown12_Roy Reynolds17_royr@BERKELEY.EDU31_Tue, 22 Jun 2010 16:38:03 -0400557_UTF-8 PCLOSEN=5 PCLOSET=10
Roy
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L49_0694683549627148.WA.royrberkeley.edu@www.idug.org
2003 74 37_Re: FIFO vs LRU for DSNDB07 sortworks13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Tue, 22 Jun 2010 17:34:26 -0500351_UTF-8 Not sure what was discussed here... however....
Is the data access in the pool all sequential? Or do you still have
some random access for the pool? If you have a random access, even if
it's low, FIFO may have a negative affect because you could lose pages
that are being used...
This is just my opinion of course... [...]30_4C213A72.2030205@attglobal.net
2078 163 63_Re: Checking for use of reserved words in existing applications10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Tue, 22 Jun 2010 20:06:14 -0400564_UTF-8 Thanks for your replies, Peter and Todd.
Peter, your query is only to check catalog object names against the reserved words list. What about the usage of these reserved words within the application code itself ? (like, use of a reserved word for a Cursor name).
Todd, I will see if I can do the re-precompile for all the pgms. I suppose I would just have to specify the new v9 library in my STEPLIB while I do the precompile, so as to be identify impacts before actually upgrading. (Rebinding is an option, but that can be done only after [...]54_7846348223446554.WA.teldb2kalstelstra.com@www.idug.org
2242 44 42_DB2 10 Temporal Tables Improve Performance11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 22 Jun 2010 20:20:31 -04000_51_1421374774810548.WA.davedavebeulke.com@www.idug.org
2287 34 30_trace multiple read to a table12_Jeremy Huang23_huangjh@SDC.ICBC.COM.CN31_Wed, 23 Jun 2010 10:57:19 +0800337_US-ASCII Dear listers,
My purpose is to track the CICS transactions to see if a transaction would
read a table more than once.
I altered the tables to AUDIT ALL and start audit trace class(5) but it
only records the first read access within an UR. And as we know SELECT
statement wouldn't write a log record. [...]69_OF023125DD.33EA2170-ON4825774B.000EF9F0-4825774B.00103C76@icbc.com.cn
2322 60 37_SV: FIFO vs LRU for DSNDB07 sortworks13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Wed, 23 Jun 2010 07:02:21 +0200658_utf-8 As long as you have 100% hits in the sort bufferpool it should be positive to use FIFO.
Just another opinion.....
Olle
-----Ursprungligt meddelande-----
Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För Case, Missy J
Skickat: den 22 juni 2010 21:22
Till: DB2-L@IDUGDB2-L.ORG
Ämne: [DB2-L] FIFO vs LRU for DSNDB07 sortworks
Long ago, in a faraway place...sorry, it's only Tuesday.
We were looking at our bufferpools and thresholds. The question came up about using FIFO for our sortwork pools. I used to have them set to FIFO, but changed it back to LRU. The trouble is I can't recall why I changed it [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A998E5@FSPAS01EV011.fspa.myntet.se
2383 274 47_SV: [DB2-L] DB2 9 z/OS CM...+ 100% DB2 CPU time13_Olle Brostrom25_olle.brostrom@SWEDBANK.SE31_Wed, 23 Jun 2010 07:15:46 +0200427_iso-8859-1 Have you done REBIND of your packages?
Olle
Från: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] För nguyen duc tuan
Skickat: den 22 juni 2010 17:55
Till: DB2-L@IDUGDB2-L.ORG
Ämne: [DB2-L] DB2 9 z/OS CM...+ 100% DB2 CPU time
Dear all ,
Just migrated to V9CM and did some performance test between V8 NFM and V9 CM and on our test application , we get the double in DB2 CPU time for V9. [...]70_B0000573F0F67C438DC58043C06F4CAB49D3A998E6@FSPAS01EV011.fspa.myntet.se
2658 27 11_HIGH VDWQT?13_fruitgumPhill20_fruitgum@HOTMAIL.COM31_Wed, 23 Jun 2010 02:29:22 -0400333_UTF-8 I'm being told that setting VDWQT high on large 1GB+ bufferpools will be good for a system were pagesets have numerous DML statements against them. I'm thinking this might not be true as when DB2 externalizes the dirty pages any Re-reference to a page will need to wait for all the change pages to be written to disk? TIA [...]52_8480239433201606.WA.fruitgumhotmail.com@www.idug.org
2686 252 39_Re: DB2 9 z/OS CM...+ 100% DB2 CPU time15_nguyen duc tuan17_ndt.db2@GMAIL.COM31_Wed, 23 Jun 2010 08:46:20 +0200609_ISO-8859-1 One month separates the 2 tests.
The access path is the same , rebind has been done with V9
On Tue, Jun 22, 2010 at 6:05 PM, Daniel Luksetich wrote:
> What are the getpage counts between the two tests?
>
>
>
> *From:* IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] *On Behalf Of *nguyen duc
> tuan
> *Sent:* Tuesday, June 22, 2010 10:55 AM
> *To:* DB2-L@IDUGDB2-L.ORG
> *Subject:* [SPAM] DB2 9 z/OS CM...+ 100% DB2 CPU time
>
>
>
> Dear all ,
> Just migrated to V9CM and did some performance test between V8 NFM and V9
> [...]60_AANLkTilUANGCHSpQ0rELdOmIHCi0JXHxpctoG2NsTRUd@mail.gmail.com
2939 126 15_Re: HIGH VDWQT?0_24_hhuang@DCCSH.ICBC.COM.CN31_Wed, 23 Jun 2010 15:58:30 +0800411_GB2312 Phill£¬
I think there must be some misunderstandings.
The VDWQT parameter consists of two parts, like VDWQT(integer1,interger2).
For a large BP, which intger1=0 is too small and interger2 is too large,
you can specify integer2 with an absolute number of buffer pages, to
control
your BP with better granularity. The interger 2 ranges from 0 to 9999,
which
could be HIGH. [...]69_OF4B722896.82615C78-ON4825774B.002B3A92-4825774B.002BCEED@icbc.com.cn
3066 209 71_AW: [DB2-L] Checking for use of reserved words in existing applications35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Wed, 23 Jun 2010 12:30:06 +0200502_iso-8859-1 Hi Todd
Since I am working with DB2 for a long, long time, there were a lot of migrations to new versions and I was never reported problems from our application programmers that they might use reserved words. So I don't know, what kind of situation must exist, that a reserved word is refused by DB2. May be because we are a German shop, that it is likely, not to used reserved words, but there are a lot of reserved words, which also appear in the German language (e.g. "name") [...]43_DB2-L%201006230630167224.19B5@IDUGDB2-L.ORG
3276 252 75_Re: AW: [DB2-L] Checking for use of reserved words in existing applications12_Travis, John25_john.travis@CAPGEMINI.COM31_Wed, 23 Jun 2010 12:05:30 +0100390_iso-8859-1 Walter,
Reserved words can come and bite you at any time. Our client being in the steel industry used the column name CAST extensively. Then DB2 V8 introduced the CAST() function and suddenly all the programs trying to access CAST columns failed. That delayed the V8 migration by 6 months whilst CAST was changed to "CAST" and the code tested and promoted to production. [...]71_B5F7E128A29C7B4CAF780CD2A648BF4B0D64D8C8@MISTOLXVS31.corp.capgemini.com
3529 140 63_Re: Checking for use of reserved words in existing applications13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Wed, 23 Jun 2010 07:22:36 -0400311_utf-8 Maybe I'll be accused of shirking my responsibilities but ....
Back when I was a system DBA, I didn't care AT ALL whether a new version of DB2 could cause application problems with reserved words in source code. If there were any problems, they'd get fixed next time anyone touched the source [...]60_4440F5DA00E3F3459BBCB97431B91B6612B84D2D2B@MAILR004.mail.lan
3670 513 63_Re: Checking for use of reserved words in existing applications11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 23 Jun 2010 13:30:19 +0200370_ISO-8859-1 I know that SYSDATE has caused problems...so doing a column hunt for
reserved words is not such a waste of time...It's just building the
correct list of "new" reserved words that is a PITA!
I hope you all have your fingers crossed for the big match today - England
v Slovenia of course not that other 2nd Division game with Ghana..... ;) [...]64_OF8C55540B.5E7E945F-ONC125774B.003EE4B6-C125774B.003F3330@seg.de
4184 343 75_Re: AW: [DB2-L] Checking for use of reserved words in existing applications10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Wed, 23 Jun 2010 08:23:33 -0400308_UTF-8 Hi John,
When did you actually notice the problem ? Was it while trying to run the program after the migration, or did you proactively check code for usage of v8 reserved words ? (from your description, it sounds like you migrated and then got the errors). Did you not get a rebind error ? [...]54_6615343512279686.WA.teldb2kalstelstra.com@www.idug.org
4528 250 75_Re: AW: [DB2-L] Checking for use of reserved words in existing applications12_Travis, John25_john.travis@CAPGEMINI.COM31_Wed, 23 Jun 2010 13:34:00 +0100346_utf-8 Kals,
A brand new V8 subsystem was built on a test LPAR then using a rexx script all the existing packages in the DEV lpar were bind copy into the test V8 system and any bind failures analyzed. That was when the sheer scale of the issue occurred. Don't forget it affects not just code in programs but also view definitions etc. [...]71_B5F7E128A29C7B4CAF780CD2A648BF4B0D64D956@MISTOLXVS31.corp.capgemini.com
4779 71 15_Re: HIGH VDWQT?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 23 Jun 2010 22:34:59 +1000495_ISO-8859-1 DB2 will be able to read the dirty page without waiting for it to be written to
disk.
The real problem is the flood of dirty pages that will occur when pages are
written. Even if VDWQT isn't triggered, dirty pages are written after two
checkpoints - so lots of datasets might have some number of pages to be
written at checkpoints. In a multi GB bufferpool with relatively infrequent
checkpoints you could easily have many thousands of pages to be written. [...]48_4C228C13.3636.3E142A68@jacampbell.acslink.net.au
4851 131 81_Re: Is there a way to define a function that only certain parameters can be null?14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Wed, 23 Jun 2010 22:34:59 +1000330_ISO-8859-1 My reading of the OP's question is: how do you get the same program to
process the function irrespective of whether you call it using the 2 parameter
or the 3 parameter version. So the "correct version" of the program is the
same in both cases.
It's not a DDL question but a program code question. [...]49_4C228C13.30176.3E14295F@jacampbell.acslink.net.au
4983 91 69_Re: Auto Reply: DB2-L Digest - 19 Jun 2010 to 22 Jun 2010 (#2010-168)17_Dell'Anno, Aurora22_Aurora.Dellanno@CA.COM31_Wed, 23 Jun 2010 14:19:31 +0100537_us-ascii Oh goodness,
The enemy is in our camp ;-)
Thanks.
Aurora
Aurora Emanuela Dell'Anno
CA Technologies - MSC
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
Aurora.Dellanno@ca.com
CA Limited
Ditton Park, Riding Court Road, Datchet, SL3 9LL, UK
CA Limited is a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179. [...]56_3D02E8610514C04F991CF832BA154C6610567843@UKSLMS11.ca.com
5075 105 24_Re: DBM1 slower shutdown14_Heckman, Carol23_carol_heckman@MEDCO.COM31_Wed, 23 Jun 2010 09:34:25 -0400393_US-ASCII Roy,
Dataset closes is the most likely culprit. However, if you are data
sharing and it is the last member to come down, it has to do castout for
everything. You can see castouts in the subsystems stats. If that number
is significantly higher for the last time period, you should look into
that.
Carol Heckman
Medco DB2 Systems Group
201 703 7365 [...]64_BDCF28CC3A7F9F4AB5E2BB2F46EC4D930B565838@fr1-mx-usr03p.medco.com
5181 47 34_Re: trace multiple read to a table10_Todd Burch17_toddburch@MAC.COM31_Wed, 23 Jun 2010 09:00:42 -0500520_US-ASCII You could also activate the I/O IFCIDS (6, 7, 8, 9, 10), but if you do
so, filter the trace narrowly.
Todd
On Jun 22, 2010, at 9:57 PM, Jeremy Huang wrote:
Dear listers,
My purpose is to track the CICS transactions to see if a transaction
would
read a table more than once.
I altered the tables to AUDIT ALL and start audit trace class(5) but it
only records the first read access within an UR. And as we know SELECT
statement wouldn't write a log record. [...]44_64E4D83C-D2A9-4337-8B3E-82E2B0C545D6@mac.com
5229 437 15_Re: HIGH VDWQT?35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 23 Jun 2010 10:16:46 -0400605_gb2312 First look at your avg pages/write.
When this a low single digit value like 2.4 (or whatever), then usually setting vdwqt=(0,x) is appropriate to avoid flooding the system with writes when DB2 takes
a checkpoint. Depending upon your DB2 version and z/OS version, the usual reason for having a low pages/write value relates to the system not writing
non-contiguous pages that span more than a cylinder. We know that this approach is often obsolete with current disk storage technology, but may be in place for your system.
It still appears to be for every set of system data I have [...]46_95216B3796B04E6AB5212CC0C2BF02CC@DellNotebook3
5667 27 15_Virtual Classes12_Bob Jeandron21_bob.jeandron@USDA.GOV31_Wed, 23 Jun 2010 09:33:36 -0400773_UTF-8 Anyone take an DB2 virtual class? Thinking about taking one instead of traveling across the country.
Were you bored? Made sleepy? Lost interest? Tech problems?
Thanks.
_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________ [...]53_0019406766535387.WA.bob.jeandronusda.gov@www.idug.org
5695 143 69_Re: Auto Reply: DB2-L Digest - 19 Jun 2010 to 22 Jun 2010 (#2010-168)10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 23 Jun 2010 10:25:54 -0400450_us-ascii Can't blame them for wanting to learn how a real DBMS works. -:)
Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Dell'Anno,
Aurora
Sent: Wednesday, June 23, 2010 9:20 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Auto Reply: DB2-L Digest - 19 Jun 2010 to 22 Jun
2010 (#2010-168)
Oh goodness, [...]62_1A700EEF49343148A08879B1E3CA5BA615570B1A@NSTMC101PEX1.ubsw.net
5839 98 37_Re: FIFO vs LRU for DSNDB07 sortworks35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 23 Jun 2010 10:28:54 -0400584_UTF-8 Missy,
I would not use FIFO for a sort pool.
Sort object pages are handled quite differently in this pool, compared to
normal objects.
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
www.responsivesystems.com
Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1 [...]46_3DEBDAF78008436B99306A0229C89FD5@DellNotebook3
5938 62 15_Re: HIGH VDWQT?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 23 Jun 2010 10:29:15 -0400460_us-ascii You did get that externalizing a dirty page is "copying it to DASD", *not* "copying it to DASD and erasing it from the buffer", right? The only time you lose the ability to reference a page in the buffer is when _some other_ page has come along and overwritten it, or when it's been locked/updated by another process which has not yet committed. (In the latter case, if you are using the Uncommitted Read isolation option, it's still available.) [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46A59@MAIL02.fisalan.nycnet
6001 101 34_Re: trace multiple read to a table35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 23 Jun 2010 12:24:05 -0400381_iso-8859-1 It depends upon what "read" means... accessing the object, or accessing
specific pages of the object.
If you need pages, then you don't care about IOs, you need getpages and
that's a 198 record, may be huge volume..
For IOs, you only need the 6,7 records as these are the read, and probably
don't need the 7 as that's the read completion record. [...]46_A2CDFD2A1E034C6386D22F5E1E11B826@DellNotebook3
6103 87 19_Re: Virtual Classes10_Roger Hecq18_Roger.Hecq@UBS.COM31_Wed, 23 Jun 2010 13:47:34 -0400566_us-ascii I'll bite.
I have taken several web classes and I have been happy with all of them.
I prefer an actual class, if I can take it locally. Otherwise, a web
class is better than being out-of-town. It is a little harder to
maintain focus at times, but you also have the freedom to get up & walk
around your desk, etc., without disturbing your classmates. I have not
experience any technical problems that I would not have had with an
onsite class. For me, the biggest loss is the lack of interaction with
the other participants. [...]62_1A700EEF49343148A08879B1E3CA5BA615570C45@NSTMC101PEX1.ubsw.net
6191 42 20_Number of Partitions4_Anil21_alisha_kale@YAHOO.COM31_Wed, 23 Jun 2010 13:48:43 -0400622_UTF-8 Hi All,
Although my question is in the context of DB2 for z/os, it may really be a question across all platforms (and rdbms).
Typically, you would choose to partition a table for
maintenance, and/or
performance, and/or
availability
However, the art and science is when you want to decide how many partitions. There are times where the application processing dictates the number of partitions. But what if that is not the case. How do you decide on the number of partitions while designing a partitioned table.
I would appreciate if my fellow DBAs across the world can share some of [...]52_6794949247605969.WA.alishakaleyahoo.com@www.idug.org
6234 51 24_Re: Number of Partitions10_Todd Burch17_toddburch@MAC.COM31_Wed, 23 Jun 2010 13:38:16 -0500656_US-ASCII The worst reason I have heard is retirement. (In other words, the
tablespace will max out after the designer retires).
Todd
On Jun 23, 2010, at 12:48 PM, Anil wrote:
Hi All,
Although my question is in the context of DB2 for z/os, it may really
be a question across all platforms (and rdbms).
Typically, you would choose to partition a table for
maintenance, and/or
performance, and/or
availability
However, the art and science is when you want to decide how many
partitions. There are times where the application processing dictates
the number of partitions. But what if that is not [...]44_1E818595-ECAE-43B8-9C6F-FD816EF9883E@mac.com
6286 33 34_Question on Compression dictionary13_Sekhar Mekala23_SEKHAR.MEKALA@GMAIL.COM31_Wed, 23 Jun 2010 15:38:30 -0400312_UTF-8 Hi all,
If I load a table space with SYSREC DUMMY, do I have to create a new compression dictionary? My table space is initially having compression dictionary and I am trying to LOAD the table space with SYSREC DUMMY to delete the Table space data in one shot, instead of issuing DELETE Statement. [...]55_4855651639487390.WA.SEKHAR.MEKALAGMAIL.COM@www.idug.org
6320 199 24_Re: Number of Partitions12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Wed, 23 Jun 2010 21:47:58 +0200533_Windows-1252 Hi Anil,
I don't think there's a general rule of thumb for the number of partitions or the best approach. There's a variety of reasons to go for partitioning and they lead to different conclusions.
My understanding of the terms maintenance and availability in this context is about the same. The improved availability basically comes from quicker maintenance on many, individually maintainable partitions. Leading to the slightly cursory conclusion "the more partitions, the higher the availability". [...]43_SNT107-W215CA8A18C919554F5BA70F1C50@phx.gbl
6520 202 24_Re: Number of Partitions9_Ford Wong14_fordie@SHAW.CA31_Wed, 23 Jun 2010 14:27:35 -0600309_us-ascii In a nutshell, the number of partitions depends on how much data you want to keep in each partition without running out of space and how long you want Utiloties to take against them.
Indirectly, each partition is sized for processing so that utility jobs donot take more than 2 hours to run. [...]30_bc26f7046b580.4c2219d7@shaw.ca
6723 56 38_Re: Question on Compression dictionary15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Wed, 23 Jun 2010 15:47:57 -0500534_utf-8 I started to write a simple answer, but with DB2 it is never simple.
I have to assume you are doing a LOAD REPLACE. If so and you want to keep the existing dictionary you need to code KEEPDICTIONARY YES.
If you don't, LOAD will wipe out the dictionary and when you really load data none of the rows will be compressed, unless you do a LOAD REPLACE. In which case why didn't you just do a LOAD REPLACE with the data in the first place? I am guessing you have a table that the application inserts data into and [...]69_476996CBBE9AF14285E09E63C370072A13FB18F7B3@PHXCCRPRD01.adprod.bmc.com
6780 65 38_Re: Question on Compression dictionary14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Wed, 23 Jun 2010 16:54:12 -0400384_us-ascii Sekhar,
If you wish, you may retain the existing dictionary with KEEPDICTIONARY in a LOAD with REPLACE, per the manual. If you don't need the dictionary for future LOADs (i.e. you intend to build a new one the next time you run a LOAD with data), you may omit this parameter; a LOAD with no KEEPDICTIONARY and no data will omit building a compression dictionary. [...]64_1BA00CBB414DA34AA25ED82F4647CDE2033BC46A5F@MAIL02.fisalan.nycnet
6846 375 24_Re: Number of Partitions35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 23 Jun 2010 17:05:17 -0400343_Windows-1252 Great analysis of approaches.
One other thing to consider, if or when your access becomes very random across large objects, you may encounter death by random IO.
Even with current technology, and getting 1 Ms access for synch IOs, cause a couple of hundred thousand synch IOs
and you have a performance problem. [...]46_5805E5A692FA4627AFBDC2052F92B179@DellNotebook3
7222 30 38_Re: Question on Compression dictionary13_Sekhar Mekala23_sekhar.mekala@GMAIL.COM31_Wed, 23 Jun 2010 17:51:38 -0400464_UTF-8 Thanks Steve and Chris. Yes, KEEPDICTIONARY option can be used to avoid the building of the data dictionary, I forgot about this option :) .
BTW, is there any way to check whether a compression dictionary is currently existing by querying the catalog tables?
Thanks
Sekhar
_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU * [...]55_2830140782600243.WA.sekhar.mekalagmail.com@www.idug.org
7253 76 81_Re: Is there a way to define a function that only certain parameters can be null?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Thu, 24 Jun 2010 01:08:39 +0300421_US-ASCII On Tue, 22 Jun 2010 10:27:29 -0500 Todd Burch wrote:
:>James is correct - this aspect of the User Defined Function parameter
:>list definition is a pain. The fixed stuff should be prior to the
:>varying stuff, or, there should be a count of varying items so you can
:>easily get to the fixed stuff after the varying items. Doesn't
:>really make overloading very easy. [...]42_kc15265irvidiboifmjghdvql9s2vkutnm@4ax.com
7330 51 20_SPUFI, REXX and CALL15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Thu, 24 Jun 2010 01:13:44 +0300363_ISO-8859-1 I wanted to test a stored procedure call in SPUFI but got a -84.
Strangely enough, I can do the CALL via DSNREXX.
The manual states for CALL:
"This statement can also be dynamically prepared, but only from an ODBC or CLI
driver that supports dynamic CALL statements. IBM’s ODBC and CLI drivers
provide this capability." [...]42_8h1526lj4jvrp1i38ehttbvlh0bd7baitn@4ax.com
7382 37 38_Re: Question on Compression dictionary15_Blaicher, Chris22_Chris_Blaicher@BMC.COM31_Wed, 23 Jun 2010 17:32:52 -0500512_utf-8 Not to tell if one is built, just to see if one can be built. The only way to tell is to read the header page (page 0), and I don't think the regular user wants to try and do that in a program.
Chris Blaicher
Phone: 512-340-6154
Mobile: 512-627-3803
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sekhar Mekala
Sent: Wednesday, June 23, 2010 4:52 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Question on Compression dictionary [...]69_476996CBBE9AF14285E09E63C370072A13FB18F9CA@PHXCCRPRD01.adprod.bmc.com
7420 151 38_Re: Question on Compression dictionary14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 24 Jun 2010 09:04:41 +0930438_utf-8 Although one could easily run DSN1PRNT with PRINT(0,0),FORMAT and
check the contents of HPGZLD and HPGZ3PNO/HPGZ4PNO.
James Campbell
On Thu 24/06/10 08:02 , "Blaicher, Chris" Chris_Blaicher@BMC.COM
sent:
Not to tell if one is built, just to see if one can be built. The
only way to tell is to read the header page (page 0), and I don't
think the regular user wants to try and do that in a program. [...]32_4308.1277336081@internode.on.net
7572 25 15_Re: HIGH VDWQT?14_fruitgum Phill20_fruitgum@HOTMAIL.COM31_Wed, 23 Jun 2010 20:12:07 -0400745_UTF-8 Cheers this is what I was looking for, I had thought I read that we would see other wait time if a thread wanted a page that was scheduled for write.
_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________ [...]52_4339202378604305.WA.fruitgumhotmail.com@www.idug.org
7598 151 23_Re: Question on LISTCAT16_Robert Catterall21_rfcatterall@GMAIL.COM31_Thu, 24 Jun 2010 01:11:27 -0400613_ISO-8859-1 Very late addition to this thread: besides the REPORTONLY option, it seems
to me that you could accomplish the goal by running RUNSTATS for the objects
of interest (tablespaces and associated tables and indexes) with a
specification of UPDATE NONE and HISTORY ALL. That would not affect the
stats used for access path selection (this because of UPDATE NONE), but it
would populate tables such as SYSINDEXPART_HIST with values such as
FAROFFPOSF, and you could use that (for a table's clustering index) to
determine whether or not the related tablespace should be reorganized. [...]60_AANLkTilUZ_JZqp4f0tKo2I-J0X6hSdXEYyPYlTWEhQvI@mail.gmail.com
7750 306 23_Re: Question on LISTCAT11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 24 Jun 2010 07:53:11 +0200736_ISO-8859-1 but then your dynamic SQL Cache is killed....dont forget RUNSTATS is a
natural born DSC killer!
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
Robert Catterall
Gesendet von: IDUG DB2-L
24.06.2010 07:11
Bitte antworten an
IDUG DB2-L
An
DB2-L@IDUGDB2-L.ORG
Kopie
Thema
Re: [DB2-L] Question on LISTCAT [...]64_OF204F3D69.17A37F60-ONC125774C.00203C37-C125774C.00205605@seg.de
8057 51 24_Re: SPUFI, REXX and CALL14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Thu, 24 Jun 2010 21:28:15 +1000604_ISO-8859-1 A static call. See SDSNDBRM(DSNREXX).
James Campbell
On 24 Jun 2010 at 1:13, Binyamin Dissen wrote:
> I wanted to test a stored procedure call in SPUFI but got a -84.
>
> Strangely enough, I can do the CALL via DSNREXX.
>
> The manual states for CALL:
>
> "This statement can also be dynamically prepared, but only from an ODBC or CLI
> driver that supports dynamic CALL statements. IBM´s ODBC and CLI drivers
> provide this capability."
>
> Obviously DSNREXX also has this capability. What does it use if not PREPARE?
>
> --
> [...]49_4C23CDEF.12550.42FD6DC8@jacampbell.acslink.net.au
8109 62 38_Re: Question on Compression dictionary10_Joe Geller21_joerg6666@HOTMAIL.COM31_Thu, 24 Jun 2010 09:06:37 -0400385_UTF-8 The simplest way is to check the catalog to see the % saved by compression. If there is no dictionary, then there will be no compression.
Joe
Although one could easily run DSN1PRNT with PRINT(0,0),FORMAT and check the contents of HPGZLD and HPGZ3PNO/HPGZ4PNO.
James Campbell
On Thu 24/06/10 08:02 , "Blaicher, Chris" Chris_Blaicher@BMC.COM sent: [...]53_6981084052869663.WA.joerg6666hotmail.com@www.idug.org
8172 34 38_Re: Question on Compression dictionary10_Joe Geller21_joerg6666@HOTMAIL.COM31_Thu, 24 Jun 2010 10:12:58 -0400389_UTF-8 Of course, this is only accurate as of when Runstats was last run. If you do a load replace with
an empty input dataset and do not have Statistics and do not have Keep Dictionary, the catalog
will not match reality.
Joe
The simplest way is to check the catalog to see the % saved by compression. If there is no dictionary, then there will be no compression. [...]53_2511730084386695.WA.joerg6666hotmail.com@www.idug.org
8207 173 63_Re: Checking for use of reserved words in existing applications10_Todd Burch17_toddburch@MAC.COM31_Thu, 24 Jun 2010 09:35:38 -0500713_US-ASCII Another aspect of the V9 SQL changes, beyond new reserved words, are
some holes in SQL that V9 closed. For instance, see this link - we
see these a lot in support.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.inst/db2z_relincompatapplsql.htm
See the headings for:
DB2 enforces restrictions about where an INTO clause can be specified
Enhanced data type checking for zero-length characters
And if you will be converting V8 SQL procedures to V9 Native SQL
Procedures, ... there are a ton of differences that are not apparent
that have to be changed. It's not just a matter of dropping the WLM
and other keywords that cause it [...]44_D4BDF882-0317-413B-BFEC-027F77B41C1B@mac.com
8381 54 79_MODIFY RECOVERY with DSNUM option - Not able to delete obsolete SYSCOPY records8_Sushanth24_slloyds@NATIONALLIFE.COM31_Thu, 24 Jun 2010 10:34:19 -0400477_- Hi,
I am encountering an issue with MODIFY RECOVERY utiilty when run on partitioned tablespaces, iam getting the following message,
DSNU050I DSNUGUTC - MODIFY RECOVERY TABLESPACE WARDDATP.WARSLSWC DSNUM 1 DELETE AGE(95)
DSNU577I # DSNUMDEL - MODIFY HAS NOT DELETED ALL SYSCOPY RECORDS
AS REQUESTED.
DSNU573I # DSNUMDEL - NO BACKUP COPIES FOUND FOR THIS TABLESPACE
OR DATASET
DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4 [...]56_6372830939922895.WA.slloydsnationallife.com@www.idug.org
8436 102 83_Re: MODIFY RECOVERY with DSNUM option - Not able to delete obsolete SYSCOPY records11_Enrico Haak21_eh@INSOFT-SOFTWARE.DE31_Thu, 24 Jun 2010 17:01:09 +0200656_ISO-8859-15 Hi Sushanth,
it depends on the way you run the image copies.
Maybe there exists an old image copy for the tablespace.
Then you have to run once with DSNUM ALL.
Please have a look at the Utility Guide:
V8 p.303
V9 p.322
Enrico
Am 24.06.2010 16:34, schrieb Sushanth:
> Hi,
>
> I am encountering an issue with MODIFY RECOVERY utiilty when run on partitioned tablespaces, iam getting the following message,
> DSNU050I DSNUGUTC - MODIFY RECOVERY TABLESPACE WARDDATP.WARSLSWC DSNUM 1 DELETE AGE(95)
> DSNU577I # DSNUMDEL - MODIFY HAS NOT DELETED ALL SYSCOPY RECORDS
> AS REQUESTED.
> [...]35_4C237335.9010402@insoft-software.de
8539 95 83_Re: MODIFY RECOVERY with DSNUM option - Not able to delete obsolete SYSCOPY records13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Thu, 24 Jun 2010 11:38:01 -0400469_iso-8859-1 Do you copy ALL partitions every time?
It seems to be saying that the MOST RECENT recoverable point FOR THE WHOLE TABLE SPACE is earlier than your -95 days
I think
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 - EMEA, the premiere event for DB2 professionals.
8-12 November 2010, Vienna
Learn more at http://www.idug.org [...]60_4440F5DA00E3F3459BBCB97431B91B6612B8E811DD@MAILR004.mail.lan
8635 30 83_Re: MODIFY RECOVERY with DSNUM option - Not able to delete obsolete SYSCOPY records8_Sushanth24_slloyds@NATIONALLIFE.COM31_Thu, 24 Jun 2010 11:53:40 -0400751_- Thank You Very Much Enrico,
Somewhere, in the beginning we had taken IC's at the tablespace level and later we have changed it to the partition level.
Thank you again,
Sushanth
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]56_5259923017188567.WA.slloydsnationallife.com@www.idug.org
8666 36 28_Page Latch Contention in DB214_Vidya Attuluri27_vidya.attuluri@MARRIOTT.COM31_Thu, 24 Jun 2010 12:02:22 -0400404_UTF-8 Hi,
DB2 V9 CM
we are doing some performance tests, where 40 distributed threads are calling the Cobol Stored Procedures and the only insert statement in the Stored Procedure is insert into a Created Temporary table. we see 30% of total elapsed time in DB2 as Page Latch Contention wait.
Surprised to see for temp tables also, is there anyway we can avoid or reduce this.. [...]59_8779165538897346.WA.vidya.attulurimarriott.com@www.idug.org
8703 24 83_Re: MODIFY RECOVERY with DSNUM option - Not able to delete obsolete SYSCOPY records8_Sushanth24_slloyds@NATIONALLIFE.COM31_Thu, 24 Jun 2010 11:56:43 -0400603_- Yes Phil, we take IC's of all the partitions.
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________ [...]56_1041106032373441.WA.slloydsnationallife.com@www.idug.org
8728 70 24_Re: SPUFI, REXX and CALL15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Thu, 24 Jun 2010 19:45:39 +0300428_ISO-8859-1 So does PREPARE build the SQLDA with the first thing being the function name?
Does REXX parse out the statement?
So that it can eventually do a bound statement
CALL :proc USING :sqlda
?
On Thu, 24 Jun 2010 21:28:15 +1000 James Campbell
wrote:
:>A static call. See SDSNDBRM(DSNREXX).
:>On 24 Jun 2010 at 1:13, Binyamin Dissen wrote: [...]42_3o2726p56nrhoubg6ea1j5b2k96457uuui@4ax.com
8799 62 19_Re: Virtual Classes10_Mir, Sally15_SMir@BBANDT.COM31_Thu, 24 Jun 2010 12:45:33 -0400431_us-ascii I recently took a virtual class, one that was pre-recorded. I have to
say that I had trouble paying attention to it. But it sure was funny
when the instructor started talking to the recording engineer, directing
him to "back up and erase that; I want to do this part over." I think
even the recording engineer had fallen asleep because there was a lot of
that kind of thing near the end of the course! [...]62_994039AECC75DF4CAFE407FA0F32CDD00E1BB483@wil-exmb01.bbtnet.com
8862 78 32_Re: Page Latch Contention in DB215_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Thu, 24 Jun 2010 13:46:00 -0400464_us-ascii Hi,
The buffer pool settings for the SYSPKAGE T/S are :
Deferred Write = 50
Vertical Deferred Write = 10, 0
And from the Buffer Pool stats, I see that Vertical DWT HIT = 1000
So, may be the updated pages are getting written to DASD quickly, thus
causing Page Latch contentions..
The TS SYSPKAGE, we have TRACKMOD = YES, could this also be potential
candidate for Page Latch Contentions on Space map pages? [...]81_3E90A949C5684E46AEDAB76BD03B818307F57EF1@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
8941 124 19_Re: Virtual Classes14_Larry Kintisch17_LKint@VERIZON.NET31_Thu, 24 Jun 2010 14:10:07 -0400608_us-ascii Hi List,
I've never taken a prerecorded DB2 class.
As some of you know I teach for IBM [as a contractor] and its
business partners [Jack Morton and M/UX in the western US]. I've
taught live for 19 years and using M/UX Live Virtual 2-way video
conferencing for about 5 years which I enjoyed teaching [there were a
few network glitches over the years but mostly the live 2-way video
and audio worked well for me and had few complaints from students in
remote classrooms]. M/UX labs use the IBM virtualization systems so
Z/OS labs connect to IBM's French datacenter [...]40_0L4J007Z76ILCC90@vms173007.mailsrvcs.net
9066 121 32_Re: Page Latch Contention in DB235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 24 Jun 2010 15:08:14 -0400593_iso-8859-1 Unlikely it has anything to do with vdwqt.
Except for sort pools, setting vdwqt=(0,40) is usually better..... when
pages/write is a single digit.
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
www.responsivesystems.com
Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at:
www.responsivesystems.com/Movie1 [...]46_D469C3BE90DA4B819AA30F4816F99A3A@DellNotebook3
9188 164 32_Re: Page Latch Contention in DB215_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Thu, 24 Jun 2010 15:14:40 -0400616_us-ascii Joel,
Another interesting thing is, we even see page latch contention on a
Select from a Base table (we have a inline view), which is not getting
updated in parallel. So, I am thinking that BP7 is getting written too
quickly.. we have 2500 buffers allocated, with DWQT=50 and VDWQT=10,0.
Any hints?
Regards
Vidya
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Joel
Goldstein - Responsive Systems
Sent: Thursday, June 24, 2010 3:08 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Page Latch Contention in DB2 [...]81_3E90A949C5684E46AEDAB76BD03B818307F57F7E@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
9353 187 32_Re: Page Latch Contention in DB216_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 24 Jun 2010 14:24:01 -0500608_us-ascii What is CPU utilization at during the test?
Dan
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Attuluri, Vidya
Sent: Thursday, June 24, 2010 2:15 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [SPAM] Re: Page Latch Contention in DB2
Joel,
Another interesting thing is, we even see page latch contention on a
Select from a Base table (we have a inline view), which is not getting
updated in parallel. So, I am thinking that BP7 is getting written too
quickly.. we have 2500 buffers allocated, with DWQT=50 and VDWQT=10,0. [...]35_005701cb13d2$cd98bc70$68ca3550$@com
9541 227 32_Re: Page Latch Contention in DB215_Attuluri, Vidya27_Vidya.Attuluri@MARRIOTT.COM31_Thu, 24 Jun 2010 15:44:39 -0400465_us-ascii Dan,
Before we start our test, it is at 20 to 30%. When we start the test (40
distributed threads running in parallel executing the COBOL Stored
Procedures) it jumps to 80 to 100%.
Regards
Vidya
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Daniel
Luksetich
Sent: Thursday, June 24, 2010 3:24 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Page Latch Contention in DB2 [...]81_3E90A949C5684E46AEDAB76BD03B818307F57FAE@HDQNCEXCL1V1.mihdq.marrcorp.marriott.com
9769 253 32_Re: Page Latch Contention in DB216_Daniel Luksetich18_danl@DB2EXPERT.COM31_Thu, 24 Jun 2010 15:18:20 -0500350_us-ascii So then, after 90% you will typically see page latch suspend once the
machine gets to maximum utilization and begins thrashing. I would scale the
test down 50% so the machine is not maxed out and see what % page latch you
get. If it is much lower then you know the page latch is a symptom and not a
cause.
Cheers,
Dan [...]35_006d01cb13da$641f1cf0$2c5d56d0$@com
10023 123 24_Strobe help....PKG ALLOC10_Tom Glaser25_tom_glaser@MASTERCARD.COM31_Thu, 24 Jun 2010 16:37:26 -0400684_UTF-8 Hi,
I'm looking at a strobe report (below) and trying to determine what "DSNXGRDS DSNXEFDA PKG ALLOC" is actuall
telling me:
.SYSTEM SYSTEM SERVICES .DB2 DB2 SYS
MODULE SECTION FUNCTION INTERVAL % CPU TIME
NAME NAME LENGTH SOLO TOTAL
DSNACAF CALL ATTACH FACILITY 90112 .34 .34
DSNALI LANGUAGE INTERFACE 1040 .23 .23
DSNBBM DSNB1DBP DISPLAY BUFPOOL 21472 .03 .03
DSNBBM DSNB1GET RETRIEVE REQUESTED PAGE 23760 2.02 2.02
DSNBBM DSNB1REE PROCESS XES EVENTS 13112 .03 .03
DSNBBM DSNB1REL PAGE RELEASE ROUTINE 10664 1.00 1.00
DSNIDM DSNICINV INVALIDATE DB OBJECTS 14360 .01 .01
DSNIDM DSNICLPM RLS RESRCES HELD BY PSRM 1808 .21 [...]56_9711748132467700.WA.tomglasermastercard.com@www.idug.org
10147 221 32_Re: Page Latch Contention in DB235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 24 Jun 2010 19:47:13 -0400512_iso-8859-1 Vidya,
Is BP7 your sort pool, or user data?
If a sort pool, that's a bit small. Then it depends upon how many objects
you have in this pool.
How many pages/write? Read IO rate/sec?
What processor, how many engines,how much memory on the LPAR, is the system
paging, etc, etc
As Dan wrote, once you hit 90% CPU, usually all throughput is getting
degraded, and at what load point does the problem start?
Perhaps you're trying to put the old 10# into a 5# bag? [...]46_D6E282C7B3FB4A76BFAFC1B4A03D7EF0@DellNotebook3
10369 279 65_How to get the resultset with multiple rows from an SQL PROCEDURE16_Laurens Zwanepol31_lbn.zwanepol@BELASTINGDIENST.NL31_Fri, 25 Jun 2010 08:41:31 +0200454_us-ascii Hello,
I am strugling with an native stored procedure and i could use a little
bit of help
When i want to execute an query which returns more than 1 row you usually
use a cursor en then you fetch all rows.
But how does this work with a native stored procedure.
I manage to do the open cursor and then loop through the fetches till the
end in the SP, but then i only get the last fetch-data returned from the
SP. [...]76_OFE20CB30E.7E7C4AEC-ONC125774D.0023A3B6-C125774D.0024C324@belastingdienst.nl
10649 345 69_Re: How to get the resultset with multiple rows from an SQL PROCEDURE10_Todd Burch17_toddburch@MAC.COM31_Fri, 25 Jun 2010 06:30:52 -0500723_US-ASCII Here's a high-level picture of how this works: (Note steps 6 and 10)
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.apsg/db2z_xmpsimplesp.htm
In your calling program, define a RESULT SET LOCATOR.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/db2z_ref2storedprocresultsets.htm
The last thing you'll do in your SP is an OPEN, then it exits.
You're calling program will do the fetches.
Here's an example:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.apsg/db2z_writeprogreceiveresultsetsp.htm
Todd
On Jun 25, 2010, at 1:41 AM, Laurens Zwanepol wrote: [...]44_11EE6F52-4AE4-4152-8C16-C23B3A21B835@mac.com
10995 71 24_Re: SPUFI, REXX and CALL10_Todd Burch17_toddburch@MAC.COM31_Fri, 25 Jun 2010 06:38:19 -0500388_ISO-8859-1 The procedure name won't be the first entry in the SQLDA. The first
parm passed to the procedure will be the first entry in the SQLDA.
The procedure name will be in the host variable :proc as you coded it.
Todd
On Jun 24, 2010, at 11:45 AM, Binyamin Dissen wrote:
So does PREPARE build the SQLDA with the first thing being the
function name? [...]44_53056F14-BEE6-4F0B-B937-91DCCF269196@mac.com
11067 91 24_Re: SPUFI, REXX and CALL15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Fri, 25 Jun 2010 15:17:59 +0300354_ISO-8859-1 On Fri, 25 Jun 2010 06:38:19 -0500 Todd Burch wrote:
:>The procedure name won't be the first entry in the SQLDA. The first
:>parm passed to the procedure will be the first entry in the SQLDA.
:>The procedure name will be in the host variable :proc as you coded it.
I mean for writing my own SPUFI. [...]42_g97926tpumqu3mdl8j0sgtrnqebg3hdtn2@4ax.com
11159 97 24_Re: SPUFI, REXX and CALL10_Todd Burch17_toddburch@MAC.COM31_Fri, 25 Jun 2010 08:34:33 -0500306_ISO-8859-1 If you want to be able to process the CALL statement in your own
SPUFI, then yes, you will need to parse the CALL statement and put the
proc name into a host variable and parse the parms and build your own
SQLDA. Yes, your assembler(?) version of CALL would be as you coded
it. [...]44_C444C694-7865-4285-8F2E-D822D84B89C7@mac.com
11257 45 29_IDUG Europe becomes IDUG EMEA10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 25 Jun 2010 10:29:52 -0400351_UTF-8 Over the years, attendance at the IDUG Europe conference has expanded to include attendees from more non-European countries.
To better reflect this change, the conference this year has been rebranded as the IDUG EMEA Conference (for those who have not heard of the acronym EMEA before, it stands for Europe, Middle East and Africa). [...]48_4880492818820358.WA.mscarpacesve.it@www.idug.org
11303 25 32_Re: Page Latch Contention in DB213_Mike Lefebvre31_michael_lefebvre@FREDDIEMAC.COM31_Fri, 25 Jun 2010 15:38:43 -0400297_UTF-8 Are you only using 4K pages? If you are using 32K pages, try allocating multiple 32K tablespaces with their own separate 32K bufferpool in DB07, or add multiple TS/BP in the TEMP dababase for 8K or 32K. I had a similar issue in a prior job and I think extra TS in the TEMP DB fixed it. [...]62_7245990471934891.WA.michaellefebvrefreddiemac.com@www.idug.org
11329 124 24_Re: SPUFI, REXX and CALL8_Michal B20_ekspert123@GMAIL.COM31_Fri, 25 Jun 2010 22:24:06 +0200676_ISO-8859-1 How about instead of writing own SPUFI just use CLP on USS (Unix Services z/OS)
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.inst/db2z_useclp.htm
Can be used in script, etc..
Just a thought, another way to test stored proc call quickly
Michal
2010/6/25 Todd Burch :
> If you want to be able to process the CALL statement in your own SPUFI, then
> yes, you will need to parse the CALL statement and put the proc name into a
> host variable and parse the parms and build your own SQLDA. Yes, your
> assembler(?) version of CALL would be as you coded it.
>
> [...]60_AANLkTikxEqd0lDGpXR9Xalv1MKDYGk0SxBaVHgyIEDR6@mail.gmail.com
11454 110 28_Re: Strobe help....PKG ALLOC10_Dave Barry14_dbarry@UPS.COM31_Fri, 25 Jun 2010 16:55:12 -0400494_us-ascii 18 percent of the CPU time, yes.
First thing I would look at is the number of packages listed in the applicable collection. The larger the list, the more time it takes to search.
Dave Barry
Sr. Performance and Capacity Planning Analyst
UPS
-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Tom Glaser
Sent: Thursday, June 24, 2010 4:37 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Strobe help....PKG ALLOC [...]66_CF6886BAE7BCA244B2593F11C2FD1B58017E3CB1CC@njrarsvr3bf7.us.ups.com
11565 54 33_SWARUG Meeting - Phoenix, July 1413_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Fri, 25 Jun 2010 19:14:36 -0700432_ISO-8859-1 All,
SWARUG has been on hiatus for the last couple of years, but we are
trying to restart it.
The next SWARUG meeting will take place on Wednesday, July 14. This is
short notice, but you won't want to miss the meeting, because we were
able to get one of the best speakers in the world to come -- Willie
Favero from IBM -- and he'll be giving 2 presentations relating to DB2
on System z. [...]60_AANLkTilElRwSEoQLn421IIcgYvYFftY8fqCRq-nCVMXd@mail.gmail.com
11620 54 40_Using .Net 2003 to connect to DB2 9 z/OS12_Isaac Yassin22_isaac.yassin@GMAIL.COM31_Sun, 27 Jun 2010 17:26:17 +0300160_ISO-8859-1 Hi
Is there anyone using .Net 2003 to connect to DB2 9 on z/OS ?
I know it's not supported according to the publications :-)
Thanks,60_AANLkTilPhhFbD-nQgifo75hEKdPd_-yQq4N0hr34afCK@mail.gmail.com
11675 46 28_Re: Strobe help....PKG ALLOC13_John Saunders27_john_j_saunders@YAHOO.CO.UK31_Sun, 27 Jun 2010 13:26:58 -0400349_UTF-8 The paste from STROBE shows just percentages - the first thing I'd ask is - 18% of how much?
If the STROBE just covers the first 10 seconds of a batch job that allocates hundreds of packages, then 18% doesn't seem that big.
If the STROBE covers 30 minutes elapsed and 15 minutes CPU time, then 18% is much more significant. [...]57_9495933056063234.WA.johnjsaundersyahoo.co.uk@www.idug.org
11722 102 44_Ed Benoit wants to stay in touch on LinkedIn9_Ed Benoit17_db2tech@YAHOO.COM31_Sun, 27 Jun 2010 22:54:08 -0700936_UTF-8 LinkedIn
------------
I'd like to add you to my professional network on LinkedIn.
- Ed Benoit
Ed Benoit
Owner at Recovery Knowledge
Greater Los Angeles Area
Confirm that you know Ed Benoit
https://www.linkedin.com/e/1ciomw-gayw8lwg-2g/isd/1420463433/oxqVOcJ7/
------
(c) 2010, LinkedIn Corporation
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________ [...]60_408366862.3207142.1277704448226.JavaMail.app@ech3-cdn05.prod
11825 76 31_Tablespace in STOP PENDING Mode13_Prakash Singh30_prakash.singh@SOMERFIELD.CO.UK31_Mon, 28 Jun 2010 09:35:08 -0400761_UTF-8 Hi,
Last night when one of the platinum utility job started loading a tablespace with replace option, we got following warning message many times before the job failure.
PUT0017W - Tablespace in STOP PENDING Mode - Utility will Retry
When I looked into PTIIMSG of platinum utility, I got
-DISPLAY DATABASE(DFLXPRP1) SPACENAM(SFWST ) LOCKS LIMIT(*)
DSNT360I - ***********************************
DSNT361I - * DISPLAY DATABASE SUMMARY
* GLOBAL LOCKS
DSNT360I - ***********************************
DSNT362I - DATABASE = DFLXPRP1 STATUS = RW
DBD LENGTH = 16142
DSNT397I -
NAME TYPE PART STATUS CONNID CORRID LOCKINFO
-------- ---- ---- ------------------ -------- ------------ ---------
SFWST TS [...]62_9948019231249920.WA.prakash.singhsomerfield.co.uk@www.idug.org
11902 122 35_Re: Tablespace in STOP PENDING Mode11_Mike Turner19_mike.turner@GMX.COM31_Mon, 28 Jun 2010 15:15:22 +0100355_UTF-8 Hi Prakash
The 01 in the first line is the lock waiter's position in the queue for the
lock it is trying to obtain, in this case first in the queue. 020.STOPDB09
indicates a DB2 -STOP DATABASE command is trying to get the lock. The 26's
are the internal OBID of the table on which the table (note TYPE is TB)
locks are held. [...]44_955A185126EC45CEBF6806B7804389B9@ToshibaA300
12025 51 35_Re: Tablespace in STOP PENDING Mode10_Max Scarpa16_mscarpa@CESVE.IT31_Mon, 28 Jun 2010 17:01:23 +0200799_US-ASCII Usually it's a long-running batch (or a batch doing rollback) blocking
STOP command, but there are some other reasons. Did you execute a DISPLAY
DB(...) Spacenam(....) USE /CLAIMERS ?
Max Scarpa
_____________________________________________________________________
* IDUG Europe * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EU *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________ [...]66_OF2FEAA7B9.2C9F54AD-ONC1257750.004EB3BF-C1257750.0052851F@cesve.it