heavy sequential insert to the end of the table and index&platch wait time

ÖZGÜR ÖZEN

heavy sequential insert to the end of the table and index&platch wait time
Hi all,
we have a response time critical application which has too many concurent
transactions that inserts 4 log records to 4 tables,and we have designed
these tables as pctfree 0 freepage 0 and having an ascending nonclustering
index with pctfree and freepage 0 also for inserting all the records to the
last pages of data and index for eliminating I/O s.

SQL_CALL INDB2_TIME INDB2_CPU GETPAGE GETPFAIL SYNCREAD
-------- ------------ ------------ ---------- ---------- ----------
INSERT 00:00.020894 00:00.001744 4.37 0.00 0.03
INSERT 00:00.038894 00:00.001867 5.20 0.00 0.03
INSERT 00:00.017939 00:00.001528 5.92 0.00 0.01
INSERT 00:00.006975 00:00.001470 5.87 0.00 0.00
SETHSTVR 00:00.000249 00:00.000230 0.00 0.00 0.00

These ara average accountings for each insert,we succeed the elimination of
syncreads but we still have too much indb2_time because of latching and
global locking(we have 4 way datasharing)

SQL_CALL PLATCH_WTIME PLATCHWCNT DRAIN_WTIME DRAINWCNT CLAIM_WT
-------- ------------ ---------- ------------ ---------- --------
INSERT 00:00.005605 0.27 00:00.000443 0.03 00:00.00
INSERT 00:00.013001 0.36 00:00.000462 0.04 00:00.00
INSERT 00:00.002315 0.05 00:00.000380 0.03 00:00.00
INSERT 00:00.000293 0.02 00:00.000205 0.02 00:00.00
SETHSTVR 00:00.000000 0.00 00:00.000000 0.00 00:00.00

GLOCK_WTIME
------------
00:00.001615
00:00.002124
00:00.003383
00:00.001639
00:00.000000

Can anyone suggest any design for eliminating these locking times.

These tables are segmented and I could not use member cluster option for
eliminating spacemap contention,we could partition the tables but I am not
sure how much time we can earn from this contention.

Thanks for your helps.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

ÖZGÜR ÖZEN

heavy sequential insert to the end of the table and index&platch wait time
(in response to Markus Liebenberg)
Hi all,
we have a response time critical application which has too many concurent
transactions that inserts 4 log records to 4 tables,and we have designed
these tables as pctfree 0 freepage 0 and having an ascending nonclustering
index with pctfree and freepage 0 also for inserting all the records to the
last pages of data and index for eliminating I/O s.

SQL_CALL INDB2_TIME INDB2_CPU GETPAGE GETPFAIL SYNCREAD
-------- ------------ ------------ ---------- ---------- ----------
INSERT 00:00.020894 00:00.001744 4.37 0.00 0.03
INSERT 00:00.038894 00:00.001867 5.20 0.00 0.03
INSERT 00:00.017939 00:00.001528 5.92 0.00 0.01
INSERT 00:00.006975 00:00.001470 5.87 0.00 0.00
SETHSTVR 00:00.000249 00:00.000230 0.00 0.00 0.00

These ara average accountings for each insert,we succeed the elimination of
syncreads but we still have too much indb2_time because of latching and
global locking(we have 4 way datasharing)

SQL_CALL PLATCH_WTIME PLATCHWCNT DRAIN_WTIME DRAINWCNT CLAIM_WT
-------- ------------ ---------- ------------ ---------- --------
INSERT 00:00.005605 0.27 00:00.000443 0.03 00:00.00
INSERT 00:00.013001 0.36 00:00.000462 0.04 00:00.00
INSERT 00:00.002315 0.05 00:00.000380 0.03 00:00.00
INSERT 00:00.000293 0.02 00:00.000205 0.02 00:00.00
SETHSTVR 00:00.000000 0.00 00:00.000000 0.00 00:00.00

GLOCK_WTIME
------------
00:00.001615
00:00.002124
00:00.003383
00:00.001639
00:00.000000

Can anyone suggest any design for eliminating these locking times.

These tables are segmented and I could not use member cluster option for
eliminating spacemap contention,we could partition the tables but I am not
sure how much time we can earn from this contention.

Thanks for your helps.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


*************************************************************************************
The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.
*************************************************************************************

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Janißen

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
I think the space map could be a problem, because as I know, these pages
are treated as if you have row level locking. Did you try TRACKMOD NO. May
be then some of your global lock contention will be alleviated.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

ÖZGÜR ÖZEN

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to Walter Janißen)
thanks I will plan to use it
do you have any idea about the member cluster parameter,can't it be used
with segmented ts's




[login to unmask email]

Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09.12.2003 11:09
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: heavy sequential insert to the end of the table and index&platch wait
time


I think the space map could be a problem, because as I know, these pages
are treated as if you have row level locking. Did you try TRACKMOD NO. May
be then some of your global lock contention will be alleviated.

---------------------------------------------------------------------------------
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". If you will be out of
the office, send the SET DB2-L NO MAIL command to
[login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm




OZGUR OZEN
YAPI VE KREDI BANKASI A.S. / TEKNOLOJI YONETIMI
Tel : 262 647 32 33
Fax : 262 647 17 11
www.ykteknoloji.com





________________________________________________________
Bu mesajda, yalnizca muhatabini ilgilendiren, kisiye veya kuruma özel
bilgiler yer aliyor olabilir. Mesajin muhatabi degilseniz, içerigini ve
varsa ekindeki dosyalari kimseye aktarmayiniz ya da kopyalamayiniz. Böyle
bir durumda lütfen göndereni uyarip, mesaji imha ediniz. Göstermis
oldugunuz hassasiyetten ötürü tesekkür ederiz.

This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden. Thank you for your co-operation.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

&Ouml;ZG&Uuml;R &Ouml;ZEN

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
also I see 0 in glocal lock wait time,main problem of me is platch wait
time ,is it related with datsaharing or is it only because of inserts,if
so I would not work on member cluster and track mod no
thanks.




[login to unmask email]

Sent by: DB2 Data Base Discussion List <[login to unmask email]>
09.12.2003 11:09
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: heavy sequential insert to the end of the table and index&platch wait
time


I think the space map could be a problem, because as I know, these pages
are treated as if you have row level locking. Did you try TRACKMOD NO. May
be then some of your global lock contention will be alleviated.

---------------------------------------------------------------------------------
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". If you will be out of
the office, send the SET DB2-L NO MAIL command to
[login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm




OZGUR OZEN
YAPI VE KREDI BANKASI A.S. / TEKNOLOJI YONETIMI
Tel : 262 647 32 33
Fax : 262 647 17 11
www.ykteknoloji.com





________________________________________________________
Bu mesajda, yalnizca muhatabini ilgilendiren, kisiye veya kuruma özel
bilgiler yer aliyor olabilir. Mesajin muhatabi degilseniz, içerigini ve
varsa ekindeki dosyalari kimseye aktarmayiniz ya da kopyalamayiniz. Böyle
bir durumda lütfen göndereni uyarip, mesaji imha ediniz. Göstermis
oldugunuz hassasiyetten ötürü tesekkür ederiz.

This e-mail may contain confidential and/or privileged information. If you
are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail. Any
unauthorised copying, disclosure or distribution of the material in this
e-mail is strictly forbidden. Thank you for your co-operation.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
Hi

I've no experience with member cluster, but according to the manual it is
only allowed for partitioned tablespaces.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Markus Liebenberg

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to Walter Janißen)
What does the key column(s) look like?

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of zgür Özen
Sent: 09 December 2003 08:06
To: [login to unmask email]
Subject: heavy sequential insert to the end of the table and index&platch wait time


Hi all,
we have a response time critical application which has too many concurent transactions that inserts 4 log records to 4 tables,and we have designed these tables as pctfree 0 freepage 0 and having an ascending nonclustering index with pctfree and freepage 0 also for inserting all the records to the last pages of data and index for eliminating I/O s.

SQL_CALL INDB2_TIME INDB2_CPU GETPAGE GETPFAIL SYNCREAD
-------- ------------ ------------ ---------- ---------- ----------
INSERT 00:00.020894 00:00.001744 4.37 0.00 0.03
INSERT 00:00.038894 00:00.001867 5.20 0.00 0.03
INSERT 00:00.017939 00:00.001528 5.92 0.00 0.01
INSERT 00:00.006975 00:00.001470 5.87 0.00 0.00
SETHSTVR 00:00.000249 00:00.000230 0.00 0.00 0.00

These ara average accountings for each insert,we succeed the elimination of syncreads but we still have too much indb2_time because of latching and global locking(we have 4 way datasharing)

SQL_CALL PLATCH_WTIME PLATCHWCNT DRAIN_WTIME DRAINWCNT CLAIM_WT
-------- ------------ ---------- ------------ ---------- --------
INSERT 00:00.005605 0.27 00:00.000443 0.03 00:00.00
INSERT 00:00.013001 0.36 00:00.000462 0.04 00:00.00
INSERT 00:00.002315 0.05 00:00.000380 0.03 00:00.00
INSERT 00:00.000293 0.02 00:00.000205 0.02 00:00.00
SETHSTVR 00:00.000000 0.00 00:00.000000 0.00 00:00.00

GLOCK_WTIME
------------
00:00.001615
00:00.002124
00:00.003383
00:00.001639
00:00.000000

Can anyone suggest any design for eliminating these locking times.

These tables are segmented and I could not use member cluster option for eliminating spacemap contention,we could partition the tables but I am not sure how much time we can earn from this contention.

Thanks for your helps.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
Confidentiality Note: The information contained in this email and
document(s) attached are for the exclusive use of the addressee and may contain confidential, privileged and non-disclosable information. If the recipient of this email is not the addressee, such recipient is strictly prohibited from reading, photocopying, distribution or otherwise using this email or its contents in any way. Please notify the Sapiens (UK) Ltd. Systems Administrator via e-mail immediately at [login to unmask email], if you have received this email in error.

Disclaimer: The views, opinions and guidelines contained in this confidential e-mail are those of the originating author and may not be representative of Sapiens (UK) Ltd.
------------------------------------------------------------------------

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris White

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
From a previous post by walter janissen...
>>I've no experience with member cluster, but according to the manual it is
>>only allowed for partitioned tablespaces.

Walter,
Are you sure about that? I think member cluster can be used on all but LOB
and TEMP tablespaces. Here's how I would evaluate whether to use member
cluster vs trackmod no...

"Trackmod no" does not update the space map page. It can greatly improve
concurrency for inserts, updates, and deletes. But if you use incremental
image copy, the copy utility will have to do tablespace scans to find the
updated pages. It might be a problem if you can't tolerate the negative
impact to incremental image copy. Also, I believe applications that try to
insert at the end of a table can still experience serious concurrency
problems in a data sharing environment (contention for the last data page
among different DS members).

"Member cluster" is a nice solution for heavy inserts at the end of a
tablespace in a data sharing environment. It still tracks changes on the
space map page but each data sharing member has its own range of data pages
for inserts and its own space map page for its range of data pages. So
contention is almost eliminated (at least at the data sharing level) for
space map pages and data pages . Incremental image copies can still use
space map pages to determine which pages to copy.

Our shop has had good luck using member cluster to improve concurrency in a
3-way data sharing group for applications that insert at the end.

Regards,
Chris White
DB2 Sysprog
Caterpillar Inc.

These are my opinions, not my employer's and blah, blah, blah...

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

&Ouml;ZG&Uuml;R &Ouml;ZEN

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to Chris White)
Can we use these 2 parameters together.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
Chris

