DB2_COMPATIBILITY_VECTOR registry variable is set at Instance level , so it will affect all the databases present in the instance including old (which are created without setting ORA) and new ones (created with ORA).
The only difference will be that old databases will not be able to use any functionality which contain VARCHAR2 (And will not have number and date compatibility as well).
Summary : Creation of any PL/SQL object depends on the value of DB2_COMPATIBILITY_VECTOR at that particular moment . It does not matter whether the database is created in ORA or Non ORA.
Usage of varchar2 , number and date compatibility depends on the value of DB2_COMPATIBILITY_VECTOR at the time of database creation . If it was set at the time of db creation , than only database can use these features.
For detailed explanation Please refer the following examples :
Example 1) Please have a close look at step c .
a) I have not set DB2_COMPATIBILITY_VECTOR to ORA . I created a db NONORA . Now If i try to create a table with varchar2 , it fails as expected.
db2 "create table tt1 (c1 varchar2(100))"
SQL0104N An unexpected token "varchar2" was found following "reate table TT1
(C1 ". Expected tokens may include: "VARCHAR". SQLSTATE=42601
b) If I try to create a PL/SQL function , it fails.
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
SQL0104N An unexpected token "return integer is" was found following "on
func1(in1 in int)". Expected tokens may include: "<func_attribute_list>".
LINE NUMBER=1. SQLSTATE=42601
c) Now If I set DB2_COMPATIBILITY_VECTOR to ORA , I can create PL/SQL objects in this database as well
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2start
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
DB20000I The SQL command completed successfully.
d) Even after setting DB2_COMPATIBILITY_VECTOR to ORA , if I try to create a table with varchar2 , it fails because database is not created in ORA mode.
db2 "create table tt1 (c1 varchar2(100))"
SQL0104N An unexpected token "varchar2" was found following "reate table TT1
(C1 ". Expected tokens may include: "VARCHAR". SQLSTATE=42601
Example 2 ) If I have created my database after setting DB2_COMPATIBILITY_VECTOR to ORA. I can create tables with varchar2 at any point of time irrespective of current value of DB2_COMPATIBILITY_VECTOR .
But If I reset the registry variable DB2_COMPATIBILITY_VECTOR , I will not be able to create PL/SQL object in this database as well , even though it is created in ORA mode.
a) I have ORA mode set . I can create PL/SQl object
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
DB20000I The SQL command completed successfully.
b) Now I am resetting DB2_COMPATIBILITY_VECTOR . Creation of PL/SQL objects in this database will fail as well.
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
SQL0104N An unexpected token "return integer is" was found following "on
func1(in1 in int)". Expected tokens may include: "<func_attribute_list>".
LINE NUMBER=1. SQLSTATE=42601
c) But even after resetting DB2_COMPATIBILITY_VECTOR , I can still able to use varchar2 for this database as these cfg parameters are set at the time of db creation
db2 "create table tt1 (c1 varchar2(100))"
DB20000I The SQL command completed successfully.
The only difference will be that old databases will not be able to use any functionality which contain VARCHAR2 (And will not have number and date compatibility as well).
Summary : Creation of any PL/SQL object depends on the value of DB2_COMPATIBILITY_VECTOR at that particular moment . It does not matter whether the database is created in ORA or Non ORA.
Usage of varchar2 , number and date compatibility depends on the value of DB2_COMPATIBILITY_VECTOR at the time of database creation . If it was set at the time of db creation , than only database can use these features.
For detailed explanation Please refer the following examples :
Example 1) Please have a close look at step c .
a) I have not set DB2_COMPATIBILITY_VECTOR to ORA . I created a db NONORA . Now If i try to create a table with varchar2 , it fails as expected.
db2 "create table tt1 (c1 varchar2(100))"
SQL0104N An unexpected token "varchar2" was found following "reate table TT1
(C1 ". Expected tokens may include: "VARCHAR". SQLSTATE=42601
b) If I try to create a PL/SQL function , it fails.
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
SQL0104N An unexpected token "return integer is" was found following "on
func1(in1 in int)". Expected tokens may include: "<func_attribute_list>".
LINE NUMBER=1. SQLSTATE=42601
c) Now If I set DB2_COMPATIBILITY_VECTOR to ORA , I can create PL/SQL objects in this database as well
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2start
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
DB20000I The SQL command completed successfully.
d) Even after setting DB2_COMPATIBILITY_VECTOR to ORA , if I try to create a table with varchar2 , it fails because database is not created in ORA mode.
db2 "create table tt1 (c1 varchar2(100))"
SQL0104N An unexpected token "varchar2" was found following "reate table TT1
(C1 ". Expected tokens may include: "VARCHAR". SQLSTATE=42601
Example 2 ) If I have created my database after setting DB2_COMPATIBILITY_VECTOR to ORA. I can create tables with varchar2 at any point of time irrespective of current value of DB2_COMPATIBILITY_VECTOR .
But If I reset the registry variable DB2_COMPATIBILITY_VECTOR , I will not be able to create PL/SQL object in this database as well , even though it is created in ORA mode.
a) I have ORA mode set . I can create PL/SQl object
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
DB20000I The SQL command completed successfully.
b) Now I am resetting DB2_COMPATIBILITY_VECTOR . Creation of PL/SQL objects in this database will fail as well.
db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
SQL0104N An unexpected token "return integer is" was found following "on
func1(in1 in int)". Expected tokens may include: "<func_attribute_list>".
LINE NUMBER=1. SQLSTATE=42601
c) But even after resetting DB2_COMPATIBILITY_VECTOR , I can still able to use varchar2 for this database as these cfg parameters are set at the time of db creation
db2 "create table tt1 (c1 varchar2(100))"
DB20000I The SQL command completed successfully.
Sumit, congrats on the blog. Here are my comments
ReplyDelete1) In Summary you have mentioned that "It does not matter whether the database is created in ORA or Non ORA.
Usage of varchar2 , number and date compatibility depends on the value of DB2_COMPATIBILITY_VECTOR at the time of database creation . If it was set at the time of db creation , than only database can use these features."
This statement poses a few question
a) Statement where you say "It does not matter" conflicts with statement where you say "...depends on the value of vector at time of database creation. Even the 3rd statement is confusing. If the database can use these feature then isnt it a database level setting rather than an instance level setting.
2) Regarding Example1 - Why is it that the PL/SQL objects are created but we are still not able to use VARCHAR2. If PL/SQL support is there then VARCHAR2 and any other oracle compatible data type support should also be there. There are a few questions in this regard
a) Do you think that users who creates PL/SQL objects would not use the oracle compatibile data type inside the objects.
b) Do you think that the users who create PL/SQL objects would use DB2 data types inside them.
c) In migration I have generally seen oracle compatibile data types inside PL/SQL objects. So in that case what is the need for this type of support?
3) Example 3 - Similar question. Do you think that after resetting the compatibility vector the users would use just the VARCHAR2 or related oracle data types without the PL/SQL objects?
4) DB2 has been working on providing a lot of workaround while migrating Oracle code. This is a new feature, does it affect the performance of the system in any way (just curious to know). An example - Think about a big chunk of code where 50% PL/SQL and 50% ANSI SQL code is being used. You migrate the database and now 50% of your ANSI SQL is being supported by DB2, and the remaining 50% is supported by the Oracle Compatibility feature. Would the code give the similar performance that it had given previously. Oracle Compatibility looks like a wrapper on top of SQL/PL which DB2 supports. Performance is bound to get affected over here.
The whole point of Oracle Compatbility is to provide easy migration from Oracle to DB2. This feature was basically developed to resue the Oracle code in DB2. Now if a part of the Oracle code can be used and other part cannot then how does this feature help. On top of that if the user has to recreate the database in ORA mode and then keep the setting, you can very well imagine the amount of time and resources the user has to spend on recreating and reloading the database. Imagine the amount of downtime. Well these things can be prevented but still this has an overhead.
-- Thanks Amit B
Thanks for your comment . Please find my answers to your queries below :
Delete1) In Summary section , the line "It does not matter whether the database is created in ORA or Non ORA." is in the context of Creation of any PL/SQL object .
And the next line is about usage of varchar2 compatibility by the database.
Let's take a slightly different approach to make it simpler to understand.
There are actually two different layers . One is at the database level and other at database manager level.
The value of this parameter is essential for both.
A) Database level : When we set this compatibility vector to ORA and create the database and check the database configuration parameter for that db , it will show the values of following 3 compatibility related parameters :
db2 get db cfg for testdb | find "compatibility"
Number compatibility = ON
Varchar2 compatibility = ON
Date compatibility = ON
These parameters get hard coded and once the database is created , these can not be changed.
As the usage of varchar2 , number and date compatibility depends on these 3 parameter's value. That is why I mentioned the once these parameters are set to a particular value (ON/OFF) , the value of registry value DB2_COMPATIBILITY_VECTOR's scope ends in usage of varchar2,date and number compatibility perspective.
B) Instance level : The creation of PL/SQL objects depend on the current value of DB2_COMPATIBILITY_VECTOR . As there might be a flag whose value is checked every time it parses PL/SQL. So to parse PL/SQL at any point will only depend upon the current value of flag (basically the DB2_COMPATIBILITY_VECTOR registry parameter's current value) irrespective of whether the database was create in ORA or NON ORA mode.
2) Your concern is correct that if somebody is migrating from Oracle to DB2 then he will obviously use varchar2 .
but there are always two sides of coin.
If we have an old database on db2 itself which was not created under ORA . But I want to create some PL/SQL objects.
So in spite of having this great feature with us , should we ask the user to first drop the database and recreate it ?
will it not be sensible to let the user use this feature .
And if we are concerned about user using varchar2 inside PL/SQL then no body is stopping us from doing so . what we have to do is , just to keep this registry variable to ORA all the time . That should not be a big deal I guess :).
And anyhow , DB2 supports varchar2 just for namesake . Internally it maps to varchar only :).
3) Same as above :)
4) Correct . As per first look it seems obvious that if it leaves it's domain(ANSI SQL) , the performance may be impacted to some an extent .
But we can not be so sure about it. It actually varies from query to query. As while working with Emptoris , I worked closely with performance benchmarking and they were happy with DB2's current performance in ORA mode.
And comparing the license cost of both , I guess people may compromise a lil bit :)
A quick response to your point (2)...This feature was developed for customers who want to migrate from Oracle to DB2 without rewriting much of the code. The example you gave regarding two sides of the coin was considering an old DB2 database. And why would any customer want to use PL/SQL in an old DB2 database. I fail to understand that so please enlighten me :-)...
DeleteHe He ..
DeleteGood point !!
But still I would say that it is about perception .
The main motive of this blog was to explain about how it actually works in DB2 . This is the actual design and behavior :)
Now why it behaves so , that I can just put my perception.
And I believe that for DB2 to behave like this , it might have been the result of long discussions and business decisions :)
Question: I have a DB under ORA compatability can I restore the backup to a DB with non-ORA compatabilty. (goal is to keep data & structures but remove ORA compatability). Thanks.
Delete