DB2 UDB LUW: (c1,c2) = ('10000','01/16/2004'): OK ( c1,c2) >= ('10000','01/16/2004'): NOK

[Bernard Dhooghe]

Environment: DB2 UDB LUW (8.1.1.64)

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

*************** ***** PACKAGE *************** *************** *********

Package Name = "BDH002"."DYNEX PLN" Version = ""

Prep Date = 2004/08/31
Prep Time = 09:59:10

Bind Timestamp = 2004-08-31-09.59.10.620080

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"BDH002"

-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
select *
from bds001.ledger
where (account, dateins)=('6000 10100200', '06/25/2001')
fetch first 10 rows only
Section Code Page = 819

Estimated Cost = 100.130157
Estimated Cardinality = 1.000000

Access Table Name = BDS001.LEDGER ID = 4,24
| Index Scan: Name = BDH002.MY1 ID = 9
| | Regular Index (Not Clustered)
| | Index Columns:
"2" 86 lines, 2274 characters
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ACCOUNT (Ascending)
| | | 2: DATEINS (Ascending)
| #Columns = 29
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: '600010100200'
| | | | 2: 2001-06-25
| | Stop Key: Inclusive Value
| | | | 1: '600010100200'
| | | | 2: 2001-06-25
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data to Application
| #Columns = 31

End of section

SQL query works fine (and the optimization with index access we want
to have, even with no optimize for ... rows, neither order by).

But:

select * from bds001.ledger
where (account, dateins)>=('600 010100200', '06/25/2001')
fetch first 10 rows only

says:

SQL0104N An unexpected token "(" was found following "". Expected
tokens may
include: "". SQLSTATE=42601

So DB2 UDB understand = as part of row value constructor in a where
clause and finds the index, but >= is not understood in the same
syntax construct.
Cured (or should I write: implemented)in the 8.2 release?
Bernard Dhooghe

[Serge Rielau]

I thought I answered this row-constructor thread sufficiently very
recnetly....

[Philip Sherman]

Serge Rielau wrote:

I thought I answered this row-constructor thread sufficiently very
recnetly....

I remember seeing it also. This query is attempting to do something that
cannot be coded as written.

The >= qualification should not distribute to multiple columns. When
multiple predicates are specified; either of these two interpretations
could be valid:
given: (a,b)>=(c,d)
1. ((a=c and b>=d) or (a>c))
2. (a>=c and b>=d)

There are a number of additional interpretations that could also be
valid. I'm happy the query was flagged as an error.

Phil Sherman

[Bernard Dhooghe]

Serge Rielau <sr*****@ca.e ye-bee-em.com> wrote in message news:<pb******* *************@t wister01.bloor. is.net.cable.ro gers.com>...

I thought I answered this row-constructor thread sufficiently very
recnetly....

Hello Serge,

Indeed.

But.

At every release there is hope the SQL92 feature will be there
because:

1. this missing capabability is a lack of orthogonality (which does
not only matters for purists but also for practitioners), if there is
one thing that kills the confidence, respect and usage of a tool
(product in this case, it is not open source)is a lack of
orthogonality

2. quite a number of postings in this newgroup ask for it when
talking about searching and scrolling where keys composed of multiple
parts are involved and performance problems are reported

3. DB2 UDB can does not support what is clearly claimed as being
basic for a relational database: search and processing by content

So: again: when, when, when?

Bernard Dhooghe

Bernard Dhooghe

[Serge Rielau]

Bernard Dhooghe wrote:

Serge Rielau <sr*****@ca.e ye-bee-em.com> wrote in message news:<pb******* *************@t wister01.bloor. is.net.cable.ro gers.com>...

I thought I answered this row-constructor thread sufficiently very
recnetly... .

Hello Serge,

Indeed.

But.

At every release there is hope the SQL92 feature will be there
because:

1. this missing capabability is a lack of orthogonality (which does
not only matters for purists but also for practitioners), if there is
one thing that kills the confidence, respect and usage of a tool
(product in this case, it is not open source)is a lack of
orthogonality

NO, if there is on ething that killd confidence then it is missing
_highly_needed_ features. 2. quite a number of postings in this newgroup ask for it when
talking about searching and scrolling where keys composed of multiple
parts are involved and performance problems are reported Yes, but:
SELECT COUNT(DISTINCT authors) FROM postings WHERE content =
'ROWCONTRUCTORS '
=> 2
In other words; I admire your persistence. But please grant me that
asking for the same thing by the same person is not the same as asking
for the same thing by many distinct customers.
3. DB2 UDB can does not support what is clearly claimed as being
basic for a relational database: search and processing by content I'm obviously missing something here.... what are all thsoe customers
doing without this alleged core feature?
Note that there is no law about which features of SQL a given DBMS has
to support.
So: again: when, when, when?

As soon as you, instead of repeating the same pokes buidl a business
case that folks like I can present to their managers to get the resources.
And, no, orthogonality is nice, but it is not a business case.
So far I know of 1(!) example for which I don't understand the business
logic (>= for composit zip-codes).
We are running in circles on this.... and, by golly, we got enough
features requested in this group into the product. It's not that I mean
to be stuborn...

