Ever growing table despite of delete

Nguyen Duc Tuan

Ever growing table despite of delete
Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite
of deletes !

This is a 32K table which rows are inserted and then deleted at the end of
the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key
is on col1
The problem is that the size is continuously growing, it seems that the new
records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table
is taking extents too quickly, the dbas had to add more partitions today.

My explanation is that as the key is ever growing , the rows are added to
the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?


Thank you for your inputs

Duc

Steen Rasmussen

Ever growing table despite of delete
(in response to Nguyen Duc Tuan)
Try to alter the tablespace to be MEMBER CLUSTER.

Steen Rasmussen

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 11:39 AM
To: [login to unmask email]
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !
This is a 32K table which rows are inserted and then deleted at the end of the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?

Thank you for your inputs
Duc





-----End Original Message-----

Paul Ogborne

Ever growing table despite of delete
(in response to Nguyen Duc Tuan)
Hi Duc,


You have omitted a number of details, but two quick options which might be suitable would be to look into increasing DSSIZE and MAXPARTITIONS.
Discuss with your DBAs but they may have already done the latter.


Regards,
Paul.



-----Original Message-----
From: ducky <[login to unmask email]>
To: db2-l <[login to unmask email]>
Sent: Wed, 14 Dec 2016 17:39
Subject: [DB2-L] - Ever growing table despite of delete




Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !


This is a 32K table which rows are inserted and then deleted at the end of the transaction.

The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1

The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace


Until now the table is deleted each night at the end of the CICS session.

But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.


My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?

Is there anything as a quick solution we can do to relieve this ?



Thank you for your inputs


Duc












Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Nguyen Duc Tuan

Ever growing table despite of delete
(in response to Steen Rasmussen)
Hi Steen, Paul
It is already Member Cluster
Yes MAXPARTITIONS was increased today on a partition with DSSIZE 64G

On Wed, Dec 14, 2016 at 7:01 PM, Rasmussen, Steen <[login to unmask email]>
wrote:

> Try to alter the tablespace to be MEMBER CLUSTER.
>
>
>
> Steen Rasmussen
>
>
>
> *From:* ducky [mailto:[login to unmask email]
> *Sent:* Wednesday, December 14, 2016 11:39 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - Ever growing table despite of delete
>
>
>
> Hello colleagues,
>
> DB2 V10
>
> we have a production problem with a table which grows continuously despite
> of deletes !
>
> This is a 32K table which rows are inserted and then deleted at the end of
> the transaction.
>
> The table columns is : col1 sort of timestamp and col2 varchar 32K , key
> is on col1
>
> The problem is that the size is continuously growing, it seems that the
> new records are added to the end of the table.
> It is a UTS PBG tablespace
>
> Until now the table is deleted each night at the end of the CICS session.
>
> But as the transactions number is high at the end of the year, the table
> is taking extents too quickly, the dbas had to add more partitions today.
>
> My explanation is that as the key is ever growing , the rows are added to
> the end to respect the growing sequence ?
>
> Is there anything as a quick solution we can do to relieve this ?
>
> Thank you for your inputs
>
> Duc
>
>
>
>
>
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

John Bucaria

Ever growing table despite of delete
(in response to Nguyen Duc Tuan)
Have you tried reorg after delete?

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 12:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !
This is a 32K table which rows are inserted and then deleted at the end of the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?

Thank you for your inputs
Duc





-----End Original Message-----

Wayne Campbell

Ever growing table despite of delete
(in response to Nguyen Duc Tuan)
Hi all,

If no Clustering index is defined, DB2 will use the first index created as the clustering index. In this case, col1 will be it. Since this column is a timestamp, and I assume it’s a current timestamp, then it will never use previous used space.

A couple of options are:


1. Create a clustering index on the second column if it would be more random.


2. Create a third column load it with load the column with a random value (say one of the digits in the millisecond of the time stamp). Create a duplicate index making it the clustering index.

Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]<mailto:[login to unmask email]>

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 9:39 AM
To: [login to unmask email]
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !
This is a 32K table which rows are inserted and then deleted at the end of the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?

Thank you for your inputs
Duc





-----End Original Message-----

Bill Gallagher

Ever growing table despite of delete
(in response to John Bucaria)
I would think periodic reorgs on a regular basis should do the trick.
Nightly, after the scheduled deletes would be best. Weekly would be the
next best option.

-------------------------------------------------------
Bill Gallagher, DBA
Phoenix Life Insurance Company



From: "Bucaria, John" <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>,
Date: 12/14/2016 01:30 PM
Subject: [DB2-L] - RE: Ever growing table despite of delete



Have you tried reorg after delete?

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 12:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite
of deletes !
This is a 32K table which rows are inserted and then deleted at the end of
the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key
is on col1
The problem is that the size is continuously growing, it seems that the
new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table
is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to
the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?

Thank you for your inputs
Duc





-----End Original Message-----


Site Links: View post online View mailing list online Start new thread
via email Unsubscribe from this mailing list Manage your subscription


This email has been sent to: [login to unmask email]
** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



-----------------------------------------

*******************************************************************

CONFIDENTIAL: This communication, including
attachments, is intended only for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, you are hereby notified that you have received this document in error, and any use, review, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy any and all copies of this communication.

*******************************************************************

Philip Sevetson

Ever growing table despite of delete
(in response to John Bucaria)
“ducky,”

Two ideas.

One is a slight variation of John’s suggestion: since you have DELETE happening in every transaction, there should be no persistent data in the table. I recommend REORG with SHRLEVEL CHANGE, at a time when few transactions are occurring. Then run the SWITCH phase and accept a handful of failed or delayed transactions.

Two: Why, if none of this data persists past the transaction boundary, are you using a permanent table instead of a Created or Declared Global Temporary Table? Those would seem to meet the business use which you are describing, and CGTT/DGTT objects don’t add to the space of their tablespace (the System Temp/Work database, usually called DSNDB07). Are there other reasons to put the rows in a permanent table?

--Phil Sevetson

P.S. Excuse me if this issue has been raised already, but “ducky” doesn’t look like a real name. One of the community rules is that you use your real name in DB2-L.

PLS

From: Bucaria, John [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 1:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Ever growing table despite of delete

Have you tried reorg after delete?

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 12:39 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !
This is a 32K table which rows are inserted and then deleted at the end of the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?
Thank you for your inputs
Duc




-----End Original Message-----

-----End Original Message-----

James Campbell

real names [was Ever growing table despite of delete]
(in response to Philip Sevetson)
As someone who made a similar comment a few weeks ago about real names:

Since the signature, "Duc", is a Vietnamese name - perhaps he(?) did use his real name.

One can hardly expect an email address to be a real name.

James Campbell

<snip>
>  
> P.S. Excuse me if this issue has been raised already, but "ducky" doesn´t look like a real
> name. One of the community rules is that you use your real name in DB2-L.
>  
>  
> From: ducky [mailto:[login to unmask email] ]
> Sent: Wednesday, December 14, 2016 12:39 PM
> To: [login to unmask email]
> Subject: [DB2-L] - Ever growing table despite of delete
>  
> Hello colleagues,
>
<snip>
> Thank you for your inputs
> Duc
>  

John Bucaria

Ever growing table despite of delete
(in response to Bill Gallagher)
Yes, this is what I was suggesting.

From: Bill Gallagher [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 1:41 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Ever growing table despite of delete

I would think periodic reorgs on a regular basis should do the trick. Nightly, after the scheduled deletes would be best. Weekly would be the next best option.

-------------------------------------------------------
Bill Gallagher, DBA
Phoenix Life Insurance Company



From: "Bucaria, John" <[login to unmask email]<mailto:[login to unmask email]>>
To: "[login to unmask email]<mailto:[login to unmask email]>" <[login to unmask email]<mailto:[login to unmask email]>>,
Date: 12/14/2016 01:30 PM
Subject: [DB2-L] - RE: Ever growing table despite of delete
________________________________



Have you tried reorg after delete?

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 12:39 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !
This is a 32K table which rows are inserted and then deleted at the end of the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?

Thank you for your inputs
Duc





-----End Original Message-----
-----End Original Message-----

Mick Graley

Ever growing table despite of delete
(in response to John Bucaria)
I wouldn't REORG.
I would either TRUNCATE TABLE, or DELETE FROM table without a WHERE clause.
It's a UTS so it's segmented, so it will just reset all the space maps, no
need for all the "costly" allocate/drain/switch/delete processing.
Cheers,
Mick.


On 14 December 2016 at 20:10, Bucaria, John <[login to unmask email]> wrote:

> Yes, this is what I was suggesting.
>
>
>
> *From:* Bill Gallagher [mailto:[login to unmask email]
> *Sent:* Wednesday, December 14, 2016 1:41 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: Ever growing table despite of delete
>
>
>
> I would think periodic reorgs on a regular basis should do the trick.
> Nightly, after the scheduled deletes would be best. Weekly would be the
> next best option.
>
> -------------------------------------------------------
> Bill Gallagher, DBA
> Phoenix Life Insurance Company
>
>
>
> From: "Bucaria, John" <[login to unmask email]>
> To: "[login to unmask email]" <[login to unmask email]>,
> Date: 12/14/2016 01:30 PM
> Subject: [DB2-L] - RE: Ever growing table despite of delete
> ------------------------------
>
>
>
>
> Have you tried reorg after delete?
>
> *From:* ducky [mailto:[login to unmask email] <[login to unmask email]>]
> * Sent:* Wednesday, December 14, 2016 12:39 PM
> * To:* [login to unmask email]
> * Subject:* [DB2-L] - Ever growing table despite of delete
>
> Hello colleagues,
>
> DB2 V10
>
> we have a production problem with a table which grows continuously despite
> of deletes !
> This is a 32K table which rows are inserted and then deleted at the end of
> the transaction.
> The table columns is : col1 sort of timestamp and col2 varchar 32K , key
> is on col1
> The problem is that the size is continuously growing, it seems that the
> new records are added to the end of the table.
> It is a UTS PBG tablespace
> Until now the table is deleted each night at the end of the CICS session.
> But as the transactions number is high at the end of the year, the table
> is taking extents too quickly, the dbas had to add more partitions today.
> My explanation is that as the key is ever growing , the rows are added to
> the end to respect the growing sequence ?
> Is there anything as a quick solution we can do to relieve this ?
>
> Thank you for your inputs
> Duc
>
>
>
>
>
> -----End Original Message-----
> -----End Original Message-----
>

Harishankar Mohapatra

RE: Ever growing table despite of delete
(in response to Philip Sevetson)

I  completely  agree with you . Periodic reorg with shrlevel change should work.

Harishankar 

Russell Peters

RE: Ever growing table despite of delete
(in response to Nguyen Duc Tuan)

As was asked before, what is your clustering index? If it's clustering by the timestamp it will insert at end. If that's the case then setting the tablespace percent free and free page both to zero will help cut down on space being used daily. But as others have said, you will need a periodic reorg to clean up the empty space.

Muthuraj Kumaresan

Ever growing table despite of delete
(in response to Russell Peters)
We also have the same problem.. we have scheduled REORG every weekend .. we made sure that the tablespace won't run out of space in a week by coding proper DSSIZE and MAXPARTITIONS.. There are a couple of PTFs to fix the problems with space search algorithm in UTS.. I don't have them handy.. it would be good for you to open a PMR with IBM to get the list and make sure those are applied..



Sent from my iPhone

> On 15 Dec 2016, at 7:48 PM, Russell Peters <[login to unmask email]> wrote:
>
> As was asked before, what is your clustering index? If it's clustering by the timestamp it will insert at end. If that's the case then setting the tablespace percent free and free page both to zero will help cut down on space being used daily. But as others have said, you will need a periodic reorg to clean up the empty space.
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Venkat Srinivasan

RE: Ever growing table despite of delete
(in response to Steen Rasmussen)

Steve, Curious to know why you say member cluster I wd have thought member cluster is adding to the woes here.

Venkat

In Reply to Steen Rasmussen:

Try to alter the tablespace to be MEMBER CLUSTER.

Steen Rasmussen

From: ducky [mailto:[login to unmask email]
Sent: Wednesday, December 14, 2016 11:39 AM
To: [login to unmask email]
Subject: [DB2-L] - Ever growing table despite of delete

Hello colleagues,

DB2 V10

we have a production problem with a table which grows continuously despite of deletes !
This is a 32K table which rows are inserted and then deleted at the end of the transaction.
The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
It is a UTS PBG tablespace
Until now the table is deleted each night at the end of the CICS session.
But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
Is there anything as a quick solution we can do to relieve this ?

Thank you for your inputs
Duc





-----End Original Message-----

Muthuraj Kumaresan

Ever growing table despite of delete
(in response to Venkat Srinivasan)
In our case, we introduced the problem after changing it to member cluster only.. but then, I can't simply believe that it was the real root cause.. the growth was abnormal compared the amount of the data inserted.. the DATASIZE of RTS column is way less compared to the INUSE SPACE..

but I do believe that the lock avoidance to improve the performance of concurrent inserts due to member cluster will cause spaces to be wasted..

Sent from my iPhone

> On 16 Dec 2016, at 4:20 AM, Venkat Srinivasan <[login to unmask email]> wrote:
>
> Steve, Curious to know why you say member cluster I wd have thought member cluster is adding to the woes here.
>
> Venkat
>
> In Reply to Steen Rasmussen:
>
> Try to alter the tablespace to be MEMBER CLUSTER.
>
> Steen Rasmussen
>
> From: ducky [mailto:[login to unmask email]
> Sent: Wednesday, December 14, 2016 11:39 AM
> To: [login to unmask email]
> Subject: [DB2-L] - Ever growing table despite of delete
>
> Hello colleagues,
>
> DB2 V10
>
> we have a production problem with a table which grows continuously despite of deletes !
> This is a 32K table which rows are inserted and then deleted at the end of the transaction.
> The table columns is : col1 sort of timestamp and col2 varchar 32K , key is on col1
> The problem is that the size is continuously growing, it seems that the new records are added to the end of the table.
> It is a UTS PBG tablespace
> Until now the table is deleted each night at the end of the CICS session.
> But as the transactions number is high at the end of the year, the table is taking extents too quickly, the dbas had to add more partitions today.
> My explanation is that as the key is ever growing , the rows are added to the end to respect the growing sequence ?
> Is there anything as a quick solution we can do to relieve this ?
>
> Thank you for your inputs
> Duc
>
>
>
>
>
> -----End Original Message-----
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>