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
6  4263 
"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
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
"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
"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
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
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: leonecla |
last post by:
Hi everybody,
I'm facing a very very strange problem with a very very simple C
program...
My goal should be to write to a binary file some numbers (integers),
each one represented as a sequence...
|
by: TB |
last post by:
I am seeing a very strange problem as follows...
I have a loop where a fair amount of processing is going on and near
the top of the loop I access a class that has only static helper
functions...
|
by: cody |
last post by:
I have a very funny/strange effect here.
if I let the delegate do "return
prop.GetGetMethod().Invoke(info.AudioHeader, null);"
then I get wrong results, that is, a wrong method is called and I...
|
by: Shapper |
last post by:
Hello,
I have this code:
Dim cultureList(,) As String = {{"E", "en-GB"}, {"P", "pt-PT"}}
Select Case Session("culture")
Case "pt-PT"
...
Dim cultureList(,) As String =...
|
by: John |
last post by:
I can't get my head around this!
I have the following code:
<%
.... Code for connection to the database ...
.... Code for retrieving recordset ...
If Not rs.EOF Then
... Do something...
|
by: peter |
last post by:
Hi,
I have very strange situation but first description ;)
I have:
1) project in VB.NET, in this f.e. 1 function:
Public Function Login(ByVal UserName As String, ByVal UserPassword As...
|
by: VijaKhara |
last post by:
Hi all,
I just write a very simple codes in C and vthere is a very strange bug
which I cannot figure out why.
The first loop is for v, and the second for k. There is no
relationship between v...
|
by: Gotch |
last post by:
Hi,
I'm getting a very strange behaviour while running a project I've
done.... Let's expose it: I've two projects. Both of them use a Form
to do some Gui stuff. Other threads pack up messages...
|
by: Prisoner at War |
last post by:
Friends, your opinions and advice, please:
I have a very simple JavaScript image-swap which works on my end but
when uploaded to my host at http://buildit.sitesell.com/sunnyside.html
does not...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
