1 WWW.IDUGDB2-L.ORG /home/listserv/home/db2-l October 2005, week 5 2 112 35_Re: forcing DB2 to not use an index14_James Campbell25_jacampbell@ACSLINK.NET.AU31_Sat, 29 Oct 2005 17:45:44 -0700595_US-ASCII How to force a tablespace scan - an optimization hint.

Another technique to delete duplicate rows: 1) add a ROWID (generated by default) column to the table. On retrieval, DB2 will generate unique value for each row if the row doesn't have an actual rowid value stored in it. 2) create two indexes: a unique index on the rowid column (DB2 insists on this), an index with normal primary key values (all the other columns in this case) and rowid. 3) delete from table a where exists (select 1 from table b where a.primary_keys = b.primary_keys and hex(a.rowid) > hex(b.rowid) [...] 115 28 33_Re: EDM POOL monitoring Question?13_Martin Packer24_martin_packer@UK.IBM.COM31_Fri, 28 Oct 2005 22:48:47 +0100363_US-ASCII My metric for this is "requests per load" for all the categories. We chart that by time of day.

Martin

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

"Next time someone says a penny for your thoughts - sell!" Peter Kay, Hyde Park, July 15, 2005 [...] 144 102 43_Re: Hybrid Join vs Nested Loop - v7 on z/OS13_Terry Purcell18_tpurcel@US.IBM.COM31_Sun, 30 Oct 2005 16:04:34 -0600403_- Scott,

It appears from the counts that the preferred join sequence is tbinit_case_insrd joined to drdb01_ck_req. There is already an adequate index tbinit_case_insrd to support the filtering, join and index-only access, although the indexes on drdb01_ck_req support the join or local, but not both.

I would suggest either one of these two methods to get the performance you expect: [...] 247 55 54_Re: A DB2 version 8 (for z) compatibility mode caution12_Roger Miller19_millerrl@US.IBM.COM31_Sun, 30 Oct 2005 21:12:48 -0600632_- PK14145 is open to remove the new function from compatibility mode.

Roger Miller

On Mon, 24 Oct 2005 02:38:55 -0500, Roger Miller wrote:

>The story is a bit more complex, but we're discussing this situation in >the DB2 design and development group to see what can be done. > >Roger Miller > >On Tue, 18 Oct 2005 11:27:59 -0500, Larry IS.Kirkpatrick@MUTUALOFOMAHA.COM> wrote: > >>I have found one SQL coding practice that can be implemented on a Version >8 >>- compatibility mode platform that will fail on a version 7 platform. >This >>means that if you have installed version [...] 303 126 27_DSNB209I - Need information21_Sriramulu, Vijayababu28_vijayababu.sriramulu@EDS.COM31_Mon, 31 Oct 2005 14:33:10 +0530311_us-ascii Dear List Users,

Last week we ran into a situation where we had threads knocked out because of the message DSNB209I on a specific tablespace. Display on the claimers on this object showed a screenfull of threads and not sure which ones were holding X locks. We are out of this problem now. [...] 430 131 27_DSNB209I - Need information21_Sriramulu, Vijayababu28_vijayababu.sriramulu@EDS.COM31_Mon, 31 Oct 2005 15:42:13 +0530336_us-ascii Dear List Users,

Last week we ran into a situation where we had threads knocked out because of the message DSNB209I on a specific tablespace. Display on the claimers on this object showed a screenfull of threads and not sure which ones were holding X locks. We are out of this problem now. We are in DB2 V7.1 ZO/S. [...] 562 19 24_Re: Lock/Latch on Insert33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Mon, 31 Oct 2005 04:24:03 -0600326_- Hi

An insert has to wait for other applications, if there is a unique index defined on the table. We have a similar discussion in this list some time ago.

Also the space map can be the reason for locks or latches.

Are your locks all local or are you in data sharing and you have also global locks? [...] 582 45 79_[DB2 V8 NFM] Problems with timestamp columns in join between UNICODE and EBCDIC33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Mon, 31 Oct 2005 06:24:21 -0600570_- Hi

I don't know if that is a bug or a problem in my understanding of conversion. Probably the latter will be the case.

I executed the following select remotely from a DB2 V8 NFM to another DB2 V8 NFM (all host systems)

SELECT STATSTIME FROM DPLG.SYSIBM.SYSCOLDIST A INNER JOIN DPLG.DB2.ISTB0251 B ON A.TBNAME = B.NAME AND B.LOCATION IN ('DB2T', 'DB2D', 'DGLG', 'DILG') INNER JOIN (SELECT CREATOR FROM DPLG.DB2.ISTB0251 WHERE NAME = 'PLAN_TABLE' GROUP BY CREATOR ) AS C ON B.CREATOR = C.CREATOR WHERE B.CREATOR = 'DB2' AND B.NAME = 'AZTB0Z03' [...] 628 34 83_Re: [DB2 V8 NFM] Problems with timestamp columns in join between UNICODE and EBCDIC33_Walter Jani=?ISO-8859-1?Q?=DFen?=27_walter.janissen@VICTORIA.DE31_Mon, 31 Oct 2005 06:35:11 -0600468_- Hi

I can simplify that SQL:

SELECT STATSTIME FROM DPLG.SYSIBM.SYSTABLES INNER JOIN DPLG.SYSIBM.SYSDUMMYU ON 1 = 1 WHERE CREATOR = 'SYSIBM' AND NAME = 'SYSTABLES'

result: o.k.

SELECT STATSTIME FROM DPLG.SYSIBM.SYSTABLES INNER JOIN DPLG.SYSIBM.SYSDUMMYE ON 1 = 1 WHERE CREATOR = 'SYSIBM' AND NAME = 'SYSTABLES'

result: garbage

But, mind you, you have to execute these two selects against a remote host DB2 V8 NFM system. [...] 663 65 24_Re: Lock/Latch on Insert11_Mike Turner29_michael_turner@COMPUSERVE.COM31_Mon, 31 Oct 2005 14:12:22 -0000546_us-ascii Dave

I assume that when you refer to Lock/Latch waits, you are referring to the data stored in IFCID field QWACAWTL. The latch referred to here is not a DB2 page latch, but a latch taken by IRLM to control access to its lock control blocks. You are correct that Insert should not wait for a Lock. It can however wait for the IRLM latch. IRLM has to latch the control block chain before adding the new lock that protects the inserted row. At times of heavy insert activity you can get IRLM latch waits. I am afraid that there [...] 729 63 35_Re: forcing DB2 to not use an index9_Joe Burns25_joseph.burns@HIGHMARK.COM31_Mon, 31 Oct 2005 08:27:18 -0600530_- Hi John,

I think someone already mentioned this, but I would guess that an optimization hint is the way to go. We've had similar issues with suspected dups and an out of sync index. We've always been able to force a tablescan with some creative SQL though. But I must admit I don't think we've had the situation where all the columns of the table were also in an index. I could see where that would make things more difficult. So if you can't get it by using special SQL predicates I would try an optimization hint. [...] 793 56 25_COPY USING DB2 V7 LISTDEF11_Tek-Hoe Tan18_areksby@ROGERS.COM31_Mon, 31 Oct 2005 09:28:37 -0600504_- My shop just upgraded DB2 V7 recently, and we also converted the image copy jobs to using LISTDEF with sharelevel reference and parallel(03).

Since the deployment of new COPY with LISTDEF, We have been experiencing problem with the execution of the COPY jobs.

Using the old method, the copy job that used to run in 2 minutes, the same job ran for over 2 hours with LISTDEF. When examining the job statistics from Insight for DB2, the statistics show high degree of "OTHER DB2 TIME". [...] 850 86 35_Re: forcing DB2 to not use an index13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Mon, 31 Oct 2005 12:45:46 -0500437_iso-8859-1 What's the chance of doing a quiesce write yes and then using dsn1copy to a 'like' table without an index?

Scott Hodgin, Database Administrator South Carolina Farm Bureau Insurance Company shodgin@scfbins.com

-----Original Message----- From: Joe Burns [mailto:joseph.burns@HIGHMARK.COM] Sent: Monday, October 31, 2005 9:27 AM To: DB2-L@WWW.IDUGDB2-L.ORG Subject: Re: [DB2-L] forcing DB2 to not use an index [...] 937 26 53_UDB LUW 8.2.3 - Update udb and federated in same uow?13_Hodgin, Scott19_shodgin@SCFBINS.COM31_Mon, 31 Oct 2005 16:51:28 -0500460_iso-8859-1 Hi list,

I need some clarification on something I've been testing.

We're on Windows 2003 server and using UDB ESE for LUW 8.2.3. I was trying to update both a UDB table and a z/os federated table, but received errors saying this was not supported. At one time I remember testing the multi-site update configuration stuff from with Control Center and it succeeded, but I never could get the 2 different updates to work in one UOW. [...] 964 315 31_Re: DSNB209I - Need information19_Humphris,Richard P.24_Richard.Humphris@CNA.COM31_Mon, 31 Oct 2005 17:07:22 -0600556_us-ascii

Hi Vijay,

Hopefully someone else has more information. Take the following post with a grain of salt.

First, I don't think you really have the data you want. I think the only number the accounting information can provide you would be the maximum amount of time a claim COULD have been acquired for a specific thread (in your display claimers) but not much more without knowing more about the application and how it was bound. For example: release/deallocate vs.. release/commit with a transaction taking regular commits. [...] 1280 72 29_Re: COPY USING DB2 V7 LISTDEF14_James Campbell25_jacampbell@ACSLINK.NET.AU30_Tue, 1 Nov 2005 12:47:29 -0800556_US-ASCII I would change the scheduling so that the mvs catalog backup occurs at a different time from the DB2 COPY.

A possible cause of this is:

- when you use JCL to allocate/catalog datasets all cataloging is done at the same time - end of step execution. It appears that the mvs catalog back up is creating locks on the catalog (I'm not sure which of several methods you are using to back up the mvs catalog, so I don't know the actual locking strategy used) which are being held for "some" time and then being released. Hence at DB2 [...] 1353 363 37_Re: Connections from Z/OS to Unix DB214_Randall Ibbott15_randall@QBE.COM30_Tue, 1 Nov 2005 12:58:45 +1100571_UTF-8 Finally got it working... The symptom I was getting seemed to be related to another situation - not sure if it is a problem, or working as designed. It seems the entries in the IPNAMES and LOCATIONS tables are still available to SPUFI (at least) when you change or delete them. A bounce of DDF (presumably that's all that's needed - but I haven't tested it as we bounce the entire subsystem) clears out any "cached" entries (or whatever). These entries that hung around seemed to be causing the problem trying to obtain connectivity to LUW. I was finally able [...]