1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l March 2009, week 5 2 620 25_Re: FASTSWITCH NO no more14_Grainger, Phil20_Phil.Grainger@CA.COM31_Sun, 29 Mar 2009 11:23:05 +0100483_iso-8859-1 Problem is, if you have a CLONE you CANNOT do a FASTSWITCH YES online reorg of either the base OR the clone





You HAVE to do FASTSWITCH NO to keep the I/J bits synchronised......





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





Ditton Park Riding Court Road Datchet Slough SL3 9LL [...] 623 87 44_Re: Reorg with Discard - Does it log changes14_Grainger, Phil20_Phil.Grainger@CA.COM31_Sun, 29 Mar 2009 11:24:43 +0100518_iso-8859-1 The way I read the original question, it was

"If I run a REORG with DISCARD are the rows that are deleted logged?"

Well, the answer to that has to be NO - regardless of SHRLEVELS or LOG settings

Why?

Well, reorg is UNLOADING ALL of the data, and splitting the "keep" and "discard" rows as it does so. Then ONLY the "keep" rows are reloaded into the table space. Now, THEY can be logged (with a LOG YES) reorg, but the deleted rows are just "gone" and will NOT be in the log [...] 711 261 34_Re: Multi Ftech Issue with VARCHAR14_Grainger, Phil20_Phil.Grainger@CA.COM31_Sun, 29 Mar 2009 11:24:45 +0100505_iso-8859-1 SYNC IS actually required - just not documented anywhere that I know of (and DCLGEN doesn't add it either.....)

Phil Grainger CA Senior Principal Product Manager Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com

Ditton Park Riding Court Road Datchet Slough SL3 9LL

CA plc 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. [...] 973 510 25_Re: FASTSWITCH NO no more14_Grainger, Phil20_Phil.Grainger@CA.COM31_Sun, 29 Mar 2009 11:24:49 +0100539_us-ascii Hi Cathy (et al)





Take a look at PK55180 J





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc 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. [...] 1484 33 34_Re: DSN1COPY from DB2 v8 to DB2 v914_Peter Vanroose17_pvanroose@ABIS.BE31_Sun, 29 Mar 2009 13:13:25 +0000491_ISO-8859-1 On Thu, 26 Mar 2009 18:52:56 +0000, Mukesh Jain wrote: > ... couple of tablespaces are LOB tablespace and they have millions of > records. So unloading LOB takes hours.

I would think it's possible to use UNLOAD/LOAD for the simple/segmented tablespaces and DSNCOPY1 for the LOB tablespaces. Never tried this, but from my understanding of v8-v9 differences, there should be no problem overlying v9 LOB tablespaces with v8 data using DSN1COPY. Can anybody confirm this? [...] 1518 35 34_Re: Multi Ftech Issue with VARCHAR10_Todd Burch17_toddburch@MAC.COM31_Sun, 29 Mar 2009 08:29:03 -0500359_US-ASCII Thanks Phil. I'll add this bit to my Tech Note as well. If I have time, I'll try to run through all the languages and provide examples of each.

Todd DB2 Support

On Mar 29, 2009, at 5:24 AM, Grainger, Phil wrote:

SYNC IS actually required - just not documented anywhere that I know of (and DCLGEN doesn't add it either.....) [...] 1554 132 34_Re: DSN1COPY from DB2 v8 to DB2 v914_Grainger, Phil20_Phil.Grainger@CA.COM31_Sun, 29 Mar 2009 14:37:23 +0100458_iso-8859-1 Not sure the "links" between the base data and the LOB data would be correct though - if you reload the base data then the rows will be at different rids

Also not tried this, but it does sound "iffy"

Phil G CA

________________________________

From: DB2 Data Base Discussion List on behalf of Peter Vanroose Sent: Sun 29/03/2009 14:13 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DSN1COPY from DB2 v8 to DB2 v9 [...] 1687 80 18_{ADMIN} New Footer10_Mark Labby21_mlabby.idug@GMAIL.COM31_Sun, 29 Mar 2009 23:02:39 -0400400_ISO-8859-1 You will notice the Footer changing on the list messages. As part of the IDUG family, the DB2-L listserv is pleased to support the IDUG North American Conference taking place in Denver this May. We will be attempting to highlight some of the comments from attendees of last yearıs conference and providing a convenient link for list members to get full details for this annual event. [...] 1768 353 25_Re: FASTSWITCH NO no more13_Michael Ebert18_mebert@AMADEUS.COM31_Mon, 30 Mar 2009 07:32:53 +0000609_ISO-8859-1 Why is that? We cloned our SAP subsystems occasionally and never had problems (V7).

Dr. Michael Ebert DB2 & Oracle Database Administrator aMaDEUS Data Processing Erding / Munich, Germany







"Grainger, Phil" To DB2-L@WWW.IDUGDB2-L.ORG cc

bcc

Subject Re: [DB2-L] FASTSWITCH NO no more









"Grainger, Phil" Please respond to : DB2 Database Discussion list at IDUG Sent by: DB2 Data Base Discussion List 29-03-09 12:23 [...] 2122 89 47_Re: [LUW] Odd CLP auto-commit OFF (+C) behavior13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Mon, 30 Mar 2009 01:17:33 -0700548_windows-1252 On Fri, Mar 27, 2009 at 1:22 PM, Jeffrey Benner, DB2 DBA wrote: > Thank you gentlemen for your responses but I have tested them and there is > still a problem. > > @Chad: I disagree, I think that the docs clearly state that with +c active, > if there is a failure in one command, all preceding statements will be > rolled back. Docs don't state that the behavior changes with the -f > parameter usage. If you think there is a way within a db2 CLP -f file to put > conditional ROLLBACK logic, please share [...] 2212 474 25_Re: FASTSWITCH NO no more11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Mon, 30 Mar 2009 09:26:27 +0100531_iso-8859-1 I think Phil is referring to the new V9 Clone tables.

Regards Mike Turner Email: michael_turner@compuserve.com Tel: +44 (0)1565-873702 Web: www.michael-turner.ltd.uk Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159 44. ----- Original Message ----- From: Michael Ebert Newsgroups: bit.listserv.db2-l To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Monday, March 30, 2009 8:32 AM Subject: Re: [DB2-L] FASTSWITCH NO no more [...] 2687 157 43_Presentations from Colorado DB2 Users Group10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 30 Mar 2009 04:34:41 -0400469_iso-8859-1

Hi DB2 User,

Here is a non-profit Blog about DB2 for z/OS (IBM mainframes): http://db2usa.blogspot.com

Last update on Monday, March 30th 2009



Here are several presentations from Colorado DB2 Users Group, available on CDB2UG website:



- DB2 9 for z/OS Migration Planning and Experiences by John Campbell





- Optimizing System Performance Using DB2 Statistics Trace by John Campbell [...] 2845 27 82_AUTO: Gareth Jones is out of the office until 8 April 2009. (returning 08/04/2009)14_Gareth Z Jones19_jonesgth@UK.IBM.COM31_Mon, 30 Mar 2009 10:05:21 +0100331_US-ASCII I am out of the office until 08/04/2009.

I'm out of the office on leave and on business 6 -7 April. I'll reply to your message as soon as possible upon my return.



Note: This is an automated response to your message "DB2-L Digest - 28 Mar 2009 to 30 Mar 2009 (#2009-93)" sent on 30/3/09 2:00:06. [...] 2873 46 27_lock table share - question13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Mon, 30 Mar 2009 11:10:03 +0200398_us-ascii We have a discussion on how to understand "lock table xxx in share mode" From time to time some PLI-programs abends with -911 00C9008E.

I am now setting up datasharing, and I have problems with these batch jobs. I had to stop the 2.member to get these batch jobs running. The first statement is table in share mode, and it seams that it's this statement we have problems with. [...] 2920 94 31_Re: lock table share - question11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Mon, 30 Mar 2009 10:48:20 +0100611_iso-8859-1 Hi Hanne

LOCK TABLE xxx IN SHARE MODE attempts to take a Share lock (S-lock) on the entire table (Segmented tablespace) or tablespace (other tablespace types). If successful this will prevent any updating of the table(space) (taking of X-locks) by other transactions until it is released. If another transaction holds an X-lock on any page when you attempt the LOCK TABLE, the LOCK TABLE must wait until the X-lock is released and may time out (-911) if the X-lock is not released within the timeout interval. There may be many X-locks on different pages blocking the LOCK TABLE and they [...] 3015 997 25_Re: FASTSWITCH NO no more14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 30 Mar 2009 11:03:13 +0100526_iso-8859-1 Indeed I was - that's why CLONE was in capitals J





Phil Grainger

CA

Senior Principal Product Manager

Phone: +44 (0)1753 577 733

Mobile: +44 (0)7970 125 752

eMail: phil.grainger@ca.com





Ditton Park Riding Court Road Datchet Slough SL3 9LL





CA plc 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. [...] 4013 104 44_Antwort: [DB2-L] lock table share - question0_17_Rolf.Drees@GAD.DE31_Mon, 30 Mar 2009 13:57:25 +0200435_ISO-8859-1 Hello Hanne,

in case of -911 take a look at the holder of the resource. In your case the package of the holder should be bound with RELEASE (COMMIT) instead of DEALLOC.

When an update occurs the Table (-space/partition) is locked with IX and the row/page is locked with X. The page/row-lock is always released at commit, but the "higher" locks are released depending on the RELEASE-Option of the package. [...] 4118 300 25_Re: FASTSWITCH NO no more13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Mon, 30 Mar 2009 15:16:18 +0200414_us-ascii Ah, I see. I thought you were just SHOUTING... ;o)

4 more days 'til Friday. Who'd have thought, eh?

Cheers,



Raymond

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Grainger, Phil Sent: 30 March 2009 11:03 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] FASTSWITCH NO no more

Indeed I was - that's why CLONE was in capitals :) [...] 4419 236 47_Re: [LUW] Odd CLP auto-commit OFF (+C) behavior23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Mon, 30 Mar 2009 10:44:46 -0500326_windows-1252 Ian, thanks, your latest suggestions work. They test out great. I am writing up the experience now. Basically, if you might want to rollback, do so manually after the script has died, within the same connection state. Do not end the script with QUIT/CONNECT RESET/TERMINATE if a rollback might be attempted. [...] 4656 26 33_Re: Dynamic Prefetch DB2 z/OS v 811_Mike Bracey22_mike_bracey@UK.IBM.COM31_Mon, 30 Mar 2009 17:57:13 +0000490_ISO-8859-1 Hello A couple of points: 1. Dynamic pre-fetch pages are subject to the VPSEQT threshold. 2. The eligibility rules for dynamic pre-fetch apply for forward and backward scans of index or data so it is the absolute distance to the next page that matters.

_____________________________________________________________________

* IDUG 09 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 4683 195 48_Heart of Texas DB2 Users Group meeting - April 78_Lo, Mary17_mary.lo@TGSLC.ORG31_Mon, 30 Mar 2009 13:18:21 -0500560_us-ascii Our 2nd quarterly meeting will be held on April 7 (Tuesday) at Teachers Retirement System (1000 Red River St. Room 110 West Building). We have exciting speakers in the line-up: DB2 rising star, David Simpson, who was IDUG Best Speaker, and SQL guru, Joe Celko.

08:30 - 9:00 Registration and Breakfas 9:00 - 9:10 Business and Announcements 09:10 - 10:20 Physical Database Design for the 21st Century David Simpson, Themis, Inc. 10:20 - 10:40 Break 10:40- 11:50 Trees & Hierarchies in SQL Joe Celko 11:50 - 12:00 Closing - Raffle for Prizes [...] 4879 99 42_DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL15_Douwe van Sluis21_d.b.van.sluis@HOME.NL31_Mon, 30 Mar 2009 21:04:55 +0200423_us-ascii Dear list,

A colleague was surprised to find no support for ALTER TABLE to remove the NOT NULL clause of a COLUMN. I thought that should be possible via ALTER TABLE ALTER COLUMN. But so far we failed to find the syntax to do so. The syntax diagram of ALTER TABLE shows no syntax for this. Any ideas on how to achieve this via ALTER TABLE? We know how to make the change via UNLOAD, DROP TABLE, RELOAD. [...] 4979 207 46_Re: DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL10_Roger Hecq18_Roger.Hecq@UBS.COM31_Mon, 30 Mar 2009 15:35:54 -0400382_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. [...] 5187 144 31_Re: Cost of dynamic SQL prepare10_Nicola Nur17_nick_nur@YAHOO.CA31_Mon, 30 Mar 2009 13:02:32 -0700612_us-ascii Thank you Chad. Very helpful thoughts. Much appreciated. Nick



--- On Fri, 3/27/09, Chad A. Walmer wrote:

