1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l January 2010, week 3 2 93 59_Re: AW: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="13_David Simpson22_dsimpson@THEMISINC.COM31_Thu, 14 Jan 2010 19:42:23 -0500295_iso-8859-1 I just did some experimenting and it appears that even in DB2 9 optimizer will not rewrite the two predicates as a BETWEEN. Perceived costs for the BETWEEN seem to be much lower than writing the two predicates so it would seem to be a good standard to use BETWEEN when possible. [...] 96 81 73_AUTO: Ashvin Amin/Dallas/IBM is out of the office. (returning 01/28/2010)11_Ashvin Amin17_ashvin@US.IBM.COM31_Thu, 14 Jan 2010 22:02:17 -0700467_US-ASCII



I am out of the office until 01/28/2010.

I will be out of the country on vacation until Jan 28 and will not have email access. I will repsond to your messages when I return.

If you need immediate assistance, please contact my manager, Maureen Kinard at ...mkinard@us.ibm.com



Note: This is an automated response to your message "DB2-L Digest - 14 Jan 2010 to 15 Jan 2010 (#2010-11)" sent on 1/14/10 18:00:01. [...] 178 332 37_Re: db2 9.1 cancel thread ur rollback13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Fri, 15 Jan 2010 09:48:26 +0100457_us-ascii If it's a subsystem that can take an outage, and you have certain Zparms set correctly, you could intentionally bring down the DB2 subsystem (cancelling the IRLM is effective) and immediately -STArt DB2. DB2 should start quickly and leave this UR in - if memory serves - postponed abort, and the database objects it was touching unavailable. It's then up to you to do something about those objects, but everything else is able to be accessed. [...] 511 667 19_Re: DSN1COPY V9 NFM11_Roy Boxwell16_R.Boxwell@SEG.DE31_Fri, 15 Jan 2010 09:53:38 +0100545_ISO-8859-1 Nope. Wrong on that point. You do not need shareable catalogs. I use model on a different LPAR with no physical connection to the source system. I do not see the problems you seem to get. However what explains it all is that I do not use -1 space allocations (I like my DB2 catalog to contain valid data!) and as I use "ExtentManager" to manage my spaces (It is, IMHO, much better than DB2 at managing space) I never have extent problems, over or under allocation problems and DSN1COPY works 100% of the time - I agree 100% that [...] 1179 27 56_Re: Differences in Statistics Taken in Runstats VS Reorg10_Joe Geller21_joerg6666@HOTMAIL.COM31_Fri, 15 Jan 2010 14:12:26 +0000521_utf-8 Your original description indicated that you ran the Runstats before the Reorg. You should see a change in some the statistics because of the Reorg (clusterratio in particular). Cardinalities should be the same, but the Reorg could certainly result in a different access path.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 1207 110 38_Re: Versioning am I missing Something?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Fri, 15 Jan 2010 16:39:12 +0000615_UTF-8 This looks like a bug, we have the previous maintenance that addresses versioning issues. Now we have to fight through level 1 support. Charge!

On Thu, 14 Jan 2010 16:38:19 +0000, Jorg Lueke wrote:

>In looking at the Admin Guide and SQL Reference it appears that the >oldest_version column in systablespace/systablepart can be reset by running >a reorg and a modify removing all the image copies in the old version. This >does not appear to happen, at least not in all cases. So I took an object >and ran Reorg. Then a modfy removing all image copies, followed by [...] 1318 356 38_Re: Versioning am I missing Something?10_Kathy Culp21_Kathy_Culp@BCBSTX.COM31_Fri, 15 Jan 2010 11:22:15 -0600677_us-ascii You may need to run REPAIR VERSIONS TABLESPACE dbname.tsname to get the versions back in sync.

Kathy Culp Database Administration 972.766.3410 8.766.3410 or 63410(internal)







Jorg Lueke Sent by: IDUG DB2-L 01/15/2010 11:15 AM Please respond to IDUG DB2-L



To DB2-L@WWW.IDUGDB2-L.ORG cc

Subject Re: [DB2-L] Versioning am I missing Something?











This looks like a bug, we have the previous maintenance that addresses versioning issues. Now we have to fight through level 1 support. Charge! [...] 1675 74 30_Re: view to convert data types13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 15 Jan 2010 13:24:37 -0500533_utf-8 I suggest you put it and some extra shrink wrap in the box and send it back -- unless of course the vendor is one whose name starts with a C and is headquartered in downtown Detroit.

Dave

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. [...] 1750 98 30_Re: view to convert data types13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Fri, 15 Jan 2010 14:12:56 -0500564_utf-8 Just in case you would appreciate a more lengthy reply...:

Among the questions that occur to me: 1. how many tables do you need to support with this client? Are you willing to undertake the costs and time required to develop, test, and maintain these views? 2. what do they suggest you have your view do about the decimal scale amounts lost in converting decimal to integer? 3. what do they suggest you do about null values in your data? 4. does this vendor claim to support DB2? Do they understand that DB2 is a relational database, not just an [...] 1849 73 60_[LUW] question about automatic storage and autoresize option23_Jeffrey Benner, DB2 DBA22_consulting@EBENNER.COM31_Fri, 15 Jan 2010 15:46:30 -0600403_ISO-8859-1 The way it appears to me is, if you created an automatic storage tablespace which was DMS for some reason with AUTORESIZE NO, and that tablespace were to become full, your only recourse would be to issue an ALTER TABLESPACE... AUTORESIZE YES in order to increase the size of the tablespace. Correct? (since you cannot use DMS container operation against an automatic storage tablespace) [...] 1923 58 47_Re: Which is better? "BETWEEN" vs "<=" and >"="14_Peter Vanroose17_pvanroose@ABIS.BE31_Sat, 16 Jan 2010 20:56:24 +0000373_UTF-8 A small example, to illustrate the difference in filter factor, and the potential importance of carefully choosing between either "<= & >=" and "BETWEEN":

Suppose a column A contains the following values in its 20 rows: 0 5 5 5 5 5 10 10 10 10 15 15 15 15 20 25 25 25 25 25. Comparing the two predicates "A>= 10 && A <= 15" and "A BETWEEN 10 AND 15": [...] 1982 45 23_Xephon DB2 Update Books9_Sam Golob19_sbgolob@CBTTAPE.ORG31_Sat, 16 Jan 2010 18:24:36 -0500618_us-ascii Hi Folks,

I'd like to announce that since Xephon stopped publishing their journals (they had been given over to Thomas Publications, and Thomas stopped publishing them in early 2008) the support for most of the magazines has been passed to www.cbttape.org (the CBT Tape collection). So you can look at (and download) XEPHON DB2 Update issues from 1998 thru 2005 inclusive at www.cbttape.org/xephon/xephond without having to register for anything or be a member. We happen to be missing a PDF of the April 2004 issue (Issue 138), and if anyone has it, we would appreciate having it to complete our [...] 2028 186 28_Re: DB2 Table Update Columns4_Anil21_alisha_kale@YAHOO.COM31_Mon, 18 Jan 2010 03:34:56 +0000536_utf-8 Hi Ken,

The question is not necessarily academic. I am working on a mass insert startegy using a COBOL program (COBOL program is not negotiable). We want to estimate the time for a 250 million rows insert operation. Keeping as aside the number of indexes and number of threads (parallel insert jobs), I am examing the various parameters that would influence the insert efficiency. It is in this context that I am researching the overhead associated with logging, and the alternatives to optimize the logging overhead. [...] 2215 44 26_INSERT Statements Per Hour4_Anil21_alisha_kale@YAHOO.COM31_Mon, 18 Jan 2010 03:49:05 +0000591_utf-8 Hi All !

I have been reviewing the performance of the inserts volume at our shop. We are using DB2 V9 CM on z/os (non-sysplex, non-data sharing env). We use a product called Optim (formerly princeton softech) and it performs a few millions of inserts in an hour. In contrast, the COBOL programs performs a max of 100K inserts per hour. I was wondering if any of you have done some analysis with regards to a performance test for massive inserts using COBOL program. And what were your findings/lessons learned. For those who are familiar with optim/softech tool, why would [...] 2260 65 23_SQL from DB2 Admin Tool4_Anil21_alisha_kale@YAHOO.COM31_Mon, 18 Jan 2010 04:26:18 +0000564_utf-8 Hi All

Env: DB2 V9 CM on z/os

For Db2 Admin Tool Users,

Invoke: DB2 Admin Tool >> DB2 Administration Menu 7.2.0 >> DSN_Name System Catalog >> DSN_Name Table Spaces

And this will list you the table spaces based on the selection criteria specified from the DSN_Name System Catalog menu panel.

Line command "SM" is used to display the space statistics for the table space. The "SM" option displays information about the tablespace along with the VSAM datasets utilization - VSAM KB Alloc,VSAM KB Used, Pct Usd,VSAM Exts. [...] 2326 556 28_Re: DB2 Table Update Columns11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 18 Jan 2010 09:10:49 +0100591_ISO-8859-1 this also screams MEMBER CLUSTER very very loudly....





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de

Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894 Geschäftsführung: Siegfried Fürst, Gerhard Schubert







Anil Gesendet von: IDUG DB2-L 18.01.2010 04:34 Bitte antworten an IDUG DB2-L [...] 2883 269 30_Re: INSERT Statements Per Hour11_Roy Boxwell16_R.Boxwell@SEG.DE31_Mon, 18 Jan 2010 09:16:34 +0100571_ISO-8859-1 we would need to see

1) The DDL of the tables, indices and tablespaces 2) The code used to insert

to actually begin to get a grip on the possibility of the answer (Which of course is "It depends") however my gut feeling (and ask anyone who knows me - it is a big feeling ;) ) is simply good DDL and good code running against "bad" DDL and "bad" code. There are so many possibilites - better IO from faster disks, better preformat, better extent managment, better DDL (MEMBER CLUSTER being one thing) better usage of tablespaces FREEPAGE etc [...] 3153 98 55_AW: [DB2-L] Which is better? "BETWEEN" vs "<=" and >"="35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Mon, 18 Jan 2010 09:30:41 +0100472_iso-8859-1 Hello Peter

I think, the correct value for HIGH2KEY in your example must be 20 mustn't it? I just did a test with a small table containing 1, 2, 2, 2, 6, 10, 10, 10 and I got 6 as HIGH2KEY for this column.

Mit freundlichen Grüßen Walter Janißen

ITERGO Informationstechnologie GmbH Anwendungsentwicklung Laufzeitarchitektur Victoriaplatz 2 40477 Düsseldorf Tel.: +49 211 477-2928 Fax: +49 211 477-2615 mailto:walter.janissen@itergo.com [...] 3252 199 28_Re: DB2 Table Update Columns13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Mon, 18 Jan 2010 08:45:38 -0000539_US-ASCII Hi Anil

I hope you are looking at the possibilities of multi-row inserts (inserting from a COBOL array)?

This will probably give you a bigger performance boost than almost anything else you can do with "parameters" and tweaking the table design to optimize logging

Phil G Grainger Database Solutions Ltd

-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Anil Sent: 18 January 2010 03:35 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 Table Update Columns [...] 3452 94 27_Re: SQL from DB2 Admin Tool11_Nenad Vidak21_Nenad.Vidak@GMAIL.COM31_Mon, 18 Jan 2010 13:34:38 +0000360_UTF-8 Anil, any SQL monitoring tool ( like Query Monitor ) can capture any SQL executed against DB2 being monitored, you just need to adjust thresholds...(not sure if this is 100% legal - is SQL generated by a tool considered to be a proprietary code ?)

Regards, Nenad

On Mon, 18 Jan 2010 04:26:18 +0000, Anil wrote: [...] 3547 182 32_WITH HOLD & FOR UPDATE - CURSORS14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Mon, 18 Jan 2010 14:45:44 +0100460_us-ascii Sorry,

but we are converting IBM's database DB2 on z/Os to Oracle10G on Solaris.





In the DB2-environment, we use Sql-cursors with options "WITH HOLD" and "FOR UPDATE OF" in the same cursor. In these programs we are also using commit-points before fetching the next record in the cursor. How can we implement the same feature/behaviour in Oracle ? I think this db2-feature is incompatible with Oracle . Is this true ? [...] 3730 410 36_Re: WITH HOLD & FOR UPDATE - CURSORS13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Mon, 18 Jan 2010 09:20:30 -0500722_utf-8 You think you're sorry now, ....





Dave

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Steurs Patrick Sent: Monday, January 18, 2010 8:46 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] WITH HOLD & FOR UPDATE - CURSORS





Sorry,

but we are converting IBM's database DB2 on z/Os to Oracle10G on Solaris.





In the DB2-environment, we use Sql-cursors with options "WITH HOLD" and "FOR UPDATE OF" in the same cursor. In these programs we are also using commit-points before fetching the next record in the cursor. How can we implement the same feature/behaviour in Oracle ? I think this db2-feature is incompatible with Oracle [...] 4141 245 28_Re: DB2 Table Update Columns13_McDonald, Ken20_Ken_McDonald@BMC.COM31_Mon, 18 Jan 2010 08:37:46 -0600309_us-ascii Hi Anil,

I agree with Phil that multi-row insert will be a better approach for performance. The entire row will be logged for an insert no matter the order of columns. And, restating my original comment, if logging I/O is an issue, consider striping the actives to improve the I/O rates. [...] 4387 139 36_Re: WITH HOLD & FOR UPDATE - CURSORS15_Dirk Herzhauser16_dherzhau@CSC.COM31_Mon, 18 Jan 2010 14:40:48 +0000 4527 442 36_Re: WITH HOLD & FOR UPDATE - CURSORS14_Steurs Patrick21_Patrick.Steurs@NBB.BE31_Mon, 18 Jan 2010 16:39:41 +0100346_utf-8 Hi,





Oracle can't process a cursor with hold and for update. It's always been treated as a cursor for update. So, if you use and Oracle-database, the programmer has to make a choice , either he has to lock the data or to keep a cursor-position. But if you need both features : locking & keeping cursor-position ? [...] 4970 26 27_Re: SQL from DB2 Admin Tool18_Tom Ulveman Jensen18_ulveman@DK.IBM.COM31_Mon, 18 Jan 2010 17:10:44 +0100708_US-ASCII The DB2 Admin Space Manager function uses the Catalog Search Interface (IGGCSI00 not LISTCAT) to capture the VSAM catalog information.

Tom Ulveman Jensen IBM DB2 Admin development

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 4997 196 57_DB2 for z/OS Troubleshooting and Monitoring presentations10_DB2usa !!!19_db2usa3@HOTMAIL.COM31_Mon, 18 Jan 2010 11:51:51 -0500471_Windows-1252

Hi DB2 user,

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

Last update on Monday, January 18th 2010



Here are several presentations about DB2 for z/OS Troubleshooting and Monitoring, available on IDUG website:



- DB2 Systems Programming Tools of the Trade by Linda Hagedorn





- The Diagnosis Manual Secrets and Intrigue by Linda Hagedorn [...] 5194 40 10_DB2 CURSOR10_Ron Thomas17_ron5174@GMAIL.COM31_Mon, 18 Jan 2010 17:13:34 +0000334_utf-8 Hi,

I have a table in which the primary key is store no, orderid and another field order entry which is of time stamp , i need to download all the records from the table which is 2 yrs old, in the cursor i have defined as below

select col1, col1.. from table1 where order_entry <= Current timestamp - 2 year, [...] 5235 56 14_Re: DB2 CURSOR13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Mon, 18 Jan 2010 17:36:49 +0000348_utf-8 Ron,

It is not clear if you are using an UNLOAD utiltiy or a program to perform the download........ As this is a select it can't really enable restart processing.

Instead select smaller groups of data to reduce the re-run time if any part fails. To do this you will need to apply a little more control over the select SQl [...] 5292 45 28_Re: DB2 Table Update Columns13_Jack Campbell23_jackrcampbell@YAHOO.COM31_Mon, 18 Jan 2010 17:42:51 +0000337_utf-8 Anil,

Here are a few options I can think of:

1) Break the data into some kind of logical group, and run multiple versions of the insert in parallel...........there will be some index contention. But if you consider breaking by PARTITION (preferred), or CLUSTER seqeunce, you will be able to up your insert rate. [...] 5338 27 14_Re: DB2 CURSOR10_Ron Thomas17_ron5174@GMAIL.COM31_Mon, 18 Jan 2010 17:58:06 +0000627_utf-8 Thanks Jask for the suggestion, this i am doing thru program & will go with suggestion 2.

Regards Ron

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations! Did you miss out on attending an IDUG conference? Many of the presentations were recorded and are available on our website! _____________________________________________________________________ [...] 5366 74 14_Re: DB2 CURSOR14_Peter Vanroose17_pvanroose@ABIS.BE31_Mon, 18 Jan 2010 18:07:46 +0000521_UTF-8 On Mon, 18 Jan 2010 17:13:34 +0000, Ron Thomas wrote: > I have a table in which the primary key is store no, orderid and another field > order entry which is of time stamp , i need to download all the records from >the table which is 2 yrs old, in the cursor i have defined as below > >select col1, col1.. from table1 where order_entry <= Current timestamp - 2 >year, > >Here in this case if we want to do the restart processing do we need to >include one of the primary keys in the query? [...] 5441 26 38_Re: Versioning am I missing Something?12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Mon, 18 Jan 2010 11:46:02 -0700511_UTF-8 You don't mention what version of db2 you're running- but I recall an issue we had here with some tablespaces created a long time ago- having a version error when they were trying to refresh the data from production to test. They use dsn1copy which had issues with the version number. We got around it by running loads- or by dropping and recreating the tablespace. Since it was test it wasn't an issue. Maybe this will help you- do you don't have to go through IBM support and pay. Hope this helps. [...] 5468 510 26_DB2 Forum meeting Thursday9_Bob Brock28_brock@KRYPTON-CONSULTING.COM31_Mon, 18 Jan 2010 16:45:45 -0600614_iso-8859-1 DB2 FORUM DB2 User Group





Quarterly Meeting

Thursday, January 21, 2010

8:00 AM - 12:00 PM





Location: Compuware Corporate Office

15305 Dallas Parkway Addison, TX 75001

972-960-0960





Park in garage - Compuware has passed to get out free

Meeting in first floor Colonnade '5 Star Conference Center'









Breakfast Sponsor: Compuware Corp.





08:00 - 09:00 Registration and Breakfast





09:00 - 09:05 Business and Announcements [...] 5979 26 28_Re: DB2 Table Update Columns11_Mike Bracey22_mike_bracey@UK.IBM.COM31_Tue, 19 Jan 2010 10:40:24 +0000737_UTF-8 Hello Anil You might pick up some tips from the presentation on "Optimizing INSERT Performance" by John Campbell: http://www-01.ibm.com/software/os/systemz/telecon/dec1/ Register to get the foils and listen to the recorded presentation.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings, Webcasts, Conferences- what is going on next? RUG leaders- get your events on the calendar today! _____________________________________________________________________ [...] 6006 671 37_Re: db2 9.1 cancel thread ur rollback16_Broyles, Carol L27_Carol.L.Broyles@ACS-INC.COM31_Tue, 19 Jan 2010 07:43:23 -0600609_us-ascii We have successfully gotten rid of these threads using CANCEL THREAD(token) NOBACKOUT.









Carol L. Broyles

Infrastructure Mgt. Consultant

Commercial Solutions

Office Phone: 937-495-4003

carol.l.broyles@acs-inc.com





Affiliated Computer Services Inc.

CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,

is for the sole use of the intended recipient(s) and may contain confidential and

privileged information. Any unauthorized review, use, disclosure, or distribution is [...] 6678 25 38_Re: Versioning am I missing Something?10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Tue, 19 Jan 2010 15:03:34 +0000441_utf-8 No, these are DB2 9, created in May of last year and altered twice since then. These are totally new so there should be no need to run Repair Versions. I'll update the list when the ETR is closed.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 6704 111 32_REORG on Partitioned Tablespaces0_26_barbara.j.nigh@US.HSBC.COM31_Tue, 19 Jan 2010 09:52:53 -0800608_US-ASCII If I run a conditional reorg on a partitioned tablespace by part, will the corresponding partitioned portion of the index also be reorged too? What about the NPI associated with the table?

Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________

Phone. 831 754 1400 2048 Fax. 831-759-7073 Mobile. 831-776-6194 Pager 8317766194@messaging.sprintpcs.com Email. barbara.j.nigh@us.hsbc.com Internet. http://www.hsbc.com ______________________________________________________ [...] 6816 114 47_Running REORG on Partitions Concurrently if NPI0_26_barbara.j.nigh@US.HSBC.COM31_Tue, 19 Jan 2010 10:28:38 -0800436_US-ASCII I would like to split a REORG utility which reorgs a large tablespace by part into two jobs to save elapsed time but am concerned about the NPI. Is it possible to run two REORG jobs concurrently against a partitioned tablespace it there is an NPI?

Barbara J Nigh CONSULTANT DATABASE MGMT | HSBC Technology Services (HTS) 1441 Schilling Place Salinas, CA 93901 ______________________________________________________ [...] 6931 451 36_Re: REORG on Partitioned Tablespaces14_Sevetson, Phil22_PSevetson@FISA.NYC.GOV31_Tue, 19 Jan 2010 13:44:24 -0500589_us-ascii Barbara,

The answer depends on which version of DB2 you're running. 1) in DB2V8 and earlier, a part-level REORG will not cause a true REORG of NPIs; instead, index keys are updated with new RIDs using the SQL update engine, during the BUILD2 phase of the REORG. 2) In DB2V9, NPIs are completely REORGed when a table partition is REORGed. This can cause a prolonged run for the utility. With SHRLEVEL CHANGE, this impact is limited to a need for enough space to duplicate any NPIs, a potentially longer LOGAPPLY phase, and a slightly longer SWITCH phase because of the [...] 7383 233 51_Re: Running REORG on Partitions Concurrently if NPI18_Bufford, Donald E.24_Donald.E.Bufford@SSA.GOV31_Tue, 19 Jan 2010 14:03:18 -0500758_us-ascii I ran two online reorgs (SHRLEVEL CHANGE) yesterday on the same partitioned tablespace with one NPI.



Donald Bufford Database Administrator Lockheed Martin 410-496-9550







________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of barbara.j.nigh@US.HSBC.COM Sent: Tuesday, January 19, 2010 1:29 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: [DB2-L] Running REORG on Partitions Concurrently if NPI





I would like to split a REORG utility which reorgs a large tablespace by part into two jobs to save elapsed time but am concerned about the NPI. Is it possible to run two REORG jobs concurrently against a partitioned tablespace it there is an NPI? [...] 7617 521 51_Re: Running REORG on Partitions Concurrently if NPI16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Tue, 19 Jan 2010 14:36:54 -0500507_us-ascii Hi Don, are the columns in the NPI a subset of the partitioning scheme ?





Steen Rasmussen CA









From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bufford, Donald E. Sent: Tuesday, January 19, 2010 1:03 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Running REORG on Partitions Concurrently if NPI





I ran two online reorgs (SHRLEVEL CHANGE) yesterday on the same partitioned tablespace with one NPI. [...] 8139 21 37_can I restore only a particular table10_anne Stout23_anne.stout@DOWJONES.COM31_Tue, 19 Jan 2010 19:42:19 +0000 8161 558 51_Re: Running REORG on Partitions Concurrently if NPI18_Bufford, Donald E.24_Donald.E.Bufford@SSA.GOV31_Tue, 19 Jan 2010 15:48:31 -0500639_us-ascii Steen,

The NPI is a unique index for the table's primary key and couldn't be explicitly dropped. Three other NPI's were dropped and rebuilt after the reorgs completed. The tablespace is partitioned by another column that isn't part of the primary key.



Donald Bufford Database Administrator Lockheed Martin 410-496-9550







________________________________

From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Rasmussen, Steen Sent: Tuesday, January 19, 2010 2:37 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] Running REORG on Partitions Concurrently if NPI [...] 8720 29 41_Re: can I restore only a particular table13_Ian Bjorhovde23_ian.bjorhovde@GMAIL.COM31_Tue, 19 Jan 2010 14:21:36 -0700404_ISO-8859-1 On Tue, Jan 19, 2010 at 12:42 PM, anne Stout wrote: > > On DB2 UDB database, if I take a full online backup everyday, can I restore > only a particular table from the whole backup image rather than restoring full db/full > tablespace?

Nope - tablespace-level is the most granular that you can get (without resorting to another tool like Recovery Expert). [...] 8750 191 30_Re: INSERT Statements Per Hour10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Tue, 19 Jan 2010 17:39:17 -0800639_iso-8859-1 If the underlying tables that the rows are inserted into are the same for both workloads (optim & cobol program). The huge difference could be due to many factors.. I would start looking at.. A) Insert key sequence (sequential ascending/descending by clustering index vs. random insert that will drag performance down) B) Commit Frequency C) Is Optim utilizing MRI ? D) State of tablespace/indexspaces when insert workload runs. (free space, was Reorg run? , contention etc..) E) Compare class1/class2 elapsed and cpu time between optim and DB2 workload, maybe the cobol program is spending most of the time in application [...] 8942 216 41_Re: can I restore only a particular table12_Phil Gunning19_pkgunning@GMAIL.COM31_Wed, 20 Jan 2010 01:06:02 -0500404_utf-8 As Ian said, only can do at tablespace level. A workaround you could use is to only create one table per tablespace, then you could backup and restore the tablespace, which would in effect give you table level restore. Just do the same to the index tablespace if the indexes are in a separate tablespace. Of course you can also export the table data as IXF and load or import it as needed. PG [...] 9159 524 30_Re: INSERT Statements Per Hour11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 20 Jan 2010 07:23:16 +0100362_ISO-8859-1 Careful! I use MEMBER CLUSTER to improve insert in non-datasharing....



MEMBER CLUSTER Specifies that data inserted by an insert operation is not clustered by the implicit clustering index (the first index) or the explicit clustering index. Instead, DB2 chooses where to locate the data in the table space based on available space. [...] 9684 89 30_Re: INSERT Statements Per Hour11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 20 Jan 2010 07:40:53 +0100331_ISO-8859-1 just did some research... MEMBER CLUSTER seems to have appeared half-way through DB2 V6. Text is the same as now except the limitations were simply No LOB, Work or temp. That hasnt changed upto V8. New in V9 is no Segsize or UTS. I only use MEMBER CLUSTER for partitioned spaces of course so have no worries there! [...] 9774 46 44_Invalid output for index after REORG utility21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM31_Wed, 20 Jan 2010 08:39:11 +0000542_utf-8 Hi All,

We have a problem after running REORG on indexes of a table .It happens everytime to one index ( primary or alternate one).The Reorg job is ended with zero condition which shows It is ok ,but the number of rows for the index is not equal with the number of rows for the table.It seems we lost some rows after running REORG ,although the REORG job is ended with zero code.This is happenning to primary index or an alternate index . My guess is that there should be problem with some pages into index, but there is no [...] 9821 206 41_Re: can I restore only a particular table13_Mark Horrocks22_agentlease@HOTMAIL.COM31_Wed, 20 Jan 2010 08:56:58 +0000489_iso-8859-1

Another manageble solution depending on the size of the table is to backup the table to a federated database and then restore from the federated database.





This can be applied to hot tables etc.





Many Thanks,





Mark Horrocks.





Date: Wed, 20 Jan 2010 01:06:02 -0500 From: pkgunning@GMAIL.COM Subject: Re: [DB2-L] can I restore only a particular table To: DB2-L@WWW.IDUGDB2-L.ORG [...] 10028 90 48_Re: Invalid output for index after REORG utility13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 20 Jan 2010 09:34:41 -0000517_US-ASCII Hi Sanaz

This does indeed sound odd, but so does your description of the problem!

So, some more questions to try and understand what is going on

1. You say this problem happens to " primary index or an alternate index " - so it is NOT ALWAYS the same index? 2. Are you running a full tablespace reorg or a partition level one 3. When you say " number of rows for the index is not equal with the number of rows for the table " - I assume you mean the number of KEYS for the index is [...] 10119 260 48_Re: Invalid output for index after REORG utility11_Roy Boxwell16_R.Boxwell@SEG.DE31_Wed, 20 Jan 2010 10:45:05 +0100503_ISO-8859-1 they only thing that springs to mind is this old HIPER from 2006...

PK32423 (UK19095) MISSING DATA CHANGES (INSERT/UPDATE/DELETE) AFTER REORG SHRLEVEL CHANGE SUCCESSFUL COMPLETION 06/10/05 PTF PECHANGE

But I would sincerely hope you have it applied!!





Roy Boxwell SOFTWARE ENGINEERING GMBH -Product Development- Robert-Stolz-Straße 5 40470 Düsseldorf/Germany Tel. +49 (0)211 96149-675 Fax +49 (0)211 96149-32 Email: R.Boxwell@seg.de http://www.seg.de [...] 10380 100 27_New IDUG groups in LinkedIn10_Max Scarpa16_mscarpa@CESVE.IT31_Wed, 20 Jan 2010 12:20:09 +0100457_US-ASCII Esteemed listers

IDUG is simplifing LinkedIn groups to avoid to duplicate the same informations among different groups so now there's a new 'main' DB2 group:



IDUG: The World Wide DB2 User Community ----> http://www.linkedin.com/groups?home=&gid=46747&trk=anet_ug_hm

Here you'll find all news,people,webcasts and all informations about IDUG events. And to have more 'granularity' some sub-groups have been created: [...] 10481 48 23_REORG - UNABLE TO DRAIN4_Anil21_alisha_kale@YAHOO.COM31_Wed, 20 Jan 2010 12:50:13 +0000498_utf-8 Hi All !

Env: DB2 V9 CM on z/os Task Performed: Online REORG

One of our reorg jobs was performing lot of iterations in the log phase of a online reorg. I noticed that it had done about 200 iterations and each time it said 0 records processed. I also saw multiple occurences of the following message. DSNU1122I # 017 03:52:13.90 DSNURLOG - JOB DBAREOJ1 PERFORMING REORG . WITH UTILID DBAREOJ1 UNABLE TO DRAIN dbname.tsname . RETRY 120 OF 255 WILL BE ATTEMPTED IN 30 SECONDS [...] 10530 119 27_Re: REORG - UNABLE TO DRAIN8_duam lee20_duam_lee@HOTMAIL.COM31_Wed, 20 Jan 2010 13:52:45 +0000629_ks_c_5601-1987

Hi Anil,





issue dis db(dbname) space(spname) claimers and see the thread. Then you may have to cancel the thread if you found any.





With Thanks Duam.







> Date: Wed, 20 Jan 2010 12:50:13 +0000 > From: alisha_kale@YAHOO.COM > Subject: [DB2-L] REORG - UNABLE TO DRAIN > To: DB2-L@WWW.IDUGDB2-L.ORG > > Hi All ! > > Env: DB2 V9 CM on z/os > Task Performed: Online REORG > > One of our reorg jobs was performing lot of iterations in the log phase of a > online reorg. I noticed that it had done about 200 iterations and each time it [...] 10650 54 16_Index compession36_=?ISO-8859-1?Q?Tor=2DRoger_L=F8ken?=18_torroger@GMAIL.COM31_Wed, 20 Jan 2010 15:41:04 +0100832_ISO-8859-1 Hi !

We are thinking of using index compression in our production enviroment on secondary indexes. Have anyone out there good/bad experience with index compression or ...... ?

Tor Roger

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...] 10705 416 30_Re: INSERT Statements Per Hour12_Hepp Shery C21_Shery.Hepp@SRPNET.COM31_Wed, 20 Jan 2010 08:04:23 -0700535_US-ASCII Sudhir- IBM had a free webinar on December 1,2009 called





DB2 for z/OS Optimising Insert Performance presented by bell

Distinguished Engineer

John Campbell

Distinguished Engineer





It had a lot of VERY GOOD information. Maybe you can find the presentation out on IBMs website to listen too. Otherwise I'm including the presenters contact information from the presentation- maybe he can send you the presentation if you're unable to find it on IBMs website. [...] 11122 28 27_Re: REORG - UNABLE TO DRAIN10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 20 Jan 2010 14:47:52 +0000303_utf-8 You really shouldn't just cancel a thread. Often times the business may prefer to have an app up rather than the reorg run through. There's several behaviors that can be set in the utilit. Is this an online reorg with fastswitch? Sometimes a simple schedluing change is all that is required. [...] 11151 27 20_Re: Index compession10_Jorg Lueke23_jorg_h_lueke@BCBSMN.COM31_Wed, 20 Jan 2010 14:45:10 +0000438_utf-8 We are just prepping to roll our first wave into production next month. We are expecting 50% DASD savings and not too much cost. We'll know by the end of February how smoothly things are going.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 11179 203 28_AW: [DB2-L] Index compession15_Martin Ketterer31_MARTIN.KETTERER@DE.KAERCHER.COM31_Wed, 20 Jan 2010 16:12:11 +0100333_iso-8859-1 Hello Tor

since we run SAP systems on zDB2 there was a good reason for us to "compress" some of our indexes. The main reason was to save space but when my colleague made a test in a sandbox system it also seemed that even SAP programs had a benefit of it - which indeed is one of the benefits being announced. [...] 11383 26 48_Re: Formatting table row printing and SQL in DB225_SUBSCRIBE DB2-L Anonymous17_mairecj@YAHOO.COM31_Wed, 20 Jan 2010 15:24:09 +0000831_utf-8 I appreciate everyone for your very helpful and prompt responses on formatting SQL and table rows. It is nice to now have several good options thanks to the generosity and helpfulness of the DB2 posters.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations! DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among many more categories of help waiting for you! Whether you are an old hand or a DB2 newbie, we have presentations for every level. _____________________________________________________________________ [...] 11410 36 45_Finding DB2 objects in questionable status...5_David17_mairecj@YAHOO.COM31_Wed, 20 Jan 2010 15:30:14 +0000355_utf-8 Are there SYSIBM catalog/directory tables I can interrogate to get a list of database objects and their status (tables, indexes, SPs, triggers, etc) that are not in a normal, useable state? I know I can retrieve COPY PENDING tables from SYSTABLESPACE STATUS but there are so many different states an object can be in that may not be desirable. [...] 11447 54 20_Re: Index compession14_Peter Vanroose17_pvanroose@ABIS.BE31_Wed, 20 Jan 2010 15:55:19 +0000434_UTF-8 For most indexes, you should indeed go for 8K or 16K index bufferpools, combined with compression. Beware that you only will have DASD storage reduction (either 50% or 75%), no bufferpool use reduction (since in memory the index data is non-compressed). On the other hand, in the BP the index pages are larger so on average you loose less unusable space at the end of a page. And the # levels might be smaller than before. [...] 11502 78 49_Re: Finding DB2 objects in questionable status...13_Phil Grainger34_phil@GRAINGERDATABASESOLUTIONS.COM31_Wed, 20 Jan 2010 16:58:37 -0000342_US-ASCII Unfortunately IBM don't externalize many of the "questionable statuses" so a -DIS DATABASE is the only guaranteed way to find the oddities (like RECP, AREORP, RBDP etc.)

If you want to do this from SQL, you could write a table UDF that does the -DIS DATABASE and returns the result

Then you ought to be able to do [...] 11581 277 30_Re: INSERT Statements Per Hour10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Wed, 20 Jan 2010 12:32:41 -0800684_iso-8859-1 Thanks Roy, I always assumed (wrongly! ) that MEMBER CLUSTER only works in datasharing, i now will add this to my tool box! Sudhir RealTime-DBA ________________________________ From: Roy Boxwell To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wed, January 20, 2010 1:23:16 AM Subject: Re: INSERT Statements Per Hour Careful! I use MEMBER CLUSTER to improve insert in non-datasharing.... MEMBER CLUSTER Specifies that data inserted by an insert operation is not clustered by the implicit clustering index (the first index) or the explicit clustering index. Instead, DB2 chooses where to locate the data in the table space based on available space. many many moons [...] 11859 90 30_Re: INSERT Statements Per Hour10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Wed, 20 Jan 2010 12:37:12 -0800658_iso-8859-1 When I want the performance benifit of MEMBER CLUSTER on a non-partitioned table space, I create a one partition partitioned tablespace. Sudhir RealTime-DBA ________________________________ From: Roy Boxwell To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Wed, January 20, 2010 1:40:53 AM Subject: Re: INSERT Statements Per Hour just did some research... MEMBER CLUSTER seems to have appeared half-way through DB2 V6. Text is the same as now except the limitations were simply No LOB, Work or temp. That hasnt changed upto V8. New in V9 is no Segsize or UTS. I only use MEMBER CLUSTER for partitioned spaces of course so have no worries [...] 11950 114 27_Re: REORG - UNABLE TO DRAIN10_Sudhir Raj26_sudhir.raj@REALTIMEDBA.COM31_Wed, 20 Jan 2010 13:13:00 -0800574_iso-8859-1 Zero rows processed! Looks like the problem is not the writers, it the readers. You might have long running read thread , since DB2 will have to drain all writers and readers in the switch phase it will retry # of times as specified on your RETRY parm. I suggest -> adding -DISPLAY DB(xx) TS(xx) USE command step in your jcl, before and after the REORG utility, to find the culprit! -> move to reorg to a quieter time if possible. -> recommend adding frequent commits to SELECT/read-only batch programs. -> last but not the best! cancel the offending thread [...] 12065 44 48_Re: Invalid output for index after REORG utility21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM31_Thu, 21 Jan 2010 05:51:28 +0000585_utf-8 Hi Phil,

You're right, It's not always happening to same index and It's a partition level one .For the third question,they are unique ones. I mean for primary index , the number of keys should be equal to number of rows for each partion , which It's not sometimes(after running REORG for a partition). For Alternate indexes,the number of keys for them should be the same (according to key combinations w have on them(3 alternate indexes as e.g)), which after running REORG for one of them , It 's different .Please notice that REORG job is ended with zero condition. [...] 12110 38 48_Re: Invalid output for index after REORG utility21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM31_Thu, 21 Jan 2010 06:20:04 +0000355_utf-8 Hi Roy,

Many thanks for your good responses always.

I read the explanation for this PTF .It says that this is the fix for Missing data after REORG, for the data being updated, inserted,deleted in the mean time RORG was running.But our problem is that we do'nt have any of them during the running utility, just we read some rows . [...] 12149 250 48_Re: Invalid output for index after REORG utility11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 21 Jan 2010 08:07:28 +0100451_ISO-8859-1 then no...but simply losing data is very very bad...are you sure no-one is doing online work? If yes then this apar does address the problem. One really horrible thought is that with V7 you could use an external sort routine (SYNCSORT etc.) - Could it be that someone has done something to this sort? Perhaps they have used a hard coded exit routine....E15 or such...I can never remember the names but I do recall that it was possible [...] 12400 28 48_Re: Invalid output for index after REORG utility21_SUBSCRIBE DB2-L sn.pr25_pourdarab_sanaz@YAHOO.COM31_Thu, 21 Jan 2010 08:41:54 +0000628_utf-8 Roy,

We're doing online work ,but just reading from that table in the mean time.

Regards Sanaz Pourdarab

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ [...] 12429 32 43_Connectivity between DB2 sub systems in zOS14_Ramachandran A25_ramachandran2yk@GMAIL.COM31_Thu, 21 Jan 2010 18:02:19 +0530559_ISO-8859-1 Dear list,

Our project needs to establish connectivity between two DB2 sub systems which are running on two different zOS systems in the same sysplex. But I am not sure this feature is available or not.

Can anyone suggest some inputs on this same.

Thanks in advance.

Regards, Rams.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________ [...] 12462 34 47_Re: Connectivity between DB2 sub systems in zOS11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Thu, 21 Jan 2010 14:17:18 +0000387_- >Our project needs to establish connectivity between two DB2 sub systems which are running on two different zOS systems in the same sysplex.

The simplest is a DB2PLEX, building a DB2 sharing group.

>But I am not sure this feature is available or not.

Somebody should be doing research before asking their questions. The feature has been around for 15+ years. [...] 12497 154 37_[DB2 V9] Lobs and FETCH WITH CONTINUE35_Walter Jani=?WINDOWS-1252?Q?=DFen?=26_Walter.Janissen@ITERGO.COM31_Thu, 21 Jan 2010 15:30:10 +0100423_iso-8859-1 Hi

I want to use for the first time LOBs with FETCH WITH CONTINUE, but have problems to do so. I want to read the statement-text from my own DSN_STATEMENT_CACHE_TABLE using a COBOL-program.

So I declared my Cursor as follows:

EXEC SQL DECLARE STMTID_STMT CURSOR FOR SELECT STMT_TEXT FROM V011570.DSN_STATEMENT_CACHE_TABLE WHERE STMT_ID = :QUERY-QUERY-NR FETCH FIRST 1 ROW ONLY END-EXEC [...] 12652 202 32_Oracle to DB2 IBM Migration Tool7_Kim May28_kim.may@THEFILLMOREGROUP.COM31_Thu, 21 Jan 2010 09:50:34 -0500343_us-ascii I would like to get some feedback from anyone who has worked with IBM on an Oracle to DB2 V9.7 migration. I am interested in finding out what the output of the Migration Enablement Evaluation Tool ("MEET") consists of and how accurately it estimated the application changes required. Were you satisfied with the results? Thanks! [...] 12855 80 32_DB2 for z/OS V9 Catalog poster ?14_Jan Vanbrabant23_vanbrabantjan@GMAIL.COM31_Thu, 21 Jan 2010 15:59:22 +0100825_ISO-8859-1 Hi,

Is there a catalog poster in zDB2 9 such as there was this one for V8

http://www.williefavero.com/DB2V8_catalog_Poster_2006April12.pdf





As far as I know, there is no such one from IBM this time?





Perhaps one fom CA? BMC ?





Jan

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA * _____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal Technical atricles from world famous authors in DB2's most prestigious, peer reviewed magazine now on-line! _____________________________________________________________________ [...] 12936 130 49_Re: Finding DB2 objects in questionable status...12_Martin Hubel17_Martin@MHUBEL.COM31_Thu, 21 Jan 2010 10:16:50 -0500 13067 89 36_Re: DB2 for z/OS V9 Catalog poster ?11_Roy Boxwell16_R.Boxwell@SEG.DE31_Thu, 21 Jan 2010 16:35:58 +0100385_ISO-8859-1 theres one from BMC but I did not like it! they forgot one table and had SYSINDEXSPACESTATS twice....and as I get older I feel the need for glasses when I try and read it....personally the "old" CA poster from V8 was my all time favourite ok ok they didnt bother with the _HIST tables but that is peanuts! (Hint hint to any CA people out there! my address is below!!!) [...] 13157 77 47_Re: Connectivity between DB2 sub systems in zOS13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 21 Jan 2010 10:39:36 -0500633_utf-8 I disagree Ted. Data-sharing is a more complicated means of connecting 2 subsystems.

I suggest the simplest method is using DRDA to define DDF connections between the 2 subsystems.

Rams, go to the DB2 information center for the DB2 version you have and search for Connecting Distributed database systems.

Dave

The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify [...] 13235 90 47_Re: Connectivity between DB2 sub systems in zOS12_Kirk Hampton37_kirk.hampton@ENERGYFUTUREHOLDINGS.COM31_Thu, 21 Jan 2010 09:39:30 -0600354_us-ascii Good morning Rams, The feature you are looking for is called Distributed Data Facility (DDF) and is included in DB2 for z/OS, and most likely is already active and available on your two subsystems. You should probably just have to insert the proper entries in SYSIBM.LOCATIONS and SYSIBM.LUNAMES to make the subsystems known to each other. [...] 13326 401 36_Re: DB2 for z/OS V9 Catalog poster ?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 21 Jan 2010 10:57:45 -0500630_iso-8859-1 Guten Tag Roy,





In fact - just go to www.ca.com/db

In the upper right hand corner you can order one yourself and we'll snail mail it to you Ji





Steen Rasmussen CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS









From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Roy Boxwell Sent: Thursday, January 21, 2010 9:36 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS V9 Catalog poster ? [...] 13728 167 36_Re: DB2 for z/OS V9 Catalog poster ?9_Chris Tee25_chris.tee@NOVAMAGIC.CO.UK31_Thu, 21 Jan 2010 16:06:24 +0000601_iso-8859-1

CA have done a V9 poster (with the HIST tables!). I think I ordered it off the CA website but can't find the link now.

Date: Thu, 21 Jan 2010 16:35:58 +0100 From: R.Boxwell@SEG.DE Subject: Re: [DB2-L] DB2 for z/OS V9 Catalog poster ? To: DB2-L@WWW.IDUGDB2-L.ORG





theres one from BMC but I did not like it! they forgot one table and had SYSINDEXSPACESTATS twice....and as I get older I feel the need for glasses when I try and read it....personally the "old" CA poster from V8 was my all time favourite ok ok they didnt bother with the _HIST tables [...] 13896 74 55_SV: [DB2-L] Connectivity between DB2 sub systems in zOS13_Hanne Lyssand20_Hanne.Lyssand@VPS.NO31_Thu, 21 Jan 2010 17:19:29 +0100579_us-ascii We are doing this both with datasharing and with plain TCPIP connect. You have to have TCPIP installed, but I guess you have?

The command: -DIS DDF

Will give you the ip-adress and port

DSNL080I -DB2P DSNLTDDF DISPLAY DDF REPORT FOLLOWS: DSNL081I STATUS=STARTD DSNL082I LOCATION LUNAME GENERICLU DSNL083I LLLLLLLLDB2P XXXXXX.YYYYYYY -NONE DSNL084I TCPPORT=446 SECPORT=0 RESPORT=447 IPNAME=-NONE DSNL085I IPADDR=::123.45.678.000 DSNL086I SQL DOMAIN=xxx002.yyy.no DSNL086I RESYNC DOMAIN=xxx002.yyy.no DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE [...] 13971 102 36_Re: DB2 for z/OS V9 Catalog poster ?0_24_carol.sutfin@REGIONS.COM31_Thu, 21 Jan 2010 10:19:14 -0600323_ISO-8859-1 CA has a new V9 poster but you need about a 10 foot wall to put it on.

My personal favorite is a V2.3 poster about the size of a desk top blotter. It's on poster board and still sits on my desk at home.

Carol Sutfin Corporate DBA Regions Financial Corp. (205)261-5214 carol.sutfin@regions.com [...] 14074 323 36_Re: DB2 for z/OS V9 Catalog poster ?13_Bell, Raymond20_Raymond_Bell@BMC.COM31_Thu, 21 Jan 2010 17:19:29 +0100382_iso-8859-1 A-ha. I also now have your address. So I know where to send the phony dog poo... ;o)

I'm too mean to post you a BMC one so you'll have to swing by at IDUG Europe this year (wherever it ends up being) and pick one up from the stand.

MfG,





Raymond PS. Sysindexspacestats is such an important table we thought we'd mention it twice. [...] 14398 128 36_Re: DB2 for z/OS V9 Catalog poster ?16_Rasmussen, Steen22_Steen.Rasmussen@CA.COM31_Thu, 21 Jan 2010 11:29:22 -0500444_iso-8859-1 Hi Carol - I agree - I really enjoyed the desk version back in the early 90's, but unfortunately IBM has quadrupled the number of tables in the catalog

Steen Rasmussen CA



-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of carol.sutfin@REGIONS.COM Sent: Thursday, January 21, 2010 10:19 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS V9 Catalog poster ? [...] 14527 77 36_Re: DB2 for z/OS V9 Catalog poster ?9_Sam Orner21_sorner@AESSUCCESS.ORG31_Thu, 21 Jan 2010 11:33:21 -0500611_utf-8 This is the third time I have applied for the poster. Maybe a third time is the charm!



(Embedded image moved to file: pic21264.jpg)





From: "Rasmussen, Steen"

To: DB2-L@WWW.IDUGDB2-L.ORG

Date: 01/21/2010 11:21 AM

Subject: Re: [DB2-L] DB2 for z/OS V9 Catalog poster ?

Sent by: "IDUG DB2-L"











Guten Tag Roy,

In fact – just go to www.ca.com/db In the upper right hand corner you can order one yourself and we’ll snail mail it to you Ji [...] 14605 149 23_LUW TEMPSPACE Disk Full9_Andy Hunt21_andy_hunt@YAHOO.CO.UK31_Thu, 21 Jan 2010 17:17:56 +0000559_utf-8 Hi, We are running DB2 ESE V7 on AIX. Obviously an unsupported release, but I can't do anything about that. We are getting disk full errors on Tempspace when there appears to be lots of space. Googling shows other users have had this but I can't find a satisfactory conclusion. There seems to be lots of free space and no OS system constraints. There is only one query running when the error occurs. Only messages come out about Container 0. I was expecting that it would also try to use Container 1 too. The ContPage is always around 191300. That [...] 14755 30 47_Re: Connectivity between DB2 sub systems in zOS11_Ted MacNEIL18_eamacneil@YAHOO.CA31_Thu, 21 Jan 2010 17:45:27 +0000330_- >I disagree Ted. Data-sharing is a more complicated means of connecting 2 subsystems.

