How I can delete rows in one table with foreign keys

[Ivan]

Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTION.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdate()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.
Greetings

Iván

[jefftyzzer]

Hi, Ivan:

OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?

--Jeff

Ivan wrote:

Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTION.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdate()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.
Greetings

Iván

[Ivan]

Excuse me, to that you talk about with autocommit setting ?
jefftyzzer wrote:

Hi, Ivan:

OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?

--Jeff

Ivan wrote:

Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTION.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdate()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.


Greetings

Iván

[jefftyzzer]

Right. My reasoning is that under the explanation for the error you're
getting, it says (in part):

"The application program attempted to access an object within the
same unit of work in which either:
<SNIP>
The application program issued a statement against the object
that added or dropped a constraint."
<SNIP>

And under the user response section, it says (in part):

"Modify the application program so there is no attempt to access
an object within the same unit of work in which the modification
was made."

So, the issue surrounds units of work. If you've got autocommit turned
off (in which case you need to issue explicit COMMITs after DDL and DML
statements), then it sounds like you need to add a COMMIT after you do
the ALTER.

--Jeff

Ivan wrote:

Excuse me, to that you talk about with autocommit setting ?
jefftyzzer wrote:

Hi, Ivan:

OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?

--Jeff

Ivan wrote:

Hello to all and thanks for answer to my topics.
>
>
I made one stored procedure that delete one table, but when
call/execute the procedure this show
>
SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTION.FK_SCLI " restricts the deletion.
>
Then in the procedure it adds one delete of the foreign keys. This it
is the procedure
>
CREATE procedure eis.DeleteUpdate()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);
>
SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
>
>
FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO
>
DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;
>
DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;
>
>
END FOR;
>
SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end
>
>
But when call this stored procedure show the following message
>
SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.
>
What I really need with the procedure is that it allows me to delete
registries or rows of the table.


Greetings

Iván

[Knut Stolze]

Ivan wrote:

Hello to all and thanks for answer to my topics.
I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTION.FK_SCLI " restricts the deletion.

Any particular reason why you are not using ON DELETE CASCADE for the
foreign key definition?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Ivan]

I put commit as follow

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
COMMIT;

But show this message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20112N A SAVEPOINT cannot be set because a SAVEPOINT already exists
and
nested SAVEPOINTS are not supported. LINE NUMBER=9. SQLSTATE=3B002

If I use SAVEPOINTS does not leave me either

Greetings

Iván

jefftyzzer wrote:

Right. My reasoning is that under the explanation for the error you're
getting, it says (in part):

"The application program attempted to access an object within the
same unit of work in which either:
<SNIP>
The application program issued a statement against the object
that added or dropped a constraint."
<SNIP>

And under the user response section, it says (in part):

"Modify the application program so there is no attempt to access
an object within the same unit of work in which the modification
was made."

So, the issue surrounds units of work. If you've got autocommit turned
off (in which case you need to issue explicit COMMITs after DDL and DML
statements), then it sounds like you need to add a COMMIT after you do
the ALTER.

--Jeff

Ivan wrote:

Excuse me, to that you talk about with autocommit setting ?
jefftyzzer wrote:

Hi, Ivan:
>
OK--just dealing with the error you've reported (as opposed to talking
about your design/approach), it looks like you're dealing with the
second bullet-item under the help description for this error code
(execute "? SQL-0910N" in a command editor and you'll see what I mean).
Can you tell us what your autocommit setting is when executing the SP?
>
--Jeff
>
Ivan wrote:
Hello to all and thanks for answer to my topics.


I made one stored procedure that delete one table, but when
call/execute the procedure this show

SQL0532N A parent row cannot be deleted because the relationship
"TXN_TRANSACTION.FK_SCLI " restricts the deletion.

Then in the procedure it adds one delete of the foreign keys. This it
is the procedure

CREATE procedure eis.DeleteUpdate()
begin atomic
declare txtdrop varchar(1000);
declare txtadd varchar(1000);

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;


FOR V1 AS
SELECT
B.CLIENTID AS IDCLIENTID,
B.MAPPEDCLIENTID AS MAPPED
FROM EIS.CLI_MAPING B
WHERE
STATUSID = 3 AND
CLIENTID != MAPPEDCLIENTID
DO

DELETE FROM EIS.CLI_BANK
WHERE CLIENTID = IDCLIENTID;

DELETE FROM EIS.CLI_CLIENT
WHERE CLIENTID = IDCLIENTID;


END FOR;

SET txtadd = 'ALTER TABLE EIS.CLI_CLIENT
ADD CONSTRAINT SQL051102111045710 PRIMARY KEY (CLIENTID)';
EXECUTE IMMEDIATE txtadd;
end


But when call this stored procedure show the following message

SQL0910N The SQL statement cannot access an object on which a
modification is
pending. SQLSTATE=57007
SQL0910N The SQL statement cannot access an object on which a
modification is pending.

What I really need with the procedure is that it allows me to delete
registries or rows of the table.


Greetings

Iván

[Serge Rielau]

Ivan wrote:

I put commit as follow

SET txtdrop = 'ALTER TABLE EIS.CLI_CLIENT DROP CONSTRAINT
SQL051102111045710';
EXECUTE IMMEDIATE txtdrop;
COMMIT;

But show this message

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20112N A SAVEPOINT cannot be set because a SAVEPOINT already exists
and
nested SAVEPOINTS are not supported. LINE NUMBER=9. SQLSTATE=3B002

If I use SAVEPOINTS does not leave me either

That's because you stated BEGIN ATOMIC.You can't COMMIT inside of an
ATOMIC BLOCK. Conflicting request.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/