AtoOmigration

Make your own free website on Tripod.com

   Access to Oracle 8i Migration Project
                                             Just Call me Sam!!!
                                                                        M.S.(M.I.S.) University of Memphis, TN.

(Client:Wingate Inn Memphis, TN 38106
Mode of Execution : Part Time
Started : 08/21/02
Ended : 09/18/02
Actual Time Consumed : 30 days
Database Size : 30 gig
Record Time Span : Jan 95 to Aug 02
)

Why Migrate ?

This was the clients fundamental requirement due to the vast size of the data that was needed to integrate. The earliest Access files were Access 95 Files , difficult to access using the current version. Many files where misplaced, and had to be found .Acess Database Hangovers were very common over Windows 2000 O.S. Response query times were longer as applications depended more heavily with time for vast data on Access as Back End.Backups were problematic and had to be done manually , the task scheduler almost inevitably hung up, the back up process.

This was the reason in effect. Some more academic reasons explaining the technicalities between the two are given below:

Products such as Microsoft Access (MS Access) allow developers and advanced users to build complete business systems. However, because MS Access is based on file sharing technology, it lacks the speed, reliability, and robustness provided by independent relational database management system (RDBMS) server.Using an RDBMS server with MS Access in a client/server architecture provides the strengths of both technologies. MS Access provides excellent forms and reports systems, as well as a complete programming language (MS Access Basic). An independent RDBMS provides reliable, robust, and secure high speed data management. The Oracle RDBMS is a modern, scaleable, high performance database server that can run on a wide range of computers from PCs to mainframes. Oracle operates in a networked, client/server environment and can support tens, hundreds, or thousands of users depending on the server.

Click for Menu

Approaches to Migration:

