By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,586 Members | 2,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,586 IT Pros & Developers. It's quick & easy.

very strange ... SQL0910

P: n/a
Hi Folks,

I have a SQL proc that does the following below (this is a MDC rollout; I
drop indexes because YEAR_NUM which is a dimension, is part of the unique
index, and the optimizer decides to delete based on the Unique index for a
800 timeron difference out of 33 millions)

/* Prune Table PPSSYS.PS_FACT (month - 14) */
DROP INDEX PPSSYS.PSB_FACT;
DROP INDEX PPSSYS.PS_FACT;
DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);

The problem I have is SQL0910 on the DELETE (under the debugger).

PPSSYS.PS_ROLLOUT - Debug started.
PS_ROLLOUT - Exception occurred while debugging:
Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
Driver][DB2/6000] SQL0910N
The SQL statement cannot access an object on which a modification is
pending. SQLSTATE=57007

PS_ROLLOUT - Roll back completed successfully.
PPSSYS.PS_ROLLOUT - Debug failed.

I don't understand why on this code particularly; I have about 4 other
packets on other tables like this before and it works. And it fails only on
this part ...

Do you have an advice ? It should word, shouldn't it ? This table has 35
columns, others have less (about 2- to 30) but I don't see any reason that
could explain this behavior ?

Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with
50Mb logging against 3 Gb or more log, and more that 1 hour in normal stuff.
People, you should try it, and apply FP9 :-)

Thanks for your help,

Jean-Marc
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:42***********************@news.wanadoo.fr...
Hi Folks,

I have a SQL proc that does the following below (this is a MDC rollout; I
drop indexes because YEAR_NUM which is a dimension, is part of the unique
index, and the optimizer decides to delete based on the Unique index for a
800 timeron difference out of 33 millions)

/* Prune Table PPSSYS.PS_FACT (month - 14) */
DROP INDEX PPSSYS.PSB_FACT;
DROP INDEX PPSSYS.PS_FACT;
DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);

The problem I have is SQL0910 on the DELETE (under the debugger).

PPSSYS.PS_ROLLOUT - Debug started.
PS_ROLLOUT - Exception occurred while debugging:
Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
Driver][DB2/6000] SQL0910N
The SQL statement cannot access an object on which a modification is
pending. SQLSTATE=57007

PS_ROLLOUT - Roll back completed successfully.
PPSSYS.PS_ROLLOUT - Debug failed.

I don't understand why on this code particularly; I have about 4 other
packets on other tables like this before and it works. And it fails only on this part ...

Do you have an advice ? It should word, shouldn't it ? This table has 35
columns, others have less (about 2- to 30) but I don't see any reason that
could explain this behavior ?

Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with
50Mb logging against 3 Gb or more log, and more that 1 hour in normal stuff. People, you should try it, and apply FP9 :-)

Thanks for your help,

Jean-Marc

Hi Serge,

Don't you have your idea on this one ?

Cheers,

Jean-Marc
Nov 12 '05 #2

P: n/a
Jean-Marc Blaise wrote:
"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:42***********************@news.wanadoo.fr...
Hi Folks,

I have a SQL proc that does the following below (this is a MDC rollout; I
drop indexes because YEAR_NUM which is a dimension, is part of the unique
index, and the optimizer decides to delete based on the Unique index for a
800 timeron difference out of 33 millions)

/* Prune Table PPSSYS.PS_FACT (month - 14) */
DROP INDEX PPSSYS.PSB_FACT;
DROP INDEX PPSSYS.PS_FACT;
DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);

The problem I have is SQL0910 on the DELETE (under the debugger).

PPSSYS.PS_ROLLOUT - Debug started.
PS_ROLLOUT - Exception occurred while debugging:
Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
Driver][DB2/6000] SQL0910N
The SQL statement cannot access an object on which a modification is
pending. SQLSTATE=57007

PS_ROLLOUT - Roll back completed successfully.
PPSSYS.PS_ROLLOUT - Debug failed.

I don't understand why on this code particularly; I have about 4 other
packets on other tables like this before and it works. And it fails only


on
this part ...

Do you have an advice ? It should word, shouldn't it ? This table has 35
columns, others have less (about 2- to 30) but I don't see any reason that
could explain this behavior ?

Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with
50Mb logging against 3 Gb or more log, and more that 1 hour in normal


stuff.
People, you should try it, and apply FP9 :-)

Thanks for your help,

Jean-Marc


Hi Serge,

Don't you have your idea on this one ?

Nope.. No clue. This is low level stuff.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:3j************@individual.net...
Jean-Marc Blaise wrote:
"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:42***********************@news.wanadoo.fr...
Hi Folks,

I have a SQL proc that does the following below (this is a MDC rollout; Idrop indexes because YEAR_NUM which is a dimension, is part of the uniqueindex, and the optimizer decides to delete based on the Unique index for a800 timeron difference out of 33 millions)

/* Prune Table PPSSYS.PS_FACT (month - 14) */
DROP INDEX PPSSYS.PSB_FACT;
DROP INDEX PPSSYS.PS_FACT;
DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);

The problem I have is SQL0910 on the DELETE (under the debugger).

PPSSYS.PS_ROLLOUT - Debug started.
PS_ROLLOUT - Exception occurred while debugging:
Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
Driver][DB2/6000] SQL0910N
The SQL statement cannot access an object on which a modification is
pending. SQLSTATE=57007

PS_ROLLOUT - Roll back completed successfully.
PPSSYS.PS_ROLLOUT - Debug failed.

I don't understand why on this code particularly; I have about 4 other
packets on other tables like this before and it works. And it fails only


on
this part ...

Do you have an advice ? It should word, shouldn't it ? This table has 35
columns, others have less (about 2- to 30) but I don't see any reason thatcould explain this behavior ?

Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with
50Mb logging against 3 Gb or more log, and more that 1 hour in normal


stuff.
People, you should try it, and apply FP9 :-)

Thanks for your help,

Jean-Marc


Hi Serge,

Don't you have your idea on this one ?

Nope.. No clue. This is low level stuff.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Hi Serge,

I add one element here: creating the SQL proc and binding with EXPLSNAP
shows that the DELETE plan is using one RID index that I am dropping before.
All other code before have plans using the BID index.

What I would have supposed is that an implicit rebind of the package occured
at the DELETE step instead of hitting a SQL0910N (of course, if I use an
EXECUTE IMMEDIATE, I can bypass this problem). Is that the right behavior ?

Thanks for your help,

Jean-Marc

Nov 12 '05 #4

P: n/a
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:3j************@individual.net...
Jean-Marc Blaise wrote:
"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:42***********************@news.wanadoo.fr...
Hi Folks,

I have a SQL proc that does the following below (this is a MDC rollout; Idrop indexes because YEAR_NUM which is a dimension, is part of the uniqueindex, and the optimizer decides to delete based on the Unique index for a800 timeron difference out of 33 millions)

/* Prune Table PPSSYS.PS_FACT (month - 14) */
DROP INDEX PPSSYS.PSB_FACT;
DROP INDEX PPSSYS.PS_FACT;
DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);

The problem I have is SQL0910 on the DELETE (under the debugger).

PPSSYS.PS_ROLLOUT - Debug started.
PS_ROLLOUT - Exception occurred while debugging:
Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
Driver][DB2/6000] SQL0910N
The SQL statement cannot access an object on which a modification is
pending. SQLSTATE=57007

PS_ROLLOUT - Roll back completed successfully.
PPSSYS.PS_ROLLOUT - Debug failed.

I don't understand why on this code particularly; I have about 4 other
packets on other tables like this before and it works. And it fails only


on
this part ...

Do you have an advice ? It should word, shouldn't it ? This table has 35
columns, others have less (about 2- to 30) but I don't see any reason thatcould explain this behavior ?

Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with
50Mb logging against 3 Gb or more log, and more that 1 hour in normal


stuff.
People, you should try it, and apply FP9 :-)

Thanks for your help,

Jean-Marc


Hi Serge,

Don't you have your idea on this one ?

Nope.. No clue. This is low level stuff.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Hi,

From my understanding, SQL0910 is a protection introduced in FP9 for
objects: cannot issue DML after DDL modification in the same transaction....

The little trouble is that:
DROP INDEX ...
DROP INDEX....
DELETE ... ==> raises SQL0910, and leaves you with the 2 indexes dropped,
despite of the rollback ...

Regards,

JM
Nov 12 '05 #5

P: n/a
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:3j************@individual.net...
Jean-Marc Blaise wrote:
"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:42***********************@news.wanadoo.fr. ..
Hi Folks,

I have a SQL proc that does the following below (this is a MDC rollout;
I
drop indexes because YEAR_NUM which is a dimension, is part of the
unique
index, and the optimizer decides to delete based on the Unique index for
a
800 timeron difference out of 33 millions)

/* Prune Table PPSSYS.PS_FACT (month - 14) */
DROP INDEX PPSSYS.PSB_FACT;
DROP INDEX PPSSYS.PS_FACT;
DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);

The problem I have is SQL0910 on the DELETE (under the debugger).

PPSSYS.PS_ROLLOUT - Debug started.
PS_ROLLOUT - Exception occurred while debugging:
Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
Driver][DB2/6000] SQL0910N
The SQL statement cannot access an object on which a modification is
pending. SQLSTATE=57007

PS_ROLLOUT - Roll back completed successfully.
PPSSYS.PS_ROLLOUT - Debug failed.

I don't understand why on this code particularly; I have about 4 other
packets on other tables like this before and it works. And it fails only

on
this part ...

Do you have an advice ? It should word, shouldn't it ? This table has 35
columns, others have less (about 2- to 30) but I don't see any reason
that
could explain this behavior ?

Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with
50Mb logging against 3 Gb or more log, and more that 1 hour in normal

stuff.
People, you should try it, and apply FP9 :-)

Thanks for your help,

Jean-Marc
Hi Serge,

Don't you have your idea on this one ?


Nope.. No clue. This is low level stuff.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi,

From my understanding, SQL0910 is a protection introduced in FP9 for
objects: cannot issue DML after DDL modification in the same transaction....

The little trouble is that:
DROP INDEX ...
DROP INDEX....
DELETE ... ==> raises SQL0910, and leaves you with the 2 indexes dropped,
despite of the rollback ...

Which rollback? Statement failure does not imply transaction rollback.
It merely implies statement rollback.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3l*************@individual.net...
Jean-Marc Blaise wrote:
"Serge Rielau" <sr*****@ca.ibm.com> a écrit dans le message de
news:3j************@individual.net...
Jean-Marc Blaise wrote:

"Jean-Marc Blaise" <no****@nowhere.com> a écrit dans le message de
news:42***********************@news.wanadoo.fr. ..
>Hi Folks,
>
>I have a SQL proc that does the following below (this is a MDC rollout;

I
>drop indexes because YEAR_NUM which is a dimension, is part of the


unique
>index, and the optimizer decides to delete based on the Unique index
for
a
>800 timeron difference out of 33 millions)
>
>/* Prune Table PPSSYS.PS_FACT (month - 14) */
>DROP INDEX PPSSYS.PSB_FACT;
>DROP INDEX PPSSYS.PS_FACT;
>DELETE FROM PPSSYS.PS_FACT WHERE YEAR_NUM=v_YEAR_NUM14M;
>CREATE INDEX PPSSYS.PSB_FACT ON PPSSYS.PS_FACT (CUST_ID);
>CREATE UNIQUE INDEX PPSSYS.PS_FACT ON PPSSYS.PS_FACT
>(POSITION_ID,BU_ID,CUST_ID,YEAR_NUM);
>
>The problem I have is SQL0910 on the DELETE (under the debugger).
>
>PPSSYS.PS_ROLLOUT - Debug started.
>PS_ROLLOUT - Exception occurred while debugging:
>Une erreur de gestionnaire de base de données s'est produite.[IBM][CLI
>Driver][DB2/6000] SQL0910N
>The SQL statement cannot access an object on which a modification is
>pending. SQLSTATE=57007
>
>PS_ROLLOUT - Roll back completed successfully.
>PPSSYS.PS_ROLLOUT - Debug failed.
>
>I don't understand why on this code particularly; I have about 4 other
>packets on other tables like this before and it works. And it fails
only
on
>this part ...
>
>Do you have an advice ? It should word, shouldn't it ? This table has 35>columns, others have less (about 2- to 30) but I don't see any reason


that
>could explain this behavior ?
>
>Bw, DB2_MDC_ROLLOUT=YES (8.2.2 FP9) is great. 2 minutes for Deletes with>50Mb logging against 3 Gb or more log, and more that 1 hour in normal

stuff.
>People, you should try it, and apply FP9 :-)
>
>Thanks for your help,
>
>Jean-Marc
>

Hi Serge,

Don't you have your idea on this one ?

Nope.. No clue. This is low level stuff.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi,

From my understanding, SQL0910 is a protection introduced in FP9 for
objects: cannot issue DML after DDL modification in the same

transaction....
The little trouble is that:
DROP INDEX ...
DROP INDEX....
DELETE ... ==> raises SQL0910, and leaves you with the 2 indexes dropped, despite of the rollback ...

Which rollback? Statement failure does not imply transaction rollback.
It merely implies statement rollback.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab


Hi Serge,

Humm ... the default exit handler takes me out from the proc but I need to
rollback, that I have forgotten.

Thanks a lot for your point,

Best regards,

Jean-Marc
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.