Newbie questions: case sensitivity, deleting 0 records 
Author Message
 Newbie questions: case sensitivity, deleting 0 records

Ahoy DB2ers,

Forgive these newbie questions. We're embarking on our first DB2 project
after becoming entrenched in the MS SQL Server way of doing things, and 2
things (so far) confuse us. We're using DB2 7.1 on Linux.

1. It appears that searches and sorts are case-sensitive by default. Can
this be changed, either on a query- or database-wide basis?

2. When we try to delete records with a WHERE clause that doesn't match any
records (e.g., DELETE FROM Products WHERE ProductID = 123, but there isn't
actually a product with ID 123), we get an error. Have we done something
wrong in setting up relationships, or is this the intended behavior?

TIA,
Will D.

--
Will Doolittle
Faludi Computing, Inc.            (415) 512-8212

"Better living through better databases"



Sat, 23 Aug 2003 03:58:32 GMT
 Newbie questions: case sensitivity, deleting 0 records

Hi Will,
See my answers below:

Quote:

> Ahoy DB2ers,

> Forgive these newbie questions. We're embarking on our first DB2 project
> after becoming entrenched in the MS SQL Server way of doing things, and 2
> things (so far) confuse us. We're using DB2 7.1 on Linux.

> 1. It appears that searches and sorts are case-sensitive by default. Can
> this be changed, either on a query- or database-wide basis?

This is on our to do list; but, not sure when this feature it will appear.
Other customers use strategies like:
- search on the UPPER() (even going so far as to mirror UPPER data in an index
for better perf.)
- using text extenders, which is NOT case sensitive.

Quote:

> 2. When we try to delete records with a WHERE clause that doesn't match any
> records (e.g., DELETE FROM Products WHERE ProductID = 123, but there isn't
> actually a product with ID 123), we get an error. Have we done something
> wrong in setting up relationships, or is this the intended behavior?

You are probably using an ODBC product to access the data, and it is barfing on
the warning:
    SQL0100W  No row was found for FETCH, UPDATE or DELETE;
    or the result of a query is an empty table.  SQLSTATE=02000

For such poorly coded ODBC applications you can update your db2cli.ini settings
with:
    IGNOREWARNLIST = "'02000'"
Check out the CLI Guide, Ch.4, Keyword listings for more details.

Hope this helps,
David.

Quote:

> TIA,
> Will D.

> --
> Will Doolittle
> Faludi Computing, Inc.            (415) 512-8212

> "Better living through better databases"



Sat, 23 Aug 2003 04:52:48 GMT
 Newbie questions: case sensitivity, deleting 0 records
Hi,
Newbie questions are fine.
1. I'm not sure about database-wide setting, but to do a case-insensitive
search you can operate on UCASE(colname), which would not be good for
performance, or you can use a generated column.  See the online doco for CREATE
TABLE and ALTER TABLE (to add column to existing table); you can implement a
GENERATED column that always sets itself to UCASE(another column).

2.  An empty searched update or delete returns a warning, correctly.  Some MS
products (esp Access, VB) treat this as an error.  Assuming you're having
problems with MS-based client applications using ODBC, there are a couple of
client configuration items that will help you.  At the client, use the client
configuration assistant, select the database, properties, odbc properties,
optimisation, MS VB.  Then look in the advanced properties, in the service tab,
and look at what is selected in the PATCH1 and PATCH2 keywords.  One of them is
a fix for empty searched update/delete.  Another to do with passing timestamps
as strings (MS time resolution is not as fine as ISO).

If you're not using an MS client, see if you can distinguish whether the error
is really an error or a warning.

--Greg

Quote:

> Ahoy DB2ers,

> Forgive these newbie questions. We're embarking on our first DB2 project
> after becoming entrenched in the MS SQL Server way of doing things, and 2
> things (so far) confuse us. We're using DB2 7.1 on Linux.

> 1. It appears that searches and sorts are case-sensitive by default. Can
> this be changed, either on a query- or database-wide basis?

> 2. When we try to delete records with a WHERE clause that doesn't match any
> records (e.g., DELETE FROM Products WHERE ProductID = 123, but there isn't
> actually a product with ID 123), we get an error. Have we done something
> wrong in setting up relationships, or is this the intended behavior?

