Posted by When does case matter in DB2? Well, it doesn’t unless it does. Nice and clear, huh?
When Text Must be in the Correct Case
Text must be in the correct case whenever it is part of a literal string. Things that you have to enclose in single quotes are case sensative.
select * from table where col1='text'
is different from
select * from table where col1='Text'
and also different from
select * from table where col1='TEXT'
There are functions that you can use to convert either side or both sides of that where clause the same case.
select * from table where lower(col1)='text'
While that one will work, it may not be the best for performance. When using functions like this, we prefer that they not be applied to column data, because that can prevent DB2 from using indexes on the column at all. This can be a drastic impact.
Most of the DBAs in the room at a conference when it was announced that expression-based indexes were likely to be included in 10.5 cheered. 10.5 does indeed include expression based indexes. This used to be a cool thing that both z/OS and Oracle had that DB2 LUW did not. This makes it so we can create this:
create index ix_table_lower_col1 on table (lower(col1))
That index is fully separate and different than an index simply on col1, so we have to be careful to not over-index, but can help us out of a tight spot. Ideally, the information would be entered in the correct text and validated when it was inserted into the table.
Knowledge center entries on:
The LOWER function
The UPPER function
When Case MIGHT Matter
For the most part, case does not matter on the names of objects. The following will usually all work:
select * from table where col1='text'
select * from TABLE where col1='text'
select * from TaBlE where col1='text'
However, it is possible to specifically create objects with case sensitive names. Like creating objects with spaces in them, it’s a bad idea and not something I recommend. To create a table with a case sensitive name, you use:
create table "YourTable" (id INTEGER)
Now, every time you work with that table, you have to use "YourTable" instead of just yourtable or YOURTABLE. I find this highly annoying, but there are enough annoying vended databases out there that my runstats and reorg scripts are coded to handle this.
The above is true for all object names – tables, columns, indexes etc.
Internally, if you do not force mixed case, DB2 for LUW stores object names in all UPPER CASE. This means if you’re querying a system table for information about an object, you have to put the object name in upper case like this:
select * from syscat.tables where tabname='TABLE'
When Case Does Not Matter
Case never matters for DB2 keywords. The following will all work the same way:
select * from table where col1='text'
Select * From table Where col1='text'
SELECT * FROM table WHERE col1='text'
DB2 Tools
In non-SQL applications, the rules are very much the same for objects. When working with DB2 tools, their names are always fully lower case – such as db2pd, db2advis, and so forth. But when you pass in database or table names, the same case rules laid out above should apply. Option flags on the tools are case sensitive – db2fm -d and db2fm -D are two different commands.
SQL Best Practices for Case
Do not used mixed case for object names.
Beyond that, it would be nice if there was consensus on what the best practice is. Some prefer that all keywords are in upper case, with object names in lower case like this:
SELECT * FROM table WHERE col1='Text'
Others prefer the opposite – keywords in lower case, with object names in upper case:
select * from TABLE where COL1='Text'
Others go for everything upper case:
SELECT * FROM TABLE WHERE COL1='Text'
And still others take the (lazy) all lower case approach:
select * from table where col1='Text'
I tend to go with the last (lazy) approach when writing quick one-liners at the command line, not worrying if I mix up the case when copying and pasting. I tend to like the keywords in upper case, with object names in lower case when I’m writing longer queries in a file. Honestly, I don’t think there any technical reasons one way or the other, it’s just what you find more usable.
What is your capitalization best practice or preference, and why?
References
developerWorks article on dealing with case: http://www.ibm.com/developerworks/data/library/techarticle/0203adamache/0203adamache.html
Hi,
Very good article! And I would like to say that the problem is even worse when you have to deal with these case-sensitive tables from DB2CLP.
I asked and solved a question in StackOverFlow that tries to solve this problem: http://stackoverflow.com/questions/12937463/how-to-access-tables-with-same-name-different-case-in-db2
Andres Gomez (AngocA)
I like to use UPPER CASE for keywords, lower case for columns and Start Case for other objects in the database. Abbreviations are not capitalised in column names, but are in the other object names. I also use underscores between words and numbers (unless aliasing a table).
This is a style I have adopted and modified form Joe Celko’s SQL Programming Style book.
I find the different case usage makes it easier to read and locate the different parts of the SQL statement (clauses, objects and columns)
Indentation also has a huge part to play in readability but that’s a different subject altogether.
Example of capitalisation (not indentation)…
SELECT T1.my_column_1, T2.my_column_2
FROM My_First_Table AS T1
INNER JOIN My_Second_Table AS T2
ON T1.my_column_1 = T2.my_column_1
WHERE T1.my_column_2 = ‘some value’;