1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l December 2000, week 5 2 15 62_Michael J. Lynch/Household International is out of the office.13_Michael Lynch21_mjlynch@HOUSEHOLD.COM31_Fri, 29 Dec 2000 01:00:50 -0500408_us-ascii I will be out of the office starting 12/28/2000 and will not return until 01/02/2001.

I will respond to your message when I return. Happy New Year! :)

================================================ 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. 18 14 47_Frank Yao/CACI/Prudential is out of the office.9_Frank Yao24_frank.yao@PRUDENTIAL.COM31_Fri, 29 Dec 2000 01:02:32 -0500387_us-ascii I will be out of the office from 12/28/2000 until 01/02/2001.

If this is an emergency, please contact Eileen Bennett at 716-6451.

================================================ 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. 33 14 55_Daryl G Spletzer/Pr/Consumers/CMS is out of the office.16_Daryl G Spletzer24_dgspletzer@CMSENERGY.COM31_Fri, 29 Dec 2000 02:08:08 -0500365_us-ascii I will be out of the office from 12/29/2000 until 01/02/2001.

I will respond to your message when I return.

================================================ 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. 48 100 13_4GB partition0_21_kkane@PAINEWEBBER.COM31_Fri, 29 Dec 2000 06:34:49 -0500316_iso-8859-1 Friends, Here is my problem. A partition of a V5.1 tablespace is going to exceed 4gb and I have no time to re-engineer. I thought I could create a new segmented TS to replace the partition in quesstion and create a new view to Union the new table with a select subset of the existing Table such as.. [...] 149 90 17_Re: 4GB partition0_18_mebert@AMADEUS.NET31_Fri, 29 Dec 2000 13:31:40 +01001711_us-ascii Compress the TS partition, if it isn't already....

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



From: kkane@PAINEWEBBER.COM on 29/12/2000 11:34 GMT

Please respond to DB2 Data Base Discussion List |---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |To: | |---------> >---------------------------------------------------------------------------> |DB2-L@RYCI.COM | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> |cc: | |---------> >---------------------------------------------------------------------------> | (bcc: Michael Ebert/MUC/AMADEUS) | >---------------------------------------------------------------------------> >-------------------------------------------| | | | | >-------------------------------------------| |---------> | | |---------> >---------------------------------------------------------------------------> | | >---------------------------------------------------------------------------> >-------------------------------------------| | | >-------------------------------------------| |---------> |Subject: | |---------> >---------------------------------------------------------------------------> |4GB partition | >---------------------------------------------------------------------------> [...] 240 200 31_Re: Insert into nullable column14_Scott Trometer22_scott.trometer@RCI.COM31_Fri, 29 Dec 2000 07:30:21 -0500384_iso-8859-1 Scott,

Getting off on a little bit of a tangent from your question....but, how were you able to add a nullable column to the end of your table in V5? My understanding is that you would have had to drop the table to be able to do this, which, as you said, would invalidate the plans. So, wouldn't you have had to rebind these plans to execute them under v5 too? [...] 441 86 31_Re: Insert into nullable column10_John Hardy27_john_j_hardy@CONSULTANT.COM31_Fri, 29 Dec 2000 07:41:27 -0600575_- Hello Scott (the latest one!),

You can ALTER ADD a column with the NULL (default), or NOT NULL WITH DEFAULT. The only one you can't add is NOT NULL.



On Fri, 29 Dec 2000 07:30:21 -0500, Scott Trometer wrote:

>Scott, > >Getting off on a little bit of a tangent from your question....but, how were >you able to add a nullable column to the end of your table in V5? My >understanding is that you would have had to drop the table to be able to do >this, which, as you said, would invalidate the plans. So, wouldn't you [...] 528 42 5_PSIDs0_18_mebert@AMADEUS.NET31_Fri, 29 Dec 2000 15:06:45 +0100579_us-ascii Hello List!

1. When I create a TS, how does DB2 determine the PSID to use? Is this documented somewhere? 2. How can I create a TS with a given PSID (one not used otherwise)?