> TIA,
> Will D.

> --
> Will Doolittle
> Faludi Computing, Inc.            (415) 512-8212

> "Better living through better databases"



Sat, 23 Aug 2003 05:11:45 GMT
 Newbie questions: case sensitivity, deleting 0 records
You can mirror upper case in an index more easily using the generated column
feature in v7.1 to keep a copy of  of the column in uppercase.
Quote:

> Hi Will,
> See my answers below:


> > Ahoy DB2ers,

> > Forgive these newbie questions. We're embarking on our first DB2 project
> > after becoming entrenched in the MS SQL Server way of doing things, and 2
> > things (so far) confuse us. We're using DB2 7.1 on Linux.

> > 1. It appears that searches and sorts are case-sensitive by default. Can
> > this be changed, either on a query- or database-wide basis?

> This is on our to do list; but, not sure when this feature it will appear.
> Other customers use strategies like:
> - search on the UPPER() (even going so far as to mirror UPPER data in an index
> for better perf.)
> - using text extenders, which is NOT case sensitive.

> > 2. When we try to delete records with a WHERE clause that doesn't match any
> > records (e.g., DELETE FROM Products WHERE ProductID = 123, but there isn't
> > actually a product with ID 123), we get an error. Have we done something
> > wrong in setting up relationships, or is this the intended behavior?

> You are probably using an ODBC product to access the data, and it is barfing on
> the warning:
>     SQL0100W  No row was found for FETCH, UPDATE or DELETE;
>     or the result of a query is an empty table.  SQLSTATE=02000

> For such poorly coded ODBC applications you can update your db2cli.ini settings
> with:
>     IGNOREWARNLIST = "'02000'"
> Check out the CLI Guide, Ch.4, Keyword listings for more details.

> Hope this helps,
> David.

> > TIA,
> > Will D.

> > --
> > Will Doolittle
> > Faludi Computing, Inc.            (415) 512-8212

> > "Better living through better databases"



Sat, 23 Aug 2003 05:04:18 GMT
 Newbie questions: case sensitivity, deleting 0 records

Quote:
> > 1. It appears that searches and sorts are case-sensitive by default. Can
> > this be changed, either on a query- or database-wide basis?

> This is on our to do list; but, not sure when this feature it will appear.
> Other customers use strategies like:
> - search on the UPPER() (even going so far as to mirror UPPER data in an
index
> for better perf.)
> - using text extenders, which is NOT case sensitive.

Wow, I'm surprised this isn't a feature. OK, in the meantime we'll generate
an UPPER column and search on that. What are "text extenders"? I did a
search in online docs and didn't find anything useful.

Quote:
> > 2. When we try to delete records with a WHERE clause that doesn't match
any
> > records (e.g., DELETE FROM Products WHERE ProductID = 123, but there
isn't
> > actually a product with ID 123), we get an error. Have we done something
> > wrong in setting up relationships, or is this the intended behavior?

> You are probably using an ODBC product to access the data, and it is
barfing on
> the warning:
>     SQL0100W  No row was found for FETCH, UPDATE or DELETE;
>     or the result of a query is an empty table.  SQLSTATE=02000

We're building a ColdFusion application, using the native driver that comes
with CF App Server Enterprise on Linux. I guess I can assume that the driver
isn't as well written as it could be, and we just need to put in test
queries before each delete or update to make sure there's something there to
operate on.

Thanks for your help everyone,
Will

--
Will Doolittle
Faludi Computing, Inc.            (415) 512-8212

"Better living through better databases"



Sat, 23 Aug 2003 06:35:32 GMT
 Newbie questions: case sensitivity, deleting 0 records
http://www-4.ibm.com/software/data/db2/extenders/about.html
Quote:

> What are "text extenders"? I did a
> search in online docs and didn't find anything useful.



Sat, 23 Aug 2003 07:32:29 GMT
 Newbie questions: case sensitivity, deleting 0 records
Hi Will,
You can find Text Extender info on the web at:
    http://www-4.ibm.com/software/data/db2/extenders/text/index.html

Hope this helps,
David.

Quote:



> > > 1. It appears that searches and sorts are case-sensitive by default. Can
> > > this be changed, either on a query- or database-wide basis?

> > This is on our to do list; but, not sure when this feature it will appear.
> > Other customers use strategies like:
> > - search on the UPPER() (even going so far as to mirror UPPER data in an
> index
> > for better perf.)
> > - using text extenders, which is NOT case sensitive.

> Wow, I'm surprised this isn't a feature. OK, in the meantime we'll generate
> an UPPER column and search on that. What are "text extenders"? I did a
> search in online docs and didn't find anything useful.

> > > 2. When we try to delete records with a WHERE clause that doesn't match
> any
> > > records (e.g., DELETE FROM Products WHERE ProductID = 123, but there
> isn't
> > > actually a product with ID 123), we get an error. Have we done something
> > > wrong in setting up relationships, or is this the intended behavior?

> > You are probably using an ODBC product to access the data, and it is
> barfing on
> > the warning:
> >     SQL0100W  No row was found for FETCH, UPDATE or DELETE;
> >     or the result of a query is an empty table.  SQLSTATE=02000

> We're building a ColdFusion application, using the native driver that comes
> with CF App Server Enterprise on Linux. I guess I can assume that the driver
> isn't as well written as it could be, and we just need to put in test
> queries before each delete or update to make sure there's something there to
> operate on.

> Thanks for your help everyone,
> Will

> --
> Will Doolittle
> Faludi Computing, Inc.            (415) 512-8212

> "Better living through better databases"



Sat, 23 Aug 2003 20:53:10 GMT
 Newbie questions: case sensitivity, deleting 0 records
In the doc, search for "collating sequence".  This is the implemented feature
you are looking for.

User-defined collating sequence is possible.
It is set at database creation (not instance).  Refer to CREATE DATABASE.
Collating sequences define the way comparaisons/sorts, .. are done.

Actual weights depend on the collating sequence table used which depends on the
code set and locale. Note that a collating
sequence table is not the same as a code page table, which defines code points.

User defined collating sequence in only available from the admin API call. (you
cannot used "db2clp>> create db" nor the gui...)
see sqlecrea
SQLEDBDESC SQLDBUDC (this is a char(256), get the picture...)
SQLDBCSS SQL_CS_USER

There is a program in the sample directory that is using this api (if i
remember correctly)
It may not specify the collating sequence but at least it's a start.
I had to check...  Here it is sqlecrea.c.

Also check in sqllib/include/*.h  there are some already defined structures you
can reuse as a base to define your own.

Read also on the TRANSLATE() function.
You can create select-views and insert-viewsif you wish.  By granting, revoking
access i guess you can force people to use
the views.

Now, this is enough, do the rest...

PM

Quote:

> Ahoy DB2ers,

> Forgive these newbie questions. We're embarking on our first DB2 project
> after becoming entrenched in the MS SQL Server way of doing things, and 2
> things (so far) confuse us. We're using DB2 7.1 on Linux.

> 1. It appears that searches and sorts are case-sensitive by default. Can
> this be changed, either on a query- or database-wide basis?

> 2. When we try to delete records with a WHERE clause that doesn't match any
> records (e.g., DELETE FROM Products WHERE ProductID = 123, but there isn't
> actually a product with ID 123), we get an error. Have we done something
> wrong in setting up relationships, or is this the intended behavior?

> TIA,
> Will D.

> --
> Will Doolittle
> Faludi Computing, Inc.            (415) 512-8212

> "Better living through better databases"



Sat, 23 Aug 2003 15:18:43 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Question on Scope of Case-Sensitivity on a Case-Sensitive SQL Server 7.0/2000

2. Newbie -- case sensitivity

3. Case Sensitivity question

4. Case-sensitivity question!

5. Case sensitivity question

6. MS SQL Server 6.0 case sensitivity question

7. Another Case Sensitivity question

8. Case (in)-sensitivity & preserving case

9. Simple Question: Case sensitivity - Performance?

10. Simple Question: Case sensitivity

11. Simple Question: Case sensitivity

12. Newbie Question? Deleting Records


 
Powered by phpBB® Forum Software