Multi-level Stored Procs and Savepoints

[Twan Kennis]

The following situation turns into an error:

SP1 {
Savepoint
Create record in table 1

Call SP2 {
Create sub-records in table 2 referring to table 1
}

Commit / Rollback to savepoint
}
Now, beacuse of a referring index between table 2 and table 1, an error
occurred, because the record in table 1 does not exist when the
sub-records are created in table 2.

Do I something wrong, or is this a standard situation on DB2/OS400?

Twan Kennis
SKB Vragenlijst Services, automatisering
Amsterdam, The Nertherlands

[Twan Kennis]

I solved the problem myself. :-D
What went wrong...

In SP2 I declared the following exception handler

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET pRETURN = SQLCODE;
ROLLBACK TO SAVEPOINT SAV1;
END;

(in which pReturn is an Output parameter)

Because of the SQLWARNING-option, the Stored Procedure failed.
When the exit handler only catches SQL-exceptions, everything goes well:

DECLARE EXIT HANDLER FOR SQLEXCEPTION

Twan Kennis
SKB Vragenlijst Services
Amsterdam, The Netherlands
================================================== =====================
"Twan Kennis" <co******@skb.nl> wrote in message
news:11*************@corp.supernews.com...

The following situation turns into an error:

SP1 {
Savepoint
Create record in table 1

Call SP2 {
Create sub-records in table 2 referring to table 1
}

Commit / Rollback to savepoint
}
Now, beacuse of a referring index between table 2 and table 1, an error occurred, because the record in table 1 does not exist when the
sub-records are created in table 2.

Do I something wrong, or is this a standard situation on DB2/OS400?

Twan Kennis
SKB Vragenlijst Services, automatisering
Amsterdam, The Nertherlands