Sorry, you are right and I am wrong. You can create a nonpartitioned member
cluster tablespace. So my fault.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to Walter Janißen)
On Thu, 11 Dec 2003 09:16:54 -0600, zg=?ISO-8859-1?Q?=FCr_=D6zen?=
<[login to unmask email]> wrote:

>Can we use these 2 parameters together. Answer is YES
>
>---------------------------------------------------------------------------
------
>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". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

&Ouml;ZG&Uuml;R &Ouml;ZEN

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to Walter Janißen)
I tried and could not created,so now I am trying to partition tables?
It returns segsize and member cluster parameters mutually exclusive

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

&Ouml;ZG&Uuml;R &Ouml;ZEN

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to Markus Liebenberg)
Yes I got -628 but without segsize it will be simple tablespace?

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Markus Liebenberg

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
With the SEGSIZE clause you should get SQLCODE -628. Remove the SEGSIZE clause from your CREATE TABLESPACE DDL and see what happens.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of zgür Özen
Sent: 11 December 2003 15:36
To: [login to unmask email]
Subject: Re: heavy sequential insert to the end of the table and index&platch wait time


I tried and could not created,so now I am trying to partition tables? It returns segsize and member cluster parameters mutually exclusive

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
------------------------------------------------------------------------
Confidentiality Note: The information contained in this email and
document(s) attached are for the exclusive use of the addressee and may contain confidential, privileged and non-disclosable information. If the recipient of this email is not the addressee, such recipient is strictly prohibited from reading, photocopying, distribution or otherwise using this email or its contents in any way. Please notify the Sapiens (UK) Ltd. Systems Administrator via e-mail immediately at [login to unmask email], if you have received this email in error.

