7.5 Migration Options
7 Migration Toolkit Command Options : 7.5 Migration Options
-loaderCount [value]Use the -loaderCount option to specify the number of parallel threads that Migration Toolkit should use when importing data. This option is particularly useful if the source database contains a large volume of data, and the Postgres host (that is running Migration Toolkit) has high-end CPU and RAM resources. While value may be any non-zero, positive number, we recommend that value should not exceed the number of CPU cores; a dual core CPU should have an optimal value of 2.Please note that specifying too large of a value could cause Migration Toolkit to terminate, generating a 'Out of heap space' error.Truncate the data from the table before importing new data. This option can only be used in conjunction with the -dataOnly option.Include the -enableConstBeforeDataLoad option if a non-partitioned source table is mapped to a partitioned table. This option enables all triggers on the target table (including any triggers that redirect data to individual partitions) before the data migration. -enableConstBeforeDataLoad is valid only if the -truncLoad parameter is also specified.-retryCount [value]If you are performing a multiple-schema migration, objects that fail to migrate during the first migration attempt due to cross-schema dependencies may successfully migrate during a subsequent migration. Use the -retryCount option to specify the number of attempts that Migration Toolkit will make to migrate an object that has failed during an initial migration attempt. Specify a value that is greater than 0; the default value is 2.If you include the -safeMode option, Migration Toolkit commits each row as migrated; if the migration fails to transfer all records, rows inserted prior to the point of failure will remain in the target database.Including the -fastCopy option specifies that Migration Toolkit should bypass WAL logging to perform the COPY operation in an optimized way, default disabled. If you choose to use the -fastCopy option, migrated data may not be recoverable (in the target database) if the migration is interrupted.-replaceNullChar valueHowever, the Migration Toolkit does not support importation of NULL character values (embedded binary zeros 0x00) with the JDBC connection protocol. If you are importing data that includes the NULL character, use the -replaceNullChar option to replace the NULL character with a single, non-NULL, replacement character. Do not enclose the replacement character in quotes or apostrophes.Once the data has been migrated, use a SQL statement to replace the character specified by -replaceNullChar with binary zeros.Include the -analyze option to invoke the Postgres ANALYZE operation against a target database. The optimizer consults the statistics collected by the ANALYZE operation, utilizing the information to construct efficient query plans.Include the -vacuumAnalyze option to invoke both the VACUUM and ANALYZE operations against a target database. The optimizer consults the statistics collected by the ANALYZE operation, utilizing the information to construct efficient query plans. The VACUUM operation reclaims any storage space occupied by dead tuples in the target database.Specify a single character to be used as a delimiter in the copy command when loading table data. The default value is '\t' (tab).Specify the batch size of bulk inserts. Valid values are 1-1000. The default batch size is 1000; reduce the value of -batchSize if Out of Memory exceptions occur.Specify the batch Size in MB, to be used in the COPY command. Any value greater than 0 is valid; the default batch size is 8 MB.Specify the number of rows to be loaded in a batch for LOB data types. The data migration for a table containing a large object type (LOB) column such as BYTEA, BLOB, or CLOB, etc., is performed one row at a time by default. This is to avoid an out of heap space error in case an individual LOB column holds hundreds of megabytes of data. In case the LOB column average data size is at a lower end, you can customize the LOB batch size by specifying the number of rows in each batch with any value greater than 0.Use the -fetchSize option to specify the number of rows fetched in a result set. If the designated -fetchSize is too large, you may encounter Out of Memory exceptions; include the -fetchSize option to avoid this pitfall when migrating large tables. The default fetch size is specific to the JDBC driver implementation, and varies by database.MySQL users note: By default, the MySQL JDBC driver will fetch all of the rows that reside in a table into the client application (Migration Toolkit) in a single network round-trip. This behavior can easily exceed available memory for large tables. If you encounter an 'out of heap space' error, specify -fetchSize 1 as a command line argument to force Migration Toolkit to load the table data one row at a time.-filterProp file_namefile_name specifies the name of a file that contains constraints in key=value pairs. Each record read from the database is evaluated against the constraints; those that satisfy the constraints are migrated. The left side of the pair lists a table name; please note that the table name should not be schema-qualified. The right side specifies a condition that must be true for each row migrated. For example, including the following constraints in the property file:migrates only those countries with a country_id value that is not equal to AR; this constraint applies to the countries table.-customColTypeMapping column_listUse custom type mapping to change the data type of migrated columns. The left side of each pair specifies the columns with a regular expression; the right side of each pair names the data type that column should assume. You can include multiple pairs in a semi-colon separated column_list. For example, to map any column whose name ends in ID to type INTEGER, use the following custom mapping entry:Custom mapping is applied to all table columns that match the criteria unless the column is table-qualified.The '\\' characters act as an escape string; since '.' is a reserved character in regular expressions, on Linux use '\\.' to represent the '.' character. For example, to use custom mapping to select rows from the EMP_ID column in the EMP table, specify the following custom mapping entry:On Windows, use '\.' to represent the '.' character:You can include multiple custom type mappings in a property_file; specify each entry in the file on a separate line, in a key=value pair. The left side of each pair selects the columns with a regular expression; the right side of each pair names the data type that column should assume.
7 Migration Toolkit Command Options : 7.5 Migration Options