FmPro Script Diff FmPro Migrator CGIScripter

How to migrate from FileMaker Pro to DB2

This older article was written before the addition of the PHP Migration feature along with improved functionality in the FmPro Migrator user interface. The process is much easier today!

How can FmPro Migrator help you?

Note: Additional information is contained within the <database name>_instructions.txt file created during the file generation process. The <database name>_instructions.txt file contains the actual file names created by FmPro Migrator. This document uses an example FileMaker Pro database named "example", the file created for your database will use the name of the FileMaker database you are converting.

Note: FmPro Migrator for Windows requires that Perl be installed on your Windows computer before generating scripts. Activestate Perl may be downloaded from www.activestate.com. FmPro Migrator for MacOS X utilizes the version of Perl which was automatically installed with your operating system.
1) Fill in the fields within the FileMaker and Other tabs of the FmPro Migrator application. For Windows, enter the FileMaker ODBC DSN on the FileMaker tab. Make sure that the Local and Remote Data Access Companion plug-ins are enabled for each FileMaker database. Each FileMaker database should be configured with no password required for access to the entire database, while FmPro Migrator is gathering info about the database.
2) Select or create the destination directory for the conversion files which will be generated.
3) Save the configuration information by selecting Save As from the File menu.
4) Open one (or multiple) FileMaker database files.
Note: If any FileMaker database files contain spaces, these should be removed before opening the file.
5) Press the Migrate button to generate the conversion scripts and database documentation files.
6) Create a new DB2 database and a new DB2 database user account (if needed).
7) Examine the example_create_table1.sql file to determine if any changes need to be made. Common changes include reducing the width of VARCHAR columns to meet the DB2 database rowwidth limits and changing the default USERSPACE1 tablespace name. Transfer this file to the DB2 server and execute this code with the command line db2 utility to create the table which will contain the FileMaker data. [If a migration is done from FileMaker 7.0v1 to DB2 with FmPro Migrator for Windows, please see the troubleshooting notes for more info about column type issues.]
8) Change the name of the FileMaker Pro database to remove any spaces or special characters, otherwise the SQL query which retrieves data from FileMaker within the example_fmpro_to_db2_xfer_odbc1.pl program will fail. Then re-generate the conversion scripts.
9) Transfer the example_fmpro_to_db2_xfer_odbc1.pl program to a Windows server. At the present time the example_fmpro_to_db2_xfer_odbc1.pl Perl script needs to be run from a Windows server due to the lack of a FileMaker ODBC driver for Mac OS X.
10) Install the Perl DBI, DBD::ODBC and DBD::DB2 modules on the Windows server along with the FileMaker ODBC Driver and the DB2 client software. The DB2 client software will automatically be installed as part of the DB2 installation process, or it can be installed separately. (The DBI/DBD::ODBC and DBD::DB2 modules can be downloaded from www.cpan.org) or installed with Activestate PPM. Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> install DBD-DB2
PPM> quit

11) Create the example_fmpro_dsn entry in the ODBC Control Panel. Change the configuration of the Max Text length parameter from 255 to 65000 in the Advanced tab of the FileMaker DSN.
12) The DB2 database connection alias will be created on the database server when the database is installed. The connection aliase defaults to the same name as the database. If client software is installed on another computer, this connection name will need to be created with the DB2 Configuration Assistant utility. The DB2 connection alias is the value which should be entered into the database name field on the Other tab of FmPro Migrator.
13) Enable the Local and Remote Data Access Companions within the FileMaker application preferences dialog. Enable Multi-User access and both the Local and Remote Data Access Companions by selecting Sharing from the File menu of each database file. Each database should allow complete access without requiring a password in order for FmPro Migrator to read the structure of each database file.
14) Execute the example_fmpro_to_db2_xfer_odbc1.pl program to transfer the data from FileMaker to DB2 via an ODBC/DBD::DB2 network connection.

Use the following instructions if there are repeating fields which need transferred from FileMaker to DB2.
15) If repeating fields need to be extracted from the FileMaker database, use the example_repeating_fields_create_table1.sql SQL script to create the DB2 example_repeating table for the repeating fields data. The column sizes defined within this script should be adjusted as necessary prior to creating this table.
16) Change each FileMaker repeating field to be a TEXT field so that each of the repeating values will be transferred properly. (Note: Do not make this change prior to generating the migration scripts or the repeating fields will be created with the wrong datatypes. Also, do not make this change before transferring the data from FileMaker to DB2 in Step #14 of this procedure.)
17) Execute the example_repeating_fields_xfer_odbc1.pl program to transfer the repeating fields data from FileMaker to DB2 via an ODBC network connection.

---------- Usage Instructions - Image Export Script ----------
[Enterprise Edition feature]
The example_fmpro_image_export1.pl Perl script provides a JPEG image export feature for FileMaker Pro databases. This script exports one specified container field as a JPEG file into a user-defined directory on the computer where this script is running. The name used for writing the JPEG file is retrieved from a user-specified FileMaker database field.
1) Fill in the fields of the FileMaker and either the Oracle or Other tabs of the FmPro Migrator application.
2) Select or create the destination directory for the conversion files which will be generated.
3) Save the configuration information by selecting Save As from the File menu.
4) Open the FileMaker database file.
5) Press the Migrate button to generate the migration scripts and database documentation files.
6) Create the example_fmpro_dsn ODBC DSN entry in the ODBC Control Panel. Change the configuration of the Max Text length parameter from 255 to 65000 in the Advanced tab of the FileMaker DSN.
7) Make the following changes within the example_fmpro_image_export1.pl Perl script in order to specify the name of the container field, filename field and destination directory.
The following example shows that the container field has been named "image_field", the filename field has been named "filename_field" and the output directory is named "my_images" at the top level of the C: drive. The output directory needs to be specified using \ directory separators on Windows and needs to contain a trailing directory separator at the end of the directory name.
--------- Example code follows
my $fmpro_image_data_field = 'image_field';
my $fmpro_image_filename_field = 'filename_field';
my $image_output_directory_path = 'c:\\my_images\\';
---------
8) Execute the example_fmpro_image_export1.pl Perl script on a PC running Windows (or Vitual PC running on a Macintosh) as follows:
perl example_fmpro_image_export1.pl

The JPEG images will be exported from the FileMaker database and written to the output directory. Repeat steps 7 and 8 as needed if there are multiple container fields which need exported.

# ----------------------- Usage Notes -----------------------
Large Text Fields [EE edition]
FileMaker fields containing more than 32672 characters of text will be converted to DB2 CLOB columns when migrated to the CLOB database. The list of these column names should be entered into the Large Text Fields field. Data is transferred between FileMaker and DB2 by the example_fmpro_to_db2_xfer_odbc1.pl program. This program makes use of bind variables to specify field names and ODBC field types.

Extra FileMaker Fields
If there are fields within the FileMaker database which don't need to be transferred to the DB2 database, these fields should be removed before generating the conversion scripts. This can be easily accomplished by making a copy of the FileMaker database, then removing the extra fields from the copied database file. This technique reduces the chance of making a mistake by manually editing the files generated by FmPro Migrator.

ODBC driver and client software installation [EE edition]
The only way to transfer data within large text fields and FileMaker Container fields is by using the example_fmpro_to_db2_xfer_odbc1.pl program. This program is generated by FmPro Migrator based upon the specified FileMaker database structure in order to facilitate the transfer of data to DB2.
The example_fmpro_to_db2_xfer_odbc1.pl program transfers data for all FileMaker field types including text fields up to 64000 bytes and JPEG image data from Container fields. The program makes use of bind variables to specify field names and ODBC field types. The example_fmpro_to_db2_xfer_odbc1.pl program needs to be installed on a Windows server which has DB2 client software, Perl, Perl DBI module, Perl DBD::ODBC module, Perl DBD::DB2 module and FileMaker Pro ODBC driver software installed. This is due to the lack of a FileMaker Pro ODBC driver for Mac OS X. The example_fmpro_to_db2_xfer_odbc1.pl script running on the PC can then read the data from FileMaker Pro (hosted on either Mac OS, Mac OS X or Windows) and then be written into a DB2 database.

Repeating Fields
If repeating fields are detected within the FileMaker database, two extra scripts will be created in order to extract the repeating fields data from FileMaker to DB2. The example_repeating_fields_create_table1.sql creates a new database table for the related data contained within the repeating fields. The example_repeating_fields_xfer_odbc1.pl should then be run in order to extract the repeating fields data into the new example_repeating database table.

