Hello.
After upgrading a test database to V8.2 with FixPak7a, too see if
the production servers can be safely upgraded, I noticed that inserts
on an insertable view which consists of multiple underlying tables
stopped working. After playing with it some more, the only failure mode
is when you use CURRENT TIMESTAMP or CURRENT DATE to a column which is
used to determine which underlying table to insert rows to. The following
is a minimum procedure to reproduce the problem on V8.2, but works without
a problem on V8.1:
%%%%%
# su - dbtest
$ db2level
DB21085I Instance "dbtest" uses "64" bits and DB2 code release "SQL08021" with
level identifier "03020106".
Informational tokens are "DB2 v8.1.1.80", "s041221", "U800400", and FixPak "8".
Product is installed at "/usr/opt/db2_08_01".
$ db2 -t +p
CREATE DB TEST;
CONNECT TO TEST;
CREATE TABLE foo(x DATE NOT NULL, y INTEGER, CHECK(x < '2005-01-01'));
CREATE TABLE bar(x DATE NOT NULL, y INTEGER, CHECK(x >= '2005-01-01'));
CREATE VIEW baz(x, y)
AS SELECT x, y FROM foo UNION ALL SELECT x, y FROM bar;
INSERT INTO bar(x, y) VALUES (CURRENT DATE, 0) -- OK;
INSERT INTO baz(x, y) VALUES (CURRENT DATE, 0) -- NG;
INSERT INTO baz(x, y) VALUES (CURRENT DATE + 0 DAYS, 0) -- OK;
%%%%%
The second INSERT statement spews the following error 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:
SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "Attempt to create
interval of 0-dimensions".) SQLSTATE=58004
Is anybody else seeing this?
If the column x is of other types than DATE/TIMESTAMP, insert on
this view doesn't fail.
The third INSERT statement could be a workaround for this problem, but
I don't feel like changing every piece of code this way.
I also tried FixPak8, but the problem wasn't fixed. Unfortunately, I don't
seem to recall the password necessary to submit a report for this problem.
Thanks in advance.
