1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l July 2004, week 2 2 18 41_Re: Clustering indexes and DPSIs (DB2 V8)33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Thu, 8 Jul 2004 02:51:44 -0500378_- Nick

I asked the original question, and as others replied and as I understand it, a DPSI can be defined as clustering (according to the manual). And in the meantime, because of all the answers I got, I understand, where I can make use of this feature.

BTW I am a member of the T'P'F-club as well and I am glad to see, that Terry is back again in this list. [...] 21 48 24_Check queries for DB2 V833_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Thu, 8 Jul 2004 02:57:33 -0500322_- Hi

I just look at the various queries, IBM developped, to check, whether a migration to V8 is possible without any problems. But unfortunately, there is no documentation, what the individual queries are good for. Most of the queries are easy to understand, but there is one single query, I've problems with: [...] 70 73 41_Re: Clustering indexes and DPSIs (DB2 V8)12_Cianci, Nick19_nick.cianci@EDS.COM30_Thu, 8 Jul 2004 18:32:02 +1000530_iso-8859-1 Thanks Walter,

I've been reading a bit further into the V8 release notes, and I am rapidly coming to the appreciation that there is more to this DPSI than meets the eye, especially in association with Table Partitioning (instead of the old index based partitioning).

But in the words of one "Big Kev" Australian that Terry might know: "... I'm excited !". I can think of a couple of applications that I've worked on in the past where rotating partitions & Table based partitioning MIGHT have helped. [...] 144 22 23_Indexspace Reallocation11_Akay Sharma18_aomprakash@CSC.COM30_Thu, 8 Jul 2004 04:15:59 -0500389_- Some of our indexspaces are exceeding 100 extents. I have prepared the procedure to increase the extent sizes. Would someone please very the steps as listed below.

1) Start tablespaces in utility mode. 2) Backup tablespaces. 3) Increase the primary/secondary extents sizes for the indexspaces. 4) Rebuild all indexes. 5) Backup tablespaces. 6) Start tablespaces in r/w mode. [...] 167 108 27_Re: Indexspace Reallocation19_Alekos Papadopoulos13_apapad@NBG.GR30_Thu, 8 Jul 2004 12:52:10 +0200523_ISO-8859-7 Hello Akay,

By the word 'extents' I assume DB2 for Z/OS, by the word 'rebuild' I assume version >=6. I can -somehow- understand the second step (backup of tablespaces before), but I don't understand the reason for the second tablespace backup (step 5).

You don't change your data, you only change your indexes, so tablespace copies in step 2 and 5 must be identical. If you have a failure, you will have to rebuild your indexes anyway. Probably you had in mind an indexspace backup in step 5? [...] 276 48 27_Re: Indexspace Reallocation18_Franz Josef Pohlen15_fjpohlen@GMX.DE30_Thu, 8 Jul 2004 11:48:16 +0200412_iso-8859-1 couldn't you achieve the same result with an alter index primary and secondary allocation and subsequently reorg the indices.

I'm not a real db2 expert, but it is just a thought.

----- Original Message ----- From: "Akay Sharma" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, July 08, 2004 11:15 AM Subject: Indexspace Reallocation [...] 325 81 27_Re: Indexspace Reallocation14_Grainger, Phil20_Phil.Grainger@CA.COM30_Thu, 8 Jul 2004 11:01:54 +0100473_iso-8859-1 You could, and it'd probably be faster than the rebuild too

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Franz Josef Pohlen Sent: 08 July 2004 10:48 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Indexspace Reallocation [...] 407 18 8_DSNHMVHW12_Charles Tsao21_tsao.cct@FORCES.GC.CA30_Thu, 8 Jul 2004 07:10:31 -0500693_- Hello List,

Can someone tell me what does DSNHMVHW do ? ? ? The COBOL precompiler seems to generate a call to it when it encountered x number of lines ( including comment lines ).

Thanks

Charles Tsao

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 426 87 12_Re: DSNHMVHW18_McAuliffe, Stephen35_Stephen.McAuliffe@BANKOFAMERICA.COM30_Thu, 8 Jul 2004 13:31:15 +0100512_us-ascii I can't tell you what it does, but found this in the archive:

Date: Fri, 4 Jan 2002 12:33:12 -0600 Reply-To: DB2 Data Base Discussion List Sender: DB2 Data Base Discussion List From: Roger Miller Subject: Re: DSNHMVHW/DSNHADDR You need to have the modules linkedited into the routine or brought in dynamically at run time (DYNAM option for COBOL if my memory is holding up). There are some dependencies upon setup and levels of COBOL. [...] 514 118 12_Re: DSNHMVHW13_Bell, Raymond22_raymond.bell@LANDG.COM30_Thu, 8 Jul 2004 13:35:21 +0100771_iso-8859-1 Damn, there goes my theory about it being a piece of hardware to order CDs automatically from HMV...



Raymond Bell Database Administrator

-----Original Message----- From: McAuliffe, Stephen [mailto:Stephen.McAuliffe@BANKOFAMERICA.COM] Sent: 08 July 2004 13:31 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: DSNHMVHW



I can't tell you what it does, but found this in the archive:

Date: Fri, 4 Jan 2002 12:33:12 -0600 Reply-To: DB2 Data Base Discussion List Sender: DB2 Data Base Discussion List From: Roger Miller Subject: Re: DSNHMVHW/DSNHADDR You need to have the modules linkedited into the routine or brought in dynamically at run time (DYNAM option for COBOL [...] 633 38 27_Re: Indexspace Reallocation14_Avram Friedman32_Avram.Friedman@MORGANSTANLEY.COM30_Thu, 8 Jul 2004 08:35:52 -0400371_us-ascii Akay, You do not specify in your question if the indexes are VCAT to STOGROUP allocated. If they are VCAT allocated you need some delete / defines.

Starting / stopping database objects is often done async. by DB2, I would add a manual step to verify the object is in the state you expect especially if you have any long running never ending threads. [...] 672 108 27_Re: Indexspace Reallocation11_David Nance16_DWNance@FHSC.COM30_Thu, 8 Jul 2004 09:30:20 -0400316_US-ASCII I would question why you take the tablespaces away from your application for such a long period of time. Why not just alter the priqty/secqty, then run online reorgs? This way you only have a very short period of unavailability to your application processes. Also, would be easier/less costly for you. [...] 781 61 44_Re: Store Procedure -> Triggers -> Timestamp15_Gaston, Raymond17_GastonRay@ORU.COM30_Thu, 8 Jul 2004 09:41:15 -0400696_iso-8859-1 Thanks Binyamin... That seems to have clarified everything!!!

- Ray

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Binyamin Dissen Sent: Wednesday, July 07, 2004 5:07 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Store Procedure -> Triggers -> Timestamp



On Wed, 7 Jul 2004 16:48:44 -0400 "Gaston, Raymond" wrote:

:>We are invoking a Cobol SP that updates 4 columns of a row of a target table via one UPDATE command. Each of these columns has it's own "AFTER UPDATE" trigger defined that does an "INSERT" into an audit table. Thus 4 triggers are fired off and 4 records [...] 843 45 20_BMC CM Migration job13_Michael Ebert18_mebert@AMADEUS.NET30_Thu, 8 Jul 2004 16:39:48 +0200453_us-ascii Hi List,

I'm currently trying to migrate our existing BMC stuff (Change Mgr, DASD Mgr, Cat Mgr) to the newest versions. For some reasons, the Input members and Jobs (Job steps) for migrating Change Manager were not generated. Is there a way to re-generate just the missing jobs, preferably without having to wade through the entire installation CLIST again? I don't want to clobber my existing jobs/members (fairly heavily edited). [...] 889 77 24_Re: BMC CM Migration job0_17_sjvagnier@AEP.COM30_Thu, 8 Jul 2004 10:54:07 -0400459_us-ascii I have backed up my BMC target libraries, and then rerun the BMC installation CLIST to get the migration jobs in question generated. Then I just copied the jobs I needed that were generated to my backup BMC target libraries and then did a rename to put the correct target libraries back. I know it's a pain to go through the CLIST but any other way that's not an approved method by BMC may cause you problems down the road if support is needed. [...] 967 45 12_Re: DSNHMVHW11_Mark Ediger22_MEDIGER@TRANSUNION.COM30_Thu, 8 Jul 2004 10:04:12 -0500360_US-ASCII Wow this is obscure. From a 17 year old apar:

THIS IS CAUSED BY NOT DECLARING DSNHMVHW AS AN ALIAS FOR DSNHADDR: DSNHMVHW IS A CSECT WITHIN DSNHADDR. AN ALIAS FOR DSNHMVHW WOULD CAUSE EXECUTION TO BEGIN AT THIS EXTERNAL NAME IN DSHNADDR.

Whatever it does, it does it well because 1988 was the last time it was mentioned in an apar. [...] 1013 65 24_Re: BMC CM Migration job0_15_Ale.Eba@CGI.COM30_Thu, 8 Jul 2004 11:41:10 -0400476_US-ASCII Hi Michael, I couldn't find a way to restart the clist. I had to start it from the beginning many times to get the correct installation job $C40INST.

Ale Eba











Michael Ebert cc: Sent by: DB2 Data Subject: BMC CM Migration job Base Discussion List



07/08/2004 10:39 AM Please respond to DB2 Database Discussion list at IDUG [...] 1079 35 32_Eliminate duplicates in loadfile14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE30_Thu, 8 Jul 2004 18:03:59 +0200579_iso-8859-1 Hello, in order to setup a test environment I have to merge two identical tables, each having several million rows. All rows in the target table must remain, and many keys are identical in both tables. Therefore I get a several hours long discard phase when I LOAD RESUME YES. Now I intented to unload the target table and let SYNCSORT eliminate duplicates(SUM FIELDS=NONE), which only takes a few minutes. Which one of the duplicates is kept? It seems to be the one which comes first in the concatenation sequence of the input datasets. But I'm not sure if this [...] 1115 115 36_Re: Eliminate duplicates in loadfile13_Martin Packer24_martin_packer@UK.IBM.COM30_Thu, 8 Jul 2004 17:06:38 +0100500_ISO-8859-1 I can't speak for Syncsort but in DFSORT "OPTION EQUALS" would probably do it. This option says "preserve input order if keys are the same".

Martin

Martin Packer, MBCS Martin Packer/UK/IBM 020-8832-5167 in the UK (+44) (MOBX 273643, Internal 7-325167, Mobile 07802-245584)





"Heiss, Gerhard" To Sent by: DB2 Data DB2-L@WWW.IDUGDB2-L.ORG Base Discussion cc List Eliminate duplicates in loadfile [...] 1231 58 36_Re: Eliminate duplicates in loadfile19_Ohling, Tim R - CNF18_Ohling.Tim@CNF.COM30_Thu, 8 Jul 2004 09:10:51 -0700467_iso-8859-1 For Syncsort the EQUALS clause (e.g. SORT FIELDS=(..,..,..),EQUALS) will cause records with equal sort field values to be kept in input sequence, causing the first one to be retained by the SUM statement.

Tim

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Heiss, Gerhard Sent: Thursday, July 08, 2004 9:04 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Eliminate duplicates in loadfile [...] 1290 126 24_Re: BMC CM Migration job12_Hilton, Tina23_thilton@RANDOMHOUSE.COM30_Thu, 8 Jul 2004 11:18:48 -0500305_us-ascii I agree with Steve. You might be able to borrow someone else's jcl and make it work but it's heavily dependent on what release of DB2 the old version was installed on and if DB2 was upgraded during that time. I wouldn't chance it.

Tina Hilton Random House Bertelsmann Shared Services [...] 1417 372 36_Re: Eliminate duplicates in loadfile10_Bruce, Mae19_Mae.Bruce@OA.MO.GOV30_Thu, 8 Jul 2004 11:11:57 -0500541_iso-8859-1 Syncsort uses a EQUALS/NOEQUALS parm. With EQUALS, the first record read is retained. With NOEQUALS, the record which is retained is determined arbitrarily.

Mae

-----Original Message----- From: Martin Packer [mailto:martin_packer@UK.IBM.COM] Sent: Thursday, July 08, 2004 11:07 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Eliminate duplicates in loadfile



I can't speak for Syncsort but in DFSORT "OPTION EQUALS" would probably do it. This option says "preserve input order if keys are the same". [...] 1790 134 36_Re: Eliminate duplicates in loadfile0_22_sally.mir@WACHOVIA.COM30_Thu, 8 Jul 2004 12:56:50 -0400535_ISO-8859-1 Another thing you could try is the ONLINE LOAD RESUME (SHRLEVEL CHANGE). In the case of duplicate rows, only the one being loaded is discarded, which is different from the old-fashioned load utility, which discards both records (the existing one in the table and the one being loaded). The only thing you'd have to keep in mind is the fact that it has to run LOG YES. If you can handle the logging, you might want to take a look. It would eliminate having to unload the target table and reload it with the sorted file. [...] 1925 19 29_Copy a DB2 database to Oracle12_Felton, John25_Felton.John@PRINCIPAL.COM30_Thu, 8 Jul 2004 12:24:07 -0500286_iso-8859-1 We are a DB2 for OS/390 version 7 environment. Due to a merger, we have need to replicate a DB2 database to Oracle, on a Unix box. I'd appreciate any advice, tips, etc about the most efficient and effective way to accomplish this, as well as things to watch out for ... [...] 1945 13 16_LOB out of space4_Ivan18_ikmrb@ALLSTATE.COM30_Thu, 8 Jul 2004 13:09:36 -0500608_- LOB type object is in many extents. What is preferred method one can use to reduce the number of extents, or increase the object size ?

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 1959 60 20_Re: LOB out of space14_Gonella, Vamsi22_Vamsi.Gonella@21ST.COM30_Thu, 8 Jul 2004 11:31:56 -0700472_iso-8859-1 We had a similar problem some time ago.

We did an alter to increase the space allocations then took an image copy followed by a recover to copy. Make sure that no updates happen between image copy and recovery.

Regards Vamsi



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Ivan Sent: Thursday, July 08, 2004 11:10 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: LOB out of space [...] 2020 10 13_-951 SQL code8_Michelle21_GagliardiM2@AETNA.COM30_Thu, 8 Jul 2004 14:07:30 -0500538_- Anyone know what a -951 is? I can't find it in any documentation.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 2031 39 17_Re: -951 SQL code10_Gyau, Paul15_pgyau@GEICO.COM30_Thu, 8 Jul 2004 15:31:31 -0400427_- From IBM DB2 UDB Message Refrence Volume 2 Ver 7 documentation -951 Sqlcode is

The object of type cannot be altered because it is currently in use by same application process

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Michelle Sent: Thursday, July 08, 2004 3:08 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: -951 SQL code [...] 2071 68 17_Re: -951 SQL code12_McKown, John26_john.mckown@UICIINSCTR.COM30_Thu, 8 Jul 2004 14:34:12 -0500357_US-ASCII SQL0951N

The object object-name of type object-type cannot be altered because it is currently in use by the same application process.

Explanation: An ALTER statement or SET INTEGRITY statement for an object cannot be issued when it is either locked or in use.

The statement cannot be processed. The object is not altered. [...] 2140 85 17_Re: -951 SQL code0_17_sjvagnier@AEP.COM30_Thu, 8 Jul 2004 15:36:09 -0400708_us-ascii For SQL/DS I found this on IBM Web site:



APAR: PL29020



PROBLEM SUMMARY: **************************************************************** * USERS AFFECTED: USERS WHO NEED ADDITIONAL INFORMATION ABOUT * * THE ERROR CODES RETURNED BY THE SQL/DS * * INTERFACE. * **************************************************************** * RECOMMENDATION: * ****************************************************************





PROBLEM CONCLUSION: ADDITIONAL DOCUMENTATION IS NECESSARY IN THE ESE REFERENCE MANUAL SC387004-0. AN ADDITIONAL PAGE NEEDS TO BE ADDED TO CHAPTER THIRTEEN. THE NEW PAGE NUMBER WILL BE 13-19. THE PAGE SHOULD LOOK LIKE THE FOLLOWING: [...] 2226 22 25_Re: End of an longggg era0_16_mscarpa@CESVE.IT30_Thu, 8 Jul 2004 21:41:54 +0200719_US-ASCII Hi Leslie

We discussed your choice and I see that it's a good choice !

Good luck for you future job and many thanks for the help you provided to me in many cases and to all other listers.

Best regards

Max Scarpa

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 2249 143 36_Re: Eliminate duplicates in loadfile13_Horacio Villa17_hvilla@AR.IBM.COM30_Thu, 8 Jul 2004 16:43:37 -0300407_ISO-8859-1 Pablo,

con este mail recordé lo que hay que poner en el SORT para que quede el primer registro que encuentra (de los que tienen clave duplicada):

OPTION EQUALS

HV





"Ohling, Tim R - CNF" To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Eliminate duplicates in loadfile Sent by: DB2 Data Base Discussion List [...] 2393 88 17_Re: -951 SQL code0_17_sjvagnier@AEP.COM30_Thu, 8 Jul 2004 15:36:09 -0400708_us-ascii For SQL/DS I found this on IBM Web site:



APAR: PL29020



PROBLEM SUMMARY: **************************************************************** * USERS AFFECTED: USERS WHO NEED ADDITIONAL INFORMATION ABOUT * * THE ERROR CODES RETURNED BY THE SQL/DS * * INTERFACE. * **************************************************************** * RECOMMENDATION: * ****************************************************************





PROBLEM CONCLUSION: ADDITIONAL DOCUMENTATION IS NECESSARY IN THE ESE REFERENCE MANUAL SC387004-0. AN ADDITIONAL PAGE NEEDS TO BE ADDED TO CHAPTER THIRTEEN. THE NEW PAGE NUMBER WILL BE 13-19. THE PAGE SHOULD LOOK LIKE THE FOLLOWING: [...] 2482 46 27_Re: Windows script language4_Smit24_robert.erwin.smit@WXS.NL30_Thu, 8 Jul 2004 22:30:41 +0200775_iso-8859-1 Reimer,

Perl is a good scripting language.

Greetings,

Robert.

----- Original Message ----- From: "Reimer" Newsgroups: bit.listserv.db2-l To: Sent: Thursday, July 08, 2004 3:16 AM Subject: Windows script language



> Hi, > > I would like to make a script to verify and warn operators when some DMS > tablespace is almost without free disk space. > > Does anybody have a suggestion of a Windows script language I could use? > > Thanks in advance!!! > > -------------------------------------------------------------------------- ------- > Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. [...] 2529 44 27_Re: Windows script language15_Binyamin Dissen26_bdissen@DISSENSOFTWARE.COM30_Thu, 8 Jul 2004 23:36:21 +0300595_us-ascii On Thu, 8 Jul 2004 22:30:41 +0200 Smit wrote:

:>Perl is a good scripting language.

I like CSL (C scripting language)

http://csl.sourceforge.net/index.html

:>----- Original Message ----- :>From: "Reimer" :>Newsgroups: bit.listserv.db2-l :>To: :>Sent: Thursday, July 08, 2004 3:16 AM :>Subject: Windows script language



:>> Hi,

:>> I would like to make a script to verify and warn operators when some DMS :>> tablespace is almost without free disk space. [...] 2574 63 27_Re: Windows script language18_Gert van der Kooij16_gkooij@XS4ALL.NL30_Thu, 8 Jul 2004 22:48:09 +0200736_iso-8859-1 I like Object Rexx, v2 can also interface with WMI and OLE objects etc.

----- Original Message ----- From: "Binyamin Dissen" To: Sent: Thursday, July 08, 2004 10:36 PM Subject: Re: Windows script language



> On Thu, 8 Jul 2004 22:30:41 +0200 Smit wrote: > > :>Perl is a good scripting language. > > I like CSL (C scripting language) > > http://csl.sourceforge.net/index.html > > :>----- Original Message ----- > :>From: "Reimer" > :>Newsgroups: bit.listserv.db2-l > :>To: > :>Sent: Thursday, July 08, 2004 3:16 AM > :>Subject: Windows script language > > > :>> Hi, [...] 2638 146 36_Re: Eliminate duplicates in loadfile13_Horacio Villa17_hvilla@AR.IBM.COM30_Thu, 8 Jul 2004 16:43:37 -0300407_ISO-8859-1 Pablo,

con este mail recordé lo que hay que poner en el SORT para que quede el primer registro que encuentra (de los que tienen clave duplicada):

OPTION EQUALS

HV





"Ohling, Tim R - CNF" To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Eliminate duplicates in loadfile Sent by: DB2 Data Base Discussion List [...] 2785 57 33_Re: Copy a DB2 database to Oracle11_Grant Allen28_Grant.Allen@TOWERSOFT.COM.AU30_Fri, 9 Jul 2004 09:32:13 +1000457_iso-8859-1 John,

The big question is will this be a one off task, a regular dump of data, or a live form of replication? Once you've answered this, you can consider Oracle's SQL*Loader for one-off or regular dumps (it's a very powerful util - think LOAD on steroids and you have the right idea), or for heterogeneous replication, you can try Oracle's Gateway products or DB2's equivalents (Information Integrator? Is this available on 390/zOS?) [...] 2843 50 33_Re: Copy a DB2 database to Oracle12_Cianci, Nick19_nick.cianci@EDS.COM30_Fri, 9 Jul 2004 11:26:37 +1000345_- If it's a once off and the data is not too volumous then you might be able to wrangle it through ODBC (& DB2 Connect)

If you want to near real-time replicate then you'll need to log scrape with DPropR (Data Propagator) & I believe Information Integrator will dump it onto Oracle for you. This I believe gives the best through put. [...] 2894 265 27_Re: Indexspace Reallocation22_Ashok Kumar Om Prakash18_aomprakash@CSC.COM30_Fri, 9 Jul 2004 11:17:15 +0800477_windows-1253 Hi Alekos and everyone,

Just wish to clarify that we are using DB2 V7.1 on OS/390, we have just recently switch from IDMS to DB2. My experience and knowledge on DB2 is still at its infancy level.

As at this moment I have no experience on online-reorg and wish to try sometime later, but due to the urgency in increasing the extents sizes and the fact that this index belongs to a small table we are able to stop the database for a short period. [...] 3160 164 22_HOTDUG meeting July 1312_Robert Brock28_brock@KRYPTON-CONSULTING.COM30_Fri, 9 Jul 2004 00:30:08 -0500505_iso-8859-1

Heart of Texas DB2 User Group

Third Quarterly Meeting Tuesday, July 13, 2004 8:30 AM - 11:30 AM

Location: Teachers Retirement System 1000 Red River St., Room 110 West Austin, TX

Breakfast Sponsor: HLS Technologies Inc.

08:30 - 9:00 Registration and Breakfast 09:00 - 10:00 How an Access Path Gets Selected - part 1 Gerry Hodge - HLS Technologies 10:00 - 10:30 Break 10:30 - 11:30 How an Access Path Gets Selected - part 2 Gerry Hodge - HLS Technologies [...] 3325 15 36_Re: Eliminate duplicates in loadfile14_Heiss, Gerhard20_Gerhard.Heiss@RUV.DE30_Fri, 9 Jul 2004 08:52:49 +0200526_us-ascii Thanks to all who responded :-)

Gerhard

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3341 450 27_Re: Indexspace Reallocation15_Patrick Hignett33_Patrick.Hignett@MORGANSTANLEY.COM30_Fri, 9 Jul 2004 10:04:03 +0100309_iso-8859-1 That would do it. However when you alter the secondary extent this alter will take effect when the next secondary allocation is taken so give it a lot of apsce now and the at your convenience do an other alter to the correct values and a reorg or rebuild.

Ashok Kumar Om Prakash wrote: [...] 3792 25 27_DB2/OS390 secondary extents10_Steve Lamb20_steven.lamb@CGEY.COM30_Fri, 9 Jul 2004 05:05:04 -0500315_- Dear Listers,

When I were 'lad, I was told that DB2 took a new secondary extent when the current one reached 50% full. However, there are various tablespaces on our system where the difference between the Hi-alloc RBA and the Hi-used RBA for the datsets is less than 50% of the secondary extent size. [...] 3818 20 31_Re: DB2/OS390 secondary extents10_Steve lamb20_steven.lamb@CGEY.COM30_Fri, 9 Jul 2004 05:16:51 -0500778_- Further to the last message, I'm failing to understand the following line fron the v7 Admin Guide:

"DB2 extends data sets when:

10 percent of the smaller allocation space (but not over 10 allocation units such as tracks or cylinders) exceeds the remaining space"

Regards,

Steve

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 3839 60 31_Re: DB2/OS390 secondary extents14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 9 Jul 2004 14:37:17 +0100461_iso-8859-1 Wooh - looks like some instructions I've seen on cheap Asian electronics!

I think what it means is:

DB2 will create a new secondary extent when the freespace left in the current extent is LESS than (10% of the (smaller of priqty or secqty)) or 10 (tracks or cylinders).

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com [...] 3900 223 27_Re: Indexspace Reallocation13_Solecki, Rohn19_Rohn.Solecki@GWL.CA30_Fri, 9 Jul 2004 08:44:19 -0500424_Windows-1253 Since it is a small table (of course size is relative ...), and you have the window to stop the TS, and you are learning DB2, it seems to me that now would be good time to try online reorg (instead of a rebuild). Being able to do online Reorgs is a great help, but being familiar and CONFIDENT in them, and coming up with "rules/guidelines" of when they will and won't work in your shop is a greater help. [...] 4124 57 26_IN list causes TB scan ???0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Fri, 9 Jul 2004 08:50:27 -0500347_us-ascii Hi there,

This is just a heads up. (Btw, OS/390 2.10, DB2 V7.1 here.)

I'm working on some SQL for a coworker, using Visual Explain and then DB2 PM to watch the execution. The SQL is modestly large (with several joins and sub-selects), so I won't post the whole thing. One thing that surprised me this morning was this: [...] 4182 93 30_Re: IN list causes TB scan ???13_Solecki, Rohn19_Rohn.Solecki@GWL.CA30_Fri, 9 Jul 2004 08:58:35 -0500492_iso-8859-1 I suspect that the optimiser is reorganising the separate equals clauses into alphabetical order. Try entering the IN list in alpha order, i.e.

apin.doc_status in ('a','f','M','R','S','V')

That is a recommendation I have seen in a couple of places (also remove duplicate entries, if any, from an IN list).

Rohn Solecki Software Developer Group Systems Support Services, Great-West Life Phone: (204) 946-4271 Fax: (204) 946-7064 Email: Rohn.Solecki@gwl.ca [...] 4276 51 30_Re: IN list causes TB scan ???0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Fri, 9 Jul 2004 09:21:38 -0500598_us-ascii Hi,

I forgot to mention that tried the alphabetical in list sequence as well. No change.

Stg





"Solecki, Rohn" cc: Sent by: DB2 Data Subject: Re: IN list causes TB scan ??? Base Discussion List



07/09/2004 08:58 AM Please respond to DB2 Database Discussion list at IDUG











I suspect that the optimiser is reorganising the separate equals clauses into alphabetical order. Try entering the IN list in alpha order, i.e. [...] 4328 37 30_Re: IN list causes TB scan ???33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE30_Fri, 9 Jul 2004 09:30:35 -0500337_- Hi

I not sure, what the optimizer really does, but I will list things, which could be true:

1. The IN-list will not be sorted, except it is IN (subquery). So the recommondation is to code the IN-list so that the value with the highest probability is the first, than with the second and so on. (That I know for sure) [...] 4366 82 30_Re: IN list causes TB scan ???8_Ron Root24_ron.root@CPA.STATE.TX.US30_Fri, 9 Jul 2004 09:37:41 -0500386_us-ascii I have observed that once the number of items in the in list exceeds some percent of the cardinality of the table (not sure what %) the optimizer decides to do a scan. When the list gets large I have had success inserting the values into a temp table and doing join. Also, the recommendation I have seen are to put the most commonly occurring values first in the IN list. [...] 4449 35 30_Re: IN list causes TB scan ???0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Fri, 9 Jul 2004 09:47:36 -0500453_us-ascii Regarding Walter's insight:

>> Then I would guess, it will be transformed >> to an IN-list and a tablespace scan will be chosen.

I changed ... or apin.doc_status = 'A' or ( apin.doc_status = 'C' and apin.check_date > '2004-06-30' ) )

