Dummy Table for identity column

Walter Janißen

Dummy Table for identity column
Hi list

I'm just testing the performance implications of using a dummy table with
one column defined as INTEGER GENERATED ALWAYS AS IDENTITY. The table was
created in a segmented tablespace with LOCKSIZE ANY SEGSIZE 4 MAXROWS 1.
Furthermore I created a unique index on the one column (ASC).

A test program inserts a row, gets the newly inserted value and deletes the
row, repeats this sequence 100,000 times. Every 100 inserts the program
commits. The same program was doing the same concurrently on another member
of the data sharing group.

The plan being used is bound with ISO(CS) and RELEASE(DEALLOCATE).

Now I am experiencing the follwoing:

1. A lot of exclusive global wait locks: TYPE PPAG (Omegamon language)
for the tablespace
2. The tablespace is growing: allocated with 1 track, now already using
60 tracks even the tablespace is always empty
Indexspace is only growing smoothly (I think because the numbers are not
always in ascending order)
3. A high number of getpages about three times the number of inserts and
deletes together.

Can anybody explain that to me and does anybody know, how DB2 checks, if
the page contains a row or not (MAXROWS 1)? Would it be better to commit
after every insert?

BTW: Without an index the delete always deadlocks.
(We talked about that a short time ago in the list)



[login to unmask email]

Re: Dummy Table for identity column
(in response to Walter Janißen)
hi walter

i am not sure about it but my guess regarding your questions is:

2. tablespace growing: combination of maxrows 1, commit frequency of
100 and parallel execution in 2 members in a sysplex. maybe the growing
will be less if you commit after each unit of work (one insert, read and
delete).

3. getpages: is this count on the batch-job level or for the tablespace
only. if it's on the job level it's quite accurate to me because your are
inserting, reading and deleting from a table with an index and there are
some getpages on the catalog as well.

i guess db2 will use the spacemap pages to check if a page already contains
a row or not.

regards
markus



James Campbell

Re: Dummy Table for identity column
(in response to markus.schaub@SYSTOR.COM)
Walter

1) "[MAXROWS] is considered for INSERT, LOAD, and REORG."
(SQL Reference) So it's not relevent here

2) Did you use a CACHE size other than the default (20)?

3) Process 1 inserts a row, with key value, say 1. The index has a
pointer that says page 2 (page 0 is TS header, page 1 is
spacemap) contains this key.

Process 2 inserts a row with key value 21. DB2 tries to insert it on
page 2, but if ********Process 1 is the actually still doing the insert,
Page 2 is claimed (not locked), so DB2 will insert it on page 3.
(There is some fancy term for this mechanism, but I forget it) This
requires several getpages to find the actual page to be used.
******** High index value is now 21 pointing to page 3. (And even
if it wasn't claimed exactly then, if claim-wait happens at any time
then a new page will be acquired)

Process 1 will now deletes its row, and inserts key value 2.
Because of the the index, I believe DB2 will try to insert it on page
2, as it will future values up to 20. Process 2 will continue to use
page 3 up to value 40.

The same thing described above will happen when process 1 and 2
move on to insert their next set of values - with keys 41 and 61.
Depending on timings it is likely that either:
- process 2 will use page 3 for values 41-60, and process 1 will use
page 4 for values 61-80; or
- process 1 will use page 4 for values 41-60, and process 2 will use
page 5 for values 61-80.

Increasing the CACHE will therefore reduce the frequency of this
"rollover".

So whenever a process tries to insert a row while the other _is
actually working on the target page_ another page is acquired for
the insert.

This is a sketched outline of what I think happens - explains new
pages being acquired and excessive getpages. But to be really
sure you would have to run some detailed traces.


James Campbell



On 4 Jan 2002 at 9:17, Walter Janissen wrote:

> Hi list
>
> I'm just testing the performance implications of using a dummy table with
> one column defined as INTEGER GENERATED ALWAYS AS IDENTITY. The table was
> created in a segmented tablespace with LOCKSIZE ANY SEGSIZE 4 MAXROWS 1.
> Furthermore I created a unique index on the one column (ASC).
>
> A test program inserts a row, gets the newly inserted value and deletes the
> row, repeats this sequence 100,000 times. Every 100 inserts the program
> commits. The same program was doing the same concurrently on another member
> of the data sharing group.
>
> The plan being used is bound with ISO(CS) and RELEASE(DEALLOCATE).
>
> Now I am experiencing the follwoing:
>
> 1. A lot of exclusive global wait locks: TYPE PPAG (Omegamon language)
> for the tablespace
> 2. The tablespace is growing: allocated with 1 track, now already using
> 60 tracks even the tablespace is always empty
> Indexspace is only growing smoothly (I think because the numbers are not
> always in ascending order)
> 3. A high number of getpages about three times the number of inserts and
> deletes together.
>
> Can anybody explain that to me and does anybody know, how DB2 checks, if
> the page contains a row or not (MAXROWS 1)? Would it be better to commit
> after every insert?
>
> BTW: Without an index the delete always deadlocks.
> (We talked about that a short time ago in the list)
>
>
>