Performance problem ? on a positionned cursor

[Jean-Marc Blaise]

Dear all,

To DELETE rows in a EEE context (AIX 5.1 / V7.2 FP11 or V8.1 FP5), I have
used a CLI program and a shell that runs on each partition. What I am
surprised is about the slowness of this CLI program. Basically, to delete
about 7 million rows, it's going to take 7 times a single DELETE FROM TABLE
statement.

Do you have any advise or is this a normal behaviour ?

The table is in LOCKSIZE exclusive, and not on the catalog partition. The
snashot shows each "thread" on partition as an X table LOCK and is
executing.

Thanks for your help,

Jean-Marc

[Mark A]

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cb**********@news-reader5.wanadoo.fr...

Dear all,

To DELETE rows in a EEE context (AIX 5.1 / V7.2 FP11 or V8.1 FP5), I have
used a CLI program and a shell that runs on each partition. What I am
surprised is about the slowness of this CLI program. Basically, to delete
about 7 million rows, it's going to take 7 times a single DELETE FROM TABLE statement.

Do you have any advise or is this a normal behaviour ?

The table is in LOCKSIZE exclusive, and not on the catalog partition. The
snashot shows each "thread" on partition as an X table LOCK and is
executing.

Thanks for your help,

Jean-Marc

What kind of indexes are there on the table?

[Serge Rielau]

Jean-Marc,

I'm not really surprised. Your app is a bottleneck and you play
ping-pong between the the client, the coordinator and the database
partition. So you'r looking at, ~ 7M * 4 communications.
By contrast the searched delete has a total of 2 (client coordinator) +
2 * #partitions presuming a simple searched DELETE.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jean-Marc Blaise]

Hi Serge,

No, no, on each 4 partition I am doing the same stuff. A script switches to
each node, and launches in background a CLI script that opens a positionned
cursor with SELECT 1 FROM TABLE WHERE NODENUM(col) = CURRENT NODE AND
COLNAME=<VALUE>, and does the DELETE WHERE CURRENT OF CURSOR.

When I first tried from a single cli program launched without the
NODENUM(col) ... clause , I remember that I was effectively doing ping-pong
as you say between the coordinator and other partitions.

I monitored that there is no exchange between nodes, that a TABLE lock is
taken on each partition except the catalog partition that does not support
the TABLE and that each process is effectively on a different partition,
status "Executing".

I had a look to the CLI reference guide, to try delete from a positionned
cursor an array ... I have no clue why it takes 1 minute with a single
DELETE statement and about 10 minutes with my CLI programs in // (I commit
every 50000 lines).

Regards,

JM

"Serge Rielau" <sr*****@ca.eye-be-em.com> a écrit dans le message de
news:cb**********@hanover.torolab.ibm.com...

Jean-Marc,

I'm not really surprised. Your app is a bottleneck and you play
ping-pong between the the client, the coordinator and the database
partition. So you'r looking at, ~ 7M * 4 communications.
By contrast the searched delete has a total of 2 (client coordinator) +
2 * #partitions presuming a simple searched DELETE.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jean-Marc Blaise]

"Mark A" <no****@switchboard.net> a écrit dans le message de
news:x_*************@news.uswest.net...

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:cb**********@news-reader5.wanadoo.fr...

Dear all,

To DELETE rows in a EEE context (AIX 5.1 / V7.2 FP11 or V8.1 FP5), I have used a CLI program and a shell that runs on each partition. What I am
surprised is about the slowness of this CLI program. Basically, to delete about 7 million rows, it's going to take 7 times a single DELETE FROM

TABLE

statement.

Do you have any advise or is this a normal behaviour ?

The table is in LOCKSIZE exclusive, and not on the catalog partition. The snashot shows each "thread" on partition as an X table LOCK and is
executing.

Thanks for your help,

Jean-Marc

What kind of indexes are there on the table?

There are 3 indexes on the table.

JM

[Serge Rielau]

Well you're still playing ping-pong. The cursor can't be blocked because
it's for delete. So every fetch is a trip to the server and so is every
delete.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Jean-Marc Blaise]

I see ! Thanks a lot for putting me back in the right track ...

JM

"Serge Rielau" <sr*****@ca.eye-be-em.com> a écrit dans le message de
news:cb**********@hanover.torolab.ibm.com...

Well you're still playing ping-pong. The cursor can't be blocked because
it's for delete. So every fetch is a trip to the server and so is every
delete.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PM \(pm3iinc-nospam\) CGO]

You may also check your logging settings. (mincommit and so on...)

PM

"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:cb**********@news-reader4.wanadoo.fr...

I see ! Thanks a lot for putting me back in the right track ...

JM

"Serge Rielau" <sr*****@ca.eye-be-em.com> a écrit dans le message de
news:cb**********@hanover.torolab.ibm.com...

Well you're still playing ping-pong. The cursor can't be blocked because
it's for delete. So every fetch is a trip to the server and so is every
delete.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab