Insert after mass delete

Walter Janißen

Insert after mass delete
Hi

I want to talk about a problem, we recently experienced, which I have no
explanation for. May be, some else has experienced the same.

An applications inserts rows into a table and frequently commits, say after
10 inserts. Concurrently another application reads the very same table
(without UR) and externalized the read rows to a sequential dataset. After
it has finisched the application does a DELETE witout any where-clause.

The first application continues to insert rows, while in between it had to
wait for the DELETE.

Now I come to my problem. When the first application finished, an
application developer did some checks and saw, that some of the rows were
still in the table, the second application also had read and written to
dataset (rows have identical keys). How can that be?

The application developer assures, that he didn't ignore any negative
SQLCODE (such as ressource unavailable) nor can I imagine, that a mass
delete only deletes part of the rows (but I have to admit, that I do not
know, how a mass delete internally works, the only thing I know about that,
is that DB2 turns on a special bit).

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Roger Miller

Re: Insert after mass delete
(in response to Walter Janißen)
Not every delete with no where clause becomes a mass delete. This sounds
like a good situation to get the detailed trace and see what it's really
doing. If this delete becomes a scan, deleting rows as it processes, then
this would probably be the result.

Roger Miller

On Wed, 14 Jan 2004 06:31:27 -0600, [login to unmask email] wrote:

>Hi
>
>I want to talk about a problem, we recently experienced, which I have no
>explanation for. May be, some else has experienced the same.
>
>An applications inserts rows into a table and frequently commits, say
after
>10 inserts. Concurrently another application reads the very same table
>(without UR) and externalized the read rows to a sequential dataset. After
>it has finisched the application does a DELETE witout any where-clause.
>
>The first application continues to insert rows, while in between it had to
>wait for the DELETE.
>
>Now I come to my problem. When the first application finished, an
>application developer did some checks and saw, that some of the rows were
>still in the table, the second application also had read and written to
>dataset (rows have identical keys). How can that be?
>
>The application developer assures, that he didn't ignore any negative
>SQLCODE (such as ressource unavailable) nor can I imagine, that a mass
>delete only deletes part of the rows (but I have to admit, that I do not
>know, how a mass delete internally works, the only thing I know about
that,
>is that DB2 turns on a special bit).
>

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: Insert after mass delete
(in response to Roger Miller)
On Wed, 14 Jan 2004 10:56:54 -0600, Roger Miller <[login to unmask email]>
wrote:

Hello Roger

>Not every delete with no where clause becomes a mass delete.

Well, I forgot to tell some things, which were clear to me but naturaly not
to others. The table is stored in a segmented tablespace and no RI nor
anything other (like triggers) exist on that table.

>If this delete becomes a scan, deleting rows as it processes, then
>this would probably be the result.

I am not sure, if I understand that sentence. Do you mean, if the delete
scans the entire table and delete one row at a time, that it might be
possible, that some rows will not be deleted ??? You must say, that this
cannot be true or DB2 drives me crazy.

>Not every delete with no where clause becomes a mass delete. This sounds
>like a good situation to get the detailed trace and see what it's really
>doing. If this delete becomes a scan, deleting rows as it processes, then
>this would probably be the result.
>
>Roger Miller
>
>On Wed, 14 Jan 2004 06:31:27 -0600, [login to unmask email] wrote:
>
>>Hi
>>
>>I want to talk about a problem, we recently experienced, which I have no
>>explanation for. May be, some else has experienced the same.
>>
>>An applications inserts rows into a table and frequently commits, say
>after
>>10 inserts. Concurrently another application reads the very same table
>>(without UR) and externalized the read rows to a sequential dataset. After
>>it has finisched the application does a DELETE witout any where-clause.
>>
>>The first application continues to insert rows, while in between it had to
>>wait for the DELETE.
>>
>>Now I come to my problem. When the first application finished, an
>>application developer did some checks and saw, that some of the rows were
>>still in the table, the second application also had read and written to
>>dataset (rows have identical keys). How can that be?
>>
>>The application developer assures, that he didn't ignore any negative
>>SQLCODE (such as ressource unavailable) nor can I imagine, that a mass
>>delete only deletes part of the rows (but I have to admit, that I do not
>>know, how a mass delete internally works, the only thing I know about
>that,
>>is that DB2 turns on a special bit).
>>
>
>---------------------------------------------------------------------------
------
>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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Hello Roger

Well, I forgot to tell some things, which were clear to me but naturaly not
to others. The table is stored in a segmented tablespace and no RI nor
anything other (like triggers) exist on that table.

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Vin db

Re: Insert after mass delete
(in response to Walter Janißen)
Walter,

Would you be able to share more details about the Insert and delete processes ?
Namely, isolation levels & the way these processes are coded.

Cheers
Navin Agrawal

[login to unmask email] wrote:
On Wed, 14 Jan 2004 10:56:54 -0600, Roger Miller
wrote:

Hello Roger

>Not every delete with no where clause becomes a mass delete.

Well, I forgot to tell some things, which were clear to me but naturaly not
to others. The table is stored in a segmented tablespace and no RI nor
anything other (like triggers) exist on that table.

>If this delete becomes a scan, deleting rows as it processes, then
>this would probably be the result.

I am not sure, if I understand that sentence. Do you mean, if the delete
scans the entire table and delete one row at a time, that it might be
possible, that some rows will not be deleted ??? You must say, that this
cannot be true or DB2 drives me crazy.

>Not every delete with no where clause becomes a mass delete. This sounds
>like a good situation to get the detailed trace and see what it's really
>doing. If this delete becomes a scan, deleting rows as it processes, then
>this would probably be the result.
>
>Roger Miller
>
>On Wed, 14 Jan 2004 06:31:27 -0600, [login to unmask email] wrote:
>
>>Hi
>>
>>I want to talk about a problem, we recently experienced, which I have no
>>explanation for. May be, some else has experienced the same.
>>
>>An applications inserts rows into a table and frequently commits, say
>after
>>10 inserts. Concurrently another application reads the very same table
>>(without UR) and externalized the read rows to a sequential dataset. After
>>it has finisched the application does a DELETE witout any where-clause.
>>
>>The first application continues to insert rows, while in between it had to
>>wait for the DELETE.
>>
>>Now I come to my problem. When the first application finished, an
>>application developer did some checks and saw, that some of the rows were
>>still in the table, the second application also had read and written to
>>dataset (rows have identical keys). How can that be?
>>
>>The application developer assures, that he didn't ignore any negative
>>SQLCODE (such as ressource unavailable) nor can I imagine, that a mass
>>delete only deletes part of the rows (but I have to admit, that I do not
>>know, how a mass delete internally works, the only thing I know about
>that,
>>is that DB2 turns on a special bit).
>>
>
>---------------------------------------------------------------------------
------
>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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Hello Roger

Well, I forgot to tell some things, which were clear to me but naturaly not
to others. The table is stored in a segmented tablespace and no RI nor
anything other (like triggers) exist on that table.

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

---------------------------------
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Max Scarpa

Re: Insert after mass delete
(in response to Vin db)
Walter, I don't know why but your situation 'triggered' a BBW (Bonnie
Baker's warning) in my mind...

Did you try the delete process using a timestamp to establish a 'dead line'
for rows to be deleted ?

Max Scarpa

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: Insert after mass delete
(in response to Max Scarpa)
Max and Navin

I think the BBW you are thinking of, is has something reading data, where
the developer leaned on the sequence of the data without explicitly coding
an order be clause. This is not the case, because the batch application
read all rows which were in the table and wrote them to dataset. After
getting +100 it executed a delete without a where clause.

Well the whole process is a bit more complex. There are 2 tables (T1 and T2)
which are used in a flip flop kind. Each of these 2 tables is recorded in a
control table and can have 3 stati: '1' free for use, '2' in use by the
batch application (say B), which is responsible to offload all the rows,
and '3' all rows offloaded.

The first application (this is an IMS online application, say A) checks
this flag (after each commit) and if it finds the value '1' for T1, T1 will
be used for inserts. if the value is '3', it updates it to '1' before it
will use T1.

So, when B is running, A will see a '2' for T1 and will take T2. When the B
completes the status is updated to '3'. A will still use T2. Some time B
will offload T2, so A will start to use T1 again.

All the rows have a timestamp in them. During some checks, the developper
saw a row say in T1 and a row with the same timestamp in the dataset.

Application A and B are reading rows with CS, which means consistent,
commited data.

Well, if the developper is right, the only explaination I can offer, is a
bug in DB2. But that will hard to prove. So I onyl wanted to know, if
anybody else had a similar problem?

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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