 | |
Any possibilty/trick to create Oracle data dictionary-compatibleviews after CRAETE DATABASE
comp.databases.ibm-db2 comp.databases.ibm-db2
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Any possibilty/trick to create Oracle data dictionary-compatibleviews after CRAETE DATABASE -
04-15-2011
, 07:03 AM
#2
| |||
| |||
|
|
Hi all, does anyone know of a way to enable the Oracle data dictionary- compatible views in a database that was created without the appropriate DB2_COMPATIBILITY_VECTOR setting (or before DB2 9.7)? TIA for any answer Joachim Joachim, |
db2upd97 should do the trick
If that doesn't work for some reason you could dump them from an ORA
style DB2 DB and apply them as a script (there is no magic here).
But try db2upd97 first.
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau
#3
| |||
| |||
|
|
On 4/15/2011 8:03 AM, joklassen wrote:> Hi all, does anyone know of a way to enable the Oracle data dictionary- compatible views in a database that was created without the appropriate DB2_COMPATIBILITY_VECTOR setting (or before DB2 9.7)? TIA for any answer Joachim Joachim, db2upd97 should do the trick If that doesn't work for some reason you could dump them from an ORA style DB2 DB and apply them as a script (there is no magic here). But try db2upd97 first. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW, IBM Toronto Lab Blog: * *tinyurl.com/SQLTips4DB2 Wiki: * *tinyurl.com/Oracle2DB2Wiki Twitter: srielau |
unfortunately db2updv97 (DB2 9.7 FP3a) does not work ( even with the -
a option).
Copying the view definitions from another db was my first idea, but
does not work the way I would like to. You cannot create views in
schema SYSIBMADM (SQL0553), so I must use another schema and create
public synonyms afterwards. That works so far, but these views will
not be updated automatically with a next fixpack or version. So there
is always some manual work to be done which I (resp. the customer)
would like to avoid.
Any other ideas?
Thx
Joachim
#4
| |||
| |||
|
|
On Apr 18, 11:32 am, Serge Rielau<srie... (AT) ca (DOT) ibm.com> wrote: On 4/15/2011 8:03 AM, joklassen wrote:> Hi all, does anyone know of a way to enable the Oracle data dictionary- compatible views in a database that was created without the appropriate DB2_COMPATIBILITY_VECTOR setting (or before DB2 9.7)? TIA for any answer Joachim Joachim, db2upd97 should do the trick If that doesn't work for some reason you could dump them from an ORA style DB2 DB and apply them as a script (there is no magic here). But try db2upd97 first. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW, IBM Toronto Lab Blog: tinyurl.com/SQLTips4DB2 Wiki: tinyurl.com/Oracle2DB2Wiki Twitter: srielau Hi Serge, unfortunately db2updv97 (DB2 9.7 FP3a) does not work ( even with the - a option). Copying the view definitions from another db was my first idea, but does not work the way I would like to. You cannot create views in schema SYSIBMADM (SQL0553), so I must use another schema and create public synonyms afterwards. That works so far, but these views will not be updated automatically with a next fixpack or version. So there is always some manual work to be done which I (resp. the customer) would like to avoid. Any other ideas? I have spoken to the catalog folks and they think it should work. |
restarted DB2 before running db2updv97?
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau
#5
| |||
| |||
|
|
On 4/18/2011 7:49 AM, joklassen wrote: On Apr 18, 11:32 am, Serge Rielau<srie... (AT) ca (DOT) ibm.com> *wrote: On 4/15/2011 8:03 AM, joklassen wrote:> *Hi all, does anyone know of a way to enable the Oracle data dictionary- compatible views in a database that was created without the appropriate DB2_COMPATIBILITY_VECTOR setting (or before DB2 9.7)? TIA for any answer Joachim Joachim, db2upd97 should do the trick If that doesn't work for some reason you could dump them from an ORA style DB2 DB and apply them as a script (there is no magic here). But try db2upd97 first. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW, IBM Toronto Lab Blog: * *tinyurl.com/SQLTips4DB2 Wiki: * *tinyurl.com/Oracle2DB2Wiki Twitter: srielau Hi Serge, unfortunately db2updv97 (DB2 9.7 FP3a) does not work ( even with the - a option). Copying the view definitions from another db was my first idea, but does not work the way I would like to. You cannot create views in schema SYSIBMADM (SQL0553), so I must use another schema and create public synonyms afterwards. That works so far, but these views will not be updated automatically with a next fixpack or version. So there is always some manual work to be done which I (resp. the customer) would like to avoid. Any other ideas? I have spoken to the catalog folks and they think it should work. I assume you set the appropriate bit in DB2_COMPATIBILITY_VECTOR and restarted DB2 before running db2updv97? Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW, IBM Toronto Lab Blog: * *tinyurl.com/SQLTips4DB2 Wiki: * *tinyurl.com/Oracle2DB2Wiki Twitter: srielau |
db2set DB2_COMPATIBILITY_VECTOR=
db2 terminate
db2stop
db2start
db2 create db demo
db2 connect to demo
db2 list tables for schema sysibmadm (shows 74 views)
db2 terminate
db2stop
db2set DB2_COMPATIBILITY_VECTOR=11
db2start
db2updv97 -d demo -a
db2 connect to demo
db2 list tables for schema sysibmadm (still 74 views:-( )
same is true when setting DB2_COMPATIBILITY_VECTOR=ORA
If db2updv97 should work I think I'll open a PMR and quote you if you
don't mind:
"I have spoken to the catalog folks and they think it should work."
Thx
Joachim
#6
| |||
| |||
|
|
"joklassen" <joklassen (AT) web (DOT) de> wrote in message news:5deb2e25-0af1-466a-be94-66c84a6e8b4b (AT) j25g2000vbr (DOT) googlegroups.com... If db2updv97 should work I think I'll open a PMR and quote you if you don't mind: "I have spoken to the catalog folks and they think it should work." Thx Joachim |
#7
| |||
| |||
|
|
"joklassen"<joklassen (AT) web (DOT) de> wrote in message news:5deb2e25-0af1-466a-be94-66c84a6e8b4b (AT) j25g2000vbr (DOT) googlegroups.com... If db2updv97 should work I think I'll open a PMR and quote you if you don't mind: "I have spoken to the catalog folks and they think it should work." Thx Joachim I don't think he said it was a support feature. PMR is fine, this should indeed work. |
Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau
#8
| |||
| |||
|
|
On 4/19/2011 10:21 AM, Mark A wrote:>> "joklassen"<joklas... (AT) web (DOT) de> *wrote in message news:5deb2e25-0af1-466a-be94-66c84a6e8b4b (AT) j25g2000vbr (DOT) googlegroups.com.... If db2updv97 should work I think I'll open a PMR and quote you if you don't mind: "I have spoken to the catalog folks and they think it should work." Thx Joachim I don't think he said it was a support feature. PMR is fine, this should indeed work. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW, IBM Toronto Lab Blog: * *tinyurl.com/SQLTips4DB2 Wiki: * *tinyurl.com/Oracle2DB2Wiki Twitter: srielau |
"i've reviewed our source code and the answer is no, db2updv97 can't
create
Oracle data dictionary for migrated databases from previous releases .
This
functionality is enabled only for newly created database in DB2 v 9.7
as
stated in official DB2 documentation."
Cheers
Joachim
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.