Background: I recently did a cleanup of SYSLGRNX to remove entries for dropped TSs (from a discussion on MODIFY a couple of months ago, we know that old SYSLGRNX records are removed when the DBID/PSID is reused, not when the TS is dropped). I did this by simply creating dozens (or hundreds) of TSs in databases with old records, Imagecopying them, MODIFY DELETE AGE(*) (which [...] 571 18 37_Re: enable on bind of a called module12_Alan Gredell28_agredell@KEMPERINSURANCE.COM31_Fri, 29 Dec 2000 09:35:53 -0600586_- I guess I'm concerned about collections shared by multiple Plans. If one plan is for online and contains a collection (we always specify collection_name.*), another plan is for batch and contains the same collection (I didn't say it was a good idea, just a fact of life!), which takes precedence? Or is it strictly plan-based? In other words, if I call the subprogram from the plan enabled for CICS, the package inherits that environment access? And, if the plan enabled for batch calls the same subprogram, it inherits that plans ENABLE status? If I have that right, then I owe [...] 590 115 39_Re: Foreign Key indexes - when and how?9_Don Alden25_Don.Alden@I-STRUCTURE.COM31_Fri, 29 Dec 2000 08:37:25 -0700440_iso-8859-1 Gentlemen,

This will be a quick answer because the DB2 Administration has everything you need to know about DB2 RI and I am attaching the Website URL for the manual. Just a quick tuning hint.

The Foreign Key columns don't always need and index. If the child table has thousands of rows it is advisable that you use an Index to trigger a Matching Index scan. This is especially important for OLTP applications. [...] 706 23 39_Re: Transactions deadlocking on inserts0_19_Tim.Lowe@STPAUL.COM31_Fri, 29 Dec 2000 09:51:54 -0600368_us-ascii Terry, I am sorry, you are absolutely correct. I had totally forgotten about that thread. (and I remember contributing to it!) I had concentrated too much on deadlocks, and had not considered timeouts on duplicate key inserts. But, I can certainly see how this could also create a deadlock.

Andrew, Could your problem be caused by duplicate keys? [...] 730 16 60_Aurora Emanuela Dell'Anno/SI/RM/GSI/it is out of the office.25_Aurora Emanuela Dell'Anno34_auroraemanuela.dellanno@SISTINF.IT31_Fri, 29 Dec 2000 16:51:51 +0100427_us-ascii I will be out of the office starting 29/12/2000 and will not return until 04/01/2001.

I am away and unable to read my mail. I will respond to your message when I return.

================================================ 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. 747 24 47_Re: Buffer Pool: prefetch disabled/read engines16_Orndorff, Joseph24_Joseph.Orndorff@NIKE.COM31_Fri, 29 Dec 2000 09:00:03 -0800309_iso-8859-1 Joel,

Thank you for your suggestions. I have changed the VDWQT to 0 & will monitor the effects. In answer to your question, the total number of buffers for each buffer pool is the same now as when we had HP's. Where can I find the average elapsed prefetch I/O time ? (I'm new at this). [...] 772 21 27_indexable global temp table15_Amy C Whitehead14_B06ACW@FDS.COM31_Fri, 29 Dec 2000 13:07:40 -0500374_us-ascii Does anyone have information and/or steps to create an indexable global temp table in os/390 DB2? I can get the embedded create global temp table to work. I have also created a global table that is permanent and used the declare global temp code, but I could not create an index. I have not found much information on creating the indexable global temp tables. [...] 794 51 31_Re: indexable global temp table0_22_Rohn.Solecki@MTS.MB.CA31_Fri, 29 Dec 2000 12:28:51 -0600536_us-ascii What you are looking for is part of DB2 V6.1, a feature called "Declared Temporary Tables". From the V7.1 "What's new ..." document, refering to V6.1 features . . . Declared temporary tables complement the existing created temporary tables available in Version 5 of DB2 for OS/390. Declared temporary tables do not have descriptions in the catalog tables. The tables support indexes, UPDATE statements, and DELETE statements. You can implicitly define the columns and use the result table from a SELECT. [...] 846 45 31_Re: indexable global temp table13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 29 Dec 2000 13:44:10 -0500458_iso-8859-1 Hi Amy, As Rohn points out, Declared global temporary tables are a feature new in v6. It was introduced via APAR, so you might not find it in your doc if you have hardcopy or old softcopy.

You can create indices on them and there are other differences from the v5 global temp tables. See the doc. There are pre-reqs. to being able to create them such as a TEMP database and tablespace. I recall an earlier thread about these pre-reqs. [...] 892 64 28_Re: Declared Temporary Table14_Philip Gunning24_Philip.Gunning@QUEST.COM31_Fri, 29 Dec 2000 10:47:40 -0800443_- Amy, The best reference I found on this is the Redbook, DB2 UDB Server for OS/390 Version 6 Technical Update, SG24-6108-00. I know of one site that took a 20 hour job down to an hour using a declared temporary table. HTH Phil

Philip Gunning Sr. Systems Consultant Quest Software

