1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 1999, week 5
2 36 29_Re: Parallelism (When or Not)15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Wed, 27 Oct 1999 06:29:35 -0500609_us-ascii ssethi@LOT.TATASTEEL.COM wrote: > > Obviously, it helps to have multiple CPU engines, tightly coupled. MVS 5.2+ > is the necessary software, which > supports interruptable SRBs.
> Some detailed explaination from the list will be appreciated. > What is MVS 5.2+ ? Written above that it is neccessary software.
SRBs are a unit of work (in MVS' view) that are slightly different than a thread TCB. They usually (in the MVS 5.2 and earlier versions) run at a higher CPU dispatching priority than the TCBs in the same address space (let's use CICS/RCT for this example). In MVS 5.2 and [...]
39 22 25_Re: DB2/OS390 compression15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Wed, 27 Oct 1999 06:30:34 -0500569_us-ascii Gary Bernhardt wrote: >
> Also, does the CPU time used to drive compression get charged back to the > thread? I am only concerned with hardware compression in DB2 OS/390.
Yes, the TCB running the SQL (thread) accumulates all the DB2 code as it's running (compression or otherwise).
Jim Lewandowski
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
62 24 25_Re: DB2/OS390 compression15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Wed, 27 Oct 1999 06:32:08 -0500450_us-ascii Pillay, Venkat (PCA) wrote: > > Bob Omerza of UPS had a detailed presentation in IDUG last year on > compression and they have CPU gain due to reduced getpages and less I/O. I > remember he was telling 3% overhead on DBM1 address space. >
Different hardware manufacturers (and versions of hardware) treat hardware compression differently. I'm not 100% up on which ones make hardware compression look more CPU costly than others. [...]
87 57 25_Re: I/O ERROR DURING READ15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Wed, 27 Oct 1999 06:35:39 -0500527_us-ascii HARBRY ARIZA wrote:
Below... You don't see that many IOS errors.
Jim Lewandowski
> > Hi folks: > > I'm getting a lot of I/O error as you can see below with tablespaces > and indexspaces and I don't know what is the reason that is ocasioning this > problem. I know how fix it but if anyone has experienced the same problem I > would like to heard about and what are I've got to do to avoid this error. > > Thanks on advise. > harbry > > DSNB224I + DSNB1AEX - BUFFER MANAGER > I/O ERROR DURING [...]
145 59 25_Re: DB2/OS390 compression15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Wed, 27 Oct 1999 06:37:41 -0500458_us-ascii Hardware compression in general was not designed for performance reasons. It was designed primarily to reduce DASD consumption. HOwever, since a lot of DASD today does hardware compression at the controller level, it provides no pure DASD reduction benefit.
For a table that has little or no activity, it makes sense. For those that have lots of activity, remember: decompression has to occur even if the page/row is in the buffer pool! [...]
205 52 25_Re: DB2/OS390 compression15_Jim Lewandowski22_jlewand@STARNETINC.COM31_Wed, 27 Oct 1999 06:42:11 -0500571_us-ascii mebert@AMADEUS.NET wrote: > Below....
Jim Lewandowski
> Unless I got something wrong, it's like this: IBM Machine language has special > instructions for compression/decompression. You can install additional hardware > for those. If you don't do this, the instruction gets trapped and emulated in > software. It's the same as for decimal number or string support. This > instruction uses CPU time like any other. This CPU time gets billed to the > thread using DB2, not the DB2 spaces (DB2 doesn't care about data, it just moves > it around). [...]
258 60 29_Re: Parallelism (When or Not)12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 29 Oct 1999 08:21:48 +0200321_x-user-defined Hi, Just to complete the general knowledge: SRB - service request block. TCB - task control block. MVS V5.2 - older version of MVS (multiple virtual storages) operating system. The last version was 5.2.2 and OS/390 came after that. The current version (that IBM is selling nowadays) of MVS is OS/390 V2.8 .
319 88 25_Re: DB2/OS390 compression12_Isaac Yassin23_yassin@NETVISION.NET.IL31_Fri, 29 Oct 1999 08:40:58 +0200608_x-user-defined Hi,
DASD compression means your DB2 page is compressed as a whole. Tablespace "compress yes" means more rows in a page. If you're willing to pay the CPU price for row compression the you may gain twice. We had a compressed table with our own exit routine, comparing with DB2 compress we didn't get any savings in disk and/or cpu, the only benefit of using DB2 compress was that we could do "add column". We compress tables based upon amount of data, access pattern (read/write/scans) and available memory (for BP), disk space, cpu and maintenance window. From our measurements The [...]
408 40 4_test14_khalid mahsoun27_kmahsoun@SAUDIONLINE.COM.SA31_Sun, 29 Aug 1999 01:37:52 +030017_windows-1256 test
449 17 45_updation of a column defined as default value0_24_guptak@LOT.TATASTEEL.COM31_Fri, 29 Oct 1999 12:58:39 +0530395_- Hi All !!!
Can a column of a view which has been defined as some default value (say 'N') be updated in a program...???
Pls suggest athanx
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
467 44 49_SV: updation of a column defined as default value25_Heidorn Sven IT Sundsvall19_Sven.Heidorn@CSN.SE31_Fri, 29 Oct 1999 10:02:19 +0200588_iso-8859-1 Athanx, Yes it can, but, if you have a check constraint on that column, with 'N' as the only permitted value, you will not be able to update that column other than to 'N'.
Sven Heidorn
> -----Ursprungligt meddelande----- > Från: guptak@LOT.TATASTEEL.COM [SMTP:guptak@LOT.TATASTEEL.COM] > Skickat: den 29 oktober 1999 09:29 > Till: DB2-L@RYCI.COM > Ämne: updation of a column defined as default value > > Hi All !!! > > Can a column of a view which has been defined as some default value (say > 'N') > be updated in a program...??? > > Pls suggest > athanx > [...]
512 55 22_Re: bind-package error0_28_ruediger.kurtz@HUK-COBURG.DE31_Fri, 29 Oct 1999 10:33:23 +0100404_us-ascii Although I've never come across the -805 when actually BINDing a package, I've had occasions when EXECUTing a package produced sqlcode -805; you might want to check the respective timestamps in your DBRM and your load-Module or check whether the collection the package was bound into was bound into your plan. BTW, sqlcode -805 can have a whole bunch of reasons, so check Messages & Codes. [...]
568 26 31_Db2 connect again (SQL1338N)...14_Masone, Franco24_F.Masone@CEDACRIOVEST.IT31_Fri, 29 Oct 1999 11:08:07 +0200415_- Hi to all, can you help me with my installation of Db2 connect 5.2 PE on WINNT ? (thanks to the friends who helped me with Cmallc error 20, it was a NT user auth problem)
I use sna server 3.0 and APPC, on the workstation I receive the message SQL1338N, DB2CPIC symbolic destination notfound. SNABASE is active, DB2CPIC in defined on the server. SNA link was tested with the Win3270 Sna server Applet. [...]
595 38 40_Re: About DB2 Data Analyst Certification10_Marc Baime23_mbaime1@TAMPABAY.RR.COM31_Fri, 29 Oct 1999 06:48:39 -0400737_us-ascii All IBM Certication Programs can be reached from the following URL: http://www.ibm.com/education/certify/ .
Regards...Marc Baime ----- Original Message ----- From: Newsgroups: bit.listserv.db2-l To: Sent: Friday, October 29, 1999 12:29 AM Subject: About DB2 Data Analyst Certification
> Hi all, > I am new to DB2 Database.Is there any certified course on > Db2(application and DBA). > If any one having info on this please let me know. > > thanks & regards > bala > > ================================================ > To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the [...]
634 72 37_Re: DB2 OS/390 Utility Test: V5 vs V616_Richard A Yevich15_ryevich@IBM.NET31_Fri, 29 Oct 1999 07:45:54 -0400556_iso-8859-1 Greatly depends on the use of parallelism in the V6 versions and the number of indexes involved or copies or recoveries being done at the same time. IBM published at the conference the measurements and what exactly was being performed with the utilities. Could you list exactly what you did, on what type of objects, and those results. If you compared a REORG with only 1 index in V5 and V6, then you might not see any difference. And then there are the issues of running inline runstats versus running a REORG and then running a RUNSTATS. [...]
707 118 29_Re: Parallelism (When or Not)16_Richard A Yevich15_ryevich@IBM.NET31_Fri, 29 Oct 1999 07:45:57 -0400613_iso-8859-1 Some answers: (1) V5 - at least one table during a join needs to be partitioned, or even a partitioned table of 1 partition; a segmented table and a partitioned table joining could be parallel during all phases except the data accessing of the segmented table. V6 - parallel for simple, segmented, and partitioned tablespaces (2) Parallel CPU implies parallel I/O but CPU Parallelism does not imply I/O Parallelism. Each CPU parallel stream will be doing I/O true. But in DB2 there are 2 different types of parallelism: I/O Parallelism and CPU parallelism and they are mutually exclusive. You get [...]
826 42 17_Reorg new Syntax?10_Ven Ilagan18_vilagan@ONE.NET.AU31_Thu, 28 Oct 1999 20:28:33 +1000789_us-ascii Hi Folks,
While browsing here ...... found this paragraph...
http://www-4.ibm.com/software/data/db2/os390/whatsnew/whatnw.htm
What's new in Db2 v5
Performance Capacity and Availability Enhancements
Faster,More Usable Utilities
"Improved performance for DB2-managed data sets: Previously, running the REBUILD, RECOVER, REORG, or LOAD utility on DB2-managed indexes or table spaces required deleting and redefining data sets to reset the objects. Now, when you run these utilities, DB2 provides an option to logically reset and reuse DB2-managed objects, bypassing the delete and redefine steps. In addition to improving performance, this option makes it easier to use the storage management subsystem (SMS) to manage your data sets " [...]
869 23 39_Deleted Db2 Data Structures for UDB 5.28_Greg Day21_gday@DLLR.STATE.MD.US31_Fri, 29 Oct 1999 09:10:57 -0400615_- TO: L_Server Users From: Greg Day DBA, State of Maryland
Subj: Deleted Data Structures for UDB 5.2
Our DB2 Data Structures (file definitions, Column definitions, container definitions) were deleted after a complete backup was completed. This information was on a different Drive than the Data. The NT Administrator restored the information on the Drive using the backup information for that drive. We did a Start DB2 and got an error stating the database was in use. We stop the Database. We tried Force all.We did a DB2ilist but it showed nothing running. But still no success. Any Ideas ? We [...]
893 95 27_DB2 High Performance Unload0_18_damcon2@US.IBM.COM31_Fri, 29 Oct 1999 09:21:27 -0400785_us-ascii Hi folks,
Got this from a colleague and hadn't seen any mention of it on DB2-L so I thought I'd put it out here for anyone who's interested...
********************************************************************************************************************** RAPIDLY AND EFFICIENTLY UNLOAD DB2 DATABASES WITH IBM DB2 HIGH PERFORMANCE UNLOAD
DB2 High Performance Unload, product number 5697-F44, was announced for general availability October 22, 1999. DB2 High Performance Unload delivers the unload functions needed by DB2 installations with outstanding elapsed times and with very low CPU consumption. o Unloads from both active tablespaces and image copies with outstanding performance. o Provides key functions to assist in the management and [...]
989 49 49_Re: updation of a column defined as default value10_Deb Jenson17_DJenson@QUEST.COM31_Fri, 29 Oct 1999 06:33:25 -0700403_iso-8859-1 Athanx, Yes, a default column value does not preclude you from updating the table with a view. The SQL reference guide lists all the cases where a view will be considered read-only based on certain conditions (joins, sub-query with group by etc.).
Deb Jenson Director DB Tools Quest Software 4320 Winfield Rd., Suite 200 Warrenville, IL 60555 Tel: 630/ 836-8428 djenson@quest.com [...]
1039 58 21_Re: Reorg new Syntax?15_Zander, Barb J.19_bzander@STATE.ND.US31_Fri, 29 Oct 1999 08:49:36 -0500546_iso-8859-1 Yes, this is via a PTF(UQ23002). There are a couple other enhancements also. Check out UQ25008, UQ28920, and UQ29652.
Barb -----Original Message----- From: Ven Ilagan [mailto:vilagan@ONE.NET.AU] Sent: Thursday, October 28, 1999 5:29 AM To: DB2-L@RYCI.COM Subject: Reorg new Syntax?
Hi Folks,
While browsing here ...... found this paragraph...
http://www-4.ibm.com/software/data/db2/os390/whatsnew/whatnw.htm
What's new in Db2 v5
Performance Capacity and Availability Enhancements [...]
1098 78 21_Re: Reorg new Syntax?16_Richard A Yevich15_ryevich@IBM.NET31_Fri, 29 Oct 1999 10:06:41 -0400741_iso-8859-1 The new keyword required is REUSE. Works for DB2-managed, and is a big help with SMS.
ALL: Check out: DB2 Server for OS/390 Version 5 Recent Enhancements - Reference Guide, SG24-5421-00, the May 1999 edition and then carefully check out the latest V5 Refresh Documentation.
Richard Yevich +=====+======+ Information Technology Consulting, Data Modeling, Advanced Education RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 Web: Email: info@ryci.com Offices: USA and Europe DB2® Family and Oracle® Specialists - Parallel Technologies VLDB and Data Sharing Technologies (specialties SAP®, Peoplesoft®) Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 [...]
1177 141 37_Re: DB2 OS/390 Utility Test: V5 vs V623_Lee, Kenneth (Exchange)14_kmlee@BEAR.COM31_Fri, 29 Oct 1999 10:15:38 -0400365_iso-8859-1 The table I used has 1.76 M rows with 3 indexes (one clustered, all 3 non-unique). All jobs ran on the same CPU(OS/390 2.7) at the same time frame and the following are the results:
V6 V5
LOAD: 3.12 3.70 CPU time
Image Copy .03 .03
REORG .83 .82
UNLOAD .14 .14
RECOVER .63 .63
Online Reorg 1.18 1.03 [...]
1319 33 15_DB2 UDB DBA Job15_Mohammed Nayeem21_db2udbxpert@YAHOO.COM31_Fri, 29 Oct 1999 07:17:03 -0700327_us-ascii Hi All
I am IBM certified DB2 UDB V5 Database Administrator, and I have 6 year exp in DB2,CICS,COBOL,EASYTRIEVE PLUS Is it possible to get DB2 UDB DBA job anywhere in U.S.A as per my experience and skills. I am looking for suitable position.My qualification is Bachelor of Engineering in Computer Science. [...]
1353 84 8_Re: -90415_Narasim Narayan24_Narasim_Narayan@GLIC.COM31_Fri, 29 Oct 1999 10:19:20 -0400470_us-ascii James Campbell: Thank you .. I should have spelt out the problem little better. I am thankful to your help. Let me expand on the problem.. I know the resource is the 4k temp file and the reason code 00C900A5 says how there no secondary extents defined(one of the reasons). I requested the the user to try his query after hours but he got the same result. My question: is there away to guestimate the amount of space required to satisfy the query Thank you [...]
1438 18 20_Is the list working?13_Keith Gilbert17_kgilbert@CSA1.COM31_Fri, 29 Oct 1999 09:32:19 -0500402_iso-8859-1 I suddenly stopped receiving mail from the list. Is it me or is the entire list down?
Keith Gilbert DBA Central Service Association Tupelo, MS
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
1457 40 24_Re: Is the list working?12_Gautham Ravi24_rgautham@WIPSYS.SOFT.NET31_Fri, 29 Oct 1999 20:03:15 +0530690_iso-8859-1 Keith,
The list is up & running fine....
regds gautham ----- Original Message ----- From: Keith Gilbert Newsgroups: bit.listserv.db2-l To: Sent: Friday, October 29, 1999 8:02 PM Subject: Is the list working?
> I suddenly stopped receiving mail from the list. Is it me or is the entire > list down? > > Keith Gilbert > DBA > Central Service Association > Tupelo, MS > > ================================================ > To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM. > [...]
1498 44 35_Re: Db2 connect again (SQL1338N)...14_Philip Gunning20_pgunning@BOSCOVS.COM31_Fri, 29 Oct 1999 10:38:03 -0700344_us-ascii Franco, I'm a little bit confused. If you are using PE it comes with its own sna stack which is what you should be using. Are you using PE or EE since you mention a server? When you cataloged the DB the symbolic destination name must be in caps and must match what you define in SNA server if that is what you are using. HTH Phil [...]
1543 149 37_Re: DB2 OS/390 Utility Test: V5 vs V616_Richard A Yevich15_ryevich@IBM.NET31_Fri, 29 Oct 1999 10:53:26 -0400588_iso-8859-1 Kenneth, Elapsed time is the most critical measure here. Could you post those? Also, do you have something a little larger?
Richard
> -----Original Message----- > From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of > Lee, Kenneth (Exchange) > Sent: Friday, October 29, 1999 10:16 AM > To: DB2-L@RYCI.COM > Subject: Re: DB2 OS/390 Utility Test: V5 vs V6 > > > The table I used has 1.76 M rows with 3 indexes (one clustered, all 3 > non-unique). All jobs ran on the same CPU(OS/390 2.7) at the same > time frame > and the following are the [...]
1693 25 30_DBD-DB2 Perl module for NT 4.012_Mark Britton25_Mark.Britton@COMMERCE.COM31_Fri, 29 Oct 1999 09:07:24 -0600345_iso-8859-1 Anybody know where I might find a precompiled version of the DBD::DB2 module for NT that works with the ActiveState version of Perl? The ActiveState Perl site doesn't have one, and I don't have access to a C compiler to roll my own. I know I can use the ODBC driver, but I want to see if the DB2 native driver can be used on NT. [...]
1719 67 50_Re: Creation of Field Procedure on Existing Column11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Fri, 29 Oct 1999 11:50:12 -0400545_ISO-8859-1 Harvey
Since nobody else has replied to your mail, I will put in my two cents worth. I strongly advise you not to use fieldprocs. They are bad news. The restriction you mention is only one of many that apply to fieldprocs. I know of a number of sites who have fieldprocs that were implemented in the early days of DB2. Most of them would very much like to get rid of them, but that is as difficult as adding them. New features in DB2 often will not work with fieldprocs - either they are documented as not supporting them [...]
1787 46 13_Re: QMF free?14_Mullins, Craig21_Craig_Mullins@BMC.COM31_Fri, 29 Oct 1999 11:48:05 -0500441_ISO-8859-1 Regarding your question about QMF being free, here are two quotes directly from the IBM manual Introducing QMF, available at: ftp://ftp.software.ibm.com/software/os390/db2server/books/DSQGI061.PDF
"When you purchase and install DB2, you can choose to install QMF or QMF for Windows Version 6 for a trial period. For detailed information about installing and using a QMF trial version, see your DB2 program directory." [...]
1834 66 13_Re: QMF free?15_Loria, Susan C.27_Susan_C_Loria@TVRATINGS.COM31_Fri, 29 Oct 1999 12:48:28 -0400321_iso-8859-1 My company just ordered DB2 V6.1 for OS/390 and -- no QMF V6 is not free. It did however, have the same cost as our previous version 3.2. So at least it didn't go up in price as did V6.1 from V4.1 (significant jump in price) and I thought I kept hearing it would be cheaper to be a more competitive DBMS. [...]
1901 45 53_Message from the List Owners - : Is the list working?12_John Morrill12_johnm@VP.NET31_Fri, 29 Oct 1999 12:00:58 -0500540_iso-8859-1 Greetings!
If you have question about the status of the list, please e-mail the list owners at DB2-L-Request@ryci.com
If there is a significant list outage, the list owners will post the status information to www.ryci.com
Cheers!
J. Michael Morrill DB2-L Chief List Owner
-----Original Message----- From: DB2 Data Base Discussion List [mailto:DB2-L@RYCI.COM]On Behalf Of Keith Gilbert Sent: Friday, October 29, 1999 9:32 AM To: DB2-L@RYCI.COM Subject: Is the list working? [...]
1947 91 50_Re: Creation of Field Procedure on Existing Column14_Harvey Wachtel22_HWachtel@UAPC.CUNY.EDU31_Fri, 29 Oct 1999 14:01:53 -0400423_us-ascii Thanks for the advice, but we can't think of any other way to deal with hundreds of queries that either ORDER BY SEMESTER or use the MIN and MAX functions. And there are simply too many programs involved, many in hybrid DB2-batch environments, to consider expanding to four-digit years now. The current application code is being phased out in the next few years anyway, but unfortunately not in time for Y2K. [...]
2039 109 50_Re: Creation of Field Procedure on Existing Column12_Myron Miller24_myron_miller@HOTMAIL.COM29_Fri, 29 Oct 1999 11:26:51 PDT632_- What about going to Rel. 6 and using triggers to accomplish the same thing? You could do everything you wanted to do with the fieldproc and lots more with many of the inherent difficulties with fieldproc's eliminated.
>From: Harvey Wachtel >Reply-To: DB2 Data Base Discussion List >To: DB2-L@RYCI.COM >Subject: Re: Creation of Field Procedure on Existing Column >Date: Fri, 29 Oct 1999 14:01:53 -0400 > >Thanks for the advice, but we can't think of any other way to deal with >hundreds of queries that either ORDER BY SEMESTER or use the MIN and MAX >functions. And there are [...]
2149 31 24_Re: Is the list working?13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 29 Oct 1999 17:52:27 -0400331_iso-8859-1 Keith, As Gautham has already posted, the list is up & working. What I do when I question if my mail-server is misbehaving, or if the list is having problems, is: 1) check the archives site to see if there are messages being posted there which I've not received. Look at http://jupiter.ryci.com/archives/db2-l.html [...]
2181 26 31_Need help -- REXX in qmf procs.21_Devineni, KishoreBabu29_KishoreBabu.Devineni@NIKE.COM31_Fri, 29 Oct 1999 15:12:38 -0700519_us-ascii Hi all,
I tried to use the QMF global variables in my procs and they are not populated correctly. Is there anything needs to be setup to populate these values correctly. For Ex: If I want to get the SQL code of the previous query which ran in the proc. I got to use this SQL code for further validations/control flow. Suppose if the proc is to display a row and if it is not found in the database then QMF should populate the value 100 in the SQL code field. what is that field and how to get that [...]
2208 28 38_Re: Data Warehouse and surrogate keys.13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 29 Oct 1999 18:39:12 -0400508_iso-8859-1 Lisa has an excellent set of questions regarding surrogate keys.
Surrogate keys seem, in my humble, arrogant opinion, to be a data modeller's solution to every imaginable problem. I have fought them tooth & nail throughout my DBA career and, with a number of ugly exceptions, proven that real data keys, judiciously used with good SQL preclude the need for surrogate keys. Ad-hoc query environments and data warehouse seem the perfect proof of the unacceptable cost of surrogate keys. [...]
2237 24 13_DWQT Question13_Brunner Don J24_Don.J.Brunner@M1.IRS.GOV31_Fri, 29 Oct 1999 14:28:04 -0400531_iso-8859-1 In reviewing the Bufferpool statistics on our daily reports, I noticed that two of the Bufferpools have a high DW Threshold Reached count. (This particular report summarizes at the day level).
Does anyone have a ROT concerning this value? Chuck Hoover states in his TUNING DB2 FOR OS/390 booklet that this number should be very low or even zero. Craig Mullins, in his DB2 Developer's Guide, says 'Reaching the deferred write threshold does not constitute a problem; it is simply the way that DB2 operates '. [...]
2262 59 17_Re: DWQT Question14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Fri, 29 Oct 1999 19:54:42 -0400465_ISO-8859-1 The hits on DWQT should be low to zero. You want all/most of your hits on VDWQT. Calculate the number of pages/write for your pool. If it's a low single digit %, like 1.8 or 2.5, or even 5.0 Lower VDWQT to ZERO... this triggers a write at the dataset level when 40 updated/committed pages are in the pool for an object. If you are datasharing, then you want a low % for this not zero. If V6, you can set the number of buffers to trigger the writes. [...]
2322 66 38_Re: Data Warehouse and surrogate keys.16_Richard A Yevich15_ryevich@IBM.NET31_Fri, 29 Oct 1999 21:02:48 -0400371_iso-8859-1 David,
Hooray!!!!! But let's call them "natural keys" instead of "real data keys", since that is a term in the modeling/logical world.
But as with any technology decision, there is no "ALWAYS" and there are actual places where surrogates work -- but it is in the minority, not the majority, and it should "almost never" be the first choice. [...]
2389 60 17_Re: DWQT Question16_Richard A Yevich15_ryevich@IBM.NET31_Fri, 29 Oct 1999 21:07:57 -0400497_iso-8859-1 Without offending anyone, the VDWQT should be driving the writes to DASD. The DWQT is the safety value, and the checkpoints -- something else entirely. I am sure you will get more details on this from Joel, but "it is not simply the way DB2 operates".
The DWQT is for the whole pool, all objects in the pool, and is a sign of trouble in the pool, ASSUMING that its value is set properly in relation to the VDWQT and in relation to the type of objects allocated to the pool. [...]
2450 90 17_Re: DWQT Question12_Roger Miller19_millerrl@US.IBM.COM31_Fri, 29 Oct 1999 18:10:34 -0700304_us-ascii I'd tend to twist your discussion a bit:
I'd start with the classic, "It depends." The key questions are the disks and cache in back of the buffer pools, the buffer pool sizes, and the DWQT, VDWQT and LOGLOAD. If you have hiperpools or data sharing, the situation gets more complex. [...]
2541 77 49_Re: updation of a column defined as default value13_Anoop Agrawal22_anoop007@SATYAM.NET.IN31_Sat, 30 Oct 1999 10:24:34 +0530415_iso-8859-1 Hi This question was posted by a colleague. I think we some people are getting confused between a col. in the table with default value versus a col. in a view with default value. In this case the item in question is a view with default value and NOT a table. Here the view has a default value by using value(col name, default val) function. Please suggest if this col in the above view, be updated. [...]
2619 24 8_Re: test15_Mohammed Nayeem21_db2udbxpert@YAHOO.COM31_Fri, 29 Oct 1999 21:27:12 -0700492_us-ascii Your Test is successfull
--- khalid mahsoun wrote: > test >
=====
__________________________________________________ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
2644 74 21_Re: Reorg new Syntax?10_Ven Ilagan18_vilagan@ONE.NET.AU31_Fri, 29 Oct 1999 16:31:07 +1000771_us-ascii Thanks lots Barb,
This list is great isn't it!!!,
Regards, VEn
At 08:49 29/10/99 -0500, Zander, Barb J. wrote: >Yes, this is via a PTF(UQ23002). There are a couple other enhancements also. >Check out UQ25008, UQ28920, and UQ29652. > >Barb >-----Original Message----- >From: Ven Ilagan [mailto:vilagan@ONE.NET.AU] >Sent: Thursday, October 28, 1999 5:29 AM >To: DB2-L@RYCI.COM >Subject: Reorg new Syntax? > > >Hi Folks, > >While browsing here ...... found this paragraph... > >http://www-4.ibm.com/software/data/db2/os390/whatsnew/whatnw.htm > >What's new in Db2 v5 > >Performance Capacity and Availability Enhancements > >Faster,More Usable Utilities > >"Improved performance for DB2-managed data sets: Previously, running the >REBUILD, [...]
2719 98 21_Re: Reorg new Syntax?10_Ven Ilagan18_vilagan@ONE.NET.AU31_Fri, 29 Oct 1999 16:31:41 +1000716_iso-8859-1 Thanks - I'll try it out after the APAR is installed
Ven
At 10:06 29/10/99 -0400, Richard A Yevich wrote: >The new keyword required is REUSE. Works for DB2-managed, and is a big help >with SMS. > > >ALL: Check out: DB2 Server for OS/390 Version 5 Recent Enhancements - >Reference Guide, SG24-5421-00, the May 1999 edition and then carefully check >out the latest V5 Refresh Documentation. > >Richard Yevich >+=====+======+ >Information Technology Consulting, Data Modeling, Advanced Education >RYC® Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930 >Web: Email: info@ryci.com Offices: USA and Europe >DB2® Family and Oracle® Specialists - [...]
2818 21 30_UDB V6 For Windows NT ,95 & 980_24_ssethi@LOT.TATASTEEL.COM31_Sat, 30 Oct 1999 12:32:46 +0530382_- Hi all, I have installed UDB v6.1 for my NT workstation and Win 98.It is an evaluation copy .I want to know how SYSADM authority is taken on Win'98 as it asks for SYSADM in NT but not in Win'98.
As i am familiar with DSNZPARM in DB2 for OS390 but what is corresponding to that in client editions which will show me all the informations and allow me to change as well. [...]
2840 133 38_Re: Data Warehouse and surrogate keys.14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 30 Oct 1999 18:10:50 +1000335_iso-8859-1 I would also like to add my Hooray!
I have seen some terrible articles talking of the virtues of having every single table have a single column surrogate id. key. A performance nightmare!
For years I have been campaigning for natural keys over surrogate ids. whether in Data Warehouse, OLTP, or whatever. [...]
2974 93 49_Re: updation of a column defined as default value14_Michael Hannan24_mhannan@C031.AONE.NET.AU31_Sat, 30 Oct 1999 18:15:57 +1000618_us-ascii Expressions in views are not updateable nor is the original table column used. The terminology of "default value" is not recognised by many people. We would say that views do not have default values.
From: Michael Hannan
>From: Anoop Agrawal >Subject: Re: updation of a column defined as default value >To: DB2-L@RYCI.COM > >Hi >This question was posted by a colleague. I think we some people are >getting confused between a col. in the table with default value versus >a col. in a view with default value. >In this case the item in question is a view with default [...]
3068 127 28_Balt./Wash. DB2 Users' Group0_19_valeriet@US.IBM.COM31_Sat, 30 Oct 1999 12:11:20 -0400697_us-ascii Baltimore/Washington DB2 Users' Group
Meeting Date: December 8, 1999
Location: Sheraton Columbia Inn 10207 Wincopin Circle Columbia, MD (410) 730-3900
Meeting Schedule:
8:30 - 9:00 Registration
9:00 - 10:15 DB2 UDB for UNIX/Intel V6.1 Technical Overview David Seibert, Compuware Corporation
DB2 UDB for UNIX, Windows, and OS/2 V6.1 is an evolution of the DB2 product line. IBM continues to enhance DB2 to position it as the premier database for all distributed platforms--which includes Java support for e-business applications. This presentation provides an introduction to the new features found in this release of DB2 UDB for UNIX/Intel. [...]
3196 27 37_Re: DB2 OS/390 Utility Test: V5 vs V612_Roger Miller19_millerrl@US.IBM.COM31_Sat, 30 Oct 1999 18:29:23 -0700569_- You asked about the documentation for our tests.
Most of it is in the red book,
DB2 UDB for OS/390 Version 6 Performance Topics, SG24-5351. Chapter 6 is Utility Performance.
You can order it from IBM or download a PDF from www.ibm.com/redbooks Put the order number in the search and click go.
For all of the recent books on DB2, just put DB2 in the search and click Go. The most recent ones show up on top, with residencies (books to be written), red pieces (draft red books), and then the red books, with the most recent ones first. [...]
3224 13 21_Re: Reorg new Syntax?12_Roger Miller19_millerrl@US.IBM.COM31_Sat, 30 Oct 1999 18:41:25 -0700360_- Just one minor note, that we refreshed the V5 Recent Enhancements book again September 27, 1999.
Roger Miller
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
3238 32 19_regarding packages.17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Sat, 30 Oct 1999 21:40:27 PDT449_- Hi ALL,
I am having plan called PHQPXXX which contains some packages.now i wanted add two more packages to the existing plan PHQPXXX. shall i how could i add these two packages to the existing plan. but the plan with some package has been created long back and the bound time is 3 year back. now if i included two more packages to the existing plan with same BIND PLAN and AUCTION(REPLACE) does it effect to exisitng plan and package. [...]
3271 63 50_Re: Creation of Field Procedure on Existing Column11_Mike Turner29_Michael_Turner@COMPUSERVE.COM31_Sun, 31 Oct 1999 05:47:23 -0500447_ISO-8859-1 Harvey
The problems with access path selection have happened when the fieldproc effectively changes the order of the key values. I have seen examples where the fieldproc produces a complement of a numeric value, in effect reversing the order of the keys in the index. The DB2 Optimizer does not recognise this reversal of sequence. From the description of your fieldproc, I do not think this will be a problem in your case. [...]
3335 73 23_Re: regarding packages.27_Thakral, Deepak Kumar (CTS)24_TDeepak@CHN.CTS-CORP.COM31_Sun, 31 Oct 1999 06:52:23 -0500648_iso-8859-1 Hi Ravi, If you are using DB2 version 3.0 or above , the concept of plans and packages is such that a plan is a collection of packages , and if you decide to rebind a package to point to another collector id, you simply have to rebind the package without having to drop the plan and then rebind it. I think the following should work : //BIND EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT) //DBRMLIB DD DSN=xxx.yyyyy.zzzz(member name),DISP=SHR //SYSTSPRT DD SYSOUT=* //SYSPRINT DD SYSOUT=* //SYSUDUMP DD SYSOUT=* //SYSOUT DD SYSOUT=* //REPORT DD SYSOUT=* //SYSIN DD DUMMY //SYSTSIN DD * DSN SYSTEM(DB2P) BIND PACKAGE (collid-name#COLLID) [...]
3409 210 69_Greater K.C. Area HOA DB2 User group meeting in Dec & class offerings0_54_Shan_Leatherman/MO/americancentury@AMERICANCENTURY.COM31_Sun, 31 Oct 1999 16:13:25 -0600165_us-ascii
If interested in attending, please Reply back to me by Monday, Nov 29, 1999 with your meal preference : Beef, Chicken or Vegetarian.
3620 89 23_Re: regarding packages.14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Mon, 1 Nov 1999 10:12:38 +1100675_us-ascii Ravi,
What is the PKLIST of your plan? If it is: - "PKLIST(.*)", then DB2 will look at all packages in the named to find the one it wants. So you can simply bind your packages into the , and DB2 will find it. Almost all use of packages uses this technique. - "PKLIST(., .)" then DB2 will only check the named packages. As well as binding your packages, you will need to rebind the plan (yes, using "ACTION(REPLACE)") either as: -- "PKLIST(.*)"; or -- "PKLIST(., ., ., .)". [...]
3710 23 18_regarding DSN1COPY17_ravi kumar hassan18_ravibh@HOTMAIL.COM29_Sun, 31 Oct 1999 15:39:04 PST298_- HI ALL,
I have a question on DSN1COPY, i want to copy a perticular day data from prod. to test region. i know i can use a DSN1COPY to copy. but why con't i use a VSAM COPY to copy a perticular day data from prod. to test region. pl. somebody cn explain why i can not use a vsam copy. [...]
3734 98 35_FW: Need help -- REXX in qmf procs.17_Scarcella, Gary V21_ScarcellaGV@AETNA.COM31_Sun, 31 Oct 1999 18:47:30 -0500504_- 1. Use GET GLOBAL command to get QMF global variable values into REXX varibles after running a query in a QMF proc. You can issue a SHOW GLOBALS to get a list of all available QMF global variables and their current values (see QMF Reference manual for full details).
2. You must get all the globals wanted right after running the query, since other QMF commands may reset some of these variables. See example QMF proc below to get all needed QMF variables with a single GET GLOBAL command. [...]
3833 51 22_Re: regarding DSN1COPY14_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Mon, 1 Nov 1999 10:47:22 +1100502_us-ascii Ravi,
VSAM doesn't understand the ID's (Database, tablespace and table) buried in the data. To do what you want, these will need to be translated, and only DSN1COPY knows how to do that.
/* standard disclaimer */ James Campbell DBA Hansen Corporation, Tooronga +61 3 9261 1802 james.campbell@energyis.com.au -----Original Message----- From: ravi kumar hassan [mailto:ravibh@HOTMAIL.COM] Sent: Monday, November 01, 1999 9:39 AM To: DB2-L@RYCI.COM Subject: regarding DSN1COPY [...]
3885 72 8_Re: -90414_Campbell James30_James.Campbell@ENERGYIS.COM.AU30_Mon, 1 Nov 1999 11:29:58 +1100600_us-ascii Narasim,
Well, the only method of calculating the space required in the work file tablespaces is to follow the steps shown in the Installation Guide "DASD Requirements for the Work File Database".
A possible shortcut would be to try and partition the query. If you are processing a large table as the outer table (Explain output will show this), then apply a predicate to filter out half the rows in the outer table: if this works, then doubling the space in the work file tablespaces should give enough space. If it doesn't, revise the predicate to filter out two thirds [...]
3958 39 45_=?iso-8859-1?B?u9i4tDogRFdRVCBRdWVzdGlvbg==?=14_zhangxiaoliang28_zhangxiaoliang@MAIL.DCAC.COM30_Mon, 1 Nov 1999 08:37:42 +0800600_iso-8859-1 Hi! Can you tell me where I can find the books and ? Can I find them on INTERNET? If yes,any URL provided will be very appreciated. Thanks in advace.
> >In reviewing the Bufferpool statistics on our daily reports, I >noticed that two of the Bufferpools have a high DW Threshold Reached >count. (This particular report summarizes at the day level). > >Does anyone have a ROT concerning this value? Chuck Hoover states >in his TUNING DB2 FOR OS/390 booklet that this number should be very >low or even zero. Craig Mullins, [...]
3998 18 21_DB2/ORACLE COMPARISON0_14_whomer@CSC.COM30_Mon, 1 Nov 1999 14:13:26 +1000453_us-ascii Hi all,
Could anyone tell me of a good source (i.e book or web site) that functionally compares DB2 and Oracle in terms of utilities, SQL, partitioning, DDL, etc.
Thanks in advance. Warren
================================================ To change your subscription options or to cancel your subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can be reached at DB2-L-REQUEST@RYCI.COM.
4017 51 8_RACF/DB20_27_John.Matear@BANKWEST.COM.AU30_Mon, 1 Nov 1999 11:58:47 +0800458_us-ascii We are an OS/390 DB2 V5 site and about 6 months ago we converted from ACF2 to RACF. Up until now we have not had any major problems with RACF and DB2. However, last week all of our developers suddenly lost all of their access to their development DB2 subsystem. At the time we assumed that the Security Management team had made some erroneous changes but we later found out that they had not done any changes (or none they would admit to! :)). [...]
4069 39 37_stored procedures vs. DDF (on OS/390)0_23_john.lantz@ZURICHUS.COM31_Sun, 31 Oct 1999 23:42:49 -0500566_us-ascii We have been doing some benchmarks to compare the use of DDF to get at data on remote DB2's vs. using a stored procedure. Our thought being that the stored procedure would perform better since it would be "static" sql that would not have to be dynamically prepared. Our tests were pretty simple. The first program had a local cursor that fetched about 40k rows, then for each row, a SELECT was done to a remote database using DDF. The second program had the same local cursor, but then connected to a remote database and called a stored procedure. The [...]