to just

... or apin.doc_status = 'A' or apin.doc_status = 'C')

VE now says its a tablespace scan, supporting your theory that all alike OR's are converted to an IN-list. [...] 4485 208 30_Re: IN list causes TB scan ???11_David Nance16_DWNance@FHSC.COM30_Fri, 9 Jul 2004 10:50:06 -0400432_US-ASCII Steve, See you've had a few replies. I think Walter hit on what your problem is. Here's how I think it can be corrected. This is untried, but I think will get you index access again, but will cause the date check to be stage 2.

Version B:

where ( apin.doc_status in ('M','F','R','S','V','A','C') and (case when apin.doc_status = 'C' then apin.check_date else '2004-07-01' end) > '2004-06-30' ) ) and ... [...] 4694 205 30_Re: IN list causes TB scan ???0_23_KUMBHAS1@NATIONWIDE.COM30_Fri, 9 Jul 2004 11:12:06 -0400303_us-ascii Hi,

I just tried similar query.

Select * from table1 where name = 'smith' and state = 'oh' or state = 'al' or state = 'fl'

I have an index on Name, State. When I did explain on the above query it gave me match colmns are 2 and I explained same query with IN predicate [...] 4900 57 33_Re: Copy a DB2 database to Oracle11_Ray Tibbits19_TIBBIRT@GW.CCSD.NET30_Fri, 9 Jul 2004 08:11:34 -0700578_US-ASCII Is this a one time conversion or an on going process? Also, what version of Oracle will you be running?

If this is a one time conversion, then I would create flat files and use SQLLoader. See Oracle 8i/9i Database Utilities Manual.

If this is an ongoing (frequent uploads from DB2 to Oracle) process, you might want to look at Oracle Warehouse Builder with a DB2 gateway. Even though the name sounds funny, I have found it to be a good tool for moving data from one database to another. You can create PL/SQL procedures that you can schedule through [...] 4958 16 25_Re: End of an longggg era12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 9 Jul 2004 10:22:33 -0500304_- Thanks from me and many others as well. You've added insight and humor when needed to our discussions, as well as helping with a number of customer situations. We wish you all the best and will miss your contributions.

Roger Miller, speaking for myself and many others in the DB2 community [...] 4975 65 23_Unique constraint names16_Nichols, Suzanne27_Suzanne_Nichols@KYFBINS.COM30_Fri, 9 Jul 2004 11:33:05 -0400317_us-ascii Can anyone tell me what situation causes a unique contraint to be added to the sysibm.sysconst ? How are they named if you have more than one per table? Can I specify the name somehow? I have unique indexes that did not create unique contraints. Are they only created when there is a primary contraint? [...] 5041 139 27_Re: Unique constraint names14_Grainger, Phil20_Phil.Grainger@CA.COM30_Fri, 9 Jul 2004 17:04:56 +0100551_iso-8859-1 Suzanne

Version 7 allowed you to create a table and specify which columns should be unique. These columns then MUST have a unique index created on them.

Phil Grainger Computer Associates Product Manager, DB2 Tel: +44 (0)161 928 9334 Fax: +44 (0)161 941 3775 Mobile: +44 (0)7970 125 752 phil.grainger@ca.com

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On Behalf Of Nichols, Suzanne Sent: 09 July 2004 16:33 To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Unique constraint names [...] 5181 35 12_Re: DSNHMVHW12_Roger Miller19_millerrl@US.IBM.COM30_Fri, 9 Jul 2004 11:05:47 -0500527_- What is your concern? It's a lot easier to answer questions accurately about the externals: e.g. function or performance, than about how the internals or how the product works. That way you are more likely to get an answer that does not change with some other slight variation in the situation or a release or a PTF or environment or understanding. The precompiler is setting up the interface to the DB2 subsystem. This has not had any questions for performance or function that I can recall in the past 15 years or so. [...] 5217 40 30_Re: IN list causes TB scan ???14_Larry Kintisch19_lkint@IX.NETCOM.COM30_Fri, 9 Jul 2004 12:19:07 -0700604_us-ascii Hi Steve and others responding, As you may know, the "IN" predicate is usually invoked as multiple "Probes" of a matching index if there is one: probe for ='M' ; find qualifying entries; probe for ='F' ; find qualifying entries; etc. As we teach in Tapio's "Cost-Saving Database Index Design" class, failure to effectively use indexes to do the qualification of the result is usually quite costly. This is either due to random reads to the table, or as you saw, table scans. An index on (apin.doc_status, apin.check_date) might be beneficial. Just for comparison, try a UNION or UNION ALL: [...] 5258 23 41_DB2 access to Code-1 via stored procedure12_Mark Fleming21_mfleming@ALLSTATE.COM30_Fri, 9 Jul 2004 11:33:59 -0500635_- Group1 Software, you may know, is a company that specializes in address standardization products (and probably a number of other services). We use their Code-1 Plus product to standardize our addresses. It has batch and on- line interfaces. In batch, you have to include the VSAM files in the JCL while from a CICS environment, they are dynamically allocated (I think). We are interested in accessing their interface(s) via a DB2 stored procedure. I am aware that Code-1 provides interface modules, such as C1MATCHI, C1MATCHB, etc., but has anyone successfully incorporated these into DB2 stored procedures? We are looking into [...] 5282 116 30_Re: IN list causes TB scan ???14_Richard Atkins16_rratkp@YAHOO.COM30_Fri, 9 Jul 2004 10:16:26 -0700527_us-ascii Ron,

Try replacing your query with EXISTS in alphabetical order rather than using IN .. It should Help avoid TS scan...

Cheers

"Solecki, Rohn" wrote: I suspect that the optimiser is reorganising the separate equals clauses into alphabetical order. Try entering the IN list in alpha order, i.e.

apin.doc_status in ('a','f','M','R','S','V')

That is a recommendation I have seen in a couple of places (also remove duplicate entries, if any, from an IN list). [...] 5399 63 30_Re: IN list causes TB scan ???0_30_Steve_Grimes@AISMAIL.WUSTL.EDU30_Fri, 9 Jul 2004 14:27:44 -0500428_us-ascii Hello again,

To David:

This version:

where ( apin.doc_status in ('M','F','R','S','V','A','C') and (case when apin.doc_status = 'C' then apin.check_date else '2004-07-01' end) > '2004-06-30' ) ) and ...

