INSERT data-change operation data retrieval

[Almund Sebi]

Hello

I use DB2 UDB V8.1.5. Using embedded SQL (SQLJava), I insert a row
into a table which has some foreign keys and check constraints. The
insert works fine, but if I encapsulate the insert into a SELECT INTO
.... FROM FINAL TABLE and then try to _precompile_ it, I get the
following:

SQL0545N (invalidates check constraint). If I try to put DEFAULT for
the column which the ck_constraint applies on, i get SQL0530N (invalid
foreign key) on another column.

I'd like to use the INSERT statement nested in a SELECT statement to
retrieve the generated primary key.

The failing statement looks like this:

SELECT id
INTO :id
FROM FINAL TABLE (

INSERT INTO mytable(id, val)
VALUES (DEFAULT, :inputhostvar)

)

Thanks for your advice.

[Serge Rielau]

Can't say much without knowing more of the schema (triggers RI, check
constraints).
Try using NEW TABLE instead of FINAL TABLE.
FINAL TABLE is more restrictive than NEW TABLE sinc eit will not accept
anything that could possible change the table after the initial insert.

If you can come up with a repro script I'll gladly take a closer look.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Almund Sebi]

> Try using NEW TABLE instead of FINAL TABLE.

FINAL TABLE is more restrictive than NEW TABLE sinc eit will not accept
anything that could possible change the table after the initial insert.
I've already tried that but no success. I'v noticed a very strange
thing though: The precompile fails, because it actually inserts a row
into the table! It uses the default values (mostly 0) for the columns,
and that's also the reason why it failed the precompile when i had RI
enabled (the 0 doesn't occur in the parent table). So why does the
SELECT x INTO :x FROM FINAL TABLE ...

construct insert a row? Somewhat scary.

[Serge Rielau]

Hmm, I have a very hard time imagining how this would happen, but
nothing is impossible.
It would mean that DB2 half way through compiling the statement decides
to switch into EXECUTE IMMEDIATE mode.
Just for sanities sake: You state you get a -545. I presume you
validated that the constraint specified is indeed related to the target
table. (and not some other object, which soemhow is not happy during
precompilation).
I think the degree of spookyness here is beyond what is likley to be
solved in this newsgroup. You may want to consider calling support.
Have a db2trc handy. That'll be the telltale for what's happening :-)

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