The DB2 system deviates from the standards just enough to cause us some troubles. But then again, all the other vendors' systems have their troublesome peculiarities too. These are just things we have to live with.

What I call a deviation is anything major that is in the SQL-2 standards and/or is discussed usually in textbooks that DB2 does not implement. Or anything standard across the other major systems but that is not in DB2. Here, I will try to map out some of the more pertinent deviations. I will add more as I run across them.

The describe command can be used for instance to describe the schema of an existing table. An oddity, however, is that one must call the table with its schema name. For instance, say that you are user fred, you are presently in your default schema space (so your schema space is fred), and you want to describe a table called matrix which belongs to you and "lives" in your schema. You must say

db2 => describe table fred.matrix;

If you just said

db2 => describe table matrix;

DB2 would complain that it could not find table matrix.

When issuing a "create table" command, one must explicitly declare all attributes (columns) as "not null" which will be part of the primary key declaration. In ORACLE 7.x, at least, one does not have to do this. It is implicitly declared that all columns that participate in the primary key are "not null". I believe the standards support ORACLE here.

The DB2 system does not allow the "on update cascade" option for foreign key constraints. The only options it allows for "on update" are "restrict and "no action". DB2 does offer the full range for "on delete". If one tries to declare an FK as "on update cascade", one simply gets a syntax error. By the way, ORACLE 7.x does not allow for "on update cascade" either.