1 IDUGDB2-L.ORG /home/listserv/home/db2-l August 2010, week 3 2 77 24_Re: Removing runstats ??13_Terry Purcell18_tpurcel@US.IBM.COM31_Sun, 15 Aug 2010 08:34:26 -0400430_UTF-8 Hello Dee,

It's not the 1st time I have heard of this need - therefore, you should open a requirement if it's important to you.

The more information you can provide the better. Knowing why you needed to remove them or why they were collected in the 1st place can help us understand whether the best solution is improve options for removal, or improve determination and automation of collection - or both? [...]50_6353466436036243.WA.tpurcelus.ibm.com@www.idug.org 80 40 47_Practical maximum values for CICS Maxopentcbs ?15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Sun, 15 Aug 2010 18:24:01 +0300360_US-ASCII I am trying to stress test a CICS/DB2 application by shoving lots of
transactions at once. I set the DB2CONN Tcblimit and Threadlimit as well as
Maxopentcbs to around 200 and let her rip. RACF 878's aplenty.

Is there some practical limit of TCB's and thread's where I can feel pretty
safe that I have given it the stress that it needs?42_ub1g66lf7grgcip2ggf2e450dpgom6bui8@4ax.com 121 275 128_The DB2Night Show Episode #26 - "DB2 in the Cloud - Tech Details Part 1", with Mark Wilding, IBM - Friday 20 August 10 am US CDT13_David Chapman24_David.Chapman@IAG.COM.AU31_Mon, 16 Aug 2010 17:29:30 +1000574_us-ascii _______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________

Hi DB2 List,

The DB2Night Show Episode #26 - "DB2 in the Cloud - Tech Details Part 1", with Mark Wilding, IBM

The International DB2 Users Group (IDUG) is pleased to partner with DBI to deliver a free Webcast by renowned presenter Scott Hayes, President & CEO, DBI. [...]43_DB2-L%201008160329428889.30A0@IDUGDB2-L.ORG 397 44 49_Performance of dynamic SQL with Parameter Markers11_Michel Helg24_michel.helg@AXA-TECH.COM31_Mon, 16 Aug 2010 03:31:11 -0400377_UTF-8 Hi all

we have an occurrence which I can't explain - does anybody of you ?

A PL/1 program with dynamic SQL. We generate the statement using parameter markers.

Elapsed time 1h, CPU 30 sec, 2 rows result

We change the statement to no parameter markers, all parameters hardcoded strings

Elapsed time 40 sec, CPU 30 sec, 2 rows result [...]55_7973895393474139.WA.michel.helgaxatech.com@www.idug.org 442 57 53_Re: Performance of dynamic SQL with Parameter Markers15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM31_Mon, 16 Aug 2010 11:55:25 +0300411_US-ASCII On Mon, 16 Aug 2010 03:31:11 -0400 Michel Helg
wrote:

:>we have an occurrence which I can't explain - does anybody of you ?

:>A PL/1 program with dynamic SQL. We generate the statement using parameter markers.

:>Elapsed time 1h, CPU 30 sec, 2 rows result

:>We change the statement to no parameter markers, all parameters hardcoded strings [...]42_t4vh66tel9lqmvtgg7uittb6r2f7feh2q2@4ax.com 500 91 53_Re: Performance of dynamic SQL with Parameter Markers11_Enrico Haak21_eh@INSOFT-SOFTWARE.DE31_Mon, 16 Aug 2010 11:09:26 +0200401_UTF-8 Good morning Michel,

it really depends on the RUNSTATS values the optimizer is going to choose.

E.g. with parameter markers the optimizer will not use the distribution
statistics as filter factors.

[AD] we are a company which will support you in analysing the dynamic
statement cache.

[PM] if you are located in Switzerland please give me a private email. [...]35_4C690046.4070104@insoft-software.de 592 31 35_SQL PATH in nested Stored Procedure11_Michel Helg24_michel.helg@AXA-TECH.COM31_Mon, 16 Aug 2010 05:33:43 -0400294_UTF-8 Hi all

we have a situation, where we call an unqualified stored proc from within a stored proc. As we want to switch between 2 qualifiers (schemaname) we use the SQL PATH = 'schemaname' command. However this only works with the outer SP i.e. it does not qualify the inner SP. [...]55_9084992852266571.WA.michel.helgaxatech.com@www.idug.org 624 48 53_Re: Performance of dynamic SQL with Parameter Markers15_Abe F. Kornelis13_abe@BIXOFT.NL31_Mon, 16 Aug 2010 11:47:50 +0200347_UTF-8 Michel,

The huge difference in elapsed time is suggestive of
something else going wrong.
Does it always take so long? How much CPU was
consumend in those 1.5 hours of elapsed time?
Was any other work going on?

If you get a space scan plus lots of waits for
locks, then time flies as if you were having fun. [...]47_27D8A1E1F964435A8D41B20BE3B2C4BF@your10f19f641c 673 242 53_Re: Performance of dynamic SQL with Parameter Markers11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 16 Aug 2010 11:50:14 +0200360_ISO-8859-1 Filter factors....with a ? the optimizer has to use default filter factors
for access path choice. With 'xxxxx' it can look into the statistics
columns (eg SYSCOLDIST) to actually see that a different access path is
much much better. That's why the golden rule of dynamic sql is:

Always use parameters except when you shouldn't [...]64_OF558B2147.10A97F8C-ONC1257781.0035DC96-C1257781.003604C2@seg.de 916 619 53_Re: Performance of dynamic SQL with Parameter Markers16_Broyles, Carol L27_Carol.L.Broyles@ACS-INC.COM31_Mon, 16 Aug 2010 07:44:49 -0500634_iso-8859-1 You could also try rebinding with REOPT(VARS) or REOPT(ONCE) and see if that fixes it. REOPT(ONCE) would be my preference if the variable doesn't change and you want the access path to stay the same.

Carol L. Broyles

Affiliated Computer Services, Inc.

A Xerox Company

Office Phone: 937-495-4003

carol.l.broyles@acs-inc.com

________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell
Sent: Monday, August 16, 2010 5:50 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] Performance of dynamic SQL with Parameter Markers [...]70_B22003942F5A0C4DBF34DC9C6121BF9B0C4ECD@A1DAL1SWPES22MB.ams.acs-inc.net 1536 253 53_Re: Performance of dynamic SQL with Parameter Markers11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 16 Aug 2010 15:02:37 +0200349_UTF-8 I agree and also remember that you can get a pretty bad performance hit
with REPORT(VARS) as it is at the package level...push all your "special"
cases into one or two packages...or use a special "booter" program with
REOPT(ONCE) that "seeds" the optimizer before anyone else can get their
grubby little hands on the cache... [...]64_OF3D4F7C5F.C1DD2755-ONC1257781.00475A77-C1257781.0047A1B2@seg.de 1790 297 48_DB2 Connect Virtual Tech Briefing Thursday, 8/1912_Kimberly May28_kim.may@THEFILLMOREGROUP.COM31_Mon, 16 Aug 2010 09:20:05 -0400366_US-ASCII Kimberly Madia's IBM/Optim team is hosting a Virtual Tech Briefing this
Thursday:

DB2 Connect for DBAs: A Primer and Look Ahead

Whether you are a DB2 for LUW DBA who would like to access enterprise
information or a z/OS DBA wondering how all those Java and .NET programmers
are getting to your data, DB2 Connect is the solution. [...]45_4c693b09.6482e50a.06da.ffffcb12@mx.google.com 2088 67 53_Re: Performance of dynamic SQL with Parameter Markers14_Adrian Collett24_adriancollett@TISCALI.IT31_Mon, 16 Aug 2010 15:37:36 +0200358_UTF-8 Hi Michel,

that's really weird...same amount of CPU but completely different
Elapsed time...the same CPU would appear to suggest that the AP is the
same so I would think your problem is somewhere else...

If you could share the accounting data for the dynamic run then we can
see what DB2 was doing/waiting for in that hour. [...]27_4C693F20.6060607@tiscali.it 2156 47 29_Utilities - Webcast - Tuesday14_Peter Backlund21_BacklundDB2@TELIA.COM31_Mon, 16 Aug 2010 16:09:56 +02000_24_4C6946B4.70105@telia.com 2204 74 53_Re: Performance of dynamic SQL with Parameter Markers6_Leslie21_db2dba@BTINTERNET.COM31_Mon, 16 Aug 2010 14:43:42 +0100322_us-ascii Michel
I have seen this before, REOPT did the trick for us, but be aware (as
mentioned here) that it can burn you too. Some good advice already here re
seeding etc.

As per Adrian's request I would like to see Account and maybe explain info
for both. That should give the game away then .. [...]41_812036FDEFC14903A064751E34684EC7@OfficePC 2279 52 26_DB2 data security question11_Sameer Rana21_sameerdrana@GMAIL.COM31_Mon, 16 Aug 2010 11:25:02 -0400407_UTF-8 I had a question regarding DB2 data security. Is it possible to grant selective access to test data for a unique group of users.

We want to grant selective access to our test data to this set of users within the prevailing application architecture and want to come up with a solution that will have a minimal impact on overall resource consumption and without compromising response times. [...]53_7241816199237312.WA.sameerdranagmail.com@www.idug.org 2332 70 30_Re: DB2 data security question0_24_carol.sutfin@REGIONS.COM31_Mon, 16 Aug 2010 11:14:13 -0500393_UTF-8 Something else to consider is what Security software you have in place.

Top Secret, RACF, etc. or are you using native DB2 security.

You can also use RLF to limit any dynamic queries to a reasonable CPU time.

Views are probably the easiest and quickest way to get a subset of the
data.
I have several application doing this with production views of data. [...]71_OFAD418E9D.6379F8A3-ON86257781.0058C9EA-86257781.00593190@corp.rgbk.com 2403 57 30_Re: DB2 data security question0_19_galambos@ROGERS.COM31_Mon, 16 Aug 2010 16:27:47 +0000420_- This begs for some clarification

1) Depending on what version you are on (assuming Its z/OS) because there is row level in the latest version.

2) And then why, if this is test data, does it matter if a tester sees another row. if it is production data which is being used for testing, that is a no no given the various privacy laws. Production Data needs to be scubed before being used for testing. [...]105_1484399260-1281976069-cardhu_decombobulator_blackberry.rim.net-1184961365-@bda274.bisx.prod.on.blackberry 2461 47 46_Re: db2 luw (v9.1) - SMS tablespace size limit13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Mon, 16 Aug 2010 13:08:15 -0400604_UTF-8 All,

I ran the supplied SQl to improve on my rough estimate - results showed 460GB as tablespace size

SELECT SUBSTR(TBSP_NAME,1,30) AS "TABLESPACE NAME",
TBSP_TYPE AS "TYPE",
SUBSTR(TBSP_STATE,1,20) AS "STATE",
INT(TBSP_TOTAL_SIZE_KB/1024/1024) AS "SIZE (GB)",
100 - SMALLINT(TBSP_UTILIZATION_PERCENT) AS "% FREE",
INT(TBSP_FREE_SIZE_KB / 1024) AS "MB FREE SPACE"
FROM SYSIBMADM.TBSP_UTILIZATION
where TBSP_NAME = 'xx_xxxx_DAT'
order by 1

results list:
TABLESPACE NAME,TYPE,STATE,SIZE (GB),% FREE,MB FREE SPACE
xx_xxxx_DAT,SMS,NORMAL,460,0,0 [...]55_7279308788419453.WA.jackrcampbellyahoo.com@www.idug.org 2509 29 30_Re: DB2 data security question11_Sameer Rana21_sameerdrana@GMAIL.COM31_Mon, 16 Aug 2010 14:22:39 -0400377_UTF-8 Both very valid questions.

1) We are running V9 CM. We plan on being NFM by end of the year. I do know Version 10 provides row-level and other sophisticated features catering to data privacy needs but we cannot wait till Version 10 at our site.
2) Test data is not scrubbed as of now and the cost of encrypting is too prohibitive so that is not an option. [...]53_5583554647931355.WA.sameerdranagmail.com@www.idug.org 2539 266 53_Re: Performance of dynamic SQL with Parameter Markers10_Fred Edgar18_fredgarx@YAHOO.COM31_Mon, 16 Aug 2010 14:34:44 -0700426_iso-8859-1 The obvious difference seems to be what Roy points out.
Explain it both ways, with ?'s and with the values you're supplying and see if they are
different.  But Adrian has a good question too, why is the CPU the same?  Could be
coincidence,  or maybe you ran the one with hard-coded values right after the one w/o
and everything was just in the bufferpools.  More info. is always good.
 
Fred [...]43_678229.52162.qm@web30202.mail.mud.yahoo.com 2806 120 30_Re: DB2 data security question12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 16 Aug 2010 20:16:37 -0700667_utf-8 I don't totally understand your comment about encryption being too MIP prohibitive? Encryption is almost totally microcode-assisted and most of it takes place outside the basic engine so the extra cost is minimal. ________________________________ From: Sameer Rana To: DB2-L@IDUGDB2-L.ORG Sent: Mon, August 16, 2010 11:25:02 AM Subject: [DB2-L] DB2 data security question I had a question regarding DB2 data security. Is it possible to grant selective access to test data for a unique group of users. We want to grant selective access to our test data to this set of users within the prevailing application architecture and want to [...]43_73024.44121.qm@web112104.mail.gq1.yahoo.com 2927 96 53_Re: Performance of dynamic SQL with Parameter Markers12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 16 Aug 2010 20:21:57 -0700354_us-ascii Exactly what is taking the elapsed time? In other words, where is it actually
waiting. For CPU, ie something else needing the resources or what?

That's the real question that you need to address. Exactly what is using the
wall clock, ie where are the waits? Until we see that , it's useless to
speculate on what's going on. [...]44_290456.91904.qm@web112102.mail.gq1.yahoo.com 3024 140 53_Re: Performance of dynamic SQL with Parameter Markers12_Myron Miller22_myronwmiller@YAHOO.COM31_Mon, 16 Aug 2010 20:24:13 -0700673_iso-8859-1 Actually that's not 100% true. There are a number of situations where the optimizer will use distribution stats to override filter factors set based upon parameter markers. Exactly why and all of these cases, I don't know, but I've got several PMRs in with IBM on cases where this is happening right now. Myron ________________________________ From: Enrico Haak To: DB2-L@IDUGDB2-L.ORG Sent: Mon, August 16, 2010 5:09:26 AM Subject: Re: [DB2-L] Performance of dynamic SQL with Parameter Markers Good morning Michel, it really depends on the RUNSTATS values the optimizer is going to choose. E.g. with parameter markers the optimizer [...]44_587529.70458.qm@web112106.mail.gq1.yahoo.com 3165 60 64_V7 DSNLIENO SOCKET=CONNECT RETURN CODE=1128 REASON CODE=766302915_Steve21_malutjuta@HOTMAIL.COM31_Tue, 17 Aug 2010 03:23:39 -0400299_UTF-8 Listers,

May take you back a couple of versions but I have a DB2 V7 for z/OS 1.9 DDF task that aborts occasionally, last time seemed to begin with following combination (& especially the message with RC=1128 which I can't get a hit on - even with Google)
Anyone seen it before? [...]53_5916733434036685.WA.malutjutahotmail.com@www.idug.org 3226 89 68_Re: V7 DSNLIENO SOCKET=CONNECT RETURN CODE=1128 REASON CODE=766302916_Leslie21_db2dba@BTINTERNET.COM31_Tue, 17 Aug 2010 08:46:18 +0100531_us-ascii Hi steve

Funnily enough I have seen this before, but before you get all excited, we
never solved the issue.

One of my good friends who is an absolute guru on DDF(it's his baby so to
speak), raised tickets with the labs, dug and dug and dug with it, after
three months he gave up as nobody could see an issue at the client and even
with packet tracing s/w on the network he could not work out why the 1128 &
1121 s were coming out. We still see them from time to time in DB2 9 (we
have [...]41_10CD463B190A42538B1C558FA84FC482@OfficePC 3316 29 18_MQ Series V7 to V97_Steve M21_malutjuta@HOTMAIL.COM31_Tue, 17 Aug 2010 03:27:46 -0400809_UTF-8 Dear listers,

Does anyone have an operating MQ Series data/query replication process happening from mainframe Db2 V7 to mainframe Db2 V9?
Even better are there any problems with doing this.

regards,
Steve

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* 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_9364785963148104.WA.malutjutahotmail.com@www.idug.org 3346 227 89_[AD] DB2 10 Is Coming- A First Look from Available Information (Part 1 REPEATED for EMEA)13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 17 Aug 2010 05:50:34 -0400467_us-ascii Just a quick reminder that the Cogito webinar "DB2 10 Is Coming- A First Look from Available Information (Part 1)" will be repeated for EMEA audiences on Thursday August 19th at 3:00PM CEST (2:00PM in the UK and Ireland)

Although DB2 10 is still in beta, and thus subject to IBM non-disclosure agreements, there is enough information now available in the public domain to give us a really good idea of what IBM are planning for this new release. [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902CA0B@MAILR004.mail.lan 3574 45 22_Re: MQ Series V7 to V90_23_j.p.slot@RN.RABOBANK.NL31_Tue, 17 Aug 2010 12:00:26 +0200498_utf-8 Why would you want to do a thing like that?
Regards,
Jaap

-----Oorspronkelijk bericht-----
Van: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Namens Steve M
Verzonden: dinsdag 17 augustus 2010 9:28
Aan: DB2-L@IDUGDB2-L.ORG
Onderwerp: [DB2-L] MQ Series V7 to V9

Dear listers,

Does anyone have an operating MQ Series data/query replication process happening from mainframe Db2 V7 to mainframe Db2 V9?
Even better are there any problems with doing this. [...]61_31FCADEDD866474DBBF19152F15CBFB802E5FFC8@VITARA.rabobank.corp 3620 235 53_IDUG EMEA and the Third Man (cue Zither music.......)13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 17 Aug 2010 08:27:08 -0400478_us-ascii I know it's not Friday (but I was on vacation last week!) but I just wanted to mention something that occurred to me recently

If you are attending IDUG EMEA in Vienna and (like me) you're a fan of older films - don't forget that one of the stars of "The Third Man" is in Vienna. Yes, the giant Ferris Wheel (or Reisenrad) in Prater Park (http://www.aboutvienna.org/sights/prater.php). (Un)Fortunately, there don't seem to be any tours of the sewers though! [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902CA1E@MAILR004.mail.lan 3856 305 65_AW: [DB2-L] IDUG EMEA and the Third Man (cue Zither music.......)12_Peter, Georg15_G.Peter@DZBW.DE31_Tue, 17 Aug 2010 14:36:13 +0200552_iso-8859-1 >>> (or Reisenrad) <<<

Meinten Sie: Riesenrad ?

SCNR ;-))

With kind regards - mit freundlichen Gruessen,
G e o r g

-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Phil Grainger
Gesendet: Dienstag, 17. August 2010 14:27
An: DB2-L@IDUGDB2-L.ORG
Betreff: [DB2-L] IDUG EMEA and the Third Man (cue Zither music.......)

I know it's not Friday (but I was on vacation last week!) but I just wanted to mention something that occurred to me recently [...]58_99E58D5AC1BE6D4CBF492AF72B942C3D061ADD2D@dzstus010.dzbw.de 4162 577 57_Re: IDUG EMEA and the Third Man (cue Zither music.......)13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Tue, 17 Aug 2010 09:01:40 -0400630_iso-8859-1 OK, so my Germanic spelling is even worse than my English :(

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

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Peter, Georg
Sent: 17 August 2010 13:36
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] AW: [DB2-L] IDUG EMEA and the Third Man (cue Zither music.......) [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902CA30@MAILR004.mail.lan 4740 62 39_Re: SQL PATH in nested Stored Procedure8_Mike Van17_mvandza@YAHOO.COM31_Tue, 17 Aug 2010 06:34:50 -0700615_us-ascii Hi

Do you use INHERIT SPECIAL REGISTERS for the inner stored procedure?

Michael

----- Original Message ----
From: Michel Helg
To: DB2-L@IDUGDB2-L.ORG
Sent: Mon, August 16, 2010 11:33:43 AM
Subject: [DB2-L] SQL PATH in nested Stored Procedure

Hi all

we have a situation, where we call an unqualified stored proc from within a
stored proc. As we want to switch between 2 qualifiers (schemaname) we use the
SQL PATH = 'schemaname' command. However this only works with the outer SP i.e.
it does not qualify the inner SP. [...]44_597638.74302.qm@web110415.mail.gq1.yahoo.com 4803 41 23_Performance is Relative11_Dave Beulke19_dave@DAVEBEULKE.COM31_Tue, 17 Aug 2010 13:18:20 -04000_51_9678056519073525.WA.davedavebeulke.com@www.idug.org 4845 191 36_IBM developerWorks DB2 z/OS Exchange14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 17 Aug 2010 13:28:37 -0400676_us-ascii Folks, some of you know about this; for the rest, the DB2 for z/OS Exchange is moving to IBM developerWorks. The new URL is:
https://www.ibm.com/developerworks/mydeveloperworks/groups/service/html/communityview?communityUuid=22586cb0-8817-4d2c-ae74-0ddcc2a409bc
Or, for the length-challenged; http://bit.ly/IBM-DB2zOS-Exchange

I have a posting there on using recursive common table expressions (a modification of original work by Suresh Sane). I'm thinking about posting the SPUFI code which I used to create (in V9) a native SQL stored procedure, which would contain some simple goodies on date manipulation for others to cut and paste as desired. [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF937@MAIL02.fisalan.nycnet 5037 256 30_DB2 Extended Datasets for Z/Os15_Schade, Jeffrey15_JSchade@ISO.COM31_Tue, 17 Aug 2010 13:38:52 -0400571_us-ascii We have been using DB2 extended datasets for some of our tables and
indexes and have not encountered any issues. We are thinking of enabling
extended datasets for all our user databases (No system datasets) and I
was wondering if anyone else has done this or researched it and decided
against it. I am looking for snags but at this point I do not see a
downside to having a database use extended datasets even if we do not
think it will ever need the larger DSSIZE. This would make the
management of our SMS rules much simpler in terms [...]59_DB138660B6B01D458B16F9205C0C263418476383@ISOEMAILP3.iso.com 5294 440 34_Re: DB2 Extended Datasets for Z/Os14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 17 Aug 2010 13:47:08 -0400438_us-ascii Jeffrey,

How have you tested your extended datasets?

If the right zOS/SMS options are not enabled, I have seen a case where the dataset created an extent whose RBA range was greater than 4GB but DB2 did not correctly insert/retrieve data from those higher RBAs. It was a while ago, so the problem might be fixed, but the concern is valid, at least if I'm the one who's being asked to allocate huge datasets... [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF938@MAIL02.fisalan.nycnet 5735 646 34_Re: DB2 Extended Datasets for Z/Os15_Schade, Jeffrey15_JSchade@ISO.COM31_Tue, 17 Aug 2010 14:02:58 -0400502_us-ascii Phil,

We have a few of our production tables that I know have data beyond the
4 GB size in an extended dataset partition and we have not seen problems
with INSERT or SELECT. That does not mean we won't have issues but I was
hoping since this feature has been out for awhile and I know there are
some pretty large databases defined in DB2 that the feature worked as
designed. What kind of errors or problems did you see? Were there abends
or unexpected SQL codes? [...]59_DB138660B6B01D458B16F9205C0C263418476385@ISOEMAILP3.iso.com 6382 821 34_Re: DB2 Extended Datasets for Z/Os14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 17 Aug 2010 14:24:09 -0400369_us-ascii Jeff,
It was at least six years ago, so I don't remember details. I think we got bad SQLCODEs but I don't remember what they were, so I could be wrong. Sorry I don't have more detail for you. All I _really_ remember is that I came out of the incident determined to thoroughly test high RBAs in the dataset for any really large DSSIZE values.
--Phil [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF93B@MAIL02.fisalan.nycnet 7204 966 34_Re: DB2 Extended Datasets for Z/Os15_Schade, Jeffrey15_JSchade@ISO.COM31_Tue, 17 Aug 2010 14:41:48 -0400784_us-ascii Thanks Phil. I will make sure we do some additional testing.

Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering

ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

FAX: (201) 748-1500
jschade@iso.com

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Sevetson,
Phil
Sent: Tuesday, August 17, 2010 2:24 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] DB2 Extended Datasets for Z/Os

Jeff,

It was at least six years ago, so I don't remember details. I think we
got bad SQLCODEs but I don't remember what they were, so I could be
wrong. Sorry I don't have more detail for you. [...]59_DB138660B6B01D458B16F9205C0C263418476386@ISOEMAILP3.iso.com 8171 102 81_Minneapolis, Dallas or Philadelphia..Free certification and $395 off IDUG NA/EMEA11_Suresh Sane21_data_arch@HOTMAIL.COM31_Tue, 17 Aug 2010 15:43:05 -0500929_iso-8859-1 If you live near Minneapolis, Dallas or Philadelphia..

Check out the IDUG Regional events which will provide:

IDUG quality close to home

Top-notch speakers for a 2-day educational event

$395 off IDUG NA/EMEA registration for anyone who attends the Regional

Free certification ($200 value)

Check out this link:

http://www.idug.org/idug-regional-events/idug-regional-forums.html

Thx
Suresh

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* 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 [...]43_COL104-W16F8D10607CE0C152C909E989C0@phx.gbl 8274 236 45_IBM Sets new TPC-C Benchmark with DB2 LUW 9.712_Phil Gunning19_pkgunning@GMAIL.COM31_Tue, 17 Aug 2010 17:40:56 -0400869_us-ascii Check it out at http://www-03.ibm.com/press/us/en/pressrelease/32328.wss

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@gts1consulting.com

IBM Advanced 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 message, or the
taking of any action based on it, is strictly [...]35_000001cb3e54$e17ef540$a47cdfc0$@com 8511 191 49_Re: IBM Sets new TPC-C Benchmark with DB2 LUW 9.713_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Tue, 17 Aug 2010 15:30:24 -0700747_ISO-8859-1 I find it very surprising that this benchmark is NOT using pureScale, and is
instead using InfoSphere Warehouse (well, the DPF feature).

I wonder when IBM will release a pureScale benchmark.

Ian Bjorhovde

On Tue, Aug 17, 2010 at 2:40 PM, Phil Gunning wrote:

> Check it out at http://www-03.ibm.com/press/us/en/pressrelease/32328.wss
>
>
>
> 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@gts1consulting.com
>
[...]60_AANLkTim4mBMgb_1sEFMW8196OrbuHd_-A98t31fRe5Ys@mail.gmail.com 8703 561 34_Re: DB2 Extended Datasets for Z/Os12_myron miller22_myronwmiller@YAHOO.COM31_Tue, 17 Aug 2010 23:02:59 -0400530_iso-8859-1 We have a number that are extended and have not had a problem with them for years. (and they are both extended with regular VSAM as well as DB2 datasets). Works as designed pretty well.

But I'd ask why allow those that are not ever going to get that big to grow that big. On some, it would make it much more efficient to keep 4 gig and just partition them (PBG if V9 NFM) rather than allowing them to grow to 8 gig. remember backing up, reorging and restoring 8 gig datasets takes a lot longer and way more [...]43_3BEF791881204FD9AF33E6E213AB122B@premierhp2 9265 125 45_[OT] About WLM, but interesting for DB2 folks10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 18 Aug 2010 12:02:13 +0200538_US-ASCII Hi all

For people interested in WLM, as DB2 is using WLM services extensively
even in DB2 10 ( WLM-managed bufferpools for instance) , there's a
recent paper presented at SHARE (link from the post in

MXG list):

http://share.confex.com/share/115/webprogram/Session7777.html

It deals even with 'skip clock' a concept that some z/OS sysprogs don't
know (in last company where I worked for example). I didn't read this
presentation for now, so please take this mail 'as-is', but it looks [...]66_OF37E97CF1.7878E11B-ONC1257783.002F2D4B-C1257783.0037224F@cesve.it 9391 871 34_Re: DB2 Extended Datasets for Z/Os12_McLaren Phil32_Phil.McLaren@AXAWINTERTHUR.CO.UK31_Wed, 18 Aug 2010 11:33:28 +0100591_us-ascii I too remember a 'feature' relating to this, also about 6 years ago. Again, sketchy on the details, but it involved a DFP bug specific to DB2, preventing DB2 accessing an extended dataset beyond 4gb. The application received the normal resource unavailable messages.
Being so old and pretty significant, I would doubt anyone still has the bug on board. It was in a z/OS 1.4 and DB2 V7 environment.
That particular installation had EA set up for all DB2 objects, old bugs aside, it worked well and gave the flexibility where it was needed and no problems to those objects [...]72_1454A0984F246445950CB759E249F50F4696DFE63D@csis564.wlp.uk.winterthur.com 10263 327 57_Re: [z/OS] Stored Procedure Definition & Graphic Datatype20_Cuneyt Goksu (Gmail)22_cuneyt.goksu@GMAIL.COM31_Wed, 18 Aug 2010 14:37:30 +0300357_US-ASCII As promised this is what I received from IBM Support

"I am advised by teammates that the GRAPHIC to VARGRAPHIC issue for
Stored Procedure is fixed by APAR PK72446/PTF UK48694 which is PE'ed.
APAR PM17236 should fix the PE and will most likely be available either
end of August or September. . Action Plan: Wait for APAR 17236" [...]42_009c01cb3ec9$bef77c70$4900fd0a@cuneytgoksu 10591 935 34_Re: DB2 Extended Datasets for Z/Os15_Schade, Jeffrey15_JSchade@ISO.COM31_Wed, 18 Aug 2010 08:27:41 -0400591_us-ascii Hi Myron,

You bring up the big concern we have with using extended datasets (>4 GB
partitions) but we do not always have an option in the design to create
more 4 GB partitions and the application really does have a large amount
of data. The developers and designers are always citing IBM literature
which states support of extremely large tables and they do not take into
account the issues involved in managing these larger tables. We do not
plan on setting DSSIZE larger than 4 GB for the majority of our tables
and indexes but would like to have [...]59_DB138660B6B01D458B16F9205C0C26341847638F@ISOEMAILP3.iso.com 11527 122 34_Re: DB2 Extended Datasets for Z/Os10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 18 Aug 2010 14:51:56 +0200605_US-ASCII Hi all

This is an argument that from time to time comes out. We started to use EA
datasets since OS/390 2.10 (pure VSAM first, DB2 LDS later in LARGE
partitioned data sets ) and we heard about some
problems but they were solved by some PTFs since then. We used them
until z/OS 1.9 without any problem on condition that you were current
with DFP/SMS PTFs (and few for DB2, if I remember well).
Usually doubts come out because 'DASD managers EA fear' as this means
more work and checks for them (new dataclass, new ACS routine
modifications,new Sgs....:-)).
[...]66_OF4C736946.9815EDE7-ONC1257783.0041462C-C1257783.0046AC31@cesve.it 11650 59 27_Re: Performance is Relative14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Wed, 18 Aug 2010 09:48:56 -0400656_UTF-8 If I might suggest
There are constructive and distructive activities.

For constructive activities performance is relative.
An example on a constructive activity would be
reading a block into a buffer pool vs finding that it is already there.
Finding the block is usually faster but readying the block supports the UOW.

Lots of distructive activities ...
Waiting to write a log image because all the logs are full and waiting archive.
Writting a block early to make space in the buffer esp if that block gets re-read.
Almost any type of pool failure.
Page faults.
These are considered destructive because [...]56_8298103787826486.WA.ibmsysproggeeksites.com@www.idug.org 11710 434 34_Re: DB2 Extended Datasets for Z/Os12_Myron Miller22_myronwmiller@YAHOO.COM31_Wed, 18 Aug 2010 08:34:09 -0700575_utf-8 Thanks and no problem. But I have one curious question about your reply. You stated "we do not always have an option in the design to create more 4 GB partitions". I really don't understand why you don't. The physical storage of the data is or at should not be a design consideration for the application team. It should be strictly in the DBAs hands working with storage administration. If it isn't, then I feel very sorry for you. Whether you have multiple partitions or one is almost totally transparent to the application (except for reorg's and load's) it is. [...]44_221395.44421.qm@web112119.mail.gq1.yahoo.com 12145 49 37_IDUG EMEA 2010 - Call for moderators.10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 18 Aug 2010 16:27:35 -0400590_UTF-8 Esteemed DB2 listers (and hopefully IDUG EMEA attendees !)

IDUG EMEA 2010 is approaching and with new DB2 10 and DB2 for LUW announcements it promises to be a very informative conference with many technical sessions to choose from. I would like to personally ask you to help make the conference a success by moderating one of the technical sessions. We have several openings for moderators and you don't have to be a speaker to moderate. We encourage * all attendees * to consider moderating a session of your interest. It is an excellent way to volunteer to support IDUG [...]48_8504402136968303.WA.mscarpacesve.it@www.idug.org 12195 84 27_Re: Performance is Relative13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Wed, 18 Aug 2010 15:33:33 -0700472_utf-8 Hi Av. Thanks for the description of constructive and destructive activities, it made a lot of sense to me and increased my understanding. I also agree about creating new threads just to promote one's personal blog. I'm not sure what the remedy is, though, because I don't want to put strictures on, say, Roger Miller posting a link to a new presentation or similarly pertinent information in a new thread. I guess let it go until it becomes _really_ annoying. [...]64_927FE8790B320742927BCF9ADF7AE48A19A56C9E54@PDXMAIL.pacificorp.us 12280 90 27_Re: Performance is Relative13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Wed, 18 Aug 2010 18:45:28 -0500614_UTF-8 Hi

A quick comment on your "Administrative Note"..

I am no longer an administrator for DB2-L list so I can't make rules and
tell people what to do or how to behave..

However, I would like to make a recommendation.. before anyone starts
to limit the self promotion of list subscriber's blogs on the DB2-L, one
needs to ask themselves first if the person pushing their blog also
contributes to the DB2-L List and if the blog entry they are pushing
offers useful information to the DB2 community. The purpose of the
List, at least originally, was to share knowledge. [...]30_4C6C7098.2060205@attglobal.net 12371 434 75_IDUG North America 2011 - Call For Presentations - Deadline: September 1st!11_paul turpin18_pault399@GMAIL.COM31_Wed, 18 Aug 2010 22:37:43 -0400535_windows-1252 Dear List Serve Members,

The International DB2 Users Group (IDUG®) is accepting presentation
abstracts for IDUG 2011- North America, May 2-6 in Anaheim, California. This
is your opportunity to share your DB2 experience and expertise with an
audience of your peers who rely on, or are considering incorporating DB2 in
their shop. If your presentation abstract is selected to be delivered at the
conference, you will receive one complimentary IDUG 2011 - North America
conference registration. [...]60_AANLkTi=ZRt+=jDqZcS0zvxdBMM8kOCDo_CaSA=LeqMa+@mail.gmail.com 12806 49 25_Omegamon Statement Number4_Anil21_alisha_kale@YAHOO.COM31_Thu, 19 Aug 2010 04:51:32 -0400378_UTF-8 Hi All !

For those using Omegamon of DB2 (on z/os) here is a question:
Lets say you are viewing a thread activity, and looking at the current sql, you would see something like
Thread Status = xxxxxxx SQL Request Type = STATIC
Total SQL Reqs = nnnnnn SQL Call Type = FETCH
SQL DBRM Name = pppppp SQL Statement Number = 04310
Collection ID = cccc [...]52_0592112788263815.WA.alishakaleyahoo.com@www.idug.org 12856 76 39_Betr: [DB2-L] Omegamon Statement Number15_Marcel Harleman31_mam.harleman@BELASTINGDIENST.NL31_Thu, 19 Aug 2010 11:33:13 +0200631_us-ascii Anil,

since it's apparently static SQL you should be able to see the text via a
query on either SYSSTMT or SYSPACKSTMT. But perhaps you're equipped with a
database administration tool that can format this text as well.

Regrads,

Marcel.

------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl. [...]76_OFC9911BBB.BC31C253-ONC1257784.00344C44-C1257784.00347B10@belastingdienst.nl 12933 28 43_Re: Betr: [DB2-L] Omegamon Statement Number11_Fred Nijdam21_fred.nijdam@XS4ALL.NL31_Thu, 19 Aug 2010 06:06:30 -0400332_UTF-8 Anni,

As Marcel stated quite well, look up the stmnt-nr. in the catalog tables representing the dbrm, than relate that fetch (from cursor) to the open cursor with the query. The query is in the plan table (if explained). Visual-explain (depreciated) might be helpful, successors also or any other explain tooling! [...]53_9441007386849808.WA.fred.nijdamxs4all.nl@www.idug.org 12962 50 35_NUOVO-INDIRIZZO-MAIL-PATRIZIA-RIZZI14_Patrizia Rizzi24_patrizia.rizzi@GMAIL.COM31_Thu, 19 Aug 2010 12:00:58 +0200636_ISO-8859-1 Buongiorno a Tutti,

Volevo comunicarvi il mio nuovo indirizzo mail.

Saluti

Patrizia Rizzi

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv60_AANLkTikxZ28Ujmmn6rhqRHZ-C9aJZtkT3beCTs7d+Qui@mail.gmail.com 13013 48 49_DB2 luw(9.1): Tablespace container redistribution13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Thu, 19 Aug 2010 11:57:20 -0400374_UTF-8 I am currently trying to clean-up (inherited) issues with a large database running in AIX 5.3, using DB2 luw v9.1.

My current problem is very poor definition of tablespaces - in that whilst 6 containers on 6 separate file system were availble, I have over 100 tablespaces which have allocated 1 container to say fs0, and the other 5 containers to say fs1. [...]55_5848855473838043.WA.jackrcampbellyahoo.com@www.idug.org 13062 93 53_Re: DB2 luw(9.1): Tablespace container redistribution13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Thu, 19 Aug 2010 10:18:46 -0700396_ISO-8859-1 You don't say whether you are using DMS or SMS, although I suspect the
latter. I'm going to assume that the database isn't using Automatic
Storage (based on the container names).

If you're using DMS: You can alter each tablespace to add containers
to the other file systems. DB2 will automatically redistribute the
extents evenly across each of the containers. [...]60_AANLkTi=dEvFbkMcpUQa2ae5=gkb_HUpWMtxEMUVzsJDK@mail.gmail.com 13156 147 54_Baltimore Washington DB2 Users Group September Meeting14_Henry L Nalven25_henry.nalven@MARRIOTT.COM31_Thu, 19 Aug 2010 13:25:12 -0400510_UTF-8 BWDB2UG z/OS Quarterly Meeting

www.bwdb2ug.org

To pre-register, please mail the appropriate fee with attendee's and employer's name to:

THE BALTIMORE/WASHINGTON
DB2 USERS' GROUP
P.O. BOX 18554
BWI AIRPORT, MARYLAND 21240

All pre-registrations must be received by
Friday, September 10, 2010. Make checks payable to the
Baltimore/ Washington DB2 Users' Group or use the PAYPAL link on website

Visit our website, www.bwdb2ug.org, for more information. [...]57_2518656160878307.WA.henry.nalvenmarriott.com@www.idug.org 13304 70 17_Hitachi VTL & DB210_Mark Labby21_mlabby@AESSUCCESS.ORG31_Thu, 19 Aug 2010 13:45:23 -04000_67_OF66A550B0.19119A78-ON85257784.006025EC-85257784.00617FB0@pheaa.org 13375 430 21_Re: Hitachi VTL & DB214_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Thu, 19 Aug 2010 14:24:14 -0400513_us-ascii Mark,
My first impulse is to say that it sounds like you need to be watching I/O channels in realtime with Omegamon/MVS or something comparable.
Second thought is to ask whether the number of EXCP's is the same by both methods? And do the CPU times compare?

--Phil

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Labby
Sent: Thursday, August 19, 2010 1:45 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] Hitachi VTL & DB2 [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF94B@MAIL02.fisalan.nycnet 13806 38 49_performance of VARCHAR columns as part of IN LIST10_John Lantz20_john.p.lantz@FRB.GOV31_Thu, 19 Aug 2010 14:29:53 -0400537_UTF-8 We are running V9 NFM. We have recently implemted some tables with VARCHAR columns. We've are seeing really poor performance (in terms of CPU cost) when using a VARCHAR column as part of the WHERE criteria with an IN LIST. We run a query that does a tablespace scan since there are no indexes that would satisfy the query. The SQL has an IN list with 100 items against a column. The query that uses a column defined as CHAR will take about 30 CPU seconds to do a scan. Another query against the same table but this time using [...]52_2382322692377865.WA.john.p.lantzfrb.gov@www.idug.org 13845 309 21_Re: Hitachi VTL & DB235_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Thu, 19 Aug 2010 14:44:25 -0400526_iso-8859-1 Have you looked at RMF reports to see what is happening to your processor, memory, channels, and device busy rates?
Do any bottlenecks show on the DB2 side?

What is the major component of each jobs elapsed time?

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 [...]46_FD3C650C5927426C80A13CC2D0E5950D@DellNotebook3 14155 38 20_LUW db2top and nohup9_Andy Hunt21_andy_hunt@YAHOO.CO.UK31_Thu, 19 Aug 2010 16:40:30 -0400417_UTF-8 Hi,
DB2 V9.5 FP5 AIX
I want to run db2top and capture stats to a file, e.g something like...

db2top -d dbname -i 60 -C D -m300

I'd like to use nohup to run this in the background.

Im scuppered when I try this because there is an interacitve part of the program that asks the following when you run it,
'Writing to myfile, should I create a named pipe instead of a file [N/y]' [...]52_6407517152550351.WA.andyhuntyahoo.co.uk@www.idug.org 14194 54 53_Re: DB2 luw(9.1): Tablespace container redistribution16_Gregory Palgrave28_gpalgrave@STAFF.IINET.NET.AU31_Fri, 20 Aug 2010 06:54:52 +0800541_utf-8 Jack,

You could try SQL Replication to a new table/tablespace which would allow you to keep downtime to a minimum when you do the cutover to the new table. There are some limitations on replicating LOBs and LONG data types, so this may not suit you.

If you can afford a full outage of db2 at all, you may be able to use the db2relocatedb tool to relocate the containers, depending on the size of the containers and the outage window. The tool itself is quick - but it requires db2 down, and you have to mv or cp the [...]73_8522673947DF7D4394C962BFE441CD27050AC98747@PER-EXCHMBX.win2k.iinet.net.au 14249 23 62_Tomas Vasko/IEA/EAGA/EAG/AT ist au=?ISO-8859-1?Q?=DFer_?=Haus.11_Tomas Vasko23_tomas.vasko@GENERALI.AT31_Fri, 20 Aug 2010 01:31:20 +0200627_ISO-8859-1 Ich bin ab 20.08.2010 außer Haus und ab 03.09.2010 wieder im Büro.
I'm out of the office from 20.08.2010 and will be back on 03.09.2010.

Ich werde Ihre Nachricht nach meiner Rückkehr beantworten.

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________ [...]68_OFE1A06888.561BCC53-ONC1257784.0081365C-C1257784.0081365C@AT.TOP.COM 14273 80 24_Re: LUW db2top and nohup11_Enrico Haak21_eh@INSOFT-SOFTWARE.DE31_Fri, 20 Aug 2010 09:30:52 +0200579_UTF-8 Hi Andy,

please have a look at
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp

IMHO you have to use the -b switch.

Regards,
Enrico

Mit freundlichen Grüßen / Best Regards

Enrico Haak

___________________________________________________________________

InSoft Software GmbH, Derendorfer Str. 70, 40479 Düsseldorf / Germany
Tel. +49 211 44 03 166, Fax +49 211 48 80 33
Geschäftsführer: Colin Oakhill, Günther Wichmann
HRB 20486 DÃœSSELDORF * UST-IDNR:DE 119253977 * www.insoft-software.de [...]35_4C6E2F2C.2090401@insoft-software.de 14354 46 67_AUTO: Mahesh Sugavanam2 is out of the office (returning 23/08/2010)17_Mahesh Sugavanam219_mahsugav@IN.IBM.COM31_Fri, 20 Aug 2010 16:01:54 +0530462_US-ASCII I am out of the office until 23/08/2010.

Hi,

I will not be available at office on 20th August 2010.

During this period , Ankur Agarwal will be taking care of my roles and
responsibilities for Datastar back office application ,
Rahul Jain will be taking care for
Dialog Backoffice from offshore , and
Anand Sankanarayanan and Rahul Jain
will be helping for Datastar Mainframe database and infrastructure support. [...]68_OF5E36BE72.7397267F-ON65257785.0039DA5D-65257785.0039DA5D@in.ibm.com 14401 255 35_[AD] IDUG EMEA - City Airport Train13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 20 Aug 2010 09:45:14 -0400535_us-ascii For those of you planning on attending IDUG EMEA in Vienna in November, please don't forget to check out the City Airport Train (CAT) from Vienna airport into the city - you can't miss it, it's BRIGHT green (http://www.railfaneurope.net/pix/at/electric/1014-1114/006-009/1014-007-x-mt.jpg)!

Their website (http://www.cityairporttrain.com/default.aspx?lang=en-US&gclid=CKaltoGVyKMCFUqX2Aod1121vA) is a mine of information on getting to and from the airport and you can even buy tickets in advance (EUR16.00 return) [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902CC84@MAILR004.mail.lan 14657 137 39_Re: [AD] IDUG EMEA - City Airport Train10_Max Scarpa16_mscarpa@CESVE.IT31_Fri, 20 Aug 2010 16:23:36 +0200421_UTF-8 And very very close to Hilton there's a nice park for runners (hope I'll
have a time for a run), nice restaurants, one of the biggest cathedral in
Vienna, the Opera house, nice italian ice-cream vendors and 2
of main streets for shopping......and more ( I hadn't the time to explore
more sorry ). For mountain lovers there's even Osterreich AlpenVerein Haus
(hope it's correct) very close :-) . [...]66_OF01ED74BB.917B3F86-ONC1257785.004D794E-C1257785.004F0B3A@cesve.it 14795 384 39_Re: [AD] IDUG EMEA - City Airport Train10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 20 Aug 2010 10:42:42 -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_1A700EEF49343148A08879B1E3CA5BA615FE16E6@NSTMC101PEX1.ubsw.net 15180 119 39_Re: [AD] IDUG EMEA - City Airport Train16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Fri, 20 Aug 2010 10:47:47 -0400677_UTF-8 Minneapolis has this service - I just used it from the airport to downtown. One USD AND 50 Cents - and faster than a taxi.

Steen Rasmussen
CA Technologies
------Original Message------
From: Roger Hecq
To: DB2-L@IDUGDB2-L.ORG
ReplyTo: IDUG DB2-L
Subject: Re: [DB2-L] [AD] IDUG EMEA - City Airport Train
Sent: Aug 20, 2010 9:42 AM

What a concept!.  Almost every airport in Europe has this service.  Very convenient.  Too bad that none of the US airports have a similar service.  Maybe they don't have enough passengers!? Roger Hecq MF IB USA DB Support 203-719-0492 / 19-337-0492
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] [...]56_0A18F096E689AC43BB2F52DAE5674D60086450BB@USILMS14.ca.com 15300 548 39_Re: [AD] IDUG EMEA - City Airport Train14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 20 Aug 2010 10:50:43 -0400535_us-ascii JFK, in New York, has the AirTrain. Though I don't believe it checks baggage through.

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq
Sent: Friday, August 20, 2010 10:43 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] [AD] IDUG EMEA - City Airport Train

What a concept!. Almost every airport in Europe has this service. Very convenient. Too bad that none of the US airports have a similar service. Maybe they don't have enough passengers!? [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF94C@MAIL02.fisalan.nycnet 15849 37 11_I blame You10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Fri, 20 Aug 2010 10:51:09 -0400524_UTF-8 Sometime in the mid 1990's (or was it earlier) someone came up with the idea to use artificial partition numbers, tie them to date ranges and then make applications aware of the physical design underlying tables. The most common form of this is the load process that figures out the date, then runs a piece of code to identify a partition and then does the insert or spits out the load file. The advantage being that you can use 24 partitions to hold any two years of data never needing to change the tablespace. [...]53_1061126003701044.WA.jorghluekebcbsmn.com@www.idug.org 15887 664 39_Re: [AD] IDUG EMEA - City Airport Train10_Roger Hecq18_Roger.Hecq@UBS.COM31_Fri, 20 Aug 2010 11:10: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_1A700EEF49343148A08879B1E3CA5BA615FE1710@NSTMC101PEX1.ubsw.net 16552 65 39_Re: [AD] IDUG EMEA - City Airport Train0_24_carol.sutfin@REGIONS.COM31_Fri, 20 Aug 2010 10:23:55 -0500706_UTF-8 Sounds much better than casinos and strip clubs in Las Vegas.

Carol Sutfin
Corporate DBA
Regions Financial Corp.
(205)261-5214
carol.sutfin@regions.com

From: Max Scarpa
To: DB2-L@IDUGDB2-L.ORG
Date: 08/20/2010 10:00 AM
Subject: Re: [DB2-L] [AD] IDUG EMEA - City Airport Train
Sent by: IDUG DB2-L

And very very close to Hilton there's a nice park for runners (hope I'll
have a time for a run), nice restaurants, one of the biggest cathedral in
Vienna, the Opera house, nice italian ice-cream vendors and 2
of main streets for shopping......and more ( I hadn't the time to explore
more sorry ). For [...]71_OF5BC8FD2D.1D39F6D0-ON86257785.0054797E-86257785.005496AF@corp.rgbk.com 16618 545 39_Re: [AD] IDUG EMEA - City Airport Train35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Fri, 20 Aug 2010 11:24:11 -0400499_iso-8859-1 Newark airport, take the Airtrain to the NJ transit station, and that right into NYC

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_8EF10C4C5E8D444AAF4E5DF14129DA4F@DellNotebook3 17164 717 39_Re: [AD] IDUG EMEA - City Airport Train13_Phil Grainger26_phil.grainger@COGITO.CO.UK31_Fri, 20 Aug 2010 12:00:56 -0400576_us-ascii And PHL has SEPTA, ORD/MDW have METRA :)
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

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roger Hecq
Sent: 20 August 2010 16:11
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] [AD] IDUG EMEA - City Airport Train [...]60_4440F5DA00E3F3459BBCB97431B91B6612B902CCAF@MAILR004.mail.lan 17882 41 33_IBM Reorg with Switch Phase Issue10_Tom Glaser25_tom_glaser@MASTERCARD.COM31_Fri, 20 Aug 2010 12:23:12 -0400608_UTF-8 All,

We have an issue with the IBM Reorg Utility with the switch phase....any ideas?

We run the IBM online Reorg utility against a 64 partitioned table space. When the utility gets to the "switch" phase, it appears to hang for a period of time, maybe 1 minute. However, during this minute is when online transactions start to fail. Using the IBM Log Analyzer tool, we can see the Reorg utility executing SQL against systablepart & sysindexpart to modify the IPREFIX column...J or I, and SQL against systablepart to update REORG_LR_TS column as well. The 1 minute of delay repeatedly [...]56_5967935993283158.WA.tomglasermastercard.com@www.idug.org 17924 200 39_Re: [AD] IDUG EMEA - City Airport Train13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Fri, 20 Aug 2010 09:58:11 -0700490_utf-8 Portland has the red line MAX train from/to the airport, passing through downtown and on to Beaverton, with many connections to buses and other trains. For $2.30, you can be within ½ mile of nearly any destination across 3 counties. Plan your trip at www.trimet.org.

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rasmussen, Steen
Sent: Friday, August 20, 2010 7:48 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] [AD] IDUG EMEA - City Airport Train [...]64_927FE8790B320742927BCF9ADF7AE48A19A57946BA@PDXMAIL.pacificorp.us 18125 467 39_Re: [AD] IDUG EMEA - City Airport Train13_Willie Favero21_wfavero@ATTGLOBAL.NET31_Fri, 20 Aug 2010 12:16:55 -0500630_ISO-8859-1 and once you are at Penn Stations, it's very easy to get to any place in
Manhattan...

Joel, that's the only way I get to New York anymore... very easy and
fast... if you fly Continental, you can even check in at NJ Transit station.

Willie

On 8/20/2010 10:24 AM, Joel Goldstein - Responsive Systems wrote:
> Newark airport, take the Airtrain to the NJ transit station, and that
> right into NYC
> Joel Goldstein
> Responsive Systems
> IBM Gold Consultant
> Buffer Pool Tool for DB2, the worldwide industry standard
> Performance software that works...... Predicts IO [...]30_4C6EB887.8030502@attglobal.net 18593 432 93_Re: Next Chat with the lab: DB2 pureScale -- Chat with the Lab, DB2 LUW see below to register12_Phil Gunning19_pkgunning@GMAIL.COM31_Fri, 20 Aug 2010 14:03:50 -0400573_us-ascii Recommendations

by Susan Visser on
Tuesday, August 17, 2010 at 10:25am

The next Chat with the Lab session will focus on DB2 pureScale:

DB2 pureScale - Always-on, Elastic Database Cluster

Date: Tue, August 31, 2010
Time: 11:30 AM - 1:00 PM Eastern (GMT-04:00)
10:30 AM Central / 8:30 AM Pacific / 16:30hrs London / 17:30hrs Frankfurt,
Paris / India 9 PM
Speakers: Sal Vella (IBM), Drew Bradstock (IBM)
Host: Rav Ahuja (IBM)
Description: [...]35_00a501cb4092$0c902fd0$25b08f70$@com 19026 106 53_Re: performance of VARCHAR columns as part of IN LIST14_Larry Kintisch17_LKint@VERIZON.NET31_Fri, 20 Aug 2010 14:21:20 -0400560_us-ascii Hi John,
I'm going to take a guess and make some suggestions.

When using an IN list, my recollection is that a temporary file of
the list values is created, sorted to eliminate duplicates, and the
remaining values are indexed by some "sparse" index perhaps of 4
values: first, 25th percentile, 50th, 75th; then each row being
tested goes through the sparse index and then tests the remaining
values in its set. [For 100 in the list, each set is 25 so with a
False there are 25 tests; With a True on average 13 tests]. [...]40_0L7G002VOQZOWP70@vms173017.mailsrvcs.net 19133 52 93_Re: Next Chat with the lab: DB2 pureScale -- Chat with the Lab, DB2 LUW see below to register14_Bruce Lightsey25_Bruce.Lightsey@ITS.MS.GOV31_Fri, 20 Aug 2010 13:56:27 -0500654_US-ASCII for those of us who don't "facebook" are there any instructions ?

Phil Gunning
COM> To
Sent by: IDUG DB2-L@IDUGDB2-L.ORG
DB2-L cc
ORG> Subject
Re: [DB2-L] Next Chat with the lab:
DB2 pureScale -- Chat with the Lab,
08/20/2010 01:03 DB2 LUW see below to register
PM

Please respond to
IDUG DB2-L
ORG>

Register
To register and receive instructions for attending this webinar, point your
browser to:
http://www.facebook.com/l.php?u=https%3A%2F%2Fevents.webdialogs.com%2Fregister.php%3Fid%3Dcc6c6565ef%26l%3Den-US&h=93bb0 [...]73_OFAAEB35D1.511A4271-ON86257785.0067EA72-86257785.00680B9F@its.state.ms.us 19186 173 50_DB2 Z/os V8 - 10 jump experiences, recommendations13_Case, Missy J24_Missy.Case@FIRSTDATA.COM31_Fri, 20 Aug 2010 14:26:50 -0500610_us-ascii All,

I was at an IBM presentation a few weeks ago where they stated that
jumping from DB2 V8 to V10 was 'possible' but not highly recommended.
They also threw 'an IPL is required' when skipping to V10 into the
discussion. Can anyone prove/verify/disprove this theory?

We have many production DB2's, one of them is five-way data sharing.
From the discussion in the class, all members must be upgraded at skip
level at the same time, i.e. 'we don't recommend running V8 & V10
concurrently', or you can't - I was not precise enough on this point in
my notes...... [...]64_3C8EB6E657433F439F22BB22A069206802E145C9@WFDROMPXMASRV01.1DC.COM 19360 28 93_Re: Next Chat with the lab: DB2 pureScale -- Chat with the Lab, DB2 LUW see below to register15_Cristian Molaro18_cristian@MOLARO.BE31_Fri, 20 Aug 2010 15:30:13 -0400642_- Hi,

To register and receive instructions for attending this webinar, point your browser to:
https://events.webdialogs.com/register.php?id=cc6c6565ef&l=en-US

Have a great day,
Cristian

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]50_0713477354344841.WA.cristianmolaro.be@www.idug.org 19389 36 21_Re: Hitachi VTL & DB213_Steve Runtsch26_steve.runtsch@ASSURANT.COM31_Fri, 20 Aug 2010 16:06:13 -0400454_UTF-8 Hi Mark,

I passed your note on to out Storage Administrator. He has a couple of questions and comments which I've listed below.

What release of the Bustech Virtuent software are you running? Are you seeing any performance problems outside of DB2?

We're running Virtuent 7 and we're seeing very long mount times and poor I/O response time for normal batch production jobs. I've not heard of any special impact to DB2 here. [...]58_6236066842365424.WA.steve.runtschassurant.com@www.idug.org 19426 367 54_Re: DB2 Z/os V8 - 10 jump experiences, recommendations14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Fri, 20 Aug 2010 17:23:31 -0400429_us-ascii You've got five-way data sharing that you can't take down, and you're proposing to skip from V8 to V10? Somebody in your organization is lots braver than I am.

________________________________
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Case, Missy J
Sent: Friday, August 20, 2010 3:27 PM
To: DB2-L@IDUGDB2-L.ORG
Subject: [DB2-L] DB2 Z/os V8 - 10 jump experiences, recommendations [...]64_1BA00CBB414DA34AA25ED82F4647CDE203665DF951@MAIL02.fisalan.nycnet 19794 27 53_Re: DB2 luw(9.1): Tablespace container redistribution13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Fri, 20 Aug 2010 18:18:06 -0400603_UTF-8 Ian

thanks for the info I will look into this option as it seems to offer a quick way to clean-up a lot of the bad definitions

regards

Jack

_____________________________________________________________________
* IDUG Australasia * Sydney, Australia * 1-3 September 2010 * http://IDUG.ORG/AU *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________ [...]55_7820211252926106.WA.jackrcampbellyahoo.com@www.idug.org 19822 70 53_Re: performance of VARCHAR columns as part of IN LIST13_Terry Purcell18_tpurcel@US.IBM.COM31_Fri, 20 Aug 2010 23:09:05 -0400336_UTF-8 John,

This degree of regression is not expected, and therefore I anticipate there is something else that may be contributing here. But I don't want to speculate.

It would be interesting to see both cases, and if you contact me offline I can gather more information and have our performance team look into this. [...]50_6841208338871111.WA.tpurcelus.ibm.com@www.idug.org 19893 67 61_AUTO: Truls Pedersen is out of office. (returning 24.08.2010)14_Truls Pedersen16_tpeders6@CSC.COM31_Sat, 21 Aug 2010 09:06:25 +0000721_ISO-8859-1 I am out of the office until 24.08.2010.

På reise til København.

Note: This is an automated response to your message "DB2-L Digest - 20 Aug
2010 to 21 Aug 2010 (#2010-226)" sent on 21/8/10 6:00:00.

This is the only notification you will receive while this person is away.
_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________ [...]65_OFFDB59C5A.8A30D199-ON80257786.003206E8-80257786.003206E8@csc.com 19961 104 53_Re: performance of VARCHAR columns as part of IN LIST12_Isaac Yassin20_yassini@BEZEQINT.NET31_Sat, 21 Aug 2010 13:36:40 +0300548_UTF-8 Hi,
You can take a look at ZPARM INLISTP as well.
Maybe it's too low for your needs.
Isaac Yassin

-----Original Message-----
From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Terry Purcell
Sent: Saturday, August 21, 2010 6:09 AM
To: DB2-L@IDUGDB2-L.ORG
Subject: Re: [DB2-L] performance of VARCHAR columns as part of IN LIST

John,

This degree of regression is not expected, and therefore I anticipate there is something else that may be contributing here. But I don't want to speculate. [...]35_00fd01cb411c$bdf79440$39e6bcc0$@net 20066 229 39_Re: [AD] IDUG EMEA - City Airport Train10_Max Scarpa16_mscarpa@CESVE.IT31_Sat, 21 Aug 2010 14:11:19 +0200565_UTF-8 For sure Portland's train has a very nice name !

Max Scarpa

***********************************************************************
Attend IDUG EMEA 2010: feel the difference between 'a DB2 Conference' and
'*THE* DB2 Conference'.
8-12 November 2010, Vienna:

Miller's DB2 10 migration
Campbell's DB2 10 for sysprogs
Hrle's Smart Analyzer
Purcell's Db2 V9 and 10 SQL
Eaton's LUW monitoring
Catteral's Data Warehousing
Scarpa's plumbery performances hints

......and more ! See EMEA online schedule at: [...]66_OF9F397388.B10A9BAC-ONC1257786.004249D7-C1257786.0042EC17@cesve.it 20296 101 79_Black Box Technology Investments (Mining Legal, Technology Risks, Lowering TCO)9_Ramu Iyer18_ramu_asm@YAHOO.COM31_Sat, 21 Aug 2010 07:14:36 -0700610_iso-8859-1 I am in learning mode trying to understand the deal structure of Teradata that has a compelling value proposition for data warehousing and business intelligence for small or large enterprises. Unlike IBM/DB2, it is possible that one does the math, the Total Cost of Ownership (TCO) of a black box, proprietary solution that is a managed service can be somewhat steep. It is regarded as a black box offering consisting of the entire system and technology stack and is typically managed by Teradata resources. Definitely an option when someone at the CxO level wants to "sleep at night".  At the [...]43_719019.55257.qm@web65612.mail.ac4.yahoo.com 20398 80 54_Re: DB2 Z/os V8 - 10 jump experiences, recommendations10_Max Scarpa16_mscarpa@CESVE.IT31_Sat, 21 Aug 2010 12:44:28 -0400359_UTF-8 Hi Missy

Well... maybe some beta tester could say more but probably they are under non disclosure agreement.

Anyway from few informations available (SHARE, IDUG NA) yes you can jump from V8 NFM (mandatory) to DB2 10 skipping V 9. IBM forecasts that, as happened for v5-> V7 migration, maybe a 20% of customers will do V8-DB2 10 jump. [...]48_4977945728989758.WA.mscarpacesve.it@www.idug.org 20479 109 53_Re: performance of VARCHAR columns as part of IN LIST14_Larry Kintisch17_LKint@VERIZON.NET31_Sat, 21 Aug 2010 19:22:21 -0400342_us-ascii Hi Terry,
Your comments come as a surprise! Does the lack of SORT for the
IN list values if non-matching also occur if there is an index column
that is "beyond" the Match Columns in these examples?

EX (1), if an index was on A,B,C,D,E and we did a SELECT F FROM T
WHERE A=? AND B>? AND D IN(...100 values...) [...]40_0L7I0061VZLP3Z20@vms173003.mailsrvcs.net