> From: Chad A. Walmer > Subject: Re: [DB2-L] Cost of dynamic SQL prepare > To: DB2-L@WWW.IDUGDB2-L.ORG > Received: Friday, March 27, 2009, 2:56 PM > Nick, > I don't have a formula or ROT but have seen anywhere > from 20ms to over 100ms additional CPU for a statement that > needs to be prepared. You can test individual queries by > using an accounting trace and running the SQL at least > twice. Make sure that the first one [...] 5332 232 47_Re: [LUW] Odd CLP auto-commit OFF (+C) behavior23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Mon, 30 Mar 2009 15:03:10 -0500593_windows-1252 I have written up our findings here http://www.ebenner.com/db2dba_blog/?p=618 with credits given to Ian and Chad, thanks all.

On Mon, Mar 30, 2009 at 03:17, Ian Bjorhovde wrote:

> On Fri, Mar 27, 2009 at 1:22 PM, Jeffrey Benner, DB2 DBA > wrote: > > Thank you gentlemen for your responses but I have tested them and there > is > > still a problem. > > > > @Chad: I disagree, I think that the docs clearly state that with +c > active, > > if there is a failure in one command, all preceding statements will be > [...] 5565 208 31_Re: Cost of dynamic SQL prepare12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 31 Mar 2009 03:34:39 +0000577_windows-1252 For a dynamic SQL statement, the rule of thumb gets to more like the length of a rubber band, very stretchy. For simple SQL statements, the rule of thumb from long ago was that the full prepare is more like 20 to 25 times a simple SQL statement. When you need to parse the SQL, retrieve information about the table, the columns, indexes, statistics, and then work out the best access path, it's not cheap. Even the prepare from the dynamic statement cache means an extra trip across memory. For a singleton select statement, dynamic SQL means open, fetch and [...] 5774 483 48_Antwort: Re: [DB2-L] Cost of dynamic SQL prepare0_17_Rolf.Drees@GAD.DE31_Tue, 31 Mar 2009 07:35:26 +0200387_ISO-8859-1 Hello Nick,

some years ago I've seen that CPU-consumption increases at prepare when the runstats-data are very detailed. We ran Runstats with FREQVAL and COUNT 50, which lead to many rows in SYSCOLDIST. The cpu-costs for prepare was much better when we changed COUNT to 10, especially for very simple access-pathes (e. g. reading 1 row via matching-index-access). [...] 6258 93 48_Antwort: Re: [DB2-L] Cost of dynamic SQL prepare0_17_Rolf.Drees@GAD.DE31_Tue, 31 Mar 2009 07:42:56 +0200363_ISO-8859-1 Helllo Roger,

the problem is, that you can qualify with a simple sql only 1 row or even 1 million rows. If you read only 1 row prepare might use 95 % and open/fetch/close 5 %. When qualifying 1 mio. rows the prepare perhaps take less than 1 % of the costs. SQL is a very complex language, that's why a often heard answer is: It depends.... [...] 6352 284 54_AW: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL35_=?iso-8859-1?Q?Kurtz=2C_R=FCdiger?=28_Ruediger.Kurtz@HUK-COBURG.DE31_Tue, 31 Mar 2009 09:47:31 +0200336_iso-8859-1 Roger,

you might want to be careful; we recently tried to change a NOT NULL column to NOT NULL WITH DEFAULT and Change Manager created an empty worklist, i.e. no changes but an "End of alter Table Section". The problem has been fixed in the meantime (PTF BPU1573 ), but still .... We run CHANGE MANAGER 09.02.00. [...] 6637 133 47_Re: [LUW] Odd CLP auto-commit OFF (+C) behavior14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 31 Mar 2009 21:13:17 +1100384_ISO-8859-1 One thing you might like to add is that the db2 CLP, command window etc don't talk to DB2 directly. There is a background processor (db2bp) which acts as the intermediate between your front end and DB2. db2bp doesn't see the premature termination of a set of SQL, the front end leaves the back-end connection alive so that you can do whatever recovery is appropriate. [...] 6771 46 46_Re: DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Tue, 31 Mar 2009 21:13:17 +1100545_US-ASCII The reason it can't be done is because a nullable column has a 'null indicator byte'. Making a column nullable would imply stuffing the null indicators into every row - even if they show "this column is NOT NULL'

James Campbell

On 30 Mar 2009 at 21:04, Douwe van Sluis wrote:

> > Dear list, > > A colleague was surprised to find no support for ALTER TABLE to remove the NOT NULL clause > of a COLUMN. I thought that should be possible via ALTER TABLE ALTER COLUMN. But so far > we failed to find the syntax to [...] 6818 31 46_Re: DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL15_Douwe van Sluis21_d.b.van.sluis@HOME.NL31_Tue, 31 Mar 2009 10:48:30 +0000300_ISO-8859-1 James,

DB2 can handle this. It is possible to go from CHAR to VARCHAR, which means adding two extra bytes to the column. DB2 uses versioning to handle this. My advice is tough to always do a REORG after these kind of changes. Not necessary, but saver if you can spend a REORG. [...] 6850 34 31_Re: Cost of dynamic SQL prepare15_Patrick Bossman25_patrick.bossman@GMAIL.COM31_Tue, 31 Mar 2009 12:56:18 +0000352_ISO-8859-1 I want to add - probably the best way to determine the prepare cost is to EXPLAIN the SQL statement. This will go through prepare without executing the SQL. However, EXPLAIN will incur overhead of writing the explain records out.

When you have a complex query and you want to isolate the prepare cost, this is the way I'd do it. [...] 6885 407 58_Re: AW: [DB2-L] DB2 z/OS V9 - ALTER COLUMN REMOVE NOT NULL10_Roger Hecq18_Roger.Hecq@UBS.COM31_Tue, 31 Mar 2009 09:08:23 -0400382_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. [...] 7293 567 52_Re: Antwort: Re: [DB2-L] Cost of dynamic SQL prepare14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Tue, 31 Mar 2009 15:10:43 +0200585_iso-8859-1 If you use Dynmaci SQL, Use also parametermarkers to reduce cpu-overhead.





DB2 CPU usage (without appl CPU) in miliseconds









Dynamic Statemtn Cache active ?

DB2 CPU Without parameter Marker

With parameter Marker without CAST

With parameter Marker and CAST

Devlopment system

N

370 ms

184 ms

- 50%

188 ms

- 49 %

Integration system

Y

487 ms

180 ms

- 63 %

166 ms

- 66 %

Production System [...] 7861 267 33_Re: Dynamic Prefetch DB2 z/OS v 811_Hardy, Dale14_DHardy@MIB.COM31_Tue, 31 Mar 2009 09:39:52 -0400440_us-ascii My notes also indicate that datasets in multiple extents will cause dynamic prefetch turn off at the end of an extent and have to start over in the next extent.

________________________________

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Joel Responsive Systems Sent: Friday, March 27, 2009 4:18 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Dynamic Prefetch DB2 z/OS v 8 [...] 8129 61 28_CPU used by DB2 image copies16_Pirotton Francis26_francis.pirotton@SKYNET.BE31_Tue, 31 Mar 2009 15:09:12 +0000349_windows-1252 We are surprised by the CPU usage of the DB2 image copies.

Our FULL image copies are executed by three jobs running in parallel. In total we have 2493 tablespaces and the total number of pages taken in copy is: 206.196.633. (Most of them are 4k pages but not all).



The details of each jobs can be found here: [...] 8191 91 44_Re: [Ad] DB2 Newbee Training - NYC Apr 14-1714_Larry Kintisch17_LKint@VERIZON.NET31_Tue, 31 Mar 2009 12:19:29 -0400612_us-ascii Hi Listers,

Today I learned IBM --WILL-- run these NYC classes: CE030 Apr 14-15; CE120 Apr 16-17 [see my original post below]. If you are new to DB2, or have a new manager that needs the overview of DB2 and the SQL Workshop, please register for these classes quickly!

Also, the following week I'm scheduled to teach the re-numbered course CV721 [was CF820] "DB2 9 for z/OS Application Programming Workshop" in NYC. This is the appropriate follow-up course for developers in a COBOL or PL/I environment who embed SQL in their code, especially those new to the z/OS environment working [...] 8283 38 16_DB2/SMS QUESTION13_Robert Knight28_bknight@REMOTEDBAEXPERTS.COM31_Tue, 31 Mar 2009 12:44:11 -0400352_us-ascii I ran across this in an environment and would greatly appreciate clarification on this issue.

I have a DB2 storage group set up DBT2A and has been defined with '*' for SMS processing. No problem.

But I noticed on the cluster definition Storage class DB2TSTCL.

