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. 1 1857
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 "parameteri zed table".
Please open a PMR, it's a bug.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: niku |
last post by:
Hello all. I'm trying to get mysql installed on OpenBSD 3.4.
Unfortunately, it appeares that the port only installes the
mysql-client, and one needs to install the -server package seperately.
This would be fine with one exception, there is a dependency that
breaks when installing from ports. This lead me to install from
source, and now I'm having problems just getting mysql to start.
If anyone could look at the error message below, and...
|
by: Robin Tucker |
last post by:
I have some code that dynamically creates a database (name is @FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one of the following fails, the
database "creation" is rolledback. Otherwise, I would try deleting on error
detection, but it could get messy.
IF @Error = 0
BEGIN
SET @ExecString = 'CREATE DATABASE ' + @FullName
EXEC sp_executesql @ExecString
|
by: cainlevy |
last post by:
I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking about .03
seconds, which compared to other queries (large inserts at .01 seconds)
and previous experience appears inordinately long. Further, it appears
that most of that .03 seconds is some kind of overhead, since the
complexity of the create table...
|
by: UnixSlaxer |
last post by:
Hello All,
While doing some TPC benchmark testing on DB2-UDB 8.2, I face the
following problem when running this query:
select *
from lineitem
where
l_shipdate <= date '1998-12-01' - interval '69' day (3)
|
by: jesper.hvid |
last post by:
Hi.
I've noticed, after moving some of our code to 2.0, that
System.Net.WebRequest.Create(System.String) and
System.Uri(System.String) no longer behave as they did in 1.1
framework.
Example:
string emailHttpPath =
| |
by: dave.j.thornton |
last post by:
I'm attempting to create a new table, and populate it using the fields
from two existing tables. The code is printed below. I get the error:
"Run-time error '-2147217900 (80040e14)': Syntax error in CREATE TABLE
statement." For what it's worth, when tested independently, the
"SELECT " part of my CREATE TABLE statement works properly.
Sub test()
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
|
by: vaibhavsumant |
last post by:
<project name="DBCreate" default="usage" basedir=".">
<property name="user" value="db2admin"/>
<property name="passwd" value="db2admin"/>
<property name="dbprefix" value=""/>
<property name="driver" value="COM.ibm.db2.jdbc.app.DB2Driver"/>
<property name="starturl" value="jdbc:db2:temp"/>
<property name="db2dir" location="${basedir}/../../../../.." />
<target name="CreateTestData" >
<echo message="in mydbs db2dir = ${db2dir}" />
|
by: maanasa |
last post by:
hi,
i've one question.
I'm supposed to create oracle user on click of a button using oracle forms.
I'm calling a procedure which i've written in sql+ which is supposed to create the user.
The problem is that i'm able to create user by writing execute immediate statement in the procedure, but i'm not able to grant connect to the user. Hence i'm getting "admin option not granted for role connect." error.
Is it that the admin_option needs to...
|
by: Evan |
last post by:
Hello,
one of my PC is window system, and in "control panel -Network
Connections", I can see some network connections such as PPPOE or VPN
which I created by click "create a new connection".
My question is, is it possible to create a new connection by using
Python script? which means I do not want to use Window UI (via
"control panel"), if it is possible, I can save so many time to
create various network connection when I want to do...
|
by: viktorijakup |
last post by:
Hi !!!
@rem = '--*-Perl-*--
@echo off
if "%OS%" == "Windows_NT" goto WinNT
perl -x -S "%0" %1 %2 %3 %4 %5 %6 %7 %8 %9
goto endofperl
:WinNT
perl -x -S %0 %*
if NOT "%COMSPEC%" == "%SystemRoot%\system32\cmd.exe" goto endofperl
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |