Table definition:
CREATE TABLE "SCHEMA1 "."X2" (
"C1" CHAR(20) NOT NULL ,
"C2" CHAR(10) NOT NULL ,
"C3" CHAR(30) NOT NULL GENERATED ALWAYS AS (C1||
C2) )
IN "USERSPACE1 " ;
-- DDL Statements for primary key on Table "SCHEMA1 "."X2"
ALTER TABLE "SCHEMA1 "."X2"
ADD CONSTRAINT "P1" PRIMARY KEY
("C1",
"C2");
IBM Data Studio Version 1.1.1.
Try edit data , insert row. Will fail.
The editor has problems with the generated columns.
I would say: great.
The generated columns capability breaks the relational model. What is
a column in a base table that is not a column?
It has been discussed already several times in the past, but one usage
of generated columns in DB2 LUW is due to the the lack of (SQL92!)
support of row value constructor in the where clause (and to close the
circle, in the cursor positioning clause as extension to SQL92 but
compatible with it in it's simple format) and corresponding optimizer
support.
Quo usque ...
Bernard Dhooghe
4  1743 
I couldn't understand your issue(might be by my poor English
capability).
Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16
9 record(s) selected.
2) One usage of generated column is to create a functional index. http://groups.google.com/group/comp....6e3ac5739087f7
On May 7, 3:54 pm, Tonkuma <tonk...@fiberb it.netwrote:
I couldn't understand your issue(might be by my poor English
capability).
Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16
9 record(s) selected.
2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...
For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne idée" (an idea
that looks good but isn't)
Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?
Bernard Dhooghe
Bernard Dhooghe wrote:
On May 7, 3:54 pm, Tonkuma <tonk...@fiberb it.netwrote:
>I couldn't understand your issue(might be by my poor English
capability).
Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16
9 record(s) selected.
2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...
For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne idée" (an idea
that looks good but isn't)
Let's not get all excited about this relational part.
The REASON for the avoidance of functionally dependent columns is the
risk of inconsistency. expression-generated columns assert consistency.
Thus there is no problem. So let's not get hung up by the letter of the
law and stick with it's spirit.
This good idea has since been copied by both MS SQL Server and Oracle
(which has expression based indexes, so they must have seen some
goodness in it beyond mere indexing).
I can't help but being proud :-)
Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?
Believe it or not. Actually making some progress towards your pet peeve.
Don't give up hope.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
I don't want to comment negatively on functional indexes or any other
kind of indexes. They can help, but they best stay out of the table
column definition. Even a simple index is just a way to help in search
speed, or validate a uniqueness, helping to alleviate the fact data
processors (computers) do not have infinite speed.
Concerning row-value constructor support, and hope, OK, the discussion
just started (...mid 1998 with the upcoming DB2 V5.2).
Bernard (Dhooghe)
On May 8, 5:37 pm, Serge Rielau <srie...@ca.ibm .comwrote:
Bernard Dhooghe wrote:
On May 7, 3:54 pm, Tonkuma <tonk...@fiberb it.netwrote:
I couldn't understand your issue(might be by my poor English
capability).
Here are some thoughts which are inspired by your article.
1) Although it is not documented, you can specify row comparison
predicate in(on?) DB2 for LUW 9.1.
For example:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ('D11', 16)
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
And you can specify full-select (including VALUES clause) in predicate
on DB2 for LUW prior V9.1.
Here are two examples:
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000170 D11 MASATOSHI J YOSHIMURA 16
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
200170 D11 KIYOSHI YAMAMOTO 16
6 record(s) selected.
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
;
------------------------------------------------------------------------------
EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000060 D11 IRVING F STERN 16
000150 D11 BRUCE ADAMSON 16
000160 D11 ELIZABETH R PIANKA 17
000170 D11 MASATOSHI J YOSHIMURA 16
000180 D11 MARILYN S SCOUTTEN 17
000190 D11 JAMES H WALKER 16
000200 D11 DAVID BROWN 16
000210 D11 WILLIAM T JONES 17
200170 D11 KIYOSHI YAMAMOTO 16
9 record(s) selected.
2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...
For point 2: I know of this, unfortunately, it breaks the relational
model, a base table contains a column that is not a column; an index
(functional or not) should not impact a table structure, generated
columns do, in french they call it "une fausse bonne idée" (an idea
that looks good but isn't)
Let's not get all excited about this relational part.
The REASON for the avoidance of functionally dependent columns is the
risk of inconsistency. expression-generated columns assert consistency.
Thus there is no problem. So let's not get hung up by the letter of the
law and stick with it's spirit.
This good idea has since been copied by both MS SQL Server and Oracle
(which has expression based indexes, so they must have seen some
goodness in it beyond mere indexing).
I can't help but being proud :-)
Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?
Believe it or not. Actually making some progress towards your pet peeve.
Don't give up hope.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Tim Greenwood |
last post by:
How do I get a columns default value into a type dataset in C#?? I see a
method for setting
columns to null but default values must be easier to get to than it seems.
|
by: DN |
last post by:
Is it possible to use Missing.Value in C#, or is that just a VB
concept?
I'm using Persits ASPEmail component in an ASP.NET page, and their
sample code shows something like this:
<%@ Page language="C#" %>
<%@ import namespace=AspEmail %>
<%@ Import Namespace="System.Reflection" %>
<%
|
by: eBob.com |
last post by:
I've got the basics of an XML ini file working. I.E. I can stash away
and retrieve user preferences. (Code below.) But how do I handle a
new preference? Say I have A and B. And then I invent C. The first
time the new version with C is run the XML file will not have a value
for C. How do I detect that case? Somewhere I got the impression
that I could use IsNull, but I was unable to find an example which I
could apply to my code. My...
|
by: Jason |
last post by:
How do I hide a column in a GridView in ASP.NET 2.0 when all of the
columns are autogenerated based on the datasource? I want to hide the
first of three columns, but the following doesn't work:
DataTable dt = new DataTable();
DataColumn dc;
dc = new DataColumn();
dc.ColumnName = "blog_entry_id";
dc.DataType = System.Type.GetType("System.Int32");
dt.Columns.Add(dc);
|
by: Martin Z |
last post by:
I'm getting acquainted with the whole XML/XSD thing, and I've run into
a wall. I have a tree of objects that I deserialize from XML for
configuration reasons. I have generated XSD from the classes for this
tree. I have made an XML file that validates against that XSD.
However, when I try to deserialize from the XML file, I get complaints
that
System.InvalidOperationException was unhandled
Message="There is an error in XML document...
| | |
by: Rahul B |
last post by:
Hi,
I have a table where one of the columns is automatically generated as
a substring of values of some other column.
When i create a copy of that schema using sysproc.admin_copy_schema,
the table goes in pending state, because the data in the table was
inserted fully and the column was not automatically generated(i think
this is the reason).
I tried to get it out of pending state by issuing "SET
|
by: isisqiero |
last post by:
I use a list of names in a Word document as the value of SELECT statement's parameter. However, my datagridview shows only the column names without the data I am hoping to see.
Public Class Form1
Public wdapp = New Microsoft.Office.Interop.Word.Application()
Dim auto As New dbhDataSet.autoDataTable
Public Sub New()
InitializeComponent()
End Sub
|
by: JeremyI |
last post by:
I have just about finished adding a module to my database to import information from an Excel spreadsheet into the appropriate table. It successfully creates some temporary tables, performs DoCmd.TransferSpreadsheet, formats and manipulates the records, does some checks against existing data and then writes to the table.
However, I started testing it with some realistic scenarios and it has broken big-time in one puzzling fashion.
The...
|
by: akselo |
last post by:
I am parsing a csv data file with comma as the delimiter. The file mostly contains integers but also some decimal numbers/floats.
It has some missing values, which are marked by a period ("."). In order to insert into a database, I want to replace the missing value periods, but NOT the decimal periods.
With python's regex flavor and the re.sub construct applied to each line read as a string, can someone help what the pattern might be for...
|
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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
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...
| |
