Generating Migration Scripts
Selecting the Status Menu item from the File menu brings up the FmPro Migrator Status Window. This feature enables you to see the
STDOUT results from the file generation process. There are rarely problems with the process, but if you do encounter issues, this is a good place to look. If there are problems executing the Perl code that generates the migration scripts, you see the error within the Status Window.
Since all of the parameters have been entered, clicking on the Migrate button will cause the migration files to be generated.
There are 10 files created for the migration process, but we will only need to use five of these files, which are described in Table 2.
|contact_management_instructions1.txt||This file contains detailed instructions concerning the migration process and is customized for the database that is currently being migrated.|
|contact_management_report_oracle1.txt||This report file shows the structure of the FileMaker database, including the names of fields, field calculation formulas, layouts, and scripts. This file also shows how the FileMaker fields will be renamed when creating the Oracle table.|
|contact_management_create_table1.sql||This is the SQL file that creates the Oracle user account, tablespace, and database table.|
|contact_management_fmpro_max_fieldsize1.pl||This is the Perl script that reads each of the FileMaker records and then creates a report showing the maximum number of characters within each field.|
|contact_management_fmpro_to_oracle_xfer_odbc1.pl||This Perl script reads the data from the FileMaker database and then writes the data into the Oracle database via an ODBC connection.|
Table 2. Migration File List
Checking Field Sizes
Transfer the generated files from the Mac OS X computer to the Windows computer (or to the instance of VirtualPC). Create both of the ODBC DSNs, which are listed at the top of the contact_management_fmpro_to_oracle_xfer_odbc1.pl file. These DSNs should be created as System DSNs within the Windows Data Sources (ODBC) control panel.
When creating the FileMaker DSN, click the Use Remote Connection check box if you are connecting to a FileMaker database that is not running on the same computer as the Perl scripts. Then enter a hostname or IP address for the computer that will be running the FileMaker database. If you are running Virtual PC on Mac OS X, you need to make sure that a TCP/IP connection can be made from the Windows OS to the FileMaker database running on Mac OS X. Usually, your VirtualPC instance will have a separate IP address from your Mac OS X, so this will be easy to configure. Just enter the IP address or hostname of the Mac OS X computer in the Server Address field.
Replace the default value of 255 with 65000 in the Max Text Length field on the Advanced tab of the ODBC FileMaker Setup dialog. It is generally best to leave the Fetch Chunk Size parameter at the default value of 100. Otherwise, FileMaker seems to have a greater tendency to crash while transferring data via ODBC. In fact, I have transferred over 700,000 records within a single FileMaker database file without seeing a single crash. But I have noticed that setting this value to 1000 has resulted in FileMaker crashing while transferring much smaller files.
The contact_management_fmp_dsn is used to connect to the FileMaker database, and the contact_management_ora_dsn makes a connection to the Oracle database. Enable the Local and Remote Data Access Companions from the Sharing... menu item of the FileMaker File menu. Then run the contact_management_fmpro_max_fieldsize1.pl script from the Windows command prompt. This script will make an ODBC connection to the FileMaker database in order to determine the maximum content sizes for each of the fields. The results from this program will be displayed on the screen and will also be written into the newly created contact_management_fmpro_max_fieldsize_report.txt file within the directory where the contact_management_fmpro_max_fieldsize1.pl script was run.
The main purpose for creating the contact_management_fmpro_max_fieldsize_report.txt report is to determine if there are any text fields within the FileMaker database that contain more characters than will fit within an Oracle VARCHAR2(4000) table column. Since FileMaker text fields can easily store up to 65000 characters, it is commonly necessary to migrate them to the Oracle database by using the CLOB column type.
The Oracle CLOB column type can store up to 4GB of text per record, so this will work fine for any FileMaker text field. For the purposes of this example, I have selected the Template Information Global field as being a field that I want to migrate to Oracle as a CLOB column. To make this change, I enter
Template_Information_Global into the Large Text Fields field within FmPro Migrator. The column names entered into this field need to be the actual names used within the destination database after special characters are changed or removed. The contact_management_report_oracle1.txt report file will show you how the FileMaker field names will get renamed when creating the new Oracle table. Press the Migrate button, and all of the scripts will be regenerated. There are many dependencies within the scripts regarding a column-type change, but each script will be completely rewritten to reflect this change.
Creating the Oracle Table
Transfer the updated scripts to Windows and open the contact_management_create_table1.sql file in a text editor. Using the fieldsize report, you can determine the optimum size to use for each column of the new Oracle table. It is generally a good idea to reduce text column widths from the default value of
VARCHAR2(255) to be closer to the actual amount of data contained within the FileMaker database. You will also need to pay attention to the number of columns that will be created within the Oracle table. I have migrated some FileMaker databases containing over 1500 fields; a FileMaker database with this many fields can't be migrated unchanged into a single Oracle database table, because Oracle can't have more than 1024 columns within a table.
This particular FileMaker example database does not contain a primary key. This is OK, if all we want to do is perform a quick migration to get the data into Oracle, and then copy the data to another table later. But if we want the migration process to produce a table that is immediately ready for production use, we should add a primary key to the table. FmPro Migrator automatically determines that you have a primary key within the FileMaker database by looking for the first numeric field, which has the attributes of
Not Empty and
Unique. Within the table-creation SQL file, a primary key column will be created as
NOT NULL, with a primary key constraint using the user-defined
INDEX tablespace along with an incrementing sequence and an insert trigger. Any of this code may be manually modified as needed prior to creating the Oracle database table. But if you have never previously created an Oracle sequence or trigger, you will probably find this code to be helpful.
After you have made manual modifications to the table-creation code, you can run it and see if Oracle is able to create the database table for you. Duplicate column name errors are common at this point, due to special characters having been removed during the automatic renaming process. If you are migrating a FileMaker database containing a small number of fields, then you can visually look at the proposed column names to see if you have duplicates. However, if you are migrating a FileMaker database containing 200 fields, you might as well just run the table-creation code with SQL*Plus and see whether you get any errors -- Oracle will let you know!
To create the database table, run the following command at the command prompt on Windows or the Terminal window on Mac OS X (depending upon where you have your database installed).
bash2.05a$ sqlplus /nolog @contact_management_create_table1.sql
The best strategy to follow is to make any column name changes within the original FileMaker database, and then regenerate the scripts with FmPro Migrator. In most cases, there is no reason to try to manually track the dependencies between the generated output files, so just let FmPro Migrator recreate these files for you.