Oracle9i Designer Migration Guide Part 3. Generating and migrating existing database designs to Oracle9i Designer |
Contents

# Chapter 1 Introduction

This part (Part 3) of the migration guide provides the information necessary for upgrading, to Oracle9i Designer, the database definitions that you designed and generated using earlier releases of Oracle Designer.

Part 3 discusses database migration from the following earlier Designer releases:

- 1.3.2
- 2.1.2
- 6.0

Part 3 assumes that you have already installed Oracle9i Designer and migrated your repository. (See instructions in Part 2 of this Migration Guide.) Part 3 then explains steps you have to take so that you can:

- generate your database design definitions from Oracle9i Designer and achieve the same generated results you had from earlier releases, and
- take advantage of new features that have been added to Oracle Designer since your previous release.

Throughout the document, special mention is made of any migration issues known at the time of publication of this document.

Note that this Part 3 fully replaces an earlier published database migration guide for Oracle Designer 2.1x/6.0 and Designer 6i (October 2000).

Part 3 starts with an introduction (Chapter 1) that gives an overview of the possible migration scenarios for database objects:

- Scenario 1. Migrate, Regenerate All, No Redesign, and
- Scenario 2. Migrate, Regenerate All, With Redesign.

Subsequently, Chapter 2 "New database features" describes all changes/new features since 1.3.2 with respect to database design that you need to know to execute your migration steps for either scenario 1 or scenario 2. The new features chapter is structured by specific Oracle Designer release: Migrating from Designer 1.3.2, Migrating from Designer 2.1.x, etc.

Chapter 3 discusses general database migration issues, integration steps for database objects you always have to consider despite your database scenario.

Chapter 4 "Scenario 1. Migrate, Regenerate All, No Redesign" describes the necessary regeneration steps for scenario 1.

Chapter 5 "Scenario 2. Migrate, Regenerate All, With Redesign" describes in addition specific redesign steps for database objects.

Note that Part 4 "Forms Migration" and Part 5 "Web PL/SQL migration" is organized with a similar structure.

This part of the migration guide, for database objects, continues where the second part of the migration guide has stopped. It is considered that you have completed all the steps from the second part: your Oracle9i Designer repository contains freshly migrated structured objects (e.g. database objects) and files. Note that it does not make any difference for this part if files and/or structured objects are checked in ("versioned") or just stored.

The database objects in Oracle9i Designer are compliant with Oracle8i, i.e. you are able to store, version, generate and - partly - visualize all your database specifications in Oracle9i Designer into an Oracle8i database and at the same time make use of specific Oracle8i functionality. Note that at the same time you are still able to generate Oracle7 and Oracle8 syntax.

There is a lot more new functionality introduced in the Oracle 8i database, specifically if you are coming from Oracle 7.3 (Oracle Designer 1.3.2 was compliant with Oracle 7.3).

For example Oracle 8.0 has introduced the following new features:

- Partitioned Tables
- Partitioned Indexes
- Oracle Object Types
- Object Tables
- Object Views
- Oracle Collection Types
- Nested Tables
- Deferrable constraints.

You could already define the above new database features in Oracle Designer 2.1.x /6.0.

Oracle 8i (aka release 8.1.6 or above) has introduced for example - on top of Oracle 8.0 - the following (sub-set of) new features:

- Index-only Tables
- function-based indexes
- Java in the database
- advanced queuing
- compute statistics option for indexes
- deterministic clause for PL/SQL functions.

Note that advanced queuing was already available in Oracle 8 (Enterprise Edition). However, you could not document queue definitions directly in Oracle Designer 2.1/6.0, let alone generate code to populate the queues.

All these new features are available somewhere in new database objects (e.g. object views) and/or in the properties of existing database objects in the Oracle Repository, either on the primary level (e.g. Table properties) or on the secondary level (column properties). You should be aware that these new features are not automatically enabled by the migration wizards of Oracle Designer 6.0 and Oracle9i Designer.

Your migration activities for database objects to Oracle 8i may be ranked in the following categories:

- Update the freshly migrated database objects by adding references and/or associations, change default values or set specific values.
- Add new relational and object database object definitions.
- Replace existing relational definitions by object definitions.

This part (Part 3) of the migration guide for database objects focuses specifically on the first category of post-migration changes. If your category of changes is more like the second and/or third category then at least the following sections from the Oracle 8i documentation provide valuable support:

- Oracle 8i Concepts, Part No. A76965-01
- Oracle 8i Application Developer's Guide - Object Relational Features, Part No. A76976-01
- Oracle 8i Data Warehouse Guide, Part No. A76994-01
- Oracle 8i Enterprise JavaBeans and CORBA Developer's guide, Part No. A81356-01
- Oracle 8i Java Developer's Guide, Part No. A81353-01.

For more Oracle 9i new object-relational-xml features, see the overall PDF index document index.pdf in the root directory of the Oracle 9i documentation.

Assuming that your database migration is likely of the first category (update the freshly migrated database objects by adding references and/or associations, change default values or set specific values), the following migration scenarios are possible in bringing your Designer-generated applications forward into Oracle9i Designer:

- Scenario 1. Migrate, Regenerate All, No Redesign
- Scenario 2. Migrate, Regenerate All, With Redesign

Note however that there are general database migration steps, steps you always have to apply despite your source Designer version. These generic steps are discussed in Chapter 3 "General database migration issues".

## Scenario 1. Migrate, Regenerate All, No Redesign

In this scenario, you will regenerate all database definitions of your entire application from Oracle9i Designer The goal of this scenario is to be able to generate your application out of Oracle9i Designer and achieve the same results you got when generating out of your previous Designer release. No attempt is made to redesign your existing application to make use of new relational features available in Oracle9i Designer.

This scenario has the following characteristics:

- Scenario 1 is fast and requires minimal changes in the database definitions within your application's Oracle Designer repository
- Scenario 1 does not take advantage of any new relational features in Oracle9i Designer. It is merely a 'technical' upgrade.

This scenario is appropriate when:

- your application is already in production
- your application is stable, no major functional modifications are expected
- maintenance is limited to simple bug fixing
- your physical database definitions are 100% derived from - or 100% generated from - the Designer repository, or post-generation modifications are minor.

## Scenario 2. Migrate, Regenerate All, With Redesign

In this scenario, you will regenerate all database definitions of your entire application from Oracle9i Designer. As you regenerate each database definition, you will make use of new relational features as appropriate.

The goal of this scenario is to take advantage of the new features available in Oracle9i Designer. As with Scenario 1, you want to be able to generate your database definitions and get the same database structure from your previous release of Designer. However, several new database features have been added to Designer to improve performance (e.g. function based indexes) or functionality (e.g. multiple database implementations).

This scenario has the following characteristics:

- Scenario 2 requires modifications in several database definitions, and is therefore more time-consuming
- Scenario 2 fully leverages the new database features in Oracle9i Designer.

This scenario is appropriate when:

- your application is still in development
- your application is in production, but major functional modifications are to be made, or expected
- your application requires modifications that can only be implemented using new functionality in Oracle9i Designer.

Note that all scenarios result in an actual usage of Oracle 9i and Oracle9i Designer and therefore you will be optimally served by Oracle Support on your Oracle tool stack.

# Chapter 2 New Database Features

Depending on which Designer release you are coming from, many features of Oracle9i Designer may be new to you.

This chapter presents a brief overview of new features that are of particular interest when migrating your database design from previous Designer releases. It is by no means an exhaustive list of all new features, and it does not try to explain each new feature in detail. Rather, it introduces the relevant features and points you to where you can find more information in the Oracle9i Designer online help.

The terminology of the different database element types is varied among the different Designer releases. The following table gives an overview of a subset of the available database object types in Oracle Designer 1.3.2, Oracle Designer 2.1.x/6.0 and Oracle9i Designer.

Database objects since Oracle Designer 1.3.2

# | Oracle Designer 1.3.2 | Oracle Designer 2.1.2/6.0 | Oracle9i Designer
| TABLE | TABLE | TABLE DEF.
| VIEW | VIEW | VIEW DEF.
| SNAPSHOT | SNAPSHOT | MAT.VIEW DEF.
| SEQUENCE | SEQUENCE | SEQUENCE DEF.
| CLUSTER DEF. | CLUSTER DEF. | CLUSTER DEF.
| PL/SQL MODULE | PL/SQL DEF. | PL/SQL DEF.
| <NA> | OBJ. TYPE | OBJ. TYPE
| <NA> | COLL. TYPE | COLL. TYPE
| <NA> | OBJ. TABLE | OBJ. TABLE DEF.
| <NA> | OBJ. VIEW | OBJ. VIEW DEF.
| <NA> | <NA> | JAVA DEF.
| <NA> | <NA> | QUEUE DEF.
| <NA> | <NA> | QUEUE TABLE DEF.

Note that you could not define all properties of Object Tables and Object Views in Oracle Designer 2.1.x/6.0.

This chapter is organized by Designer release. You should begin reading at the section for your "from" Designer release, and then continue reading the sections for any later releases. For example, if you are migrating from Designer 1.3.2, you need to read all four sections below. If you are migrating from Designer 2.1.2, you may skip the section on Designer 1.3.2 and read the sections for Designer 2.1.2 and 6.0.

## Migrating from Designer 1.3.2

The following new Oracle9i Designer database features are applicable if you are migrating from Designer 1.3.2:

- Design Editor
- Database implementation
- Obsoleteness of the create property
- Usage of the batch generation and batch design capture
- Database capture changes (previously known as "reverse engineering")
- Separate menu for generating administrative objects

### Design Editor

The functionality provided by individual Designer Release 1 design level tools is now incorporated into a single tool called the Design Editor. Each of the Designer Release 1 tools maps onto a Design Editor component. It is essential that you understand how to use the Design Editor before beginning your migration.

Release 1.x tool | Release 2.x Design Editor components
RON | Design Editor Navigator The Design Editor Navigator is a key component within the Design Editor. It is similar to the RON, but contains only objects specific to the design phase. You can use drag and drop, instead of clicking on menu options or toolbar buttons, to perform a wide range of tasks. For example, you can create a server model diagram from one or more table definitions by dragging the tables from the Design Editor Navigator component onto the worksurface. In addition, the database definitions within the Design Editor Navigator are spread across three DE tabs: Server Model - contains all “pure” database definitions like table definition properties (alias) columns, constraints and triggers DB Admin - handles the database implementation properties of existing data database definitions like storage, tablespaces and access rights Distribution - handles typical database distribution aspects like nodes, replication and (public) database links Note that at the same time all DDL generators are removed from the RON. You have to use the DE to generate DDL scripts.
RON Property Palette | Design Editor Property Palettes and Property Dialog boxes When you create and edit Repository definitions in the Design Editor, you can use either Property Dialog boxes (new for Release 2) or Property Palettes. Property Dialog boxes are wizard-style elements that walk you through complex tasks. They are especially useful when creating whole new tables and columns as they walk you through all of the required tasks in order. They are also useful as a tool for learning Designer. Property Palettes provide you with a direct way of entering/editing information for all of the properties that exist for an object. Property palettes are the quickest way of setting properties for existing objects, because all properties are displayed in a single palette.
Server Model Diagrammer | Server Model Diagrams Server Model Diagrams are created by dragging one or more tables from the Design Editor Navigator to the worksurface.
Preferences Navigator | Preferences Palette Preference values are set using the Preference Palette within the Design Editor. Note that you can define Server Generator preferences only at application level.
Module Logic Navigator | Logic Editor You can directly launch the module logic navigator - using the right mouse button option Edit logic - for a PL/SQL function, package or procedure. As a result a separate text editor is launched to add or modify the PL/SQL code.
Module Structure Diagrammer | Module Network/ PL/SQL Composition viewer A module network viewer enables you to display module networks horizontally in the Design Editor Navigator. This allows you to clearly see the relationships between the PL/SQL modules in a network.

For more information about the Design Editor, see also the Design Editor online help topic “Features added in previous releases”.

#### Database implementation

Designer 2.1.x has introduced a significant change in the meta-model for database objects also known as database implementation. To fully understand the significance of this change, first a description of the meta model of database objects in Designer 1.3.2 will be given, followed by a description of the database meta-model of Designer 2.1.x and above.

#### Database meta model of Oracle Designer 1.3.2

In Oracle Designer 1.3.2 - in the context of database implementation - the following elements were defined as Primary Access Elements:

- Database
- User
- Group (database roles)
- Relation definition (database implementation properties)
- Tablespace
- Rollback segment
- Storage definition.

At the same time the following elements were defined as Secondary Access Elements or association:

- Database objects grants for users
- Database objects grants for roles.

The above database elements and their category (PAC or SAC) are depicted in the figure below: Database meta model in Oracle Designer 1.3.2.

#### Database meta model since Oracle Designer 2.1.x

Since Oracle Designer 2.1x (and therefore also in Oracle9i Designer) only the following elements are defined as Primary Access Elements - within the context of database implementation:

- Database
- Storage definition.

At the same time, the following elements are defined as Secondary Access Elements or associations:

- User
- Group
- Relation definition implementation (database implementation properties)
- Database objects privileges for users or groups
- Tablespace
- Rollback segment.

The above database elements and their category (PAC or SAC) are depicted in the figure below: Database implementation meta model in Oracle9i Designer.

Note that this meta model change was introduced in Oracle Designer 2.1.

This significant meta model change (most database implementation elements and their properties are now defined in the context of a database) has the following consequences:

- Uniqueness. The above listed secondary access elements (and/or associations) are unique only within the context of a specific database. For example the user 'Scott' can exist within the context of database1 and also within the context of database2.
- Check-out and check-in context. Any change to the database properties and/or to its secondary access elements or associations must be preceded with a check-out, if you have an enabled repository version of the database. The check-in/check-out context is extensive since there are so many secondary database implementation properties. For example a change in a relation definition implementation property like a storage clause must be preceded within a check-out of the owning database.
- Short-cuts (aka shares). You cannot short-cut (or share) a secondary access element, or association, separately. You can only short-cut primary access elements: the database and storage definitions. As a consequence, if you short-cut the database you also implicitly short-cut its secondary elements and associations without the possibility to reference these secondary elements individually.
- Post-migration database objects steps consequences. The migration wizard of Designer 2.1/6.0 will create specific migration database definitions like a R2_UPGRADE_DATABASE database and a database user R2_UPGRADE_USER. We will deal with these steps in Chapter 3 "General Migration Issues".

Note that the above mentioned consequences were also, to a certain extent, applicable to Oracle Designer 2.1/6.0. In these previous Oracle Designer releases you should replace check-in and check-out context with 'working context' and short-cuts with shares.

In Oracle9i Designer it is no longer necessary to share an element from another container (application system) first and then reference the shared element in the context container. You can reference an element from another container immediately as long as it is 'visible' in the same workarea. In addition you can make the referenced elements more 'visible' by creating short-cuts in the context container for these elements. Another advantage of creating short-cuts is that you "publish" the previously defined elements. It will most certainly stop you from creating a duplicate element.

Note also that the “migration wizard” of Designer 2.1.x and/or Designer 6.0 translates your data model to specific database implementations. These specific translations are described in detail in the next chapter.

### Obsoleteness of the Create? property

The Create? property for each primary database object is no longer available, since Oracle Designer 2.1.x. For example you could use this property as a logical removal indicator. Instead of physically removing a specific database element you could set the create property to “No”. Similar behavior in Oracle9i Designer is available using database implementation (see also Chapter 2 “General database migration issues”).

Note that the Create? property on a secondary level like columns and constraints is transformed into the Complete? property. The migration wizard brings the Create? property value forward into the Complete? property value. Subsequently the Database Object Generator will not generate the syntax for secondary elements with a Complete? property set to 'No'.

### Use of batch generation and batch design capture

Oracle Designer 2.1.x has introduced a batch utility for database object generation and design capturing that is available from the Tools menu (Tools > Batch Generate and Tools > Batch Design Capture).

These batch generators could be applicable in the following circumstances:

- To create a persistent set of database objects in a specific workarea stored in one or more gbu files.
- To generate DDL files for a persistent set of database objects on a different location and/or on a different point in time.

Batch generation for database objects comprises the following steps:

1. Choose your specific workarea in the Design Editor (File Menu; Change Workarea)
2. Launch the Batch Generate menu from the Tools menu.
3. Choose your container within a specific workarea.
4. Choose the group of objects you would like to generate for a specific session. The following groups within the context of database objects are available: Server Model (SRM); Database Administration (DBA); Module Component API (MAPI); Table API (TAPI); Reference Code (RF).
5. Enable/disable the runtime Database Object Generator options (if applicable) (see Chapter 2 "New Database features", section "Server Generator Options").
6. Highlight a specific user within the context of a specific database.
7. Select your database objects.
8. Save the generation details to a specific 'Database Generation Batch' file with for example the following naming standard <Workarea name>_<container>_SRM/DBA/MAPI/TAPI/RF_<database selection indication> .gbu, e.g. WA_DEVELOPMENT_QMS_SRM_SEL1.gbu. Note that the 'gbu' files only contain parameters, i.e. no database syntax.

   Repeat steps 4 to 8 until no more groups are necessary.

9. Collect all 'gbu' files.
10. Run dwzrun61 from the command line multiple times, for example from a different location, for each group using the specific 'gbu' file to generate all your different kinds of database objects.

You have to re-run the batch generator for database objects each time in the following circumstances:

- different Database Object Generator options
- different set of database objects.

Note that you do not have to re-run the batch generator (regenerate the gbu files) for database objects if one or more database object definitions are changed since it does not contain any syntax.

### Database capture changes

Reverse engineering is called database capture since Designer 2.1.x. Database capture offers the following new functionality:

- General capture preferences and preferences for capturing specific database object types (e.g. views, PL/SQL definitions).
- Views and PL/SQL definitions can now be captured as declarative definitions instead of free-format text.

In addition, since Designer 6i, there is a set of General Capture preferences:

- [RECCOM] Capture Comments
- [RECGRT] Capture Grants
- [RECSYN] Capture Synonyms
- [DTAPIT] Capture TAPI Generated Triggers with Tables

Note that the design capture of database objects may need to be preceded with a check-out of matching objects in a versioned repository.

### Separate menu for generating administrative objects

Oracle9i Designer has a separate tab (introduced in 2.1x) for generating administrative database objects (e.g. Databases, tablespaces). In Oracle Designer 1.3.2 part of these administrative database objects could explicitly (e.g. roles) and implicitly (e.g. database links) be generated from the DDL generator.

The Server Generator for database administrative elements supports - explicitly - the generation of the following administrative elements:

- Databases
- Database links
- Directories
- Profiles
- Replication Groups
- Roles
- Rollback Segments
- Tablespaces
- Users.

## Migrating from Designer 2.1.2 and/or Designer 6.0

The following new Oracle9i Designer database features are applicable if you are migrating from Designer 2.1.x or above:

- Server Generator preferences - introduced in 6i
- Database Generation notes and Server Generator generation tabs
- Overview of Table API changes
- Dependency Analysis - what happened to summary table usages? - introduced in 6i

### Server Generator preferences - introduced in 6i

Designer 6i introduced preferences for the Server Generator. These preferences are however only applicable at application level. You cannot set Server Generator preferences for one or more specific database objects.

The following general Server Generator preferences categories are applicable - available via the Server Generator Product Flavor:

- General
- Generation - General
- Generation -TAPI and view
- Design capture preferences for tables, views, materialized views, PL/SQL definitions, Oracle object types
- Reconcile report preferences

For a detailed overview of Server Generator preferences see the Preference Navigator and the online help.

### Database Generation notes and Server Generator generation tabs

Database generation - implemented via a variety of “Tabbed” windows - is significantly changed since Designer 1.3.2 and some minor changes were introduced since Designer 2.1.x. This section discusses the following Database Generation subjects:

- General Generator Options
- Obsolete Database Generation options
- New or changed database object generation options

#### General Generator Options

Specific general Generator Options that previously were defined as preferences are now available as options accessible from the Design Editor.

You can launch the General Generator Options Windows (see below) from the menu Options > Generate Options > General in order to modify these general settings.

The fields on the above dialog are explained below.

Scope of Code Control Table

The Scope of Code Control and Scope of Reference Code are no longer preferences but settings that you can modify via the General Generator Options dialog (see above). The following scopes are valid for Code Controls:

- Container Wide Table
- Single Table
- Table for each Code Control (new).

The following scopes are valid for References Codes - no changes:

- Container Wide Table
- Single Table.

The 'create table' syntax for the Reference Code Table and the 'insert into ..' statements for the allowable values for reference codes can be generated via the Database Object Generation tab (see below).

Tablespace Of Reference Code Table

You can add to the 'create table' syntax for the Reference Code Table the preferred tablespace by adding here the tablespace name, e.g. QMS_STATIC_TS.

#### Obsolete options for Oracle9i Designer Database object generation options

The following Oracle Designer 1.3.2 DDL options are no longer available in Oracle9i Designer:

- Overwrite files. All files are automatically overwritten, there is no warning if the files already exist.
- Comment syntax. All comment in the DDL files starts by default with '—'.

#### Oracle9i Designer Database Object generation options (target tab)

This section will discuss the options on the target tab of the Generate Server Model Implementations dialog (opened from the Generate menu by selecting Generate > Generate Database from Server Model).

DDL files only option

The Database Object Generator will generate the 'full' DDL files if you enable the 'DDL files only' option. It ignores any existing database definition for a given schema.

Database option

If you enable the database option, then the Server Generator generates DDL scripts to create (or alter) database objects directly against a schema in an Oracle database, using a Net8 connection (using the username, password and connect string). Note that a separate reconcile option is no longer available (since Oracle Designer 2.1.x). You will receive a reconcile file called <pre-fix>.lis automatically that contains a full reconcile report between the Oracle9i Designer definition and the database for the given schema name.

In this mode the Database Object Generator will create specific DDL files based upon existing database definitions for the given schema name. For example if the table already exists but not a specific column, then an 'alter table add column …' statement is generated. If you would like the full 'create table …' definition you should choose the 'DDL files only' option.

Stop on error

Indicates whether DDL generation (and execution) should be terminated if an error is detected.

Upload generated files to repository

You can use this option only when the chosen directory - in the directory field - is mapped to the repository and the directory does not already contain downloaded DDL files that are read-only. Note that the files are not saved in the repository if the directory is not mapped. You should careful consider the enabling of an automatic file upload after generation since you cannot control individual files and the DDL generator may stop because of existing read-only files.

#### New Oracle9i Designer Database Object generation options (Options button)

This section will discuss the fields on the Database Generator Options dialog, opened by clicking the Options button on the Generate Server Model Implementations dialog.

Generate Indexes

Indicates whether indexes defined against table/cluster/materialized view definitions are to be generated when the table, cluster or materialized view is generated. Note that you can no longer select specific indexes as in Oracle Designer 1.3.2.

Generate Integrity Constraints

Indicates whether integrity constraints defined against a table are to be generated when the table definition is generated, i.e. primary key constraints, unique key constraints, foreign key constraints and check constraints.

To prevent constraints from being generated when a table is generated, uncheck this option.

Automatic Creation of REF_CODES

Indicates whether reference code tables containing domain/column allowable values are to be generated automatically. A create table block in the '.tab' file for the reference codes and a separate insert script (<pre-fix>.avt) with the reference code allowable values is generated if you enable the automatic creation of REF_CODES.

Foreign Key Generation Requires Join Table

Indicates whether one or both of the tables involved in a foreign key must be selected for it to be generated. Uncheck this option if you want all foreign keys definitions, independent of your context.

Note that if you previously relied on foreign keys always being generated when generating to file - despite your selection - you will need to uncheck this option.

Generate Triggers

Indicates whether triggers defined against table definitions are to be generated, when the table is generated.

Note that you can no longer select specific listed triggers as in Oracle Designer 1.3.2. You could however enable/disable specific triggers via the "Complete?" property of a trigger. Trigger syntax will not be generated if you disable the "Complete?" property.

Generate Grants and Synonyms for Users and Roles

Indicates whether any GRANT privileges and SYNONYMS defined for an object are to be generated to other users and roles. If a privilege has been granted for a user or role in another schema, a CREATE SYNONYM statement is generated for the object so that the object can be uniquely identified across different schemas. Note that you cannot select specific user grants and role grants as in Oracle Designer 1.3.2 from this Server Generator TAB. Note also that you can generate separately the 'create role syntax' and 'create user syntax' from the Generate Database Administration Objects utility.

Assign Objects to Replication Code

Indicates whether statements to associate database objects with a predefined replication group are to be generated.

If the target for generation is a database, the objects will be created in the replication group on the database. If the target for generation is a DDL script file, statements are generated to place the objects in the replication group.

Note that the replication group itself is generated using the Generate Database Administration Objects utility (see below).

### Table API changes

The Table API creates a set of application-specific PL/SQL API packages that provide insert, update, delete and lock procedures for each application table. In addition it validates the data provided by the calling application and generates default values when appropriate before the table is modified or inserted. Note that a table API - with limited functionality - was already available in Oracle Designer 1.3.2 as part of the Webserver Generator.

The Table API in Oracle9i Designer covers the following functionality:

- validates constraints
  - validates arcs
  - validates allowable values in reference code tables
  - validates allowable values in domain tables
- auto-generates the following column values:
  - unique and sequential values for columns that derive their values from a sequence definition
  - pre-defined default values
  - derived values
  - change history information for AutoGen Type columns such as Created by, Modified by, etc.
- converts column values to uppercase
- maintains journaling information
- maintains denormalized columns

Note that the above overview does not distinguish between API functionality introduced in Designer 2.1 and 6.0 and that introduced in Designer 6i.

You can also add your own TABLE API logic before or after a specific DML operation. The Oracle9i Designer meta model captures the full event model of database logic - introduced in Designer 2.1.x. There are in addition to the table API table API triggers to complete server logic. You can generate the table API and table API triggers via the Generate table API menu.

Note that CDMruleframe (part of the Headstart Utilities) makes extensive usage of the TABLE API and the option to add specific application logic. For more information about CDMruleframe see www.otn.oracle.com.

### Dependency Analysis or what happened to summary table usages?

Since Designer 6i a Dependency Manager tool has been introduced that allows you to store (additional) dependencies between structured elements and files and vice versa in a complete separate table structure - separate from the “normal” table structure for structured elements like tables, views, PL/SQL definitions, etc. Note that you also store dependencies between structured database elements only (like tables and pl/sql definitions) or dependencies between files only (like install scripts) and files that contain the syntax of a package. In general, dependency analysis gives you more control of your development and deployment environment by allowing you to efficiently manage your application development and release environment, thereby ensuring higher quality software.

You could ask yourself "Why do I need additional dependency information for database objects? They are already stored in a structured way, aren't they?". Yes they are, but not all dependencies are stored atomically. The table usage for PL/SQL definitions and the database object usage for files are examples of this.

As a consequence of the introduction of the Dependency Manager, summary table usages for PL/SQL modules (and other modules like Forms or Reports) are no longer available and you have to realize that the Oracle9i Designer migration wizard does not bring forward previously stored summary table usages as dependencies. For example, you have to rebuild the dependencies for packages - previously stored in the summary table usages - via the dependency manager. You will however receive much more dependency information than table usages only (e.g. procedure and function calls).

For more detailed information you can consult the online help for the Dependency Manager

# Chapter 3 General Migration issues

There are a number of actions you must take regardless of which migration scenario you choose.

This chapter is organized by Designer release. You should begin reading at the section for your "from" Designer release, and then continue reading the sections for any later releases. For example, if you are migrating from Designer 1.3.2, you need to read all sections below. If you are migrating from Designer 2.1.2, you may skip the section on Designer 1.3.2 and read the sections for Designer 2.1.2 and 6.0.

## Migrating from 1.3.2

This section covers general database migration issues when migrating from Designer Release 1.3.2. If you are migrating from a later release, you may skip this section.

### Multiple database implementations

Oracle Designer 2.1.x has introduced a distinction between database implementation independent properties and dependent properties of database objects - see also chapter 2 of this part (Part 3). All database implementation dependent properties are defined within the context of a database against a specific schema (e.g. the storage definition property of table implementation).

At the same time, all database implementation independent properties are - still - defined against the database objects (e.g. the alias property of a table).

This distinction between database implementation dependent and independent database properties allows you to define multiple implementations of the same database object. For example, you could introduce a schema in a specific database that represents a 'light' table implementation (test purposes) with associated tablespaces and (small) storage definitions. At the same time you could define another schema in another database that represents a 'production' table implementation with much larger tablespaces and storage definitions. Such a distinction was not available in Oracle Designer 1.3.2.

The migration wizard from Oracle Designer 2.1.x (or Oracle Designer 6.0) creates for each database object a database implementation. It creates a specific database (R2_UPGRADE_DATABASE) and a specific user (R2_UPGRADE_USER) during the migration process. The R2_UPGRADE_DATABASE will contain all existing users and a special user called R2_UPGRADE_USER. Subsequently the user R2_UPGRADE_USER within the existing database(s) contains one or more database objects and the associated database implementation properties if you have defined one or more database implementation properties in Oracle Designer 1.3.2.

We highly recommend that you reevaluate this migration result with respect to databases and database schemas. For example you could rename the migration database name R2_UPGRADE_DATABASE - or add another database - to a more meaningful name, reflecting the data collection within that database. Subsequently you could rename the upgrade user R2_UPGRADE_USER - or add another schema - within the database to a more meaningful schema name, reflecting the database elements owned by that schema.

These actions should take place at the 'DB admin' tab in the Design Editor. Subsequently you can create another database implementation - by creating a database schema in another database - if you need an additional database implementation for a specific database object.

It is however not necessary to create databases in Oracle9i Designer for each (promotion) database. The database implementation properties in each promotion database may not necessarily be different. For example the storage clause for a specific table in the test database may be the same as in the acceptance database.

Note that you can still generate a 'skeleton' DDL script - a script without database implementation syntax - from the 'Server Model' tab in the Design Editor. Such a 'skeleton' script does not contain database implementation properties as tablespaces or storage clauses and may be useful for development purposes.

### Obsoleteness of the Create? property

The Create? property for primary database objects (e.g. tables, views) has been removed while the create property at the secondary level (e.g. constraints, columns) is brought forward in the “Complete?” property. With the latter set to 'N' you circumvent the DDL creation of this secondary element.

You may adopt the following strategy for primary database objects to simulate a Designer 1.3.2 behavior with respect to the Create? property for primary database objects. First associate all database objects with a database schema or user within the context of a specific database. Secondly get a list of all database objects with the create property set to 'N' from your Designer 1.3.2 repository. Thirdly remove these database implementations from your database user. Subsequently generate only in the context of the DB admin tab - thus database implementations only. Note that this strategy has the disadvantage that your generated DDL will always contain implementation syntax like references to tablespaces and/or storage clauses.

## Migrating from 2.1.2 or 6.0

This section covers general migration issues when migrating from Designer Release 2.1.2 or 6.0. These issues can also affect migrations from 1.3.2. The upgrade from these two releases is the same. If you are migrating from a later release, you may skip this section.

### Server Generator preference PARSER and consequences for the generated syntax of PL/SQL definitions in combination with new pl/sql property Private Declaration

Oracle9i Designer comes with a set of server generator preferences - at application level only - as was already mentioned before. One of the Server Generator preferences - PARSER at the “Generation general” node - influences the generated syntax significantly by adding for example “BEGIN”, “END” and “DECLARE” strings in the PL/SQL code. The default value of this preference is set to "‘N" and this works fine for all free format PL/SQL definitions (packages, procedures, functions and triggers) - see also the section below about PL/SQL definitions and their storage methods. However if you have one or more PL/SQL definitions stored with the structured or non-free format method, then the PARSER Server Generator preference should be set to 'Y' and in addition you may have to move (parts of) PL/SQL code - specifically the declaration section - from the PL/SQL block property to the new Private Declaration property.

For example the correct trigger syntax is generated if you set the preference PARSER value to 'N' and if you move the following lines in the PL/SQL block property to the Private Declaration property:

    l_rowid rowid := qms_rowid_queue.qms_get_rowid;

     l_empno qms_emp.empno%type;

      l_mgrno qms_emp.mgr%type;

      l_job qms_emp.job%type;

      cursor c_emp (p_rowid in rowid)    is

         select emp.empno

               ,emp.mgr

               ,emp.job

         from   qms_emp emp

         where  emp.rowid = p_rowid;

These lines together with the remainder in the PL/SQL block property is constructed by the DDL generator as:

    PROMPT Creating Trigger 'QMS_EMP_AS'

    CREATE OR REPLACE TRIGGER QMS_EMP_AS

     AFTER DELETE OR INSERT OR UPDATE

     ON QMS_EMP

    DECLARE-- PL/SQL Specification

    l_rowid rowid := qms_rowid_queue.qms_get_rowid;

      l_empno qms_emp.empno%type;

      l_mgrno qms_emp.mgr%type;

      l_job qms_emp.job%type;

      cursor c_emp (p_rowid in rowid)    is

         select emp.empno

               ,emp.mgr

               ,emp.job

         from   qms_emp emp

         where  emp.rowid = p_rowid;

    -- PL/SQL Block

    begin

      while l_rowid is not null    loop

        begin

          open c_emp(l_rowid);

          fetch c_emp

          into   l_empno

                ,l_mgrno

                ,l_job;

          close c_emp;

        end;

        if inserting or updating    then

           if not qms_has_job_mgr(l_mgrno)    then

              /* raise error stating    the employee is not a real manager */

              raise_application_error(-20000,'QMS-00049');

           end if;

        end if;

        if updating and l_job <>    'MANAGER' then

           if not qms_manage_emp(l_empno)    then

              /* raise error stating    the job cannot be changed because

                 the employee still    manages some employee */

              raise_application_error(-20000,'QMS-00050');

           end if;

        end if;

        l_rowid:=qms_rowid_queue.qms_get_rowid;

      end loop;

      qms_rowid_queue.qms_clear_array;

    exception

       when others then

         qms_rowid_queue.qms_clear_array;

         raise;

    end;

    /

### Short-cut or reference strategy for database objects

The scope of short-cutting or referencing a database in Oracle9i Designer - previously known as sharing - has become much wider than sharing/referencing a database in Oracle Designer 1.3.2. Short-cutting a database in Oracle9i Designer not only implicates a single database short-cut but also a short-cut of all its secondary elements and associations.

In Oracle Designer 1.3.2 you could share a single database - captured in a single application system - with multiple other application systems indicating that all these application systems are implemented in a single database. Your generic share model could like this:

In the above example only one database is defined and shared out to the other application systems. At the same time the entity DEPARTMENT and the table DEPARTMENTS is shared with another application system. More specifically, application APPDEPT owns table DEPT and shares this table with APPEMP, that owns table EMP and receives the shared table DEPT. In addition table EMP has a foreign key to DEPT. Both applications receive a database share from application APPDES2000. This application provides only shares. The share model described above reflects that database objects from APPDEPT and APPDEPT are implemented in the same database. Moreover sharing DES2000 across these application systems was the only way to enforce the generation of the foreign key syntax for EMP with DEPT via the DDL generator since the foreign key definition is defined within the context of two applications. You could not generate the foreign key syntax if EMP and DEPT did not share the same database. This behavior is no longer implemented in Oracle9i Designer. A foreign key relation between table EMP and the shared table DEPT is enough to generate the foreign key syntax.

If you decide to keep this share model - or short-cut model in Oracle9i Designer - then all database implementation properties for database DES2000 are owned by application APPDES2000. If you want specific database implementation deviations for table EMP - e.g. another storage clause - then either you must create a local database in APPEMP or you must create another database in application APPDES2000.

You can keep the short-cut model described above if you exactly want to reflect the database situation in Oracle9i Designer and at the same time accept that all database implementation properties are owned by application APPDES2000.

You can circumvent the above situation by creating a local database in each application - each could have the same database name - and subsequently define the database implementations locally as opposed to centrally. You are however highly recommended to implement this specific database implementation model in Oracle Designer 1.3.2 before starting the migration steps as described in Part 2 of this migration guide. You could use Oracle Echo to reorganize your database properties effectively. Oracle Echo is a consultancy tool that you can use for reorganization purposes that comes with a consultancy service called 'repository reorganization services'. You can contact your local Consultancy Sales Representative for more information about this service and other Oracle Designer/Repository related services.

### Storage method of PL/SQL definitions

PL/SQL definitions - introduced in Oracle Designer 2.1.x. and known as PL/SQL Modules in Designer 1.3.2 - can be stored in the repository in the following ways:

- Non free format - semi-structured. This method is automatically chosen if you capture one or more PL/SQL definitions from the database. The following PL/SQL PAC and SAC elements and their properties are used to store PL/SQL definitions semi-structured:
  - PL/SQL definition, property package specification: it contains only comment, the specification syntax is determined by the program units and their arguments
  - PL/SQL definition, property private declaration: it contains the private part of package body, function or procedure
  - PL/SQL definition, property PL/SQL block: it contains the PL/SQL text for functions and procedures
  - PL/SQL definition, property pragma restrictions: it is based upon pragma restrictions in package specification
  - Sub-program units: they are based upon functions and procedure sections in the package body
  - Arguments: they are based upon parameters in functions and procedures.

> Note that program data is not recognized separately, but populated in the private declaration section and also that there is no separate property for the pragma 'autonomous transaction'. The pragma 'autonomous transaction' must be defined within the private declaration section.

- Non free format - fully structured. The PL/SQL definition is further broken down - manually - into the following elements - on top of the semi-structured method:
  - Sub-program units of type cursor
  - Program data - or local variables.
- Free format. The PL/SQL definition is stored unstructured in one or more text items. For example the package specification is stored in the text item PL/SQL specification and the package body in the text item PL/SQL block.
- In an uploaded file or files. As two separate files that contain the PL/SQL definition of the package specification (e.g. ECHO_UTIL.pks) and another file containing the package body (e.g. ECHO_UTIL.pkb) of the package ECHO_UTIL or as a single file containing specification and body.

The following table gives an overview of the advantages and disadvantages of the above mentioned PL/SQL definition storing methods:

Dimension | Semi-structured | a/d | Structured | a/d | Free format | a/d | As file(s) | a/d
Dependencies | Run Dependency Analyzer for additional usages (e.g. tables, views) | + | Run Dependency Analyzer for additional usages (e.g. tables, views) | + | All dependencies via Dependency Analyzer | - | All dependencies via Dependency Analyzer | -
Multi user | yes, multiple developers can build or change the package components | + | yes, multiple developers can build or change the package components | + | no, only one developer can build or change the package | - | no, only one developer can build or change the package | -
Reusability | Partly reusable components | +/- | e.g. Functions, Procedure, Cursors | + | No reusability | - | No reusability | -
Editor | via the Design Editor on - less multiple places | +/- | via the Design Editor on multiple places in different formats | - | via Design Editor either in text items PL/SQL block and/or specification | + | No usage of Design Editor. You do not have to edit the package on multiple places, but directly in a text editor | +
Productiveness | Little more productive in build phase | + | Probably less, changes must be defined declarative | - | Little more productive in build phase | + | Most productive in the build phase | +
CDMRule frame integration | Structured integration. You could include generated CDMrule PL/SQL definitions in text items and CDMrule could include custom PL/SQL definitions | + | Structured integration. You could call generated CDMrule PL/SQL definitions and CDMrule could call custom PL/SQL definitions | + | Non structured integration. You could include generated CDMrule PL/SQL definitions in text items and CDMrule could include custom PL/SQL definitions | - | Non structured integration. You could include generated CDMrule PL/SQL definitions in text items and CDMrule could include custom PL/SQL definitions | -
Compilation | Generates separate files. Compilation of body only is an option | + | Generates separate files. Compilation of body only is an option | + | Generates separate files. Compilation of body only is an option | + | Compilation of body only if necessary | +
Usage of Headstart utilities | Full usage of Headstart utilities | + | Full usage | + | Limited usage of Headstart utilities | - | No usage of Headstart utilities | -
Implementation properties (e.g. grants to users and/or roles) | Structured storage of implementation properties | + | Structured storage of implementation properties | + | Structured storage of implementation properties | + | Additional effort to store implementation properties like access rights | -
Usage of pragma autonomous transaction [see note below] | in Private Declaration text property | + | in Private Declaration text property |  | In one or more PL/SQL blocks in the package body | + | In one or more PL/SQL blocks in the package body | +
Migration effort | A move of PL/SQL blocks to private declaration property | - | A move of PL/SQL blocks to private declaration property | - | No migration effort | ++ | Capture in one or more files and subsequently uploaded | +

Note: The usage of the pragma autonomous transaction is very useful, for example for error handling. You can commit your log and error messages to a specific table independent of the state of your main transaction (failure or success).

As can be seen from the last row or dimension in the above table the migration effort will vary for each of the described storage methods for PL/SQL definition. There is almost no migration effort involved if your PL/SQL definitions were stored as free format, while most effort is involved with the structured or semi structured format.

In previous Designer releases there was a strong tendency to use the structured or semi-structured format - the latter is still the default during design capture. With the introduction of the Dependency Manager there is less need for (semi)-structured format. The impact analysis based on files is almost as rich as the (semi)-structured format. Note however that the file format does not support the structured storage (and generation of grant scripts) of access rights against PL/SQL definitions .

### Usage of the dependency manager to bring forward the summary table usages

The summary table usages are replaced by the output of the Dependency Manager as can also be seen from the table presented above - in the row dependencies. You therefore have to parse all dependencies for all database objects - including PL/SQL definitions - after a migration to Oracle9i Designer - to retrieve similar functionality. Note that you can parse dependencies for an entire container and its content or even for an entire workarea.

# Chapter 4 Scenario 1: Migrate, Regenerate All, No Redesign

In this scenario, you will regenerate your database objects from Oracle9i Designer.

The goal of this scenario is to be able to generate your database objects out of Oracle9i Designer and achieve the same results you got when generating out of your previous Designer release. No attempt is made to redesign your existing application to make use of new features available in Oracle9i Designer.

This chapter assumes that you already have performed all the actions against the database objects described in Chapter 3 "General Migration Issues".

## Migrating from 1.3.2

This section covers migrating from Designer Release 1.3.2. If you are migrating from a later release, you may skip this section.

There are no known specific migration issues arising from Designer 1.3.2. - provided that you have applied the steps described in the sections "Migrating from 1.3.2" and "Migrating from 2.1.2 or 6.0" in the General Migration Issues chapter of this part.

## Migrating from Designer 2.1 or 6.0

First perform all the steps described in the section "Migrating from 2.1.2 or 6.0" in the General Migration Issues chapter of this part.

Secondly evaluate the following identified migration issues with respect to database objects coming from Designer 2.1 or 6.0.

### Free format View DDL creation errors

The DDL of free format defined views - and materialized free format views - may result in syntax errors like a missing "from" clause. You can correct the view definition - and therefore ultimately the DDL syntax - by reevaluating the view select text property. Next to the “select” lines, add the “from” and the “where” clause.

#### Different handling of quotes in the column default property value

The behavior of the DDL generator with respect to default column values is changed when quotes are used around the default property value,e.g. ‘Y’, ‘N’. If you want to keep the literal string (including the quotes) in the generated DDL you have to set the next column property Default value type to “literal”. The DDL generator generates the following syntax:

… ,JOB VARCHAR2(9) DEFAULT '''P''' NOT NULL

…

If you do not want the quotes you have to set the next column property Default value type to “Database function call”.

The DDL generator generates then the following syntax:

…

,JOB VARCHAR2(9) DEFAULT 'P' NOT NULL

…

Note that migration wizard set this “Default value type” property default to literal.

### Differences in names for valid values constraints

The database object generator shows a different behavior in generating valid values constraints. It generates for each valid value constraint a separate check constraint - this behavior was introduced in Designer 2.1.2. - and it generates a unique constraint name for each DDL session - introduced in Designer 6i. A valid value constraint for example can be enforced via a domain with allowable values.

This new behavior will most likely result in a recreation of the valid value constraints each time you generate DDL syntax against an existing schema that already contains all or part of the database objects (provided obviously that these database objects contain valid values). Note that this DDL behavior is persistent despite any changes in the valid values.

There are two ways to circumvent this behavior and as a result get more control over these kinds of constraints:

1. Create custom explicit check constraints in the repository with the same check syntax as the generated constraint. Note that you then have to revaluate these custom constraints each time the valid values are altered. Note also that you have to disable the following database object generation option: “Generate valid value constraints”.

2. Use the CDM Ruleframe framework to propagate these valid value constraints as separate CDM ruleframe business rules. You also have to disable the “Generate valid value constraints” database object generation option. You can find more information about the CDM ruleframe framework for enforcing business rules on OTN (www.otn.oracle.com)

# Chapter 5 Migrate, Regenerate All with Redesign

In this scenario, you will regenerate your relational and object type database objects from Oracle9i Designer. You will use the new Oracle 9i database features provided by Oracle9i Designer. As you regenerate each database object, you will make use of new features as appropriate.

The goal of this scenario is to take advantage of the new database features available in Oracle9i Designer. As with Scenario 1, you want to be able to generate your application and get the same database layer you got from your previous release of Designer. However, many new features have been added to Designer to make more use of Oracle9i enhancements.

This chapter is organized by Designer release. You should begin reading at the section for your "from" Designer release, and then continue reading the sections for any later releases. For example, if you are migrating from Designer 1.3.2, you need to read all the sections below. If you are migrating from Designer 2.1.2, you may skip the section on Designer 1.3.2 and read the sections for Designer 2.1.2 and 6.0.

Note that the enhancement or redesign steps in this Chapter must be preceded or accompanied by the actions in Chapter 3 and Chapter 4.

You should also note that most of the new features (e.g. partition key entries, table partitions) may not be visible for existing table implementations - for a specific migrated database. These implementations are introduced in Oracle8 or later and your database 'Oracle Version' property is probably still set to Oracle7. However you can make these specific properties and/or entries visible by changing the 'Oracle Version' database property to Oracle 8i or Oracle 9i.

## Migrating from 1.3.2

This section covers migration from Designer Release 1.3.2. If you are migrating from a later release, you may skip this section.

The following new Oracle9i subjects - already introduced in Oracle 8 - are discussed:

- Table and index partitioning
- Bitmap and reverse indexes
- Global and local indexes
- New database triggers properties
- New column properties
- New view database implementation dependent and independent properties
- New materialized view database implementation dependent and independent properties
- Deferrable constraints
- Scope properties
- New grant properties to roles or users

Note that almost all the above listed improvements do not require any structural change and can therefore be classified as cost effective.

### Table Partitioning

Partitioning physically divides your table in horizontal pieces on different locations to boost performance, making use of multiple reads/writes. It is dependent on your frequently used access paths which columns are candidates for partition keys. For example suppose table EMP of Oracle (50,000 employees!) is frequently accessed on LAND_OF_ORIGINATION then that column is a fine partition key candidate. In general you should try to use columns that hold static values since the row is stored initially on a specific physical location. You should try to avoid a table reorganization as a result of an uneven distribution of rows among the partitions. You can find the following secondary table partitioning properties at the implementation level - provided that your database version is set to Oracle8 or higher:

- Table partitions. Tables can be decomposed into smaller pieces called partitions. Partitions are particularly useful where tables have grown so big that they have become difficult to manage. Each table partition definition that is recorded in the repository represents a partition into which a table is to be divided on a database. You can influence the physical storage of the partitions via the “Value Less Than” property. For example appropriate values for this property could be (A-F, G-M, etc.) for the starting alphanumeric character of the LAND_OF_ORIGINATION column in the Oracle EMP example.
- Partitioning key entries. A partition key entry defines the usage of a particular column in the partitioning key of a table or an index. Each partitioning key can be based on one column only. Partition key entries are stored against table implementations or index storages in the repository. They can be created for global index storage definitions, but not local index storage definitions.

The migration wizard does not add a Partition Key Entry nor does it define table partitions for a specific table implementation.

### Bitmap indexes

Bitmap indexes are widely used in data warehousing applications, which have large amounts of data and ad hoc queries but a low level of concurrent transactions. For such applications, bitmap indexing provides:

- Reduced response time for large classes of ad hoc queries
- A substantial reduction of space usage compared to other indexing techniques
- Dramatic performance gains even on hardware with a relatively small number of CPUs or small amount of memory
- Very efficient maintenance during parallel DML and loads

Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space because the indexes can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table. Bitmap indexes can substantially improve performance of queries with the following characteristics:

- The WHERE clause contains multiple predicates on low- or medium-cardinality columns.
- The individual predicates on these low- or medium-cardinality columns select a large number of rows.
- Bitmap indexes have been created on some or all of these low- or medium-cardinality columns.
- The tables being queried contain many rows.

You could transform any non-bitmap (and non-unique) index that fulfills the above criteria into a bitmap index by changing the index type into a bitmap.

### User Object Index Storage property: Index Type - at database implementation level

Indicates whether the storage details are for a local or global index. An Oracle7 index is equivalent to an Oracle8 global index

A B-tree index on a partitioned table can be local or global. Global indexes must be fully rebuilt after a direct load, which can be very costly when loading a relatively small number of rows into a large table. For this reason, it is strongly recommended that indexes on partitioned tables should be defined as local indexes unless there is a well-justified performance requirement for a global index. Bitmap indexes on partitioned tables are always local. See also "Table Partitioning", above, for further details.

This property does not receive any value through the migration wizard.

### User Object Index Storage property: Reverse or Nosort? - at database implementation level

Indicates whether Reverse or Nosort options are to be used for this index.

One cause of sorting is the creation of indexes. Creating an index for a table involves sorting all rows in the table based on the values of the indexed columns. Oracle also allows you to create indexes without sorting. If the rows in the table are loaded in ascending order, then you can create the index faster without sorting.

Presorting your data and loading it in order may not always be the fastest way to load a table. Specifically, if you have a single-CPU computer, you should if possible sort your data before loading. Then create the index with the NOSORT clause.

Creating a REVERSE key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. Such an arrangement can help avoid performance degradation in an Oracle Parallel Server environment where modifications to the index are concentrated on a small set of leaf blocks. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.

This property does not receive any value through the migration wizard.

### Global Index Partitions as a secondary element of User Object Index Storage - at database implementation level

A global index partition represents a partition into which an index has been divided. The index partition can have different physical characteristics to the index; for example, it can be stored in a different tablespace and can have its own storage parameters.

The partitioning of a global index is completely independent of the partitioning of its associated table. A global index is partitioned based on its own specific partition key, which is not necessarily the same as the partition key of the table.

In a global partitioned index, the keys in a particular index partition may refer to rows stored in more than one underlying table partition or sub-partition. A global index can only be range-partitioned, but it can be defined on any type of partitioned table.

A global index is created by specifying the GLOBAL attribute.

A global partitioned index contains a single B-tree with entries for all rows in all partitions. Each index partition may contain keys that refer to many different partitions or sub-partitions in the table. See also Chapter 11 "Partitioned Tables and Indexes" in Oracle 8i Concepts.

The migration wizard does not add a Global Index Partition for each table implementation.

### Local Index Partitions as a secondary element of User Object Index Storage - at database implementation level

A local index partition represents a partition into which an index has been divided. The index partition can have different physical characteristics to the index, for example, it can be stored in a different tablespace and can have its own storage parameters.

Each local index partition has exactly the same number of partitions (with exactly the same "VALUE LESS THAN" boundaries) as the number of partitions that have been defined for the table. The tablespace and the storage parameters for a local index partition do not have to be the same as those used for the table partitions.

In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute.

Oracle constructs the local index so that it is equipartitioned with the underlying table. Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions or subpartitions, and gives them the same partition bounds as corresponding partitions of the underlying table.

Local indexes have the following advantages:

- Only one index partition needs to be rebuilt when a maintenance operation other than SPLIT PARTITION or ADD PARTITION is performed on an underlying table partition.
- The duration of a partition maintenance operation remains proportional to partition size if the partitioned table has only local indexes.
- Local indexes support partition independence.
- Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.

See also Chapter 11 "Partitioned Tables and Indexes", in Oracle 8i Concepts.

The migration wizard does not add a Local Index Partition for each table implementation.

### Database Trigger property: Fire When Propagated?

Indicates whether or not the trigger is to fire when the data has been propagated, for example, in a multi-master replication environment or with views (instead of triggers). Here the trigger code would perform the validation of the rows being modified and, if valid, propagate the changes to the underlying tables.

### New column properties

#### Def Template/Library Object

The name of an object in a template or object library. This property will be populated within the context of a module - like a display property - each time it is referenced in a module and subsequently used by the Oracle Forms Generator. You could for example define a trigger on the double mouse-click behind this library object.

Enable this column property by referencing an item from your object library.

#### Server Defaulted?

Indicates whether a default value should be supplied by application logic residing on the database server if no value is supplied by a client side application. This tells the generator it should requery the row after insert/update to display the server derived data. The migration wizard sets the value of this property to ‘N’.

#### Server Derived?

Indicates whether the column's value is derived on the database server, via a database trigger, and therefore should not be set or provided by the client side. The table API will generate server API code for an auto-generated column (e.g. Date Created, User Created) if this property is enabled. The migration wizard sets the value of this property to ‘N’.

#### Derivation Expression Type

Indicates the type of derivation expression (e.g. null, function call, SQL expression) that is used to specify the column value. Obviously you have to provide a specific value for this property if you have defined a derivation expression. The migration wizard does not set a specific value.

#### Where/Validation type

Indicates whether the Where/Validation condition property defines a Function Call or a SQL Expression. Obviously you have to provide a specific value for this property if you have defined a where validation expression. The migration wizard does not set a specific value

### View properties

#### Object type view property

Identifies the Oracle object type on which the object view is to be based. For an existing object view definition, this property can be changed to reference a different object type. However you cannot change an object view into a relational view by removing the object type reference from this property. You cannot reference an object type from an existing relational view. Use the utility 'Create Oracle Object Type' to migrate existing relational views definitions. See also the section "Oracle Object Types".

#### Non-free format View property: Optimizer Hint Clause - also applicable for Materialized Views

A hint clause to be used where the view is defined declaratively. The optimizer uses the hint to choose an execution plan for the SQL statement. You should add an optimizer hint clause if you specifically want to use the rule-based optimizer. The default behavior of a database is the usage of the cost-base optimizer based upon the computed statistics. The migration wizard does not set a specific value.

#### Base Table Locations: new database implementation secondary element for views

Create a base table location if the underlying table, view or materialized view is implemented in a different schema. This is because the Database Object Generator must be told which underlying objects it is to use when creating a view or materialized view on a database.

For example, if view B is based on table A, then you can use a base table location if the latter is to be implemented for a different user.

By default, views and materialized views are implemented in the same schema as the element on which they are based. Where this is the case, base table locations are not required

### Materialized view implementation independent properties

#### Updateable

Indicates whether the materialized view can be updated, i.e., whether INSERT, UPDATE and DELETE statements can be performed on the materialized view. Only simple materialized views can be updated.

A materialized view is always synchronized with its source via a materialized view refresh (or a refresh of the surrounding refresh group). You may consider making or allowing these changes to be made to the data in a materialized view:

- intermediate (part) synchronization of the materialized view on specific DML operations on the source
- direct specific changes on the materialized view (for example in a replicated environment).

The following default migration value is applicable: 'No'.

#### Cluster

The name of the cluster in which the materialized view exists. It is used to place a materialized view on a cluster if the same grouping of columns are frequently selected from a table or tables.

#### Materialized view implementation dependent properties

The following materialized view implementation dependent properties must be reevaluated:

- Materialised view log properties. Materialized view log properties for a table implementation
- Cached. Specifies whether the block retrieved for this materialized view is placed at the most recently used end of the Last Recently Used (LRU) list. When set to Not Cached, retrieved blocks are placed at the least recently used end of the LRU. Placing the data at the most recently used end can improve performance. It is useful to set this property to Cached for small lookup tables
- Materialized View Group. The name of a set of materialized views on the same database (but not necessarily in the same schema on that database) that are to be refreshed at the same time and at the same interval. The definition specified for Materialized View Group is the definition that would apply to a Refresh Group. A Materialized View Group is the counterpart of a Master Replication Group.
- Build Type. Identifies whether the materialized view is to be populated immediately after executing the DDL, or whether it is to be deferred.
- Query Rewrite? Identifies whether the materialized view is eligible to be used for query rewrite. Valid values: Null, Disable, Enabled

### Deferrable constraints

You can defer constraints for validity until the end of the transaction. A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to roll back.

If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.

If a constraint causes an action (for example, delete cascade), that action is always taken as part of the statement that caused it, whether the constraint is deferred or immediate.

You can enable the defer status of a constraint by applying one of the following values for the constraint “Defer Status” property:

- Initially deferred. The constraint is deferrable and, by default, checked at the end of the transaction.
- Initially immediate. The constraint is deferrable and, by default, checked at the end of each DML statement.

The Migration Wizard brings the Not Deferred status value forward.

If you are using CDMrule frame or are planning to use it you should only use non-deferrable constraints. For more information about CDMrule frame see www.otn.oracle.com.

### Scope properties (global synonym name and scope) for each database implementation object

Each implementation of a database object (e.g. table, view, sequence) has received the following additional scope properties:

- Global Synonym Name. The name to be used by the Database Object Generator when creating synonyms for this object. Default Migration value: null
- Scope. The scope of visibility of the database object (Database or World). This is used by the Database Object Generator when database links and synonyms need to be created. Default Migration value: Database

### New or changed Granted to Users or Roles properties

#### Create Synonym?

Indicates whether a synonym is automatically created by the Database Object Generator when the database object is in a different schema to the user or role. This property receives by default a 'No' string from the migration wizard.

#### Execute?

Indicates that the EXECUTE privilege is granted to the user or role. Not applicable for tables. This property receives by default a 'No' string from the migration wizard.

#### Read?

Indicates that the READ privilege is granted to the user or role.

The Read privilege provides secured access to the files stored in the operating system directory to which the directory object serves as a pointer.

The directory object contains the full pathname of the operating system directory where the files reside. Because the files are actually stored outside the database, Oracle server processes also need to have appropriate file permissions on the file system server. Granting object privileges on the directory database object to individual database users, rather than on the operating system, allows Oracle to enforce security during file operations. This property receives by default a 'No' string from the migration wizard.

#### Enqueu?

Indicates that the ENQUEUE privilege is granted to the user or role. This privilege applies only to queue implementations. This property receives by default a 'No' string from the migration wizard.

#### Dequeu?

Indicates that the DEQUEUE privilege is granted to the user or role. This privilege applies only to queue implementations. This property receives by default a 'No' string from the migration wizard.

## Migrating from Designer 2.1 or 6.0

This section covers some database migration issues when migrating from Designer Release 6.0.

The following new Oracle9i new database features - already introduced in Oracle 8i - will be discussed:

- Index tables only
- Function based indexes
- Compute statistics for indexes
- Domain key constraints
- Deterministic clauses for PL/SQL definitions
- Object types
- Java definitions

Note that the introduction of object types and Java definitions involves a substantial structural change and are therefore less cost effective than the other Oracle9i new features.

### Index table only

Typically large tables with only a small number of columns like intersection tables are candidates for index-organized only.

You can change a “normal” table to an index organized table by enabling the table property Index-organized?. Note that the migration wizard does not enable this specific table property.

#### Pct Theshold - at database implementation level

Used in the case of an 'index only table' to specify a percentage of the block size. This property does not receive any value from the migration wizard.

#### Overflow Tablespace- at database implementation level

Used in the case of an 'index only table' to specify the name of the tablespace that is used for overflow purposes. This property does not receive any value from the migration wizard.

### Function Based indexes

Function based indexes are introduced in Oracle8i and are a very cost effective means of boosting your application performance without changing the underlying database structure.

You can define function based indexes at the index entry level (e.g. column level) and define the function expression in the “Index Function” property like upper(ename). In addition you should set the “type” property to “function based” as opposed to “column based”.

### Compute statistics for Indexes

Indicates whether statistics are to be collected on creation of the index. Typically you would set this value to 'yes' in the context of migration since all tables will contain data. Re-creation of the indexes will then automatically lead to the collection of statistics. Note that the migration wizard disables this property by default.

### Domain Key Constraints

Oracle Designer 6i introduced a new SAC (secondary element) for tables called Domain Key Constraints.

Domain key constraints allow you to model domains using a table other than the predefined one normally used by Oracle Designer.

A domain key constraint allows a generated application to access a specific range of domain values, from a table containing multiple domains. The domain key constraint specifies that values entered in the domain key columns do not conflict with a predefined range of acceptable values.

You may want to do this for several reasons, e.g., you may already have a domain table that you would like to design-capture and continue to use, or you may want more control over how domain values are shown within a model.

You add a domain key constraint for a specific table as a new secondary constraint type next to the ‘normal’ constraints like foreign key or check constraints.

#### Primary Key, Unique Key or Foreign Key column property: Conversion Format Mask?

The format to be used when a date or number column is joined to a VARCHAR2 column in a domain table. If a format is not specified here, the application generators use a set of rules to define the format. Note that this property is not used by the Database Object Generator. It applies only to key components of domain key constraints

#### Foreign Key column property: Second Join Column?

The name of a column in the join table that is the derivation for all or part of this foreign key.

### Usage of the Deterministic? clause for PL/SQL functions

Allows the system to use a saved copy of the function's return result - Functions only - if such a copy is available. The saved copy could come from a materialized view, a function based index, or a redundant call to the same function in the same SQL statement.

The Query optimizer can choose whether to use a saved copy or re-call the function. The function should reliably return the same result value whenever it is called with the same values for its arguments. Therefore do not define the function to use package variables or to access the database in any way that might effect the function's return result, because the results of doing so will not be captured if the system chooses not to call the function

You can simply make use of the deterministic feature by enabling the Deterministic? property for PL/SQL functions.

### Object Types

Oracle object types (aka user-defined types) provide a way of creating user-defined datatypes for the Oracle8i database server. These datatypes can be used in addition to the built-in datatypes provided by the server.

You could use Oracle object types:

- to define a datatype of a column in a relational table
- to create a table based on a specific Oracle object type, i.e. an object table
- as part of the definition of another Oracle object type, i.e. as an attribute of the new Oracle object type.

Oracle object types can be considered as templates for creating instances of objects. Typically, the objects have a set of common attributes and methods (operations), and may be structured in a hierarchy.

Use the Create Oracle Object Type and Create Object View utilities to migrate a server model based on a relational table design to an object design.

There are three key stages to migrating a relational design:

- Create Oracle object types from the relational tables/views
- Modify the default Oracle object type definitions
- Create object views for the Oracle object types

#### Stage 1 - Create Oracle object types from the relational tables/views

The first stage is to build Oracle object type definitions based on the structure of the relational table/views in your current server model.

The utility - first item on the Utility menu in the Design Editor - creates an Oracle object type for each selected table/view. It also creates an Oracle object type attribute for each non-foreign key column and a REF attribute for each foreign key. Note that for REF attributes to be correctly mapped to Oracle object types, both tables/views referenced by the foreign key need to be selected.

Note that the utility also creates a set of mapping elements to record the relationship between the relational table/columns and the Oracle object type/attributes. This mapping is only visible in the RON (see below). This mapping information is used when the Oracle object types are used to implement object views for the relational table/views (see Stage 3).

#### Stage 2 - Modify the default Oracle object types

Once you have created a set of Oracle object types based on your relational tables/views, you can review your design from an object-orientated perspective. In this stage you will typically introduce further Oracle8 database server features.

For example:

- Migrate a set of attributes to new embedded Oracle object type
- Change REF attributes to collections of VARRAY or nested table
- Change attributes to REF attributes
- Add/delete attributes (and column mappings).

#### Stage 3 - Create object views for the Oracle object types

Finally, you can build object view definitions based on the Oracle object type definitions and their mappings to relational table/views.

The utility - second item on the Utility menu in the Design Editor - creates an object view for each Oracle object type you select, together with the required SQL statements to construct the object rows from the underlying relational tables. It also creates OIDs from the underlying relational tables primary/unique key.

There are no new properties for the primary and secondary implementation independent properties of Oracle Object Types and there are no specific implementation dependent properties for Oracle Objects Types.

### Collection types

Oracle collection types are used to define datatypes. They provide support for collections of similar items, and can be implemented as either of the following:

- Nested tables - These are useful where referential integrity is required and are suited to master-detail and one-to-many relationships. Nested tables can have attributes but no methods.
- VARRAYs - These are useful for providing quick access to small or uniform-sized collections of objects in a table. VARRAYs do not have methods or attributes.

An example of when you could use a collection type is for managing orders and their associated order lines. The orders would be stored in a top-level outer table, while the order lines could be stored in a nested table or VARRAY. The nested table or VARRAY is recorded as a column (on a relational table) or an attribute (on an object relational table) on the outer table. This column/attribute embeds the collection type within the outer table and is designed to hold the order lines for each order.

Typically nested tables are used when there is no limit on the potential number of items to be stored. Conversely, VARRAYs always have a maximum number of elements, that is defined by the user.

Nested tables and VARRAYs cannot be referenced directly, but they can be referenced indirectly via the outer table.

### Object Tables

Object tables are based on Oracle object types. The objects that are stored in each row of the table are uniquely identified by a system-generated identifier, called an object identifier. This object identifier is maintained automatically by the database.

### Object Views

Object views allow you to retrieve, update, insert and delete relational data as if they were stored as objects. This allows you to use the object oriented features of the Oracle 8i database server with existing relational data. If you want to build an object view that is based on an existing relational table or view, you can use the Create ObjectView utility (see above).

### Transformation Mapping Sets (visible in RON only)

A transformation mapping set represents the overall 'collective' mapping for transformation from one level to another (for example, from the type model to the server model). It is made up of the individual mappings of pairs of specified elements.

There are three transformation types:

- Entitity Object to Relational BC4J mapping
- Entity Object to Relational Mapping
- Relational to Object Relational mapping

The last mapping type is created automatically by the utilities 'Create Oracle Object Type' and/or 'Create Object View'.

### Java definitions

Oracle Designer 6i introduced the option to store Java Definitions in a structured way. It supports the following Java Definitions:

- Source Definitions
- Class Definitions
- Resource Definitions

If you use this structured method for Java Definitions in Oracle9i Designer you will not be able to use the JDeveloper 9i IE optimally. However you could also store the Java Definitions as text files in the Oracle9i Designer repository. Adopting the file approach you will use the Oracle9i Designer repository basically as a Source Control Tool to manage the Java files, its versions, its releases and its dependencies. In addition, the Oracle9i Designer repository will control the revision data and will keep track of a change history and the lock of a file when it is in use by a developer.

The following subjects for storing Java Definitions as files will be handled:

- Organization of Oracle9i Designer repository for storing Java files
- Interactions between the Oracle9i Designer / Repository 9i and JDeveloper 9i
- Dependency analysis for Java files

#### Organization of Oracle9i Designer repository for storing Java files

Oracle9i Designer will be used as the repository to manage all Java files and its versions. Within Oracle9i Designer, files will be stored in a folder structure that matches the Java package structure. Thus, a Java package - package names preferably in lowercase! - will also be visible in the Oracle9i Designer environment.

#### Interactions between the Oracle9i Designer / Repository 9i and JDeveloper 9i

A significant amount of effort is spent in the integration between the Repository 9i and JDeveloper 9i based on the assumption that all JDeveloper 9i components (e.g. Java files, XML files) are stored as files in the repository, rather than structured objects. This tight integration - together with the Oracle9i Designer capability of storing structured database objects - makes Oracle9i Designer/Repository 9i/JDeveloper 9i an effective and productive development and deployment tool stack.

#### Dependency analysis for Java files

You could enrich the repository with dependency information for Java files by analyzing your Java files via the Dependency Manager for dependencies with other files and structured objects (e.g. table usage). See also the section about dependency analysis.

Prev | Next
| Copyright © 2002, Oracle Corporation. All Rights Reserved. |
Contents