Changing the target DBMS

Chapter 5 Customizing the PDM Environment

Changing the target DBMS

When you create a PDM, you select a target DBMS (see "Creating a PDM" in the Physical Data Model Basics chapter). You can change the DBMS of a model at any time.

You may be required to change the DBMS if you open a PDM and the associated DBMS file has been deprecated, or is otherwise unavailable. In this case the Choose DBMS dialog box will open, inviting you to select a DBMS from the list.

If you change target DBMS, the PDM may be altered to be made compatible with the new DBMS.

Data type change

Changing the DBMS of a PDM may affect the data types of the model. Each DBMS family supports different sets of data types. When you change the target DBMS of a PDM, and select a DBMS from another family, the following process occurs:

First, all data types of the model are converted into conceptual data types
Then, the conceptual data types are automatically converted back into physical data types according to the data type translation table defined in the new target DBMS

For more information on data type conversions, see Extension mechanism for ODBC reverse engineering queries in chapter DBMS Reference Guide in the Advanced User Documentation .

Non supported objects

Some objects exist only in certain DBMS. For example, sequences exist in Oracle 8 but not in Sybase AS Enterprise, join indexes exist in Sybase AS IQ but not in MS Access. The same occurs for triggers and stored procedures.

When you change the DBMS of a model containing such objects, and if the new target DBMS does not support them, these objects are destroyed.

Preservation options

When you change DBMS, depending on which objects are supported by the old and new target DBMS, you can choose to preserve, or to lose, the values associated with the following database objects:

Triggers and stored procedures
Physical options on the databases, tables, indexes, primary and alternate keys, default options, tablespaces, databases and storages
Database objects: databases, storages, tablespaces, abstract data types, sequences
Extended attributes

If a database object is not supported by the old or new target DBMS, the preserve option for that object is grayed and cannot be selected. If the new target DBMS does not support an object, you will lose the values associated with that object when you change target DBMS.

Triggers are rebuilt when a change in DBMS family is made
If you change the target DBMS family, for example from Sybase to Oracle or IBM DB2, triggers are automatically rebuilt.

Syntax incompatibility

When you change target DBMS there can be a syntax incompatibility between physical options.

If there is a syntax incompatibility, you lose the values for the physical option that is not recognized by the new target database, even if you have selected the check box to preserve that physical option.

Example

If you are using physical option in while using Sybase Adaptive Server Anywhere, and you change target database to Oracle 7 which does not recognize the option in , you will lose all the values associated with that option in your model.

Share and Copy to the model DBMS

When you change the current DBMS, you can choose to connect to a target DBMS in one of the following ways:

DBMS	Description
Share	Current DBMS for the PDM constantly refers to the DBMS definition file stored in the DBMS library. Any modifications made to the DBMS are shared by the PDM
Copy	Current DBMS for the PDM is a unique copy of the DBMS definition file stored in the DBMS library. The current DBMS is independent of the original DBMS definition file so modifications made to the DBMS definition file in the DBMS library are not available to the PDM. The copied DBMS definition is saved with the PDM and can not be used without it

To change the target database for a PDM:

Select Database→Change Current DBMS to open the Change the Target DBMS dialog box:

Select a target DBMS from the list.

Select one of the following radio buttons:
- Share the DBMS definition – use the original DBMS file in the "Resource Files\DBMS" directory. Any changes made to the DBMS are shared by all linked PDMs.
- Copy the DBMS definition in model – make a copy of the original DBMS file in the "Resource Files\DBMS" directory. The current DBMS is independent of the original DBMS, so modifications made to the DBMS in the DBMS directory are not available to the PDM. The copied object language is saved with the PDM and cannot be used without it.
  
  For more information on DBMS properties and customizing a DBMS, see the "DBMS Reference Guide" chapter in the Advanced User Documentation .

Click the DBMS Preserve Options tab, which displays options for database objects that can be preserved or lost when the target DBMS is changed:

Select check boxes for the objects and options that you want to preserve.

Preserve options selected by default
If you are changing target database within a database family, for example between Sybase SQL Server 10 and Sybase SQL Server 11, all preserve options available are selected by default. The check boxes for database objects not supported by the old and new target databases are disabled.

Click OK.

A message box opens to tell you that the target database has been changed.

Click OK to return to the model with the new DBMS.