Notes:
It is necessary for FmPro Migrator to accurately determine the primary key field within the FileMaker database in order to properly generate the repeating fields extraction scripts. The primary key for the primary database table is used to reference each repeating field record. Therefore at least one field in the main database table must be set within FileMaker with the UNIQUE and NOT EMPTY validation parameters. It is recommended that this field be a numeric field using an auto-enter serial number within FileMaker. This field will then be used as the primary key column for the transferred data.
In order for all repeating field data values to be transferred from FileMaker, the data type within FileMaker must be a Text field. Each repeating field entry is separated by an ASCII (29) character which is used by FileMaker Pro to separate values within repeating fields. The example_repeating_fields_xfer_odbc1.pl script splits the data values into separate records by using the ASCII (29) character as a record delimiter and writes out each repeating field value into a new record within the example_repeating table.
If repeating values are present within FileMaker numeric, date or time fields, only the first value will be transferred to the destination database. Therefore each repeating field needs to be converted to Text fields within the Define Fields dialog box within FileMaker. This needs to be done after creating the migration scripts. This change should not be made within FileMaker before the migration scripts are created, or the repeating field database table will be created with the wrong datatypes (i.e. all fields will be TEXT).
FileMaker container fields which are configured as repeating fields cannot be extracted.

Relationships
FileMaker Pro relationships are not automatically traversed and converted by FmPro Migrator because full pathname info is not available for each file. The FileMaker Pro developer should manually open each related file and then FmPro Migrator STD/EE Edition will create conversion scripts for each open file. Please note that it may not be necessary to manually open each related file because some files may be opened automatically in the background by FileMaker. In this case, no additional action is required. FmPro Migrator Lite edition requires the opening of each file individually prior to starting the conversion process because only one file at a time is processed.

Container Fields
FileMaker Pro uses Container fields for storing pictures, sound and QuickTime video. Data located within FileMaker Pro Container fields is transferred to DB2 BLOB columns. There are some limitations which have been discovered while retrieving Container field data from FileMaker Pro. The only Container field data supported by transfer with FmPro Migrator is JPEG image data. All container fields include the text "HTTP/1.0" prefixed to the data. This header information is removed from the data, with the remaining JPEG file data in the field being transferred into the DB2 database table. Other types of container field data are also prefixed with the "HTTP/1.0" file header information which is followed by a JPEG icon representing the type of data contained within the field.

FileMaker Pro does not support writing Container field data into a FileMaker Pro database via an ODBC connection on either Windows or Macintosh platforms.

Container Field Workaround:
One workaround to the issue of transferring container field data is to store only a pathname to the data within the FileMaker Pro database. The example_fmpro_to_db2_xfer_odbc1.pl program can be modified to read the pathname from the FileMaker Pro database, then read the binary data directly from a local or remotely shared directory. An example of this type of modification is included in the example_fmpro_to_db2_xfer_odbc21.pl program.

Working with FileMaker Pro on Windows - FmPro Migrator SEE Edition can work with FileMaker Pro databases running on Windows servers for the data transfer phase of the conversion process. However a copy of the FileMaker Pro database must be running under Mac OS X in order for FmPro Migrator to gather the list of fields and field attributes information. FmPro Migrator cannot retrieve database structure information from stand-alone applications created for the Windows platform because Apple Events are not available on Windows. Furthermore, it is not possible to retrieve data from stand-alone applications via ODBC due to the lack of networking with stand-alone solution files.

Working with Stand-Alone Solutions Files - FmPro Migrator EE Edition can read the database structure from stand-alone solutions files created with FileMaker Pro Developer on Mac OS X. It will not be possible to extract the information from the file by using an ODBC connection because networking capability is not available with FileMaker stand-alone files. The stand-alone file will also need to be the Macintosh version of the executable in order for Apple Events used by FmPro Migrator to read the structure of the database file.

Primary Key Determination - FmPro Migrator examines the structure of the FileMaker Pro database in order to determine which column should represent the primary key for the DB2 database table. The primary key column of the DB2 database table will be migrated as a primary key column using a BIGINT datatype with the IDENTITY attribute. FmPro Migrator selects the first FileMaker Pro field which is configured with UNIQUE and NOT NULL options as the primary key. This determination is made by examining each field option in the field creation order, as is listed on the database structure report. To insure that FmPro Migrator selects the correct field as the primary key, it may be necessary to temporarily disable either the UNIQUE or NOT NULL options for other database fields before generating migration scripts.

DB2 Row Size Limits - The size of each database row is limited by the page size defined for the tablespace containing the database table. The width of VARCHAR columns may need to be reduced in order to accomodate the maximum row size for the table being created. The use of CLOB columns for large text fields can also help meet this requirement. The following list shows the maximum row sizes for each page size:

Home | Products | Services | Downloads | Order | Support | Contact

Legal Notices