Changing target DBMS

Chapter 10 Generating a Database from a PDM

Changing target DBMS

When you create a PDM, you select a target DBMS. If you change target DBMS, the PDM may be altered to become 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.

The Change DBMS Target dialog box appears.

Select a target DBMS from the dropdown listbox in the New groupbox.

Select the Share: Use shared DBMS definition radio button.

or

Select the Copy: Create a copy of the DBMS definition in model radio button.

Click the DBMS Preserve Options tab.

The DBMS Preserve Options page appears. It displays options for database objects that can be preserved or lost when the target DBMS is changed.

Select check boxes for procedures and triggers, database objects, and physical 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.

Clear check boxes for procedures and triggers, objects, and physical options that you do not want to preserve.

Click OK.

A message box tells you the target database has been changed.

Click OK.

The name of the selected database appears in the status bar.