Using Migration Tools ( Oracle's MigrationWorkbench)
Manual Migration (Most reliable)

The Initial Part of the project , the involved migrating Access 2000 files was done using migration tools, the latter part involved manual migration, and involved Access 95 and Access 98 files.

Oracle's MigrationWorkbench:

Here are some of the things you can do with Oracle's Migration workbench.

The MigrationWorkbench allows you to:

Migrate a complete MS Access 2.0, MS Access 95, or MS Access 97 database to Oracle8 or Oracle8i.
Migrate tables (including validation rules and default values), indexes,relations, and primary keys.
Use unmodified MS Access forms and reports with the destination Oracle database via an ODBC connection.
Migrate MS Access databases with attached MS Access tables.
Display a representation of the source database and its Oracle equivalent.
Generate and view a summary report of the migration.
Customize users, tables, indexes, and tablespaces.
Customize the default data type mapping rules.
Create ANSI-compliant names.
Automatically resolve conflicts such as Oracle reserved words.
Remove and rename objects in the Oracle Model.
Migrate individual table data.

Click for Menu

Migration Process:

Before going into the actual migration process. Here 's a briefing that'll help understand the various architectures across databases.

MS Access Architecture

MS Access is based on a file server DBMS technology named “Jet.” Forms, reports,and Basic code in MS Access rely on Jet to manage data stored in the native “mdb”file format.

Click for Menu

In a single-user MS Access application, the mdb file and MS Access are located on the same machine. In a multi-user MS Access application, the mdb file is placed ona file server and shared. Each client runs a copy of MS Access and the Jet engine. In this configuration, Jet must move a large amount of data across the network,
including whole tables, to complete its query processing. The Data Access Objects (DAOs) DLL provides a hierarchy of classes to MS Access Basic and Visual Basic. The MS Access 2.0 DAO DLL is DA02016.DLL and the MS Access 95 DAO DLL is DAO350.DLL. DAOs define and expose databases,
workspaces, query definitions, parameters, recordsets, tables, fields, indexes, relationships, users, and groups from Jet.Jet manages links to external data sources. Links to dBase, Paradox, and Btrieve are made through an internal Jet ISAM driver interface. These DLLs are included with MS Access. Jet also supports a link to ODBC, which allows access to a wide range of DBMS servers.

Jet Recordsets

When Jet processes a query, it returns a recordset (a cursor) for the resulting set of records. Jet supports two types of recordsets; snapshots and dynasets.
A snapshot is a picture of data as it existed at the time the querywas run. When returning a snapshot, Jet runs the query to completion, extracts all of the resulting
rows and columns into a virtual table, and presents this virtual table to the user. The user of a snapshot can perform a full range of operations on a snapshot such as
query the snapshot and base forms and reports on the snapshot. You cannot make changes to snapshot data and it does not reflect changes made by other users after it has been opened.
A dynaset is a live view of the data. When returning a dynaset, Jet extracts the key values from the data and stores them in memory.When a user requests rows of
data from the dynaset, Jet fetches the rows of interest by looking them up in the base tables via the internally stored key values. Once you open a dynaset, the set of
key values cannot change. Thus, while the data pointed to by the key value may change and is reflected to the user, new rows added after the query is started will
not be a part of the set of key values and will not be made visible to the user. Rows that you delete after you run the keyset query are still part of the set of key values, however, they are marked #DELETED# when presented to the user. The dynaset model is a powerful and flexible model that gives the user of PC-based
information the opportunity to browse large quantities of data and update the data at will.When used with local data, dynasets are fast and effective. However, the
dynaset model presents one of the key performance challenges when MS Access works with an RDBMS server such as Oracle.

Click for Menu

Jet Multi-User Updates

Jet handles updates by multiple users by using optimistic and pessimistic locks. Using pessimistic locking, Jet places a hard lock on the data page that contains the
row being edited. Other users cannot start editing the locked row until the lock is abandoned or the changes are written to disk.
Jet employs an optimistic locking scheme when working with Oracle. An optimistic locking scheme does not place a hard lock on the source table(s). Instead, when a change is to be committed, Jet checks to make sure that the data has not been altered by another user before allowing the changes to be posted.

Jet Enforced Referential Integrity

Jet supports declarative referential integrity. This includes primary key/foreign key relationships with one-to-one and one-to-many cardinality with cascading
UPDATEs and DELETES.Jet Query Processor The Jet query processor does not support a full implementation of SQL. It optimizes queries, especially when the query references both local and remote tables. Jet can connect to a wide range of data sources and process queries against all of them.
Transaction support is limited to native file format database tables. Jet relies on the transaction support of any RDBMS attached via ODBC.

Jet Transactions

Jet supports an explicit transaction model. Transactions are not started until a BeginTrans statement is executed. Transactions are committed with CommitTrans and aborted with Rollback. In addition to using transactions to group related units of work, developers use transactions to improve performance. If a program makes numerous references to a table, grouping the work in a single transaction will force Jet to perform the operation in memory and then commit all work to disk when the transaction is committed. Be aware that this type of transaction may not map directly to Oracle.

Oracle Architecture

Oracle8 and Oracle8i are powerful, flexible, and scaleable relational database management system (RDBMS) servers, which run on a range of computer systems,
from personal computers to the largest mainframes. Oracle has been designed to run effectively in a client/server environment and supports hundreds to thousands
of users.
The Oracle architecture supports advanced server features such as record locking with versioning (not page locking as provided by MS Access), advanced query
optimization, the PL/SQL programming language, data replication, distributed database management, and other important features.

Triggers and Stored Procedures

Oracle allows you to write and store code in the DBMS along with data. You can associate trigger code with an UPDATE, INSERT, or DELETE event for each row or for a table as awhole. You can also set a trigger to run before or after the event. For example, you can set a trigger to run after any row is updated.
A stored procedure is a general routine (either function or subroutine) that is stored in pre-compiled form on the server. A trigger may call stored procedures, but
triggers are only activated by specific database activity such as the insertion of a row in a table. When using MS Access with Oracle, triggers and stored procedures play a role in mapping the functionality of MS Access to Oracle, such as in the support for the MS Access AUTONUMBER (COUNTER) data type in Oracle.

Click for Menu

Sequences

A sequence is a unique number generator that is implemented in shared memory on a server. It is designed to provide a set of unique values for PL/SQL programs
for use as primary keys. Sequences are designed for high performance applications that might otherwise ‘single-thread’ on table-based unique number generators. You use sequences, along with supporting code in a trigger, to emulate the ‘COUNTER’ field type in MS Access.
Transactions Unlike MS Access, Oracle supports an implicit transaction model. Each SQL statement is part of a logical transaction. A logical transaction begins with the first SQL statement and ends with a Commit or Rollback statement. Immediately after either of these statements, a new transaction takes effect with the next SQL statement. MS Access developers use transactions to improve the performance of Jet. Grouping database statements in a transaction forces Jet to attempt to complete all database work in memory; Jet defers writing to disk until the transaction is committed. When this use of transactions is mapped to Oracle via Open Database Connectivity (ODBC), Jet sends only the outer most pair of Begin/Commit transaction requests. Oracle keeps an open transaction during the entire processing period. You must decide if you want this outcome when you move from MS Access to Oracle. Other Oracle Features .A database administrator has great flexibility when configuring Oracle. Data can be written on multiple disks for increased performance, rollback and recovery options can be tuned, and computer resources can be allocated to optimize the configuration for each server. Oracle also supports distributed processing, so data can be distributed across multiple machines. Oracle offers a version of the server called Trusted Oracle Server for applications that require a higher level of user and use authentication.

Jet/ODBC/Oracle Architecture

Using Oracle with MS Access can increase the robustness and reliability of a multi-user system. This reduces network traffic because only query requests and
the resulting data are sent over the network (instead of complete tables). Jet technology is focused on single-user performance with adequate multi-user capabilities; Oracle on the other hand is a mature central server technology focused on multi-user performance, rollback and recovery, and centralized query
processing. Obtaining adequate performance from the combination of MS Access and Oracle requires an understanding of how Jet works with centralized servers.
The following diagram illustrates that MS Access requires ODBC to make its connection to Oracle.

ODBC is an API that allows client applications to connect to different RDBMS servers. Jet has been designed to make efficient use of ODBC while requiring a level
1 ODBC driver.When MS Access uses ODBC to connect to remote RDBMS servers, Jet continues to function as the DBMS engine for MS Access. MS Access forms, reports, and Basic code continue to work with Jet as if they were working with local or shared data in the mdb file format. Jet presents remote Oracle tables as attached tables. These attached tables are created at design time and appear to be local tables. Jet requires a primary key on tables in Oracle in order to support dynasets against those tables. If a remote table does not have a primary key, Jet only opens a non-updateable snapshot on the table.
It is possible to define updateable tables in MS Access that do not have a primary key. When these tables are migrated to Oracle, they do not have a primary key and MS Access is unable to update them. If you need the migrated tables to be updateable, you should ensure that either all updateable MS Access tables have
primary keys before you migrate or that you define a primary key once the tables are migrated to Oracle. Any updateable tables that do not have primary keys are
flagged with a warning in the Log Window.

Click for Menu

Preparing for Migration

It is imperative that you complete these steps before you run the Capture Source Wizard:
1. Turn off security.
2. If your application contains linked tables, refresh these links by opening the application in the MS Access IDE and choosing Tools -> Add Ins -> Linked
Table Manager.
3. Compact your MS Access database files.
4. Back up your MS Access database files.
Note: Ensure that the database is not a replica database. The MigrationWorkbench cannot migrate a replica MS Access database.

Changes Made to the MS Access Database

When you migrate an MS Access database to Oracle, some changes are required to enable your MS Access forms and reports to continue accessing the data. You must select to have your MS Access database modified in order for these changes to be made.
Figure below shows an MS Access application before and after migration to Oracle. Both before and after migration, app.mdb contains the forms, reports, macros, and Basic modules that make up the application. Before migration, app.mdb contains an attached table from data.mdb. This diagram uses <tablename> to refer to the name of this table.

After migration, app.mdb has two attached tables for each original table and a mapping query. The original MS Access tables are renamed to <tablename>_L. The
original table is exported to Oracle where it is called <TABLENAME>. A table attachment is created to the Oracle table with the name <tablename>_R. Because the forms, reports, and modules in app.mdb are expecting a table with the original table name, a mapping query called <tablename> is created. This mapping
query takes the place of the original table in the application. The mapping query can refer to either <tablename>_L or <tablename>_R; you can switch between the
local and remote table as you move your application to Oracle. The mapping query also helps resolve reserved word conflicts between MS Access and Oracle. For
example, a column named “Sequence” must be renamed as this is an Oracle reserved word. The mapping query can remap this Oracle column back to Sequence
for use by the MS Access application. Reserved Words.
All requests for data from the MS Access application are directed to the mapping query, which retrieves the data from the new Oracle table.
The original table that has been exported to Oracle is retained in case the data is needed locally and, more importantly, so that any new MS Access forms created
can be based on this table. This enables a complete set of table properties to be inherited by the MS Access form. After the MS Access form is defined, the data
source can be switched to the mapping query so that the data can be retrieved from Oracle.

If the application opens a table directly (not using a dynaset or snapshot) it will not work with linked tables. This restriction also applies after you move the data to
Oracle. If this happens, you might want to leave some tables in the app.mdb file so that each client has an independent copy. This could be appropriate for tables with lookup values such as a ‘State’ table. If you must move a table that is opened directly to the data.mdb file, you must change the application to use dynasets or
snapshots.

Click for Menu

Extending your Application

After you move the data management portion of your MS Access application to Oracle, you can rely on Oracle to protect your data and maintain all referential
integrity and business rules that you have encoded in PL/SQL. With this foundation, you can extend your application with MS Access or a wide range of other tools. Oracle offers several high productivity tools such as Oracle Web DB, Oracle Developer, and Oracle Objects for OLE. Oracle Objects for OLE (OO4O) is a high performance connectivity solution for Visual Basic, Delphi, and other client tools that can control OLE Automation Servers.
In addition, if your application grows, you can move yourOracle server to larger computers without changing your application.

The Oracle Objects for OLE and OLEDB/ADO Cookbook provides additional information about how to extend your application. You can find this document on
the Oracle Technology Network (OTN)Web site at the following URL:

Troubleshooting:

Another problem commonly encountered , was how to seperate application and database files. Here some write up I did after doing few of min from the old access 95 database.They assume that you will start with a file called app.mdb and end with two files called app.mdb and data.mdb.
1. Make a backup of app.mdb.
2. Start MS Access and use the File -> Compact Database menu to compress
app.mdb.
3. In File Manager, copy app.mdb to data.mdb.
4. Open data.mdb in MS Access, delete all forms, reports, modules, and macros.
5. In MS Access, open app.mdb and delete all the tables.
6. While still in app.mdb, use the File -> Attach Table menu item to make an attachment in app.mdb to each table in data.mdb.

Now your application should run as it did before you split it into two mdb files.

ManualMigration :

The following steps guide you through the manual conversion process:
1. Make backup copies of app.mdb and data.mdb.

2. Ensure that an ODBC connection exists to your Oracle database.

3. In app.mdb, rename each attached table to be suffixed with “_L”. For example,rename <tablename> to <tablename>_L.

4. Open data.mdb inside MS Access and prepare each table for migration as outlined at the end of this section.

5. Close the data.mdb file.

6. Use File…Compact with data.mdb to recover the space used for the table copies.

7. Open the app.mdb file.

8. Attach each <TABLENAME> table from Oracle to app.mdb as follows:

Select the File -> Attach Table menu item.
Select <SQL Database>.
Select your ODBC DSN.
Supply your Oracle logon information.
Select the table and press the Attach button.

9. Change the names of the attached tables from <TABLENAME> to <tablename>_R.

10. Create a mapping query for each attached Oracle table. The name of the query will be <tablename>, the original name of the table, as seen by your application.
Make sure that the name of each column is mapped back to the original name found in the original tables.

11. Open the attached tables in datasheet view or open a form on the tables to make sure that the exporting and mapping steps have been successful. You will notice
that you are unable to update the data in the tables. After you complete the migration steps and build primary keys, you will be able to modify your data.

12. Perform the following steps for each column that was derived from an MS Access COUNTER field (COUNTER fields are mapped to NUMBER(10, 0)):

Create a sequence for each COUNTER. You can choose any starting number and increment number; however, you must ensure that you do not overlap numbers that have been exported from MS Access. You might want to start the sequence at the next major increment. For example, if 258 is the largest value in a COUNTER field, you might want to start at 1000; this will make it clear which records were inserted after the move to Oracle.
Create trigger code for the COUNTER field that uses the sequence.
Make the COUNTER field column the primary key or at least create a unique index on the column; you must insure that this index is the oneselected by MS Access as the ‘key value’ index, so you may want to prepend “aaaa” to the name of the index.

13. Create primary key and foreign key definitions in Oracle to match the MS Access structure. Oracle supports declarative CASCADE DELETE relationships,
but not CASCADE UPDATEs. To support CASCADE UPDATEs, you need to write trigger code in Oracle.

Click for Menu

14. Map default value definitions to Oracle.

15. Map row and table validation statements to Oracle CHECK statements.

16. If you have any tables in Oracle that you need to update from MS Access and which do not have a primary key, you must define one. As in step 12 above,
you must make the primary key index the first index in alphabetic order for the table.

17. Finally, use the MS Access Attachment Manager to refresh the attached table connections. This step insures that Jet caches the latest information about
primary keys and other table parameters. After you complete this step you will be able to update your tables.

18. Open the tables in app.mdb in a datasheet or form to ensure that the migration was successful.

Click for Menu

Preparing Tables for Migration

The steps outlined below explain how to prepare your tables for migration.
1. Make a copy of the table, naming the new table <TABLENAME>. That is, rename the table name in capital letters.
2. Make sure that the column names follow the requirements for names in Oracle. Change all column names to upper case.
3. Select the File -> Export menu item in MS Access.
4. The instructions that appear in the dialog boxes together with the following six steps outline how to export each table:

Select export to <SQL Database>.
Select the table to export.
Name the Oracle table (use <TABLENAME>).
Select your ODBC DSN.
Supply your logon information. If you have checked for Oracle reserved words, there should be no errors. However, if your tables are large, it couldtake up to several hours to export each table. MS Access provides a percentage complete indicator while exporting each table.
Delete <TABLENAME> from the data.mdb file.

Performance Tuning:

During the intramigration phase , where it was important to optimize the performance of both databases I did use the Application Startup Performance and Runtime Performance tips given below, although not all, applied directly to my project . I once again doing a complete ( much as I found on it) write up on this one , tapping my sources across many manuals and guides.

ODBC Driver Selection

There are a number of ODBC drivers available for Oracle. In addition to the driver supplied by Oracle, drivers are also available from Microsoft, Visigenic, Intersolve, and others.
The performance of ODBC drivers can vary. If you are building a large-scale application, you will need to profile the different ODBC drivers with your
application. The best way to determine the performance is with ODBC or OCI ‘spy’ programs. These programs will show you the calls that Jet makes to the ODBC API and will show you the calls the Oracle ODBC driver makes against OCI.

Server-based Parameter Table

When MS Access first makes a connection to an Oracle database, it checks to see if a special parameter table is present. The table has the following definition:

create table MSysConf(
CONFIG NUMBER,
nValue INTEGER
)

The following table illustrates how Config and nValue column values can customize the way MS Access works with Oracle:

Config	nValue	Meaning
101	0	Do not allow the user to store the USERID and PASSWORD in attachments. This is important to set for secure installations.
101	1	Allow the user to store the USERID and PASSWORD in table attachments (default).
102	D	MS Access delays D seconds between each background chunk fetch when managing dynasets.
103	N	MS Access fetches N rows on each background chunk fetch when populating a dynaset.

Click for Menu

Jet’s Query Processor

Jet’s Query Processor includes a cost-based optimizer that can make decisions on joining heterogeneous data via an ODBC connection.

Application Startup Performance

You can tune MS Access to speed up the process of establishing an ODBC connection at application startup time. In the case of MS Access 2.0, you make
entries or changes in the MSACC20.INI file, found in theWindows subdirectory. In MS Access 95, you modify the registry entries under Jet >> 3.0 >> Engines >>ODBC.
When MS Access opens a connection to an ODBC database, it goes through several steps to determine the level of functionality provided by the particular ODBC
driver.
If you are relying on Oracle to provide full security, you can bypass attempts by MS Access to login to Oracle using its own user/group/password information with the following entry:
TryJetAuth = 0 (MS Access 2.0)
JetTryAuth = 0 (MS Access 95)
TryJetAuth = 0 (MS Access 97)
This step will save between one and two seconds when making the first connection to Oracle.

Runtime Performance

The most important issue related to runtime performance in a client/server configuration is the reduction of network traffic.

Form Loading Time

Performance is both perceived and actual. You should eliminate any requests for data from Oracle while a form is loading. You can do this by including a button that
will retrieve information. You can also cache the last information that a form displayed locally.

Qualified and Restricted Queries

You can reduce network traffic by requesting only the columns you need from a table. You should also use the most restrictive qualifications possible to reduce the
size of the query result set.

Snapshots vs. Dynasets

If possible, use Forward Only Snapshots to work with Oracle data, especially when the result set is small. For larger result sets, and for queries which must be updated, use a dynaset. Even if you are not going to update data, a dynaset will be faster than a snapshot if the result set is large. This is because only the key values are retrieved for the dynaset, not the full set of complete rows.

Drop-Down Lists

MS Access tries to minimize the amount of network traffic when it needs to populate a drop-down list box.When a snapshot is used to populate a drop-down
list, MS Access uses the same batch fetching of records that it uses to populate a grid or a form. MS Access will fetch an initial ‘chunk’ of data (100 rows) and then
periodically retrieve sets of 100 rows from the server.

This process works smoothly unless you makes an entry that does not match a row already fetched. In this case, MS Access will begin fetching records from the server until a match is found or until all records are retrieved. If the returned set is large,this step can be lengthy and will freeze the User Interface. MS Access does not share queries for drop-down lists. You cannot define a snapshot query and reference it from multiple list boxes and expect to use.information that is retrieved once. Instead, MS Access will treat each ‘activation’ of a query as independent. If a drop-down list is short (< 100 records), it is probably sufficient to have MS
Access perform its normal operations. If the list is long, you may want to build a synchronized shadow table in MS Access. Store the table information locally in MS
Access and periodically synchronize the local table with information from Oracle.

Click for Menu
Get a GoStats hit counter