Bug (?) in SQL: Global temporary table gets truncated on failed insert.

[fintudet]

Hello.

During some experimentation with DB2 decimal numbers representations, I
have discovered very unexpected behaviour of the global temporary
table. Consider this example.
Such I had executed the example: # db2 -td@ -f FILENAME.

When an single Insert into table fails, the table gets empty. Could
anybody shed some light on this ?

----( code )----------------------
DECLARE GLOBAL TEMPORARY TABLE #r
(
res decimal(31) not null
)
ON COMMIT PRESERVE ROWS
NOT LOGGED
@
insert into session.#r values( 1 ) @
insert into session.#r values( 2 ) @
insert into session.#r values( 3 ) @

select * from session.#r
-- here is expected result: 3 rows with consecutive values : 1,2,3
@
BEGIN ATOMIC
declare m_fact decimal(15) default 1;

FOR X AS
select
m_fact * 9999999999999999999999999999999. +
..0000000000000000000000000000001 as val
from sysibm.sysdummy1
DO
-- next line FIRES THE ERROR: SQL0802N
insert into session.#r values( X.val );
END FOR;
END
@
select * from session.#r
-- HERE the table "session.#r" is empty: 0 rows returned. WHY ?
@

[kanda]

Sorry, I forgot to mention my system:
IBM DB2, Enterprise Server Edition, 8.1.6 @ MS Windows Server 2003 @
Intel x32.

[Serge Rielau]

kanda wrote:

Sorry, I forgot to mention my system:
IBM DB2, Enterprise Server Edition, 8.1.6 @ MS Windows Server 2003 @
Intel x32.

This is working as designed for NOT LOGGED temporary tables.
http://publib.boulder.ibm.com/infoce.../v8//index.jsp
"When a rollback operation terminates a unit of work or a savepoint in
P, and that unit of work or savepoint includes a modification to
SESSION.T, then if NOT LOGGED was specified, the rollback includes the
operation DELETE from SESSION.T, else the changes to T are undone."

If an UPDATE/DELETE or INSERT operation against a temp table fails in
mid flight DB2 has to roll back the SQL statement to comply with the
"Atomic" in ACID.
Since the change was not logged there is no way to comply with
Atomicity. So three choices remain:
1) drop the table (mark the table as unusable as doen for regular tables
when NOT LOGGED operations fail)
2) go to the only well defined state: emtpy
3) leave the table in whatever state it is with an unknown amount of the
changes done.

Option 2) is deemed the most appropriate.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Konstantin Andreev]

Thank you very much for the skilled answer. The question is taken off,
but I eager to make some philosophic comments.

This is working as designed for NOT LOGGED temporary tables.
"When a rollback operation terminates a unit of work or a savepoint in
P, and that unit of work or savepoint includes a modification to
SESSION.T, then if NOT LOGGED was specified, the rollback includes the
operation DELETE from SESSION.T, else the changes to T are undone."
I realize now. IMHO, the biggest trouble with IBM (from the user's
point of view) it makes things much more arduous then they could be. In
this particular case it's DB2 documentation, which could be less
intricate. The other glaring case is the way for exporting data from
DB2 to a text file.

Historically, the most successful attempt of IBM to make the simple
thing hard was "Job Control Language" for the early IBM mainfraims
(like IBM/360). People worked with JCL are wincing at the mention of :(
Since the change was not logged there is no way to comply with
Atomicity. So three choices remain:
1) drop the table (mark the table as unusable as doen for regular tables
when NOT LOGGED operations fail)
2) go to the only well defined state: emtpy
3) leave the table in whatever state it is with an unknown amount of the
changes done.

Option 2) is deemed the most appropriate.

This decision could be contested. You could consider next arguments
against (2), but for (3).

* Rule of Least Surprise: always do the least surprising thing. (This
is also widely known as the Principle of Least Astonishment.) -- Eric
S.Raymond.

The disappearance of the innocent rows is very surprising thing.

* Since it's user responsibility for declaring "NOT LOGGED", don't
deprive him the freedom to recover the error himself, according his
current needs. If DB2 would implement (3) then user could drop table,
delete rows himself, or work further, if applicable.

This is another good principle: "Supply mechanism, not policy."
--
Konstantin Andreev.

[th_maran]

Hi

