About the DB2_COMPATIBILITY_VECTOR and what not to say at the dinner table
Comments (2) Visits (11713)
The question I keep hearing often from DB2 customers however is:
You can classify DB2 9.7 Oracle speak into four categories:
Think of words like "kindergarden" as a synonym for "child care" saying "gesundheit" for "nless you".
Here we are down to taste. DB2 supports multiple ways to say the same thing out of the box and it's up to you you pick the language that suits your skills.
Examples of these are
Earlier in the year I presented on DB2 9.7 in Bangalore at an IM Tech conference.
I recall a participant in the front shaking his head to quite a few things I was saying, some of which I didn't really consider debatable.
It took me a moment to remember that in India you don't know when you approve, you make another motion that more resembles shaking your head.
This is what I mean by incompatible. You need to know the context of the person, or in the case of DB2, application you are dealing with, in order to react appropriately.
Examples for these are
This section of course is about matters of opinion. You be the judge of those.
Our opinion on these features is at we understand we need to support them for Oracle applications.
But we do not want to encourage DB2 developers or DB2 to use them or at least give DB2 DBA's the power to forbid them
Examples for these are
It's purpose was three fold:
But, for the record, we prefer customers to be all in, or all out, because that's what's best tested - the beaten path so to speak.
So db2set DB2_
Here is a link to the detailed description: DB2_
Harmless. DUAL was supported by default, and then we realized that some IBM applications test for Oracle by testing for DUAL.
Long term this will be turned on by default.
Note that you can always define your own DUAL with a public synonym over e.g. SYSIBM.SYSDUMMY1
Harmless for DB2, but even Oracle deprecates it's usage.
Note that in Oracle if you miss a (+) operator for one of the join columns it will quietly downgrade the join to an inner join.
DB2 will raise an error. So if you want to find bugs in your Oracle join syntax you can use DB as a spell checker.
Harmless. The reason it is not on by default is because we reserve the right to tweak its semantics.
I doubt we will, but you never know.
Very harmful! NUMBER arithmetic changes the way DB2 does arithmetic for existing data types. Typically applications depend on the DBMS behavior.
This is so harmful for your existing database that teh setting is frozen into a database. That is after the database is created the setting cannot be changed for that db.
Note that I do not say that NUMBER is bad. It isn't. But it is different and inco
Extremely harmful! IT is very important to know that ion Oracle VARCHAR2 and VARCHAR have the same behavior.
That is, any assumption that by not using VARCHAR a DB2 application is safe is false.
Likely harmful. Thanks to DATE being a "subtype" of TIMESTAMP in DB2 9.7 most applications do not particularly care for DATE vs. TIMESTAMP semantics but there are issues.
Since "DATE" (I use the quotes to indicate Oracle DATE (with time)) is implemented as a TIMESTAMP(0) it is more rigid about the allowed string formats that are allowed for implicit casting.
Further more there is some date arithmetic that behaves subtly different.
Again this setting is frozen into the database at creation time.
Harmless. The difference between DB2's version of TRUNCATE table and Oracle's is that the Oracle version uses less keywords and in Oracle mode a implicit COMMIT is driven before the statement executes.
Noe that in DB2 TRUNCATE will fail anyway if the statement is not the first in a tran
It is hard to imagine a case where an existing DB2 application will behave differently with this setting.
Harmless. This setting allows round braces in addition to square brackets to access index elements and, to access attributes such as FIRTS and LAST via a method notation: <array>.FIRST.
At worst this feature makes an application confusing to read if you overload names.
Harmless. If set before a database is created a subset of the dictionary views as used by Oracle are created and made available to you.
No harm done beyond using a bit of disk space.
Harmless. The only bad thing about allowing PL/SQL is that when you have a syntax error in your SQL Procedure DB2 had to decide whether you wrote a bad PL/SQL procedure or a bad SQL PL procedure.
If DB2's guess does not match your original intentions the error message may be conf
Making a result set implicitly insensitive harms performance.
If you want your cursors to be insensitive use either the keyword INSENSITIVE in their definition or use the STATICREADONLY INSENITIVE bind option..
Harmless. This setting is for Sybase/SQL Server. It allows INOUT parameters of routines to be defaulted, instead of raising an error if the argument is omit
The feature is on by default in DB2 Express C. Longterm I image it will be available by default.
Harmless. This setting shifts the enforcement of SQL access level restrictions from compiletime to runtime.
That is instead of disallowing invocation of a function that is defined as MODIFIES SQL DATA in e.g. the WHERE clause DB2 allows the invocation as long as the function doesn't actually modify any SQL data.
If you have want to re-use your non DB2 skills when developing DB2 applications I consider the following setting save: 16F8F