You're entitled to your opinion. I didn't do it directly, myself; the DBA's and SYSPROG's that did it 15 years ago, at a bank I worked for found it relatively easy. Simpler than IMSPLEX, and infinitely less complex than CICSPLEX. [...] 14786 174 27_Re: LUW TEMPSPACE Disk Full12_Phil Gunning22_pgunning@GUNNINGTS.COM31_Thu, 21 Jan 2010 13:41:55 -0500443_UTF-8 You might be filling up one directory in the filesystm and when that happens with SMS then DB2 considers tablespace full, even if space avail in other container, a guess would be 0 is on /FS1. DB2 will stripe writes to both containers in round robin fashion. To resolve increase size of that filesystem and make sure you have enough disk space. Else you have a bug, and since V7 has long been history, that would be a problem....PG [...] 14961 34 36_Re: DB2 for z/OS V9 Catalog poster ?13_Jim Wankowski23_jim.wankowski@QUEST.COM31_Thu, 21 Jan 2010 18:53:25 +0000285_utf-8 Hi Jan We have the V9 z/OS posters here at Quest (COmplete with HIST tables!). We don't have a link to request them, but if you send me your mailing adress I can get some in the mail for you. They actually are double sided. V9 z/OS on one side and DB2 LUW 9.5 on the other. [...] 14996 73 47_Re: Connectivity between DB2 sub systems in zOS13_Seibert, Dave26_Dave.Seibert@COMPUWARE.COM31_Thu, 21 Jan 2010 14:00:11 -0500310_utf-8 Hello again Ted,

See the simple few steps described by Hanne & by Kirk. As long as TCPIP is enabled, with 2 or 3 rows inserted into the CDB, you're up and running with DRDA /DDF. You can be all set in a couple minutes.

Setting up data-sharing seems a tiny bit more involved than that. [...] 15070 60 36_Re: DB2 for z/OS V9 Catalog poster ?8_Bob Kota23_bob.kota@EXELONCORP.COM31_Thu, 21 Jan 2010 13:11:44 -0600489_us-ascii Jim,

Is there a Flash version of the catalog you can share with the DB2-L community?

Thanks.

Bob Kota Information Technology - Database Services CHQ 46/NE/010/01 Office: (312)-394-7523 Pager: (877)-396-9345





-----Original Message----- From: IDUG DB2-L [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Jim Wankowski Sent: Thursday, January 21, 2010 12:53 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] DB2 for z/OS V9 Catalog poster ? [...] 15131 131 47_Re: Connectivity between DB2 sub systems in zOS7_Ed Long19_rdhm99a@PRODIGY.NET31_Thu, 21 Jan 2010 11:46:24 -0800632_iso-8859-1 I concur with Mr. Seibert. Data Sharing is much trickier than DDF.Edward Long ________________________________ From: "Seibert, Dave" To: DB2-L@WWW.IDUGDB2-L.ORG Sent: Thu, January 21, 2010 2:00:11 PM Subject: Re: [DB2-L] Connectivity between DB2 sub systems in zOS Hello again Ted, See the simple few steps described by Hanne & by Kirk. As long as TCPIP is enabled, with 2 or 3 rows inserted into the CDB, you're up and running with DRDA /DDF. You can be all set in a couple minutes. Setting up data-sharing seems a tiny bit more involved than that. Dave The contents of this e-mail are [...] 15263 50 48_Access Production From non-Production Subsystems13_Steve Runtsch26_steve.runtsch@ASSURANT.COM31_Thu, 21 Jan 2010 15:53:57 -0600535_US-ASCII We currently do not generally allow access to production DB2 z/OS subsystems from non-production subsystems or vice versa (via three-part names or CONNECT, for example). We do have a couple of exceptions which DBAs have become accustomed to using, and this ability does simplify some database maintenance activities. For example, extracting DDL from one subsystem and using it to create objects in a second subsystem can be done in a single job step. Now we have been asked to provide that convenience on all subsystems. [...] 15314 179 25_HOTDUG meeting on Jan. 268_Lo, Mary17_Mary.Lo@TGSLC.ORG31_Thu, 21 Jan 2010 16:04:15 -0600352_us-ascii HOTDUG will ring in the new year with an exciting meeting on DB2 for LUW. Find out why all the buzz about pureScale from IBM and learn index design best practices from performance expert, Scott Hayes.

Date/Time: Jan. 26, 2010 (Tuesday) 8:45 a.m. - 12:00 noon Location: Teachers Retirement System, 1000 Red River St., Room 110 West [...] 15494 39 29_DB2 v8 NFM and DB2 v9 Upgrade10_Teldb2kals22_teldb2kals@TELSTRA.COM31_Thu, 21 Jan 2010 22:43:31 +0000422_utf-8 Hi,

We are just planning to convert from v8 CM to NFM. The planning and implementation may last a few months. And v8's End-Of-Support date is probably round the corner, maybe end of 2011, which means we have to start planning to implement v9 as well soon.

Now, can we fasttrack v8 NFM conversion, and do the v9 upgrade at the same time ? (in effect reduce the no. of times we have to do testing). [...] 15534 306 27_Re: LUW TEMPSPACE Disk Full12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Fri, 22 Jan 2010 00:03:59 +0100621_Windows-1252

Andy,

using SMS, you're possibly not short on space, but on inodes?



Peter





> Date: Thu, 21 Jan 2010 17:17:56 +0000 > From: andy_hunt@YAHOO.CO.UK > Subject: [DB2-L] LUW TEMPSPACE Disk Full > To: DB2-L@WWW.IDUGDB2-L.ORG > > Hi, > We are running DB2 ESE V7 on AIX. Obviously an unsupported release, but > I can't do anything about that. We are getting disk full errors on Tempspace > when there appears to be lots of space. Googling shows other users have > had this but I can't find a satisfactory conclusion. There seems to be lots of > free space and [...] 15841 152 36_Re: DB2 for z/OS V9 Catalog poster ?12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Fri, 22 Jan 2010 00:11:48 +0100285_iso-8859-1

CA has one made of Tyvek. Nearly as rock-solid as DB2 itself ;-)

I had ordered one from their homepage and since then they keep on sending me another one every few weeks. If they run out of stock, let me know your address and I'll send you one of these. [...] 15994 79 33_Re: DB2 v8 NFM and DB2 v9 Upgrade12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Thu, 21 Jan 2010 17:21:42 -0600377_UTF-8 Kals:

We are seeing a number of customers who are getting caught by the automatic rebinds caused by a version-to-version migration. V8 rebound V3R1 or lower packages. V9 will rebind V4R1 or lower. Many people got past the V8 automatic rebinds, but are getting caught by V9. As with V8 there is an IBM program that will detail those packages that are at risk. [...] 16074 282 47_Re: Connectivity between DB2 sub systems in zOS12_Peter Suhner24_peter_suhner@HOTMAIL.COM31_Fri, 22 Jan 2010 00:34:31 +0100439_iso-8859-1



Well, apart from discussions on which feature is more complicated to have up and running, they actually provide quite different functionality.





Of course DataSharing will allow you to directly share the same set of user data, including catalog and directory. IMHO this is less a feature which I would use to interconnect two DB2 subsystems than to enhance availability and throughput. [...]