IBM Certified Solutions Expert -- DB2 UDB Database Administration IBM Certified Solutions Expert -- DB2 Application Development Unix, Win,OS/2 [...] 957 88 31_Re: indexable global temp table15_Amy C Whitehead14_B06ACW@FDS.COM31_Fri, 29 Dec 2000 13:59:29 -0500382_us-ascii Yes, I am trying this on a version 6 system. We can get the temp table to work, but not the index. How do you make a global temp table indexable? We are creating the temp area with the DECLARE GLOBAL TEMP TABLE SESSION.TABA LIKE X.TABA clause embedded in the code. However, the end result seems to be the same as creating the global temp table with the CREATE clause. [...] 1046 78 31_Re: indexable global temp table15_Amy C Whitehead14_B06ACW@FDS.COM31_Fri, 29 Dec 2000 14:02:18 -0500390_us-ascii Thanks, for the information. I did not want to embed the index create in the COBOL Code, so we have not tried that approach. We will give it a go.

Also, thanks for crash warning!!





"Seibert, Dave" cc: Sent by: DB2 Data Subject: Re: indexable global temp table Base Discussion List [...] 1125 36 31_Re: indexable global temp table13_Seibert, Dave26_Dave_Seibert@COMPUWARE.COM31_Fri, 29 Dec 2000 16:15:41 -0500327_iso-8859-1 Hi Amy, What result do you see when you try creating an index on your declared temp table? Do you get a negative SQLcode?

>I did not want to embed the index create in >the COBOL Code, so we have not tried that approach. We will give it a go.

Ahhhh. I bet that provides the answer to your problem. [...] 1162 52 47_Re: Buffer Pool: prefetch disabled/read engines14_Joel Goldstein29_joel_goldstein@COMPUSERVE.COM31_Fri, 29 Dec 2000 19:07:32 -0500390_ISO-8859-1 Jo, You can get avg I/O wait from the application actg reports, or Buffer Pool Tool will give this to you for the pool, and for every object. If the total number of buffers is the same now, then something else changed in your system or applications or dataset placement to cause the problem you are having..... or maybe it's because of your weekly re-assignment of objects. [...] 1215 21 24_Problem with ADO update.10_Navid Khan21_navid@DPI2.DPI.NET.IR31_Sat, 30 Dec 2000 09:58:47 +0330462_US-ASCII Hi all,

When I try to delete a recordset using ADO I receive the following error: Insufficient key column information for updating or refreshing. Who knows why?

Any help is appreciated. Navid Khan.

================================================ 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. 1237 48 13_4GB partition10_Alan Smith25_alancsmith@BTINTERNET.COM31_Sat, 30 Dec 2000 10:57:18 +0000631_US-ASCII How about turning on compression?

Alan Smith



> Date: Fri, 29 Dec 2000 06:34:49 -0500 > From: kkane@PAINEWEBBER.COM > Subject: 4GB partition > > This is a multi-part message in MIME format. > > ------=_NextPart_000_0010_01C07162.57979720 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: 7bit > > Friends, > Here is my problem. A partition of a V5.1 tablespace is going to exceed > 4gb and I have no time to re-engineer. I thought I could create a new > segmented TS to replace the partition in quesstion and create a new view to > Union the new table with a select [...] 1286 179 22_Re: MAD MAX goes again21_Salim, Arli (SG-SEMA)22_arli.salim@SEMA.COM.SG31_Sun, 31 Dec 2000 17:38:54 +0800640_iso-8859-1 I agree with Isaac that the answer is (always) "it depends."

Previously, I mentioned that I was puzzled that the distinct- group-by query did not outperform the inner join query.

I think I can now make the following observations based on the explain output:

- All three queries used iindex04, so there were most likely no other better indexes that had year and knumber as their key columns. - Iindex04 was probably based on knumber, year, respectively because the distinct-group-by query had to perform sortc_groupby. - Had there been an index based on year, knumber, the distinct- group-by query would [...] 1466 28 28_Re: Don't tell me what to do7_sd sdad17_tgmeept@YAHOO.COM31_Sun, 31 Dec 2000 02:06:35 -0800447_us-ascii Hello List.

I have run out of space on my yahoo account and so I must now unsubscribe from this list.

I am subscribed still to the dba list by having sent an email to db2udbdba-subscribe@quickdoc.co.uk and hope to see my many dba friends there.

Thanks.

Tg.

__________________________________________________ Do You Yahoo!? Yahoo! Photos - Share your holiday photos online! http://photos.yahoo.com/ [...]