Now this is the same definition within the ACS routines. [...] 8322 63 20_Re: DB2/SMS QUESTION11_Moore, Tony15_TMoore@IKON.COM31_Tue, 31 Mar 2009 13:01:11 -0400467_us-ascii Bob, I can't think of any problems with a difference in names between the DB2 storage group and the SMS storage group and/or class... In fact, I don't believe I ever had a set up where they have been the same.





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Robert Knight Sent: Tuesday, March 31, 2009 12:44 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] DB2/SMS QUESTION [...] 8386 65 34_Re: DSN1COPY from DB2 v8 to DB2 v911_Rob Barbour22_rbarbour@ESAIGROUP.COM31_Tue, 31 Mar 2009 15:04:40 -0400235_ISO-8859-1 Regarding your simple to segmented issue, the BCV5 product does what you want and handles differences, while saving up to 90% in copy time.

See web page for more info http://www.esaigroup.com/products/bcv5.htm

8452 30 22_Re: DSNTIAUL in V8 NFM10_Nicola Nur17_nick_nur@YAHOO.CA31_Tue, 31 Mar 2009 12:32:40 -0700507_us-ascii Hi colleagues We are running DB2 z/OS v 8.1 in NFM. I was expecting to to see some gain in CPU in using DSNTIAUL jobs of v8.1 compared to v7.1. But I am disappointed. I have read that the CPU savings in DSNTIAUL in v8.1 v DSNTIAUL v7.1 is ranging from 30% to 70%. I did not see any of that. On the contrary some of the unload jobs have increased their CPU in v8.1. It does not make sense. Any wisdom or thought why is that? I thought once multi row fetch kicks in DSNTIAUL and DSNTEP4 we reap [...] 8483 103 32_Re: CPU used by DB2 image copies14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Tue, 31 Mar 2009 19:39:41 +0000318_windows-1252 What image copy product? What image copy options? Are TAPEUNITS specified in the IC control cards? SHRLEVEL Reference or Change? CHECKDATA option coded?

That all being said If we assume that you can copy 15 tracks a second the elapsed time seems to correctly map to the number of pages moved. [...] 8587 48 32_Re: CPU used by DB2 image copies16_Pirotton Francis26_francis.pirotton@SKYNET.BE31_Tue, 31 Mar 2009 20:33:14 +0000363_windows-1252 Hello Avram,

First thank you for your answer.

- We are in DB2 V8. - We use the DB2 utility. - SHRLEVEL CHANGE - Without the option CHECKDATA - The IC are taken on virtual tape (VTAPE). Using the dataclass DCVTPBD, VTAPE copy the IC on to different virtual tape.

COPY LIST LBCKDAT1 FULL YES SHRLEVEL CHANGE COPYDDN(TBCKLCO1) [...] 8636 84 32_Re: CPU used by DB2 image copies14_Grainger, Phil20_Phil.Grainger@CA.COM31_Tue, 31 Mar 2009 21:40:48 +0100357_iso-8859-1 One thing to note (and I know the OP is still on V8) is that in DB2 9 CHECKPAGE becomes the default (and there is NO "NO CHECKPAGE" option!)

Phil Grainger CA Senior Principal Product Manager Phone: +44 (0)1753 577 733 Mobile: +44 (0)7970 125 752 eMail: phil.grainger@ca.com

Ditton Park Riding Court Road Datchet Slough SL3 9LL [...] 8721 198 36_Three-Part Alias and DB2 Datasharing0_26_barbara.j.nigh@US.HSBC.COM31_Tue, 31 Mar 2009 13:50:35 -0700510_US-ASCII Q: Can you use DRDA to go from one Datasharing group to another Datasharing group using a three part alias if the primary member of the Datasharing group where the tables are is down?

We are using DRDA to go from one DB2 Datasharing group (where the programs run) to another DB2 Datasharing group (where the tables live) using a three part alias (location.tablename). This is working except for when the primary member of the datasharing group where the tables lives is down (during IPL). [...] 8920 234 64_[z/OS v8/v9] Managing priority for multiple distributed threads?14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 31 Mar 2009 17:16:10 -0400352_us-ascii Does anyone know what to do with an environment where we have two classes of dynamic work?





One, data warehouse free-form queries, has the bad habit of going into high degrees of parallelism and swallowing huge amounts of CPU time. (I'm recommending setting the max degree to a fairly small number to prevent this.) [...] 9155 178 68_Re: [z/OS v8/v9] Managing priority for multiple distributed threads?11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Tue, 31 Mar 2009 21:27:37 +0000394_Windows-1252 If it is (and it sounds like it) DDF, you can set up multiple periods and have high importance for only the first one. I recommend 2 periods: First, for about what 80% come to -- in our case about 0.25 CPU seconds (expressed in SU's). And, the second around IMP=4. We did this, utilisation improved and we removed the governor. Restarting a transaction consumed a lot of CPu. [...] 9334 380 68_Re: [z/OS v8/v9] Managing priority for multiple distributed threads?13_Hampton, Kirk26_kirk.hampton@CAPGEMINI.COM31_Tue, 31 Mar 2009 17:29:40 -0400399_us-ascii Your z/OS admin person can create a classification in WLM and identify each of those workloads by their correlation ID, and give them separate priorities. We identify at least 5 different distributed DB2 workloads here based on different correlation ID's.

Kirk Hampton DB2 z/OS Sysprog IBM Certified Database Administrator - DB2 UDB V8.1 for z/OS Capgemini Energy Dallas, Texas [...] 9715 95 32_Re: CPU used by DB2 image copies14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Tue, 31 Mar 2009 21:34:39 +0000575_windows-1252 Francis The reason I asked about what image copy product is there can be quite a diffrence between running inside DB2 like the base utilities do vs running out side DB2 like many of the ISV utilities do. You might want to try the following experiement. Chose a larger table space. Repro the table space to VTAPE. Specify AMP=BUFND=21 on the table space DD to get some I/O chaining. Image copy the same object to VTAPE SHRLEVEL CHANGE. Compare the total CPU for both. One of the big factors that can cause diffrences is the effectiveness of I/O chaining. In [...] 9811 133 68_Re: [z/OS v8/v9] Managing priority for multiple distributed threads?14_Avram Friedman25_ibmsysprog@GEEK-SITES.COM31_Tue, 31 Mar 2009 21:49:34 +0000404_windows-1252 Phil I hope I am not going too far off topic with the following comment. 'Operational' systems from a queueing standpoint process work when it arrives. It is possible to make very small adjustments with priorities. Generally high priority is given to processes with very little resource demand that impact the entire environment. Give the IRLM high priority is an example of this idea. [...] 9945 147 32_Re: CPU used by DB2 image copies16_Robert Catterall21_rfcatterall@GMAIL.COM31_Tue, 31 Mar 2009 17:51:47 -0400351_ISO-8859-1 I/Os do utilize central processor resources. Mainframe I/O processors carry some of the I/O CPU load, but not all of it. If your first case shows 16 minutes and 26 seconds of CPU time to image copy 63.7 million pages, that works out to a little over 15 microseconds of CPU time per page. I'm not sure that that's an unreasonable time. [...] 10093 15 32_Re: CPU used by DB2 image copies11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Tue, 31 Mar 2009 21:54:46 +0000324_Windows-1252 >If your first case shows 16 minutes and 26 seconds of CPU time to image copy 63.7 million pages, that works out to a little over 15 microseconds of CPU time per page. I'm not sure that that's an unreasonable time.

IMO, it's not. Remember, you do more - you use more.m - Too busy driving to stop for gas! 10109 35 37_GALLAGHER, BILL is out of the office.0_28_BILL.GALLAGHER@PHOENIXWM.COM31_Tue, 31 Mar 2009 17:58:16 -0400817_US-ASCII I will be out of the office starting 03/31/2009 and will not return until 04/01/2009.

If you need immediate assistance, please contact Jean Williams at x5945.



----------------------------------------- ******************************************************************* CONFIDENTIAL: This communication, including attachments, is intended only for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, you are hereby notified that you have received this document in error, and any use, review, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and [...] 10145 295 40_Re: Three-Part Alias and DB2 Datasharing16_Robert Catterall21_rfcatterall@GMAIL.COM31_Tue, 31 Mar 2009 18:41:12 -0400372_ISO-8859-1 Technically, there's no such thing as a "primary" member of a data sharing group - all members co-own all objects in the catalog. Relative to each other, the member subsystems are peers. There's an originating member, but it doesn't have a special status. What do you mean by "primary member"? Are you referring to the member to which most work is routed? [...] 10441 100 68_Re: [z/OS v8/v9] Managing priority for multiple distributed threads?12_Martin Hubel17_Martin@MHUBEL.COM31_Tue, 31 Mar 2009 19:51:20 -0400