I had also coded couple of procedures and had not faced any problems .
think over whether "ATOMIC" is required here, eventhough this doesnot affect your code , i belive the "ATOMIC" purpose is not required in this procedural code.

try adding this line to see whether the insert is having any issue:

-- next line FIRES THE ERROR: SQL0802N
insert into session.#r values( X.val );
GET DIAGNOSTICS v_row_count = ROW_COUNT;

this GET DIAGNOSTICS will display the no of rows it had inserted
declare a variable v_row_count as INTEGER;

include this condition also

if v_row_count < 0
then capture this using exceptions
you will know how many data was inserted or what error occured while inserting the data.

thanks
Thirumaran

[th_maran]

Hi

I forgot to mention one more thing , i just refereed my DB2 proc simillar codes.

my table declation is
create procedure (...)
LANGUAGE SQL

Begin
DECLARE v_row_count INTEGER;

DECLARE GLOBAL TEMPORARY TABLE session."#trans_t"(
columns .....
WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

INSERT INTO session."#trans_t"( columns....);
GET DIAGNOSTICS v_row_count = ROW_COUNT;

Select * from session."#trans_t";

RETURN 0;


END
@

i am not using ATOMIC as this does not require .
This works fine for me in
IBM DB2, Enterprise Server Edition, 8.1.6 @ MS Windows Server 2003


thanks
thirumaran

[Serge Rielau]

Konstantin Andreev wrote:

I realize now. IMHO, the biggest trouble with IBM (from the user's
point of view) it makes things much more arduous then they could be. In
this particular case it's DB2 documentation, which could be less
intricate. There is room for improvement in this paragraph, agred.
The other glaring case is the way for exporting data from
DB2 to a text file. You are a tease! Spit it out man, what about it? :-)
Historically, the most successful attempt of IBM to make the simple
thing hard was "Job Control Language" for the early IBM mainfraims
(like IBM/360). People worked with JCL are wincing at the mention of :( Is late birth on my behalf a valid excuse?

Since the change was not logged there is no way to comply with
Atomicity. So three choices remain:
1) drop the table (mark the table as unusable as doen for regular tables
when NOT LOGGED operations fail)
2) go to the only well defined state: emtpy
3) leave the table in whatever state it is with an unknown amount of the
changes done.

Option 2) is deemed the most appropriate.

This decision could be contested. You could consider next arguments
against (2), but for (3).

* Rule of Least Surprise: always do the least surprising thing. (This
is also widely known as the Principle of Least Astonishment.) -- Eric
S.Raymond.

The disappearance of the innocent rows is very surprising thing.
* Since it's user responsibility for declaring "NOT LOGGED", don't
deprive him the freedom to recover the error himself, according his
current needs. If DB2 would implement (3) then user could drop table,
delete rows himself, or work further, if applicable.

This is another good principle: "Supply mechanism, not policy."

Just like people will wear seatbelts and helmet if you just offer them.
This is a phylosophical debate indeed. It's like C vs Java.
Do you do error checks after each SQL statement?
Most(!) developers don't. Look at any procedure migrated from TSQL.
If DB2 deletes the table content on error you are bound to find out.
If it does not and their is not error test the app can silently fail,
wrong bank transactions, reservation, accounting....
For an industrial strength DBMS this is simply not acceptable.

There is a difference between what is consumable by the masses and what
is required for the high end.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Konstantin Andreev]

Serge Rielau wrote:

The other glaring case is the way for exporting data from DB2 to a text file. You are a tease! Spit it out man, what about it? :-)

Hm... I've spent some time, trying to reproduce something about it now,
but failed. Sorry, It seems I was mistaken.

[ ... skip ... ]

2) go to the only well defined state: emtpy
3) leave the table in whatever state it is with an unknown amount of the changes done.
Option 2) is deemed the most appropriate.

You could consider next arguments against (2), but for (3).

* Rule of Least Surprise: always do the least surprising thing. ...
The disappearance of the innocent rows is very surprising thing.
* Supply mechanism, not policy. ... don't deprive the freedom to recover the error himself

Do you do error checks after each SQL statement?
Most(!) developers don't. Look at any procedure migrated from TSQL.
If DB2 deletes the table content on error you are bound to find out.
If it does not and their is not error test the app can silently fail,
wrong bank transactions, reservation, accounting...

Sure, this is very strong argue. The approach that ensures less errors
is preferable. Thank you for cognitive discussion.
--
Konstantin Andreev.