[egenix-users] db2 related question

[M.-A. Lemburg]

Fernando Martins wrote:
> Hi,
> 
> I have a zope Intranet website with a MySQL backend and I'm tasked to
> migrate from MySQL to DB2 Express (the official dbms). Zope will also
> have to go away sooner or later, but I the first step is the DBMS.
> 
> The problem I'm facing with, is that DB2 "prefers" to work with tables
> and field names in capitals, whereas the db in MySQL mixes case. That
> means that I cannot migrate transparently, at first sight. I could
> change the SQL statements to use quotes and force the mixed case, but
> it's still quite a bit of work. I could migrate to a capital case system
> and change the zope front-end, but that's even more work.
> 
> Now, I also have a MS-Access frontend and as a matter of fact the ODBC
> layer (or MS Access) takes care of transparently converting from mixed
> to capital case and vice-versa.
> 
> I was wondering if the mxODBC interface for DB2 can actually do the same?

mxODBC itself does not parse the SQL you pass to it. The ODBC driver
and/or database do such parsing and thus it's possible that the ODBC
driver can implement such conversion between mixed case and all upper
case identifiers.

However, I wonder why that's a problem in your case:

DB2 adheres to the SQL standard which mandates to be case insensitive
regarding identifiers, unless those identifiers are quoted.

I'm not sure whether it's in the standard as well, but most enterprise
databases use all capital letter for storing identifiers (and some
even for things like user names and passwords).

So you have the following situation:

MyTable, MYTABLE and myTable are all the same table. The database
uses MYTABLE to reference the table internally.

"MyTable", "MYTABLE" and "myTable" are three different tables.

Unless you have a situation where case really matters, e.g.
two fields "ID" and "id", you should be able to just continue
using mixed case identifiers in your Zope application (without
quoting them) and have the database match these case-insensitive
to what it stores internally.

Here's an article about case sensitivity of DB2:

    http://www.ibm.com/developerworks/data/library/techarticle/0203adamache/0203adamache.html

Also note that the DB2 ODBC provides quite a few options to
make porting existing applications easier:


http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.cli.doc/doc/r0007964.htm

You can use those with mxODBC or our mxODBC Zope DA.

This is the official MySQL to DB2 conversion guide:

    http://www.redbooks.ibm.com/abstracts/sg247093.html

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Feb 25 2010)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/