Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / February 2005

Tip: Looking for answers? Try searching our database.

V8.2: "Attempt to create interval of 0-dimensions"

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
YONETANI Tomokazu - 09 Feb 2005 09:47 GMT
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.
Serge Rielau - 09 Feb 2005 11:59 GMT
Hi,

I can reproduce.
The area at fault is the theorem prover which attempts to prove that the
 tables are disjoint. If that is true the optimizer can collapse the
plan into what development calls a "parameterized table".
Please open a PMR, it's a bug.

Cheers
Serge
Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.