Did result in index scans, but not the multi-index scan of the "or" version. And, if you trust the milliseconds given by VE, (which I usually don't), they increased from 1462 to 2359. [...] 5463 61 45_Re: DB2 access to Code-1 via stored procedure16_Proctor, William25_William.Proctor@TGSLC.ORG30_Fri, 9 Jul 2004 14:57:18 -0500568_us-ascii I would have to investigate how it was setup but I know that we are using code 1 from stored procedures. We are running on an OS/390 2.10 z800 box using DB2 V7.1.



Bill Proctor Database Administrator (Adabas/DB2) Texas Guaranteed Student Loan Corp. Austin, Texas Phone: 512-219-4847





-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Mark Fleming Sent: Friday, July 09, 2004 11:34 AM To: DB2-L@www.idugdb2-l.org Subject: DB2 access to Code-1 via stored procedure [...] 5525 62 43_Dropping Syscopy Rows with Tablespace Drops16_Nichols, Suzanne27_Suzanne_Nichols@KYFBINS.COM30_Fri, 9 Jul 2004 16:32:17 -0400722_us-ascii Has it always been true that rows in sysibm.syscopy for a particular tablespace would be dropped when the tablespace is dropped? Was there an old version when this was not true?

Suzanne Nichols Contract DBA Kentucky Farm Bureau



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 5588 99 47_Re: Dropping Syscopy Rows with Tablespace Drops12_Brill, Steve22_Steve.Brill@ECOLAB.COM30_Fri, 9 Jul 2004 15:48:23 -0500480_us-ascii Yes, at least since version 2.3.

_____

From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Nichols, Suzanne Sent: Friday, July 09, 2004 3:32 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Dropping Syscopy Rows with Tablespace Drops





Has it always been true that rows in sysibm.syscopy for a particular tablespace would be dropped when the tablespace is dropped? Was there an old version when this was not true? [...] 5688 125 47_Re: Dropping Syscopy Rows with Tablespace Drops14_Wayne Driscoll24_wayne.driscoll@QUEST.COM30_Fri, 9 Jul 2004 15:55:31 -0500462_us-ascii Suzanne, When a tablespace is dropped, as far as I can remember, the syscopy rows where always deleted, however, I do recall a discussion about SYSLGRNGX records being deleted, and for that reason it wasn't a bad idea to do a modify recovery before doing a drop. Wayne Driscoll Sr. Product Architect Quest Software http://www.quest.com/db2 wdriscoll@listserv.quest.com NOTE: All opinions are strictly my own. EMail Address in sig must be modified. [...] 5814 144 27_Re: Unique constraint names14_Wayne Driscoll24_wayne.driscoll@QUEST.COM30_Fri, 9 Jul 2004 15:59:45 -0500565_us-ascii SYSCONST was added with V7. Prior to that you identify that a column should be unique, which would leave the table in incomplete status until a unique index was created, but the method of enforcement was less than ideal, inserting a row in SYSCOLUMNS with a blank column name to track those columns that needed unique indexes. With V7 if you say UNIQUE on the column definition, then DB2 will insert a row in SYSCONST do track this information. If however, you create a table and later create a unique index on a column that was not defined as either [...] 5959 23 37_DB2 NT Control Center Script question11_Kathy Jones19_JonesKS@GW.CCSD.NET30_Fri, 9 Jul 2004 14:22:42 -0700351_US-ASCII We have two scripts set up in control center. The first one is set up to be run nightly. We put the 2nd script dependent on the first by saying if the first script ended ok or not, run the second script BUT the second script is not running. No information in the 1st scripts results as to why it isn't run, no job history. Any thoughts? [...] 5983 143 47_Re: Dropping Syscopy Rows with Tablespace Drops14_Matthews, John25_JMatthews@MEDNET.UCLA.EDU30_Fri, 9 Jul 2004 14:42:24 -0700744_- I find it interesting that IVP job DSNTEJ0 still doesn't have MODIFY RECOVERY for the IVP objects(as of V7 anyway).

-----Original Message----- From: Wayne Driscoll [mailto:wayne.driscoll@QUEST.COM] Sent: Friday, July 09, 2004 1:56 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Dropping Syscopy Rows with Tablespace Drops



Suzanne, When a tablespace is dropped, as far as I can remember, the syscopy rows where always deleted, however, I do recall a discussion about SYSLGRNGX records being deleted, and for that reason it wasn't a bad idea to do a modify recovery before doing a drop. Wayne Driscoll Sr. Product Architect Quest Software http://www.quest.com/db2 wdriscoll@listserv.quest.com [...] 6127 71 30_Re: IN list causes TB scan ???12_Myron Miller22_myronwmiller@YAHOO.COM30_Fri, 9 Jul 2004 15:19:30 -0700560_us-ascii Steve What is the cardinality of doc_status? Is it by any chance less than 24?

Myron

--- Steve_Grimes@AISMAIL.WUSTL.EDU wrote: > Hi there, > > This is just a heads up. (Btw, OS/390 2.10, DB2 V7.1 here.) > > I'm working on some SQL for a coworker, using Visual Explain and then DB2 > PM to watch the execution. The SQL is modestly large (with several joins > and sub-selects), so I won't post the whole thing. One thing that > surprised me this morning was this: > > Version A: > > where ( apin.doc_status = 'M' > or apin.doc_status [...] 6199 20 30_Re: IN list causes TB scan ???0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Sat, 10 Jul 2004 08:56:25 -0500644_us-ascii Hello. Regarding:

What is the cardinality of doc_status? Is it by any chance less than 24?

Myron

Yes, it is. Its probably around 10.

Stg

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 6220 43 30_Re: IN list causes TB scan ???12_Myron Miller22_myronwmiller@YAHOO.COM31_Sat, 10 Jul 2004 08:09:26 -0700372_us-ascii Well, that's the reason for the TBSCAN. The optimizer looks at the cardinality of the variables in the IN List and if the total of the cardinalities for that list is greater than 25% of the table, it figures its more efficient to do a tablespace scan. Each OR = is evaluated individually so INDEXes are chosen. ORs most times will also cause MX INDEX usage. [...] 6264 56 30_Re: IN list causes TB scan ???14_Larry Kintisch19_lkint@IX.NETCOM.COM31_Sat, 10 Jul 2004 15:11:59 -0700416_iso-8859-1 Hi Steve and other responders, I asked Tapio Lahdenmäki, former IBM Finland DB2 expert and author of the "TL120 Cost-Saving Database Index Design" 2-day class [see below], to comment:

As with any optimizer problem, the SQL call may be too difficult for the optimizer (no cost estimate done) or the cost estimates may be way off. Not sure which one is the reason here but the Admin Guide says: [...] 6321 81 30_Re: IN list causes TB scan ???13_Terry Purcell18_tpurcel@US.IBM.COM31_Sat, 10 Jul 2004 18:44:00 -0500413_- Steve,

This behavior is expected.

The OR's cannot be converted to an IN list in the first query because one branch of the OR contains a compound AND. So multi-index access is the only choice for index matching (screening or tablespace scan are also options).

For the 2nd, IN list access (accesstype N) does not support list prefetch, and therefore is not valid for multi-index access. [...] 6403 106 30_Re: IN list causes TB scan ???13_Terry Purcell18_tpurcel@US.IBM.COM31_Sun, 11 Jul 2004 11:16:13 -0500595_ISO-8859-1 Larry,

FYI, the statement about difficulty, and "no cost estimate done" is incorrect.

There are types of predicates which are difficult to estimate an accurate cost (such as expressions).

With regard to difficulty, the zparm MXTBJOIN controls the maximum number of tables your site allows in a single FROM clause (default 15 in V7, can be increased to 225, which is the V8 default). If you exceed this you will receive a -129. It is possible for access path selection to receive a -101 if we run out of storage due to a very large number of tables, but this [...] 6510 39 28_IFI306 and the "lazy commit"9_John Krew21_john_krew@HOTMAIL.COM31_Sun, 11 Jul 2004 20:06:40 +0200454_windows-1255 We have been working with the IFI 306 facility to read archive and active log records from the DB2 log.

We are encountering the following scenario when reading active log records created in a fairly inactive system:

1. A DB2/CICS update transaction is performed, concluding (of course) with the commit.

2. All before- and after-images of the update operations performed are almost immediately available via IFI 306 [...] 6550 67 28_Re: Check queries for DB2 V812_Roger Miller19_millerrl@US.IBM.COM31_Sun, 11 Jul 2004 15:39:01 -0500650_- Call the service line or do it electronically. This is an old problem, but it is generally very serious. It's not directly related to V8. See APAR PQ48024 for a little discussion of the issue.

Roger Miller

On Thu, 8 Jul 2004 02:57:33 -0500, Walter Jani=?ISO-8859-1?Q?=DFen?= wrote:

>Hi > >I just look at the various queries, IBM developped, to check, whether a >migration to V8 is possible without any problems. But unfortunately, there >is no documentation, what the individual queries are good for. Most of the >queries are easy to understand, but there is one single query, I've >problems [...] 6618 43 31_Re: DB2/OS390 secondary extents12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 11 Jul 2004 15:51:25 -0500458_- One of the bigger clues to your problems is in your last line, using Guaranteed Space. The real title should be guaranteed more failures and manual management than any other choice - non-SMS or real SMS management. One of the guidelines we state as forcefully as we can, is not to use guaranteed space. There are too many anomalies with it. Documenting all of the options across the many products, components, release levels and PTFs is not feasible. [...] 6662 127 30_Re: IN list causes TB scan ???12_Myron Miller22_myronwmiller@YAHOO.COM31_Sun, 11 Jul 2004 17:17:34 -0700522_us-ascii Terry, You may have missed some of the original questions Steve asked. When he rewrote the query as an IN list, DB2 chose a Tablespace scan instead in IN list access. The problem with having all the values in an IN list is that the cardinality of the of the variable is low enough that having all the values in the IN list counts for between 70 and 80% of the rows in the table. You know more about this obviously than I do, but it was my understanding that when the cardinality of the IN list is this high, [...] 6790 169 22_HOTDUG meeting July 1312_Robert Brock28_brock@KRYPTON-CONSULTING.COM31_Sun, 11 Jul 2004 19:42:33 -0500583_iso-8859-1 Don't forget the Austin HOTDUG meeting Tuesday. Hope to see you there.



Heart of Texas DB2 User Group

Third Quarterly Meeting Tuesday, July 13, 2004 8:30 AM – 11:30 AM

Location: Teachers Retirement System 1000 Red River St., Room 110 West Austin, TX

Breakfast Sponsor: HLS Technologies Inc.

08:30 – 9:00 Registration and Breakfast 09:00 – 10:00 How an Access Path Gets Selected – part 1 Gerry Hodge – HLS Technologies 10:00 – 10:30 Break 10:30 – 11:30 How an Access Path Gets Selected – part 2 Gerry Hodge – HLS Technologies [...] 6960 121 30_Re: IN list causes TB scan ???16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Sun, 11 Jul 2004 23:37:12 -0700441_us-ascii The Admin. Guide has this to say:

"For MX accesses and index access with list prefetch, IN-list predicates cannot be used as matching predicates."

So, this should be as simple as:

Going by the queries (both queries), DB2 optimizer evaluates that multiple index access (MX) is the best. However, due to the above limitation..bad luck in case B!! MX access and matching IN predicates are mutually exclusive. [...] 7082 52 58_Error while using HPU for Delimited format: DB2 V7 OSV2R1011_Samit Goyal19_samgoyal@IN.IBM.COM31_Mon, 12 Jul 2004 16:37:45 +0530472_US-ASCII Hi All,

I am trying to use HPU utility on DB2 V7 - OSV2R10 platform for extracting data in delimited format but am getting an error. I have used HPU earlier also for DSNTIAUL format, but I am using it first time for Delimited format.

It would be of great help for me if you can help me in fixing the error as I do not want to use a program to convert data to delimited format or use CONCAT and TRIM functions in the query to make it delimited. [...] 7135 102 62_Re: Error while using HPU for Delimited format: DB2 V7 OSV2R1010_Price, Ray18_Ray.Price@DRKW.COM31_Mon, 12 Jul 2004 12:15:33 +0100423_- Hi.

There is a manual for HPU on the IBM website, you can download it.

Your error:

INZU203E COLUMN VARIABLE WITHOUT HEADER : COLUMN name Explanation: This message occurs when LOAD SYSIN GENERATION is requested. It is not possible to load a variable column without a length header. Return Code: 12 User Response: Choose an appropriate format for the UNLOAD SYSIN if you want to reload your data. [...] 7238 207 47_Re: Dropping Syscopy Rows with Tablespace Drops14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 12 Jul 2004 12:17:45 +0100317_iso-8859-1 Well, since 1.2! (getting too old....)

Also, as mentioned later SYSLGRNGX rows are NOT deleted at the time of the DROP, they are deleted (if ever) when a new object is created with the same internal IDs as the dropped object.

The "proper" way to drop a tablespace (according to IBM) is: [...] 7446 167 62_Re: Error while using HPU for Delimited format: DB2 V7 OSV2R1011_Samit Goyal19_samgoyal@IN.IBM.COM31_Mon, 12 Jul 2004 16:51:35 +0530468_US-ASCII Hi Ray,

Thanks for the prompt reply.

I was unnecessarily giving load control card as I did not want to reload the data back. I removed LOADDDN parameter and it worked !

Thanks again.

Regards, Samit Goyal.

MIW Database Administrator, IBM Global Services India, Prestige Towers,99 Residency Road, Bangalore - 560025 Board: 91-80-22079999 Ext 4013 Mobile: 91-9886192819; Resi : 91-80-51202211; e-mail: samgoyal@in.ibm.com [...] 7614 29 47_Re: Dropping Syscopy Rows with Tablespace Drops13_Michael Ebert18_mebert@AMADEUS.NET31_Mon, 12 Jul 2004 13:29:27 +0200368_us-ascii Actually taking the IC is not strictly necessary. You just need at least one row in SYSCOPY. Using QUIESCE is easier than creating an IC for this purpose. Also I've found that you should STOP & START the TS before the utility; otherwise SYSLGRNX rows might not be deleted (this seems to happen if the TS is not pseudo-closed when you run the utilities). [...] 7644 29 31_Re: DB2/OS390 secondary extents10_Steve Lamb25_steven.lamb@CAPGEMINI.COM31_Mon, 12 Jul 2004 09:22:34 -0500562_- Phil - thanks for the translation - that makes sense now! PS Why do your messages always come out on the List so that I have to use the proportional font option to read them?! Just curious ...

Roger, yes we know that the Guaranteed Space attribute is not recommended. We (the DBA's) have told the customer that this causes problems, but unfortunately there isn't the resource / will from the customer to get things changed. As long as the failure rate is low enough for them to live with they're happy to leave things as they are. They're planning [...] 7674 96 30_Re: IN list causes TB scan ???13_Terry Purcell18_tpurcel@US.IBM.COM31_Sat, 10 Jul 2004 18:44:00 -0500413_- Steve,

This behavior is expected.

The OR's cannot be converted to an IN list in the first query because one branch of the OR contains a compound AND. So multi-index access is the only choice for index matching (screening or tablespace scan are also options).

For the 2nd, IN list access (accesstype N) does not support list prefetch, and therefore is not valid for multi-index access. [...] 7771 22 15_TCB / SRB ratio0_27_mamccormack@STATESTREET.COM31_Mon, 12 Jul 2004 10:39:36 -0400483_US-ASCII A coworker sent me these questions, and I don't know the answers. Any help will be appreciated. Here is his text verbatim. Mark --------------------------------------------------------------------------- The DB2 SMF accounting records track CPU TCB time. The DDF work consumes a lot of SRB time. Do you know if the TCB / SRB ratio is similar for DDF and CICS & batch initiated DB2 work? Or does the DDF workload use a lot more SRB time for the same amount of TCB time? [...] 7794 63 28_IFI306 and the "lazy commit"9_John Krew21_john_krew@HOTMAIL.COM31_Sun, 11 Jul 2004 20:06:40 +0200454_windows-1255 We have been working with the IFI 306 facility to read archive and active log records from the DB2 log.

We are encountering the following scenario when reading active log records created in a fairly inactive system:

1. A DB2/CICS update transaction is performed, concluding (of course) with the commit.

2. All before- and after-images of the update operations performed are almost immediately available via IFI 306 [...] 7858 235 47_Re: Dropping Syscopy Rows with Tablespace Drops14_Grainger, Phil20_Phil.Grainger@CA.COM31_Mon, 12 Jul 2004 12:17:45 +0100317_iso-8859-1 Well, since 1.2! (getting too old....)

Also, as mentioned later SYSLGRNGX rows are NOT deleted at the time of the DROP, they are deleted (if ever) when a new object is created with the same internal IDs as the dropped object.

The "proper" way to drop a tablespace (according to IBM) is: [...] 8094 31 47_Re: Dropping Syscopy Rows with Tablespace Drops13_Michael Ebert18_mebert@AMADEUS.NET31_Mon, 12 Jul 2004 13:29:27 +0200368_us-ascii Actually taking the IC is not strictly necessary. You just need at least one row in SYSCOPY. Using QUIESCE is easier than creating an IC for this purpose. Also I've found that you should STOP & START the TS before the utility; otherwise SYSLGRNX rows might not be deleted (this seems to happen if the TS is not pseudo-closed when you run the utilities). [...] 8126 83 28_Re: Check queries for DB2 V812_Roger Miller19_millerrl@US.IBM.COM31_Sun, 11 Jul 2004 15:39:01 -0500650_- Call the service line or do it electronically. This is an old problem, but it is generally very serious. It's not directly related to V8. See APAR PQ48024 for a little discussion of the issue.

Roger Miller

On Thu, 8 Jul 2004 02:57:33 -0500, Walter Jani=?ISO-8859-1?Q?=DFen?= wrote:

>Hi > >I just look at the various queries, IBM developped, to check, whether a >migration to V8 is possible without any problems. But unfortunately, there >is no documentation, what the individual queries are good for. Most of the >queries are easy to understand, but there is one single query, I've >problems [...] 8210 109 62_Re: Error while using HPU for Delimited format: DB2 V7 OSV2R1010_Price, Ray18_Ray.Price@DRKW.COM31_Mon, 12 Jul 2004 12:15:33 +0100432_iso-8859-1 Hi.

There is a manual for HPU on the IBM website, you can download it.

Your error:

INZU203E COLUMN VARIABLE WITHOUT HEADER : COLUMN name Explanation: This message occurs when LOAD SYSIN GENERATION is requested. It is not possible to load a variable column without a length header. Return Code: 12 User Response: Choose an appropriate format for the UNLOAD SYSIN if you want to reload your data. [...] 8320 143 57_Plans granted privileges on tables - what does that mean?13_Rhea, Matthew29_Matthew.Rhea@INFARMBUREAU.COM31_Mon, 12 Jul 2004 10:29:28 -0500523_iso-8859-1

In DB2 V7 for Z/OS, what does it mean to have a plan that has explicit table privileges listed in SYSIBM.SYSTABAUTH? I thought only the owner of a plan needed to have the table privileges, and then execute authority would be given to the users needed to execute the plan. I don't understand what the privileges for a plan are used for. I found a couple of references to table privileges for plans in the Administration Guide, but I didn't see why someone would need to make use of these privileges. [...] 8464 87 23_Unique Constraint names12_Lappe, Cathy19_Cathy_Lappe@BMC.COM31_Mon, 12 Jul 2004 10:52:13 -0500577_iso-8859-1 Hi Suzanne! As Libby Horton mentioned, I published an article in IDUG Solutions Journal (Sept. 2001 issue) which explains the changes in V7, including examples, for unique constraints. It is on our website too, at

http://documents.bmc.com/products/documents/23/66/12366/12366.pdf



To answer your questions: Q: Can anyone tell me what situation causes a unique contraint to be added to the sysibm.sysconst ? A: When you create a primary key or a unique constraint, a row is inserted into SYSIBM.SYSTABCONST. This catalog table is new in V7. [...] 8552 103 27_Re: Unique Constraint names12_Lappe, Cathy19_Cathy_Lappe@BMC.COM31_Mon, 12 Jul 2004 10:58:13 -0500663_iso-8859-1 I forgot to mention the title of the article -

Unique and Primary Key Constraints in DB2 for OS/390 Version 7 New Catalog Tables, New DDL parameters, New Restrictions or ... The "Unique" Case of the Missing Constraint

> -----Original Message----- > From: Lappe, Cathy > Sent: Monday, July 12, 2004 10:52 AM > To: 'DB2-L@WWW.IDUGDB2-L.ORG' > Cc: 'Suzanne_Nichols@KYFBINS.COM'; Shelton, Glenn; Lappe, Cathy > Subject: Unique Constraint names > > Hi Suzanne! As Libby Horton mentioned, I published an article in > IDUG Solutions Journal (Sept. 2001 issue) which explains the changes in > V7, including examples, for unique constraints. [...] 8656 62 53_Removing originating subsystem from datasharing group31_Douwe van Sluis, Infodemic B.V.26_d.b.van.sluis@INFODEMIC.NL31_Mon, 12 Jul 2004 19:04:45 +0200653_US-ASCII Are there any pitfalls for removing the originating member of a datasharing group.



Vriendelijke groet, Douwe van Sluis, Infodemic B.V.







--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 8719 77 57_Re: Removing originating subsystem from datasharing group0_17_sjvagnier@AEP.COM31_Mon, 12 Jul 2004 14:10:17 -0400461_us-ascii I don't know why you would want to do this since I have never removed the originating member, but I have removed a third and fourth member from a 4-way data sharing group without any problems. You will need to keep at a minimum the BSDS and active logs around for any recovery of data for a period of time until the last archive log of the retired member has expired. Check the Data Sharing Planning and Administration Guide for more information. [...] 8797 82 61_Re: Plans granted privileges on tables - what does that mean?15_Marcel Harleman25_marcel.harleman@HCCNET.NL31_Mon, 12 Jul 2004 22:17:58 +0200460_us-ascii Matthew,

if an owner-id BINDs a plan or package with VALIDATE(BIND) or DYNAMICRULES(BIND) it needs to have the privileges to execute all the SQL that is included in the DBRM(s). If one of the privileges gets withdrawn (or REVOKEd if you wish) this is no longer the case. So DB2 wants to invalidate all plans and packages that are dependent on that particular privilege and that privilege has to be registered somewhere: SYSIBM.SYSTABAUTH. [...] 8880 80 28_Re: Check queries for DB2 V810_Grace Chen20_grace.chen@CELERO.CA31_Mon, 12 Jul 2004 15:46:04 -0600639_-



Where can I get the various queries that IBM developed for checking Whether the migration to V8 is possible without any problem?

Grace

-----Original Message----- From: Roger Miller [mailto:millerrl@US.IBM.COM] Sent: July 11, 2004 2:39 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Check queries for DB2 V8

This message uses a character set that is not supported by the Internet Service. To view the original message content, open the attached message. If the text doesn't display correctly, save the attachment to disk, and then open it using a viewer that can display the original character set. [...] 8961 32 61_A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.421_Bryant II, David, ISD27_DBryant106@WORLDSAVINGS.COM31_Mon, 12 Jul 2004 16:51:18 -0500618_us-ascii Hello all.

Question:

What all would we have to do/change in order for us to go to 24X7 availability for our DB2 systems?

Any help would be greatly appreciated. I was just tasked with investigating this.

Dave



***************************************************************************** If you are not the intended recipient of this e-mail, please notify the sender immediately. The contents of this e-mail do not amend any existing disclosures or agreements unless expressly stated. ***************************************************************************** [...] 8994 262 30_Re: IN list causes TB scan ???13_Terry Purcell18_tpurcel@US.IBM.COM31_Mon, 12 Jul 2004 16:52:27 -0500490_ISO-8859-1 Hi Myron,

I did see that, but it seems my initial explanation did not cover the problem (even though DB2-L duplicated my email :-)).

From Steve's original query:

where ( apin.doc_status in ('M','F','R','S','V','A') or ( apin.doc_status = 'C' and apin.check_date > '2004-06-30' ) )

There is an IN (of 6 elements) and an OR (of 1 element for doc_status). This CANNOT be written to an IN (of 7 elements). So you CANNOT get IN (accesstype=N) access. [...] 9257 91 28_Re: Check queries for DB2 V80_20_pmclaren2@CSC.COM.AU31_Tue, 13 Jul 2004 08:13:34 +1000848_us-ascii Hi Grace,

DSNTIJP8 in recent V7 SDSNSAMP libraries contains what you are looking for..... Hopefully the action to take on rows returned lives somewhere in the V8 Install Guide...(not looked yet)

________________________________________________________________________________

Phil McLaren DB2 Systems Programming Computer Science Corporation. Melbourne, Australia ----------------------------------------------------------------------------------------

This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the [...] 9349 41 28_Re: Check queries for DB2 V833_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Tue, 13 Jul 2004 02:15:53 +0200347_iso-8859-1 Roger,

DSNTIJP8 fails if DECP contains DECIMAL=COMMA. I know there is APAR but this happen to often. There is a world outside the states and IBM should add such an environment in their test cases.

We had also the same issue with DB2 LUW (APAR LI70221). Each fixpack a hotfix for V8 but already fixed in V7 FP11/FP12. [...] 9391 43 40_SIRDUG presents Bonnie Baker, Aug. 19-2011_Kathy Lisle19_Kathy.Lisle@ATT.NET31_Mon, 12 Jul 2004 21:27:43 -0400328_us-ascii SIRDUG Meeting August 19 & 20, 2004

Featuring: Bonnie Baker

Bonnie's back, with two new offerings of things we ALL wish we knew a long time ago! We are pleased to announce that Bonnie Baker is returning to share her unique perspectives on DB2 in the sixth annual Bonnie Baker class hosted by SIRDUG. [...] 9435 16 20_Archive logs and HSM12_Michael Hall21_mhhall1@ATTGLOBAL.NET31_Tue, 13 Jul 2004 01:55:56 -0500663_- Does anyone have experience managing migration of archive logs using HSM? Any suggestions for appropriate settings?

Your assistance is appreciated.

Mike Hall Millennium II Inc.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 9452 187 18_Re: Setup Question14_Davage, Marcus29_Marcus.Davage@LLOYDSTSB.CO.UK31_Tue, 13 Jul 2004 09:37:58 +0100417_iso-8859-1 I can't say I've seen a reply to this, so I'll have a go.

As far as I'm aware, you'll need data joiner for access across different databases in the same instance - don't go that route.

Putting the new tables under a different schema is convenient if they are accessed by different application suites (packages, or SET SCHEMA = ) and the table references in the programs are unqualified. [...] 9640 55 40_Sybase to DB2 for OS/390 conversion ....16_Raquel Rodriguez28_raquel_rodriguezus@YAHOO.COM31_Tue, 13 Jul 2004 01:59:46 -0700361_us-ascii Hello Esteemed Listers,

We have been asked to look into the feasibility and the method of migrating a SYBASE application to DB2. It is also being debated whether the application should be converted to UDB for LUW or DB2 for OS/390. We are looking at IBM's MTK (Migration toolkit) for conversion. Had some specific questions regarding this: [...] 9696 47 35_Accesspath alteration due to Rebind0_34_karthick.iyemperumal@UKTRANSCO.COM31_Tue, 13 Jul 2004 10:16:59 +0100549_us-ascii Hi Listers,

We are using DB2 v4.1 on OS/390 in our site. Currently we are doing Data Archival on few production tables. As part of it, after archiving the data we did runstats and re-bind which changed the accesspath and caused performance degradation to one of the program. As we are not changing the source code, I would like to know if we can skip performing a rebind after running the reload & runstat utilities on the table. In this case will DB2 take the old accesspath or will it automatically generate a new accesspath, [...] 9744 99 39_Re: Accesspath alteration due to Rebind13_Mertens, Bart18_bart.mertens@CZ.NL31_Tue, 13 Jul 2004 11:44:13 +0200753_- If you don't rebind, the access path will not change.

met vriendelijke groeten, Bart Mertens Database Administrator DB2 CZ Actief in Gezondheid - Tilburg * (013) 593 8256 * bart.mertens@cz.nl



-----Oorspronkelijk bericht----- Van: karthick.iyemperumal@UKTRANSCO.COM [mailto:karthick.iyemperumal@UKTRANSCO.COM] Verzonden: Tuesday, July 13, 2004 11:17 Aan: DB2-L@WWW.IDUGDB2-L.ORG Onderwerp: Accesspath alteration due to Rebind

Hi Listers,

We are using DB2 v4.1 on OS/390 in our site. Currently we are doing Data Archival on few production tables. As part of it, after archiving the data we did runstats and re-bind which changed the accesspath and caused performance degradation to one of the program. As we are [...] 9844 125 39_Re: Accesspath alteration due to Rebind19_Alekos Papadopoulos13_apapad@NBG.GR31_Tue, 13 Jul 2004 12:55:47 +0200330_ISO-8859-7 Hello Karthick,

Keep in mind, that under some conditions (like drop/creating an index for example) an automatic rebind may occur, at an unexpected moment. IMHO a safer way is to store your current access path for all your packages in a plan table, and to compare every rebind for changes.

hth Alekos [...] 9970 56 20_DB2 for z/OS and WLM0_18_mflavell@I-TCS.COM31_Tue, 13 Jul 2004 11:30:14 +0100330_US-ASCII Hi,

I am migrating websphere/DB2 applications from pSeries to zSeries machines using Linux on z/VM and DB2 for z/OS. I am trying to set goals in WLM for the subsystems and stored procedures. I have been told that some sets of stored procedures must be given different goals from others in the same subsystem. [...] 10027 55 25_Re: XML Extender DB2 z/OS15_Slot, JP (Jaap)23_J.P.Slot@RF.RABOBANK.NL31_Tue, 13 Jul 2004 13:02:21 +0200601_iso-8859-1 This is a repost because I cannot believe no one in this list is using the XML extender.

-----Oorspronkelijk bericht----- Van: Slot, JP (Jaap) Verzonden: donderdag 1 juli 2004 9:41 Aan: 'DB2 Database Discussion list at IDUG' Onderwerp: XML Extender DB2 z/OS



Esteemed listers,

In our shop we're looking at using the DB2 XML extender for z/OS DB2 v7.1.0 Are there any expiriences with the DB2 XML extender, are there any issues regarding the use of the XML extender. If any, I would like to know about expiriences with performance and administration issues? [...] 10083 60 66_Re: A question concerning 24X7 availability for DB2 V7 on Z/OS 1.48_Ron Root24_ron.root@CPA.STATE.TX.US31_Tue, 13 Jul 2004 07:52:49 -0500477_us-ascii Dave, This a very complex question with many different answers. You should start by carefully defining what your company means by 24X7. No DB2 outages at all, ever? A 1-2 hour outage weekly or monthly? Update access to all tables during all of that time, or is read only acceptable during part of it? These questions and others should be answered before you begin. There have been several discussions on this topic on the listserv. You might review the archives. [...] 10144 86 44_Re: Sybase to DB2 for OS/390 conversion ....8_Ron Root24_ron.root@CPA.STATE.TX.US31_Tue, 13 Jul 2004 08:14:58 -0500615_us-ascii Raquel, Our experience.... We are in the final stages of converting several applications from Sybase to DB2 OS/390. A variety of Java and Visual Basic applications. We are using Neon Shadow to connect to DB2. In general (several exceptions) the database structures were migrated with little modifications from Sybase to DB2. In some cases the data resided on both platforms, so the application was changed to access the data as it exists on DB2. The SQL is not 100% compatible. The incompatibility that caused the most problems was the increased flexibility of the SUM and AVG functions in Sybase. We [...] 10231 41 23_Outsourced DBA question10_Tom Willis25_tom.r.willis@JPMORGAN.COM31_Tue, 13 Jul 2004 08:33:37 -0500532_us-ascii I would like to here from people who have had experience with the outsourcing of DBA services. I want to know the successes or lack thereof. My personal experience as well as articles I have read suggest that it is usually not a good idea to outsource DBAs due to the nature of the job and the fact that proximity to the environment is key to its success. I have been charged with researching this idea and I want to here from fellow DBAs what their experience has been. I realize that most if not all of us are going [...] 10273 83 65_Re: A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.40_17_u.heinrich@SEG.DE31_Tue, 13 Jul 2004 15:38:07 +0200351_us-ascii Hello Dave, Take into consideration, that you don't have a batch window anymore if you realize 24x7. So get familiar with the online feature of the utilities (if you haven't yet) and check your maintenance procedures. Have a close look for the online reorg running on a NPIX for a partitioned table space. This is definitely not online! [...] 10357 69 27_Re: Outsourced DBA question12_McKown, John26_john.mckown@UICIINSCTR.COM31_Tue, 13 Jul 2004 08:38:35 -0500554_US-ASCII > -----Original Message----- > From: DB2 Data Base Discussion List > [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Tom Willis > Sent: Tuesday, July 13, 2004 8:34 AM > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Outsourced DBA question > > > I would like to here from people who have had experience with the > outsourcing of DBA services. I want to know the successes or > lack thereof. > My personal experience as well as articles I have read > suggest that it is > usually not a good idea to outsource DBAs due to the nature > of the job and > the [...] 10427 121 57_Re: Removing originating subsystem from datasharing group11_Chris White18_crick616@YAHOO.COM31_Tue, 13 Jul 2004 08:44:34 -0500573_- Steve,

I don't know why the original poster wants to delete the originating member of a DS group, but we are considering the same thing in order to reduce recurring problems we have experienced with a DELTA value. In the mid '90s we enabled, disabled, then re-enabled one-way data sharing. There was a very good reason for this, but it's a longer story for another time. Disabling requires a special kind of cold start that sets the current RBA equal to the LRSN. This is not a problem for most shops, but our subsystem produced enough log records to outpace [...] 10549 77 24_Interpreting db2diag.log22_Wilson, David (cahoot)23_David.Wilson@CAHOOT.COM31_Tue, 13 Jul 2004 15:15:17 +0100385_iso-8859-1 Experts,

DB2 UDB V7.2 AIX 4.3 speaking.

I am using a product called Expect to run scripts to backup a database on our remote AIX servers. Often the backup will fail with the message "SQL2001N The utility was interrupted. The output data may be incomplete."

Checking the db2diag.log provides the following:-

"Starting a full database backup. [...] 10627 32 61_A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.40_27_mamccormack@STATESTREET.COM31_Tue, 13 Jul 2004 10:25:40 -0400549_US-ASCII Dave,

You ask a very broad question. I will not presume to give a comprehensive answer. I will say a few things about utilities.

1. If you no longer have a standalone window for shrlevel reference copies, you must switch to shrlevel change copies. Your recovery options are then reduced. It is improper to recover tocopy with a shrlevel change copy. (The utility will do it if so instructed, but your data might not be coherent.) You will need to set potential recovery points via quiesce and recover torba / tologpoint. [...] 10660 43 65_Re: A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.412_Roger Miller19_millerrl@US.IBM.COM31_Tue, 13 Jul 2004 09:23:21 -0500895_- I looked on the Technical Presentations and searched for availability, and found 39 items that match. There are also 22 redbooks. Here are a couple of starting suggestions: ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/techdocs/s l03.pdf ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/techdocs/O J8.pdf ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/techdocs/g 12.pdf

Roger Miller

On Mon, 12 Jul 2004 16:51:18 -0500, Bryant II, David, ISD wrote:

>Hello all. > >Question: > >What all would we have to do/change in order for us to go to 24X7 >availability for our DB2 systems? > >Any help would be greatly appreciated. I was just tasked with >investigating this. > >Dave > > >************************************************************************** *** >If you are not the intended [...] 10704 85 61_"DBMS Conversion Guide IDMS to DB2", publication GH20-7562-004_BuzW15_buzw@SWBELL.NET31_Tue, 13 Jul 2004 09:34:09 -0500828_us-ascii Anybody know where I can get a copy of this?

Buz Williams Sophisticated Business Systems Inc. 12750 Merit Drive, Suite 1105 Dallas, Texas 75251 Direct : 469-385-7237 Cell : 214-402-4359 Office : 972-664-9005 x132 800-801-9005 Fax : 972-664-9007 E-Mail : buzwilliams@soph.com World Wide Web: http://www.soph.com



--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-REQUEST@www.idugdb2-l.org. Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm 10790 48 66_Re: A question concerning 24X7 availability for DB2 V7 on Z/OS 1.413_Solecki, Rohn19_Rohn.Solecki@GWL.CA31_Tue, 13 Jul 2004 09:38:38 -0500445_iso-8859-1 Take a look in the Redbooks site, http://www.redbooks.ibm.com/. Search for "High Availability". I got 131 hits, most of them not relevant to your question, but some are. "Architecting High Availability e-business on IBMEserver zSeries" deals with an example of hardware OS design.

Also take a look at DB2 V8, they have done lots of 'good stuff' to reduce down time, especially regarding maintenance of partitioned tables. [...] 10839 91 44_Re: Sybase to DB2 for OS/390 conversion ....12_Roger Miller19_millerrl@US.IBM.COM31_Tue, 13 Jul 2004 09:47:26 -0500361_- I looked at the migration toolkit site, and the version for z/OS is not there yet. You should probably send the question directly to mtk@us.ibm.com

If you are porting, DB2 UDB for z/OS Version 8 adds a large amount of SQL, making the porting easier. As I understand our conversation, the migration toolkit is going to target only DB2 for z/OS V8. [...] 10931 30 31_z/os websphere/db2 jdbc db2 cpu17_KUSTERS, ED (TAO)30_ED.KUSTERS.tao@CCRA-ADRC.GC.CA31_Tue, 13 Jul 2004 10:44:00 -0400418_- Date: Tuesday, 13 July 2004 10:44am ET To: db2-l@www.idugdb2-l.org From: KUSTERS.ED@RCT Subject: z/os websphere/db2 jdbc db2 cpu

z/os websphere/db2 jdbc db2 cpu by transaction

Hi all,

Anyone out there getting db2 cpu info (in smf 101 records) broken down by individual java jdbc transaction on their z/os or os/390 systems running java apps on websphere application server (was) and db2 ?? [...] 10962 65 65_Re: A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.413_Bright, Randy20_Randy_Bright@BMC.COM31_Tue, 13 Jul 2004 09:48:55 -0500420_- (Open opportunity for short sales pitch)

Reorg Plus for DB2 from BMC Software, Inc. eliminates the NPI BUILD2 phase problem.

LoadPlus for DB2 from BMC Software, Inc. has an online LOAD REPLACE option.

If you want further information, contact me offline.

See, I said it would be short.

Randy Bright Architect, DB2 Utilities BMC Software, Inc. Randy_Bright@BMC.COM (800) 841-2031 [...] 11028 88 32_Re: IFI306 and the "lazy commit"19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 13 Jul 2004 10:32:59 -0500357_us-ascii This sounds as if it makes sense to me. If you don't want to 'wait' to see the commit you need to use the asynch interface and use READA instead of the synch interface (READS). Of course this will require other programming changes too but typically isn't a lot of code to convert to asynch, just a different way of coding then you usually do. [...] 11117 116 32_Fw: IFI306 and the "lazy commit"9_John Krew21_john_krew@HOTMAIL.COM31_Tue, 13 Jul 2004 19:16:27 +0200583_iso-8859-1 Thanks for the reply. Some follow-up questions:

1. I wasn't aware that the READA interface supports IFCID 0306. As of which version of DB2? (The doc for version 6.1 seems to indicate that only READS will work.)

2. Our problem is not that we have to "wait" indefinitely and can't get any other work done. If the commit log record is unavailable, then the READS returns with an end-of-log indication -- it doesn't wait. But we see that the commit log record (or end-UR record) is not available even for many minutes if the system is inactive and no other [...] 11234 98 19_Re: TCB / SRB ratio0_16_khampto1@TXU.COM31_Tue, 13 Jul 2004 11:39:01 -0500518_us-ascii I'll take a crack at this, but I an confident that many others on this list know far more than I do... DDF tasks use a pool of "preemptable SRB's" known as Enclaves, instead of the normal TCB that would be present in TSO, CICS, or batch. I would think that any TCB / SRB ratio you came up with for CICS and batch would not compare at all to DDF workload, because the DDF tasks basically have no TCB to charge time to, only the Enclave thread which is a preemptable SRB. So, if you have much DDF activity [...] 11333 350 28_Re: Check queries for DB2 V810_Grace Chen20_grace.chen@CELERO.CA31_Tue, 13 Jul 2004 10:56:47 -0600456_- Phil,

Thanks for your information.

Grace

-----Original Message----- From: pmclaren2@CSC.COM.AU [mailto:pmclaren2@CSC.COM.AU] Sent: July 12, 2004 4:14 PM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: Check queries for DB2 V8

Hi Grace,

DSNTIJP8 in recent V7 SDSNSAMP libraries contains what you are looking for..... Hopefully the action to take on rows returned lives somewhere in the V8 Install Guide...(not looked yet) [...] 11684 181 32_Re: IFI306 and the "lazy commit"19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Tue, 13 Jul 2004 12:09:58 -0500573_us-ascii In the v7 Admin guide: IBM now verifies that you can get the 306 via the READA interface... even though the doc in the V7 SDSNSAMP still only talks about using "IFI READS" for QW0306.

The V7 admin guide says: "READS allows your monitor program to read DB2 status information that is collected at the time of the IFI call. Monitor class 1 must be activated prior to any READS requests. The records available are for IFCIDs 0001, 0002, 0106, 0124, 0129, 0147, 0148, 0149, 0150, 0185, 0199, 0202, 0230, 0254 0306, 0316, and 0317. For a description of the [...] 11866 24 15_DSSIZE Question13_Robert Tilkes22_tilkesr@NATIONWIDE.COM31_Tue, 13 Jul 2004 12:19:06 -0500428_- I will be setting up a LARGE partitioned tablespace (with 12 numparts on 16KB pages) where the DSSIZE will be 8GB. To account for unplanned growth, I am considering setting the DSSIZE to 64GB. Obviously the DASD needs to be available and PRIQTY/SECQTY will need to be set accordingly so I don't run out of extents. If I am not mistaken, the RID for anything larger than 4GB is 5 bytes, so this impact should be the same. [...] 11891 75 24_Re: Archive logs and HSM13_Taddei, Cathy27_Cathy.Taddei@PACIFICORP.COM31_Tue, 13 Jul 2004 10:34:33 -0700530_us-ascii We archive to DASD in our smallest sandbox subsystem. The logs are handled by HSM and expire in 21 days, migrate directly to ML2, with no HSM backup (I have found HSM backups to be just one more opportunity for contention with DB2, so none of my DB2 datasets get backed up by HSM). Again, this is a VERY small subsystem -- the archive logs are 48 cylinders each, and on average are written 8 times a day. This works very well for us, as we sometimes run this subsystem on a sandbox MVS image that has no tape drives [...] 11967 23 46_z/os websphere/db2 jdbc db2 cpu by transaction11_Kusters, Ed26_Ed.Kusters@CCRA-ADRC.GC.CA31_Tue, 13 Jul 2004 13:33:42 -0400459_iso-8859-1 Hi all,

Anyone out there getting db2 cpu info (in smf 101 records) broken down by individual java jdbc transaction on their z/os or os/390 systems running java apps on websphere application server (was) and db2 ??

What I am looking for is a was/db2 equivalent for what we already have for cics/db2 transactions where dring thread re-use a pseudo signon is done and an smf 101 record is written out for each cics/db2 transaction. [...] 11991 18 11_DB2 cloning17_Donna Hippensteel24_d.hippensteel@PECORP.COM31_Tue, 13 Jul 2004 13:23:39 -0500385_- We are looking at a product called BCV5 which does fast cloning of DB2 databases or parts of databases. According to their website: "A database or just some tables can be copied within the same DB2 subsystem or to another subsystem." This sounds exactly like what we'd like to do. But first we'd really like to hear from anyone who has had any experience with the BCV5 product. [...] 12010 35 20_Archive logs and HSM0_27_mamccormack@STATESTREET.COM31_Tue, 13 Jul 2004 14:49:04 -0400326_US-ASCII Mike,

We write our archive logs to dasd and migrate (after about 2 days) via HSM directly to tape. We do this for all subsystems, prod and non-prod. In general it works well. There have been a few rough spots in a few cases. disclaimer: HSM installations are not all the same, your mileage may vary, etc. [...] 12046 159 24_Re: Archive logs and HSM11_David Nance16_DWNance@FHSC.COM31_Tue, 13 Jul 2004 15:17:27 -0400557_US-ASCII Mark has given some good suggestions about your dasd pool. We haven't yet had an instance where it sent our subsystem into a coma, but have come close. The way we ran into the problem is we have some audit processes that use the prior day's logs. These auditing jobs kicked off and pulled back several very large logs. We are trying to get our application group to change the way they do these audit processes as intermittent throughout the day. This way they would be able to get most of their info from the current log, instead of recalling [...] 12206 36 50_AW: z/os websphere/db2 jdbc db2 cpu by transaction33_Schiradin,Roland HG-Dir itb-db/dc28_SchiradinR@ALTE-LEIPZIGER.DE31_Tue, 13 Jul 2004 22:01:58 +0200454_iso-8859-1 Ed, how could anyone in this listserver help in this area??? You have to call IBM and perhaps raise a requirement if you need this and WAS doesn't provide such a info.

Roland

-----Ursprüngliche Nachricht----- Von: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] Im Auftrag von Kusters, Ed Gesendet: Dienstag, 13. Juli 2004 19:34 An: DB2-L@WWW.IDUGDB2-L.ORG Betreff: z/os websphere/db2 jdbc db2 cpu by transaction [...] 12243 50 15_Re: DB2 cloning0_16_DBMSUser@AOL.COM29_Tue, 13 Jul 2004 16:03:41 EDT429_US-ASCII Hi Donna, You may also wish to look at GENDB2 at www.recoveryknowledge.com The product will copy all tables in a database or exclude some tables using a exclude table to the same db2 or another db2 with restart ability on the load process if failure occurs. It will also change the creator of the tables if necessary. You can load and reload the same data over and over using the same source (data and cntl cards). [...] 12294 60 27_Re: Outsourced DBA question12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 13 Jul 2004 15:17:02 -0700415_us-ascii One company I was at for a while outsourced a bit of its Y2K DBA work to India. It was a real mixed bag and usually ended up with more work for the inhouse DBAs. Not to mention the issues with timing. Like making changes to production in their prime time which turned out to be in the middle of production batch. Communications and followup were big issues and needed to be managed very very closely. [...] 12355 65 25_Re: XML Extender DB2 z/OS12_Myron Miller22_myronwmiller@YAHOO.COM31_Tue, 13 Jul 2004 15:18:24 -0700568_us-ascii To be honest, I tried to use it on Z/OS and gave up. Its value was extremely limited and the implementation left a considerable bit to be desired.

--- "Slot, JP (Jaap)" wrote: > This is a repost because I cannot believe no one in this list is using the > XML extender. > > -----Oorspronkelijk bericht----- > Van: Slot, JP (Jaap) > Verzonden: donderdag 1 juli 2004 9:41 > Aan: 'DB2 Database Discussion list at IDUG' > Onderwerp: XML Extender DB2 z/OS > > > Esteemed listers, > > In our shop we're looking at using the DB2 [...] 12421 37 25_Re: XML Extender DB2 z/OS11_Grant Allen28_Grant.Allen@TOWERSOFT.COM.AU31_Wed, 14 Jul 2004 09:21:10 +1000342_iso-8859-1 > -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG]On > Behalf Of Slot, JP (Jaap) > Sent: Tuesday, 13 July 2004 21:02 > To: DB2-L@WWW.IDUGDB2-L.ORG > Subject: Re: XML Extender DB2 z/OS > > > This is a repost because I cannot believe no one in this list > is using the XML extender. [...] 12459 157 60_JDBC application SQL -805 for NULLID.SYSLN203 -- Help please14_Gonella, Vamsi22_Vamsi.Gonella@21ST.COM31_Tue, 13 Jul 2004 16:23:56 -0700379_iso-8859-1 Hi,

We have a Java application running on Websphere App server V5 on AIX V5.2 and accessing DB2 V7 on Z/OS using JDBC Universal driver type 2.

The Application is getting SQL -805 error for the package NULLID.SYSLN203 (and some times for the package NULLID.SYSLN303). The application does open a large number of cursors as pert of a search criteria. [...] 12617 86 24_Re: Archive logs and HSM13_Mertens, Bart18_bart.mertens@CZ.NL31_Wed, 14 Jul 2004 08:46:50 +0200457_- Mike,

We are writing our archive logs to disk (model 9's) and migrate them using HSM. We haven't had any problems with migration/recalling since we started using HSM with db2 archive logs (almost 3 years).

Stats: +- 1500 archive logs each month (2880 tracks each and dual logging, total of 3000 arc log datasets). 3 3390-9 volumes for the archive logs. Migrating starts when > 40% of the archive disk space is full, checked every hour. [...] 12704 401 64_Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please13_Mertens, Bart18_bart.mertens@CZ.NL31_Wed, 14 Jul 2004 08:49:39 +0200375_- Vamsi,

I don't know if I understand your question correctly. As far as I know the sqlcode -805 means that the package is not found and should not have anything to do with the number of open cursors.

I couldn't find the SYSLN203 / SYSLH203 in any of our DB2 V7 subsystems (x02 is the highest number we got). They are probably added through some APAR/PTF. [...] 13106 212 64_Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please18_Gert van der Kooij16_gkooij@XS4ALL.NL31_Wed, 14 Jul 2004 09:12:25 +0200838_iso-8859-1 Check the db2jdbcbind command and specify a bigger size.

Description is at: http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0010332.htm

Hope this helps.





----- Original Message ----- From: "Gonella, Vamsi" To: Sent: Wednesday, July 14, 2004 1:23 AM Subject: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please



> This is a multi-part message in MIME format. > > ------_=_NextPart_001_01C46930.78171C86 > Content-Type: text/plain; charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Hi, > =20 > We have a Java application running on Websphere App server V5 on AIX V5.2 a= > nd accessing DB2 V7 on Z/OS using JDBC Universal driver type 2. > =20 > The [...] 13319 81 38_R: Accesspath alteration due to Rebind14_Adrian Collett24_adriancollett@TISCALI.IT31_Wed, 14 Jul 2004 09:23:01 +0200376_US-ASCII Karthick,

If you're not going to Rebind you might as well skip the Runstats too. Unless you require the statistics for space management, and then in that case run the Runstats with the UPDATE SPACE option as opposed to UPDATE ALL/ACCESSPATH.

Is that really V4 or was it a typo ??

HTH

Ciao, Adrian Collett DB2 Consultant Milan, Italy. [...] 13401 36 35_Accesspath alteration due to Rebind13_Gary Bronziet26_gary.bronziet@COGITO.CO.UK31_Wed, 14 Jul 2004 04:05:46 -0500559_- Hi Listers

Knowing whether to re-bind after a change such as REORG/RUNSTATS is a challenge facing all DB2 DBA's. One of the earlier replies suggested that you "store your current access path for all your packages in a plan table, and to compare every rebind for changes". However, the real challenge is to identify changes to access path WITHOUT doing the rebind. As the poster of the original question pointed out - in this case, the access path change had a detrimental effect on performance, and that is something to be avoided at all costs. [...] 13438 87 39_Re: Accesspath alteration due to Rebind12_Gerald Hodge26_ghodge@HLSTECHNOLOGIES.COM31_Wed, 14 Jul 2004 05:05:04 -0500576_US-ASCII The question of rebinds always has potential exposures. There are 3rd party products that will tell you of impacts that have or will occur from a rebind. IBM also has such a product.

Given your release level, V4R1, some of the options available in later release are not there. There is nothing that requires a rebind after a reorg and runstats. If you know you have an acceptable then do not rebind. If there are performance problems then rebinding is an option. At V4R1 once the access path is changed through a bind there is no fall back through hints. [...] 13526 98 44_Doing AIX distributed binds on the mainframe14_Flatley, Lynne31_Lynne.Flatley@LIBERTYMUTUAL.COM31_Wed, 14 Jul 2004 08:25:00 -0400333_us-ascii We have several applications that need distributed binds. They're AIX bind files. We have some new DBAs here (myself included) who find the current bind process difficult, logging onto the mid-range box and using vi to edit the bind scripts. Is there any way these bind can be done on our mainframe? We're at z/OS 1.4. [...] 13625 135 39_Re: Accesspath alteration due to Rebind14_Seth Bienstock17_seth2@SKARVEN.NET31_Wed, 14 Jul 2004 08:36:25 -0400356_us-ascii Keeping my response focused on the limited information you've given...

To answer your question directly, unless an implicit or explicit BIND/REBIND took place, the access path will not change.

IMHO there are 2 possible ways that the performance of the application program could change as a result of your data archival process: [...] 13761 270 64_Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please15_McCardle, Corey23_CMcCardle@AMERISURE.COM31_Wed, 14 Jul 2004 08:40:55 -0400592_iso-8859-1 Good Morning Vamsi,

Here's what I'm Thinking....

I didn't see these packages until UDB v8. I am wondering if your using DB2 Connect v8 to communicate to UDB on OS390. Go to the v8 USERs guide page 243 it will explain your package in question (with little detail). Like you said an -805 is a not found, verify those packages exist on OS390. The initial connection (as long as the person is a SYSADMIN) would have created and bound those packages in OS390. If the person wasn't, you may need to bind those packages (db2clibh.bnd and db2clibn.bnd) from your remote [...] 14032 28 30_DB2 V7 Recovery using Parallel15_Michael Rennick31_Michael_Rennick@TAX.STATE.NY.US31_Wed, 14 Jul 2004 10:14:51 -0400513_US-ASCII Hi List Members:

Do any of you fellow professionals have a working JCL and correct syntax for Recovery using the Parallel options?

I have tried variations and get errors.

Thanks ahead for any suggestions. Below I have tried combinations of tolastcopy, tocopy, etc.

RECOVER TABLESPACE DEVMCCNT.SNCNTCTX DSNUM ALL PARALLEL(2) SECTDB05.DEVMCCNT.SNCNTCTX.P00001.D2004196 SECTDB05.DEVMCCNT.SNCNTCTX.P00002.D2004196

Michael J. Rennick DBA - New York State Tax & Finance [...] 14061 103 65_Re: A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.421_Bryant II, David, ISD27_DBryant106@WORLDSAVINGS.COM31_Wed, 14 Jul 2004 09:29:12 -0500425_us-ascii To all that responded...Thank you. I have some research to do it looks like.

Dave

-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Bright, Randy Sent: Tuesday, July 13, 2004 9:49 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: A question concerning 24X7 availablity for DB2 V7 on Z/OS 1.4



(Open opportunity for short sales pitch) [...] 14165 42 34_Re: DB2 V7 Recovery using Parallel19_Jardine, Lawrence J19_JardineLJ@AETNA.COM31_Wed, 14 Jul 2004 10:43:14 -0400541_us-ascii LISTDEF QLIST INCLUDE TABLESPA Here is an example...



LISTDEF QLIST INCLUDE TABLESPACES DATABASE DTWAP000 INCLUDE TABLESPACE DTWBP000.RT* EXCLUDE TABLESPACE DTWBP000.RTWBB* RECOVER LIST QLIST PARALLEL (3) TAPEUNITS (3)

| Larry Jardine | Production DBA | Aetna |



-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@IDUGDB2-L.ORG] On Behalf Of Michael Rennick Sent: Wednesday, July 14, 2004 10:15 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: DB2 V7 Recovery using Parallel [...] 14208 103 25_Re: XML Extender DB2 z/OS15_Campbell, Wayne28_Wayne.Campbell@COURTS.WA.GOV31_Wed, 14 Jul 2004 08:01:27 -0700546_us-ascii There doesn't seem to be a lot of information out there. When I went to IDUG in Orlando, I was hoping there would be a session or two on it, But it was not to be.

Here's my experience with it. It seems to work as advertised. This was my first time working with XML so I started by going though the tutorial in the XML Extender Administration and Programming Guide. The tutorial shows the steps but don't explain what's going on and why. There were lots of little problems which could probably have been avoided if I had read [...] 14312 267 19_Re: TCB / SRB ratio10_Bruce, Mae19_Mae.Bruce@OA.MO.GOV31_Wed, 14 Jul 2004 08:35:47 -0500580_- I have found the following discussion in the MXG documentation. It is a copy of IBM's library item Q576462.

Q: User is doing some DDF testing and has run some accounting reports. SRB times (both class 1 and 2) are about 8 times the TCB times.

A: The SRB times in the accounting records, in general, account for SRBs that run in the user address space. These SRBs are caused by the user's processing, unrelated to anything that DB2 does, but since the SRBs are asynchronous, they sometimes run while the user is processing in DB2. With two notable exceptions, [...] 14580 115 34_Re: DB2 V7 Recovery using Parallel15_Michael Rennick31_Michael_Rennick@TAX.STATE.NY.US31_Wed, 14 Jul 2004 11:29:40 -0400443_US-ASCII Thankyou for the hint, I got it to work using the following;

LISTDEF CCNTX1 INCLUDE TABLESPACE DEVMCCNT.SNCNTCTX PARTLEVEL 1 INCLUDE TABLESPACE DEVMCCNT.SNCNTCTX PARTLEVEL 2 INCLUDE TABLESPACE DEVMCCNT.SNCNTCTX PARTLEVEL 3 INCLUDE TABLESPACE DEVMCCNT.SNCNTCTX PARTLEVEL 4 RECOVER LIST CCNTX1 PARALLEL (4) REBUILD INDEX (ALL) TABLESPACE DEVMCCNT.SNCNTCTX



Michael J Rennick DBA - New York State Tax & Finance [...] 14696 105 64_Re: JDBC application SQL -805 for NULLID.SYSLN203 -- Help please11_Mark Ediger22_MEDIGER@TRANSUNION.COM31_Wed, 14 Jul 2004 11:16:51 -0500594_US-ASCII Hi Vamsi,

We have had this problem. It occurs when you have a very very large number of dynamic statements in your program, or, as in our case, the program logic mistakenly Prepares the dynamic sql statement a large number of times. JDBC will increase the number for the package name as necessary to include the required number of sections to support the number of prepared statements. A Package name of SYSSN200 would be using 65 sections, SYSLN201 would use 385 sections. These should be enough to support most large programs if you take care to handle the dynamic preps [...] 14802 42 45_Heavy Dynamic Prefetch ... How to control ???11_Prasad Mani22_Prasad_Mani@SATYAM.COM31_Wed, 14 Jul 2004 21:57:41 +0530535_iso-8859-1 LISTers,

Version details : DB2 V6.1; OS390 V2.8

One of my bufferpool is having Negative HIT ratio.

For that BP, there is only one large 32 Partitioned TSpace containing a heavily used table (mainly for online txn) of some million rows.

The reason I observed for negative hit ratio, is that, for the tableSpace allocated to the BP the usage of DYNAMIC PREFETCH (Async i/O) is far more than the no of GETPAGES requests. I used the formula HITRATIO = (GETPAGE-(SYNC_RIO + ASYNC_i/o) )/GETPAGE. [...] 14845 42 51_SQL JOIN observation -- is NULL not equal to NULL ?0_30_Steve_Grimes@AISMAIL.WUSTL.EDU31_Wed, 14 Jul 2004 11:32:13 -0500350_us-ascii Hello,

Mucho thanks to all who participated in that last discussion about the In list and TB scan, I know I benefitted from it!

Here's a simple question/observation, requiring no response unless I'm totally missing something. Within a simple join of two tables in the ON clause that contains 4 other columns, I also have: [...] 14888 33 65_Re: "DBMS Conversion Guide IDMS to DB2", publication GH20-7562-0012_Roger Miller19_millerrl@US.IBM.COM31_Wed, 14 Jul 2004 11:36:59 -0500360_- At 15 years old, rather like 170 in program years, there are some parts that are very out of date, references to products and companies that don't exist. It was about DB2 V2, so referential integrity was new, and much of V2, plus V3, V4, V5, V6, V7 and V8 are missing. I found an old copy and ran it through the copier. It's your turn for the next one. [...] 14922 91 49_Re: Heavy Dynamic Prefetch ... How to control ???35_Joel Goldstein - Responsive Systems26_joel@RESPONSIVESYSTEMS.COM31_Wed, 14 Jul 2004 13:06:58 -0400496_iso-8859-1 Prasad, Unfortunately you can't control dynamic prefetch at the pool level. While low/negative hit ratios because of this may be psychologically upsetting, just ignore it. Application changes could affect this, but they are more difficult.

Concentrate on the I/O rate/sec, since is the most important tuning metric. If making the pool larger reduces the I/O rate, then good (depends on amount of memory vs payback). If more pool space doesn't reduce I/O, then it's wasted. [...] 15014 29 55_Re: SQL JOIN observation -- is NULL not equal to NULL ?13_Michael Ebert18_mebert@AMADEUS.NET31_Wed, 14 Jul 2004 19:08:31 +0200368_us-ascii I think I've responded two or three times to that in the past years, you may be able to find it in the archives. In short, all comparisons with NULL values return FALSE, except for ...IS NULL or ...IS NOT NULL. It's in the SQL standard. It's also a trap that many people fall into, and that can make queries involving nullable values very "interesting". [...] 15044 125 30_Re: IN list causes TB scan ???14_Larry Kintisch19_lkint@IX.NETCOM.COM31_Wed, 14 Jul 2004 13:14:43 -0700383_iso-8859-1 Hi Listers again, Not to beat a dead horse, but to improve understanding a bit, Tapio Lahdenmäki has replied with this lucid explanation below. This is typical of the writing in his new textbook due out in English in 2005. [It is offered in manuscript version with the class that he teaches in Europe and that I teach in the US, "Cost-Saving Database Index Design".] [...] 15170 399 25_Re: XML Extender DB2 z/OS15_Jackson Reavill18_damcon2@US.IBM.COM31_Wed, 14 Jul 2004 13:29:50 -0400507_US-ASCII Hi Jaap,

I've only used XML Extender on AIX so this may not apply to z/OS. We've used it quite extensively for shredding and generating XML docs into and from the database. For the most part it has accomplished what we need, but there are definite "challenges" that can become troublesome. As Wayne said, definitely familiarize yourself with XML, DAD's, and DTD's. I'd also add the DB2 XML Extender stored procedures and functions to that list. Here's some of the things we ran into... [...] 15570 222 27_Error Dropping a tablespace12_Smith, Allan23_Allan_Smith@KYFBINS.COM31_Wed, 14 Jul 2004 13:41:21 -0400577_us-ascii

Listers,

I have recently inherited a DB2 system and am having a problem trying to drop a tablespace. I have issued a 'DROP TABLESPACE PCM390.AVT14TS'; And get the following error; Abend Code 04e Reason Code 00c90110. I have then just tried to drop a table or index in the tablespace and get the same error. Also ran a REPAIR DBD TEST and get the same abend. On further investigation, I found that there is an index table in the same database but in a different tablespace with the same OBID that the table has that resides in the first tablespace. [...] 15793 45 19_Re: DSSIZE Question12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 14 Jul 2004 13:22:13 -0500337_- How many partitions will you ever want? With V8, you get more than 254 partitions. If DSSIZE is 64GB for a 4K page size, then the maximum number of partitions becomes 256. If you have smaller partitions, like 8 GB DSSIZE, then the maximum number of partitions is 2K, while for 4 GB DSSIZE, the maximum number of partitions is 4K. [...] 15839 64 39_Re: Accesspath alteration due to Rebind12_Roger Miller19_millerrl@US.IBM.COM31_Wed, 14 Jul 2004 13:34:58 -0500521_- When you did the reload, was the data clustered? Was it reorged? If not, that may be the real source of the degradation.

Are you planning to migrate to a current DB2? I had to look up V4, as it went out of service in 2001, V5 out of service in 2002, and V6 goes out of service in mid 2005. Migration to V5, then direct to V7 would be my first choice. Most of the good options for performance would require running on a more current version of the code. http://www.ibm.com/software/data/db2/zos/support/plc/ [...] 15904 82 19_Re: DSSIZE Question13_Robert Tilkes22_TILKESR@NATIONWIDE.COM31_Wed, 14 Jul 2004 13:48:40 -0500447_US-ASCII There will only be 12 partitions ever for now one for every month. Also thank you for your response Roger, it had a lot of useful information.

Robert Tilkes





Roger Miller T To: DB2-L@WWW.IDUGDB2-L.ORG Sent by: DB2 Data Base cc: Discussion List bcc: Subject: Re: DSSIZE Question

07/14/2004 01:22 PM Please respond to DB2 Database Discussion list at IDUG [...] 15987 87 10_SP Problem0_24_db46@DAIMLERCHRYSLER.COM31_Wed, 14 Jul 2004 15:11:27 -0400514_US-ASCII What is the known problem with having a 'nested' cursor opening in a SQL stored procedure as follows:

Declare cursor1 ... Delcare cusor2 ...

OPEN cursor1; FETCH cursor1;

WHILE (SQLCODE = 0) DO FETCH cursor1 INTO x;

OPEN cursor2;

WHILE (SQLCODE =0) DO FETCH cursor2 INTO y; END WHILE;

CLOSE cursor2; END WHILE;

CLOSE cursor1;

We get: [IBM][CLI Driver][DB2/6000] SQL0502N The cursor specified in an OPEN statement is already open. SQLSTATE=24502 [...] 16075 33 19_DB2 MAINFRAME JOBS.10_Dave Jones21_db2jobs2000@YAHOO.COM31_Wed, 14 Jul 2004 14:59:01 -0500364_ISO-8859-1 DB2 listeners:

Pardon this notice. But in the event one of your colleagues is in need of employment or wants to learn about an new opportunity.

I have two opening for Strong DB2 DBA’s or DB2 systems programmers Z/os. Candidates should possess at least some of the following skills. These are new positions for a growing organization. [...] 16109 260 26_FW: Script center question10_Smith, Lee26_Lee.Smith@INFARMBUREAU.COM31_Wed, 14 Jul 2004 15:44:47 -0500519_- This is prolly a dumb question, but I cant seem to be able to find the correct search criteria to find the answer on ibm's site, so I will ask here... so forgive the ID-10-T behind the keyboard asking this....

We are running db2 5.2 for windows, I know I know, not my choice either, but I get to support it.... I have a user who doesn't want to have to go through command center to run a script(again, don't ask). She wants me to set this up in script center so she just clicks and runs it.... Problem is [...] 16370 42 55_Re: SQL JOIN observation -- is NULL not equal to NULL ?18_Christopher Pomasl19_pomasl@STARBAND.NET31_Wed, 14 Jul 2004 15:26:55 -0600604_us-ascii Null values are by definition undefined. So two values that are undefined cannot be determined to be equal; equality being a definitive definition.

Chris

Steve_Grimes@AISMAIL.WUSTL.EDU wrote: > Here's a simple question/observation, requiring no response unless I'm > totally missing something. Within a simple join of two tables in the ON > clause that contains 4 other columns, I also have: > > ...AND A.DOC_DATE = B.DOC_DATE > > However, I get no rows back. (The tables have about 10 rows each.) When I > comment out the above line -- I get rows back. > > When I change the [...] 16413 43 55_Re: SQL JOIN observation -- is NULL not equal to NULL ?14_Bernd Oppolzer26_Bernd.Oppolzer@T-ONLINE.DE31_Wed, 14 Jul 2004 23:32:13 +0200367_- I would not say that comparisons with NULLs return FALSE, they return UNKNOWN instead. But for the WHERE clause there has to be a decision, and so UNKNOWN in WHERE has the same effect as FALSE.

There are other places in DB2, where UNKNOWN has the same effect as TRUE, in CHECK conditions involving NULLs for example, IIRC, and in referential integrity. [...] 16457 20 53_SQL2025N An I/O error "11" occurred on media "VENDOR"12_Jim Schaufus23_jschaufus@ONEBEACON.COM31_Wed, 14 Jul 2004 19:36:19 -0500326_- I am trying to restore a backup taken on my production machine to my disaster recovery server. When I initiate my BMC SQL-Backtrack recovery I receive the below error. Also in the db2diag.log I get the Media controller error. I would appreciate any help!!!!

SQL2025N An I/O error "11" occurred on media "VENDOR" [...] 16478 57 35_Re: z/os websphere/db2 jdbc db2 cpu17_Karthik Vinayagam18_vkarthik@YAHOO.COM31_Wed, 14 Jul 2004 20:56:56 -0500418_- Ed,

If I understand this, you want accounting records written at commit of a distributed thread.

if so, you may want to explore enabling inactive thread support. setting cmtstat=inactive marks dist threads inactive at commit (w/ some exceptions) and db2 cuts accounting records at that time.

there was a discussion sometime back about inactive threads, you may want to search the archives. [...] 16536 40 49_Re: Heavy Dynamic Prefetch ... How to control ???17_Venkat Srinivasan32_venkat_lserv@HEWITTANDLARSEN.COM31_Wed, 14 Jul 2004 21:37:17 -0500300_- Prasad, From a system standpoint, You could be having a poor work load management. Prefetch i/o completed but application did not get dispatched. That is one way you could be doing i/o repeatedly for the same page. However you said it is a CICS app; if that happens in CICS I would be damned. [...]