Cheers
Serge

[Bernard Dhooghe]

Philip Sherman <ps******@ameri tech.net> wrote in message news:<iT******* ***********@new ssvr17.news.pro digy.com>...

Serge Rielau wrote:

I thought I answered this row-constructor thread sufficiently very
recnetly....

I remember seeing it also. This query is attempting to do something that
cannot be coded as written.

The >= qualification should not distribute to multiple columns. When
multiple predicates are specified; either of these two interpretations
could be valid:
given: (a,b)>=(c,d)
1. ((a=c and b>=d) or (a>c))
2. (a>=c and b>=d)

There are a number of additional interpretations that could also be
valid. I'm happy the query was flagged as an error.

Phil Sherman

Hello Phlip,

(a,c) >=(c,d) means exactly (SQL92 standard)

(a=c and c>=d) or a>c

The real problem is that (a,c) >= (c,d) has more semantic power than
the corresponding and/or query. There is no substitute for the row
value constructor clause unless cpu and disks have infinite speed,
than the and/or construct is fine, row value constructor would just be
a short way to write it. But the assomption of infinite power is not
realized yet.

Bernard Dhooghe
PS: UDB OO has taken a lot of investment money away to the detriment
of 'old' data processing needs. But these latter needs and these kind
of customers are funding DB2 development I think, not the OO
needs/sales (if this could have been predicted is another
discussion...)

[Bernard Dhooghe]

Serge Rielau <sr*****@ca.e ye-bee-em.com> wrote in message news:<ip******* ********@news04 .bloor.is.net.c able.rogers.com >...

Bernard Dhooghe wrote:

Serge Rielau <sr*****@ca.e ye-bee-em.com> wrote in message news:<pb******* *************@t wister01.bloor. is.net.cable.ro gers.com>...

I thought I answered this row-constructor thread sufficiently very
recnetly... .

Hello Serge,

Indeed.

But.

At every release there is hope the SQL92 feature will be there
because:

1. this missing capabability is a lack of orthogonality (which does
not only matters for purists but also for practitioners), if there is
one thing that kills the confidence, respect and usage of a tool
(product in this case, it is not open source)is a lack of
orthogonality

NO, if there is on ething that killd confidence then it is missing
_highly_needed_ features.

2. quite a number of postings in this newgroup ask for it when
talking about searching and scrolling where keys composed of multiple
parts are involved and performance problems are reported

Yes, but:
SELECT COUNT(DISTINCT authors) FROM postings WHERE content =
'ROWCONTRUCTORS '
=> 2
In other words; I admire your persistence. But please grant me that
asking for the same thing by the same person is not the same as asking
for the same thing by many distinct customers.

3. DB2 UDB can does not support what is clearly claimed as being
basic for a relational database: search and processing by content

I'm obviously missing something here.... what are all thsoe customers
doing without this alleged core feature?
Note that there is no law about which features of SQL a given DBMS has
to support.

So: again: when, when, when?

As soon as you, instead of repeating the same pokes buidl a business
case that folks like I can present to their managers to get the resources.
And, no, orthogonality is nice, but it is not a business case.
So far I know of 1(!) example for which I don't understand the business
logic (>= for composit zip-codes).
We are running in circles on this.... and, by golly, we got enough
features requested in this group into the product. It's not that I mean
to be stuborn...

Cheers
Serge

Hello Serge,

My approach is just to say once in a newsgroup posting what I think,
so I will not answer a number of things that you comment on and we all
appreciate I think, even I don't agree.

On your query (result 2) and the business case to managers, these are
facts I will tackle.

The query result has a very low value because I'm the one who uses the
SQL92 qualification I've found in the Melton book on SQL92. But every
time I reply (not issuing it myself as for this one) on the newsgroup
on postings and refer to row value constructors, I answer to the same
problem people encounter when using DB2 UDB in building real life
applications (where composite keys are not an exception at all), so
the newgroup counter is certainly equal the times I replied to
postings in comp.databases. ibm-db2 when suggesting row value
constructors would help.

I refer to Chris Date who in his relational writings was first in
favor of surrogate keys, but changed it's view and accepted that in
people's world, keys can be composed of multiple parts that make sense
part by part and also all together.

Key-indexed files where good for at on-line browsing and scrolling
access and there is no reason a relational dbms could not handle the
feature.

In key-indexed files, the set can not be composed and it is based on
just one file/table, but the cursor can be set and scrolling can go up
and down.

In a rdbms the set can be composed but cursor position is weak and
most of the time/always(?) at the beginning of the set. So row value
constructors are just a partial solution but would be of great help.

Remark: I would see it as follows: split roles in set composition and
cursor positioning (orthogonality) :

select * from ... where ... order by c1, c2, c3,... position cursor
at (c1,c2,c3,...) <= > | >= | < | <=) ('...', ...)

but this is no SQL standard as far as I know.

This is why I see row-value constructors as a (first) solution and no
doubt it was introduced in SQL92 to handle real world needs.

Bernard Dhooghe