Comparing Db2 and MySQL: Naming Objects and Reserved Words

Every RDBMS has areas where the differences from other RDBMSes are noticeable, and this is one of them between Db2 and MySQL. Most RDBMSes have quirks to the way SQL is written, and this is certainly true of Db2 where some things were there before the SQL-92 standard or other standards existed. I still have a hard time typing LIMIT N instead of Db2’s FETCH FIRST N ROWS ONLY, even when I discovered by accident that the LIMIT syntax also works in Db2. All these years of typing so many extra words!

Reserved Words

Both Db2 and MySQL provide a list of reserved words that generally should not be used alone as the names for objects.

Db2

Beyond avoiding naming schemas or tables with anything that starts with SYS, I don’t think I’ve really paid much attention to the Db2 reserved words in more than 20 years of working with Db2. That’s because this fully works in Db2:

$ db2 "create table from (select varchar(10), and varchar(10), where varchar(10))"
DB20000I  The SQL command completed successfully.
$ db2 "select select from from where where='' and and=''"

SELECT
----------

  0 record(s) selected.

It’s kind of crazy that works. Even in simple bash scripts, it can be common to rely on keywords to parse what you’re passing in.

Relying on this may lead to edge cases where delimiting the object names is required, but they’re not obvious. The documentation explicitly says to avoid a column named count, and yet it works in the most basic case:

$ db2 "create table select (count varchar(10))"
DB20000I  The SQL command completed successfully.
[db2inst1@d8ab9ae65554 ~]$ db2 "select count from select"

COUNT
----------

  0 record(s) selected.

MySQL

If I try the same inappropriate use of keywords in MySQL, this is what I get:

mysql> create table from (select varchar(10), and varchar(10), where varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from (select varchar(10), and varchar(10), where varchar(10))' at line 1

Now, I can make this work in MySQL by enclosing the names in backticks:

mysql> create table `from` (`select` varchar(10), `and` varchar(10), `where` varchar(10));
Query OK, 0 rows affected (0.05 sec)

The problem is that whenever I query this table, I absolutely must enclose the columns in backticks:

mysql> select select from from where where='' and and='';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select from from where where='' and and=''' at line 1
mysql> select `select` from `from` where `where`='' and `and`='';
Empty set (0.02 sec)

The best practice in MySQL is to always enclose identifiers in backticks. Not because we actually expect to create tables named FROM, but really because we don’t know what keywords MySQL is adding in the next release. At work, we’re working on going from 5.7 to 8.0 for some databases, and in one database, we had a column named LAG, which is a reserved word in 8.0. If we were careful, and made sure every reference to this column is enclosed in backticks, this wouldn’t cause a problem, but even one miss will lead to unexpected errors.

Capitalization

Db2

When talking about object naming with Db2, we have to mention capitalization. Db2 stores all object names in uppercase, unless you force it otherwise:

$ db2 "create table table_1 (ThisColumn varchar(10))"
DB20000I  The SQL command completed successfully.
$ db2 "describe table db2inst1.table_1"

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
THISCOLUMN                      SYSIBM    VARCHAR                     10     0 Yes

  1 record(s) selected.

There is no concept in Db2 of the difference between case preservation and case sensitivity. This means that no matter how you change the case, Db2 assumes upper case for object names. The following all work:

$ db2 "select thiscolumn from table_1"
$ db2 "select ThisColumn from table_1"
$ db2 "select ThIsCoLuMn from table_1"

This may be something you’ve run into if someone has created an object or column name with forced mixed case (which some ORMs or GUIs may do) or with a space in it. I have always hated these things in object names. I can’t tell you how long it took me on one iteration of my reorg script to figure out that a particular table had a space AT THE END OF THE TABLE NAME. Future iterations of the script tried to account for all the atrocities that can be committed with object naming.

My favorite method of interfacing with Db2 makes quoting object names particularly ugly. No backticks here, this is what actually works:

$ db2 "select \"THISCOLUMN\" from table_1"

If I were actually working with these on a regular basis, I’m sure I’d be using a GUI or at least the db2 CLP:

db2 => select "THISCOLUMN" from table_1

The following all fail, because the case does not match what is in the database:

db2 => select "ThisColumn" from table_1
SQL0206N  "ThisColumn" is not valid in the context where it is used.
SQLSTATE=42703
db2 => select "thiscolumn" from table_1
SQL0206N  "thiscolumn" is not valid in the context where it is used.
SQLSTATE=42703
db2 => select "ThIsCoLuMn" from table_1
SQL0206N  "ThIsCoLuMn" is not valid in the context where it is used.
SQLSTATE=42703

MySQL

MySQL does not have this case issue:

mysql> create table table_1 (ThisColumn varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> show create table table_1;
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
| table_1 | CREATE TABLE `table_1` (
  `ThisColumn` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Notice first of all, that MySQL preserves the case I specified without using backticks. Now when I go to query this table, ALL of the following work just fine, despite having different cases specified:

mysql> select thiscolumn from table_1;
Empty set (0.00 sec)

mysql> select `thiscolumn` from table_1;
Empty set (0.00 sec)

mysql> select `ThIsCoLuMn` from table_1;
Empty set (0.00 sec)

I guess in Db2, I could have a column named THISCOLUMN and one named thiscolumn, while I couldn’t in MySQL. I’m not sure that’s a use case we want to encourage or support anyway. It does make you feel a bit for those developing cross-rdbms applications and GUIs, though. This is out of the realm of what drivers can really handle.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home