Disclaimer: The views, opinions and guidelines contained in this confidential e-mail are those of the originating author and may not be representative of Sapiens (UK) Ltd.
------------------------------------------------------------------------

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Aurora Dellanno

Re: heavy sequential insert to the end of the table and index&pla tch wait time
(in response to Chris White)
Ozgur Bey,

For a partitioned tablespace, the syntax is CREATE TABLESPACE ......
NUMPARTS int MEMBER CLUSTER ....

Your partitioined tbs cannot be segmented, and viceversa.

HTH, iyi aksamlar.

ciao!

Aurora Emanuela Dell'Anno
Database Analyst
Data Services Group - Bank of America
tel. 66192
ext. 0208 760 6192
[login to unmask email]

* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER

no trees were killed in sending this message. However, a large number of
electrons were seriously inconvenienced :-)



-----Original Message-----
From: zg|r Vzen [mailto:[login to unmask email]
Sent: 11 December 2003 15:36
To: [login to unmask email]
Subject: Re: heavy sequential insert to the end of the table and
index&platch wait time


I tried and could not created,so now I am trying to partition tables?
It returns segsize and member cluster parameters mutually exclusive

----------------------------------------------------------------------------
-----
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". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

Notice to recipient:
The information in this internet e-mail and any attachments is confidential
and may be privileged. It is intended solely for the addressee. If you are
not the intended addressee please notify the sender immediately by
telephone. If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance on it,
is prohibited and may be unlawful.
When addressed to external clients any opinions or advice contained in this
internet e-mail are subject to the terms and conditions expressed in any
applicable governing terms of business or client engagement letter issued by
the pertinent Bank of America group entity.
If this email originates from the U.K. please note that Bank of America,
N.A., London Branch, Banc of America Securities Limited and Banc of America
Futures Incorporated are regulated by the Financial Services Authority.

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chris White

Re: heavy sequential insert to the end of the table and index&platch wait time
(in response to ÖZGÜR ÖZEN)
From zgür Özen <[login to unmask email]>...
>>Can we use these 2 parameters together?


I've never tried so I don't know for sure. It may be technically possible,
but they seem to contradict each other in the way they track changes.

I think "member cluster" maintains the changed data bit on space map pages
and "trackmod no" does not, so they seem mutually exclusive in that
context. One significant difference... "member cluster" only applies to
data sharing environments while "trackmod no" can be used in either DS or
non-DS environments. But I don't think the decision for using one or the
other is that simple.

I would suggest reading more about it in the Data Sharing Planning &
Administration guide under "Reducing space map page contention" (pg 221 in
my V7 copy).

To get back to the original poster's question... I think they said they
were 4-way data sharing and they could not use "member cluster". Also,
they are inserting heavily at the end of the table. He said they
experience "too much indb2_time because of latching and global locking(we
have 4 way datasharing)". Maybe it's not the latching and locking, it's
the *WAITING* for latching and locking that's killing them (minor point,
but significant). Latching and locking are very efficient when there are
no waits to get them. Member clustering eliminates a lot of the latching
and locking between members. You don't wait for something that's
eliminated. I'm not sure why they can't use "member cluster" but I would
pursue that if possible.

Regards,
Chris White
DB2 Sysprog
Caterpillar Inc.

These are my opinions, not my employer's and blah, blah, blah...

---------------------------------------------------------------------------------
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". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm