Showing results for 
Search instead for 
Did you mean: 

How to use a user-defined condition to migrate rows in MTK

Users of EDB Migration Toolkit (MTK) can migrate specific rows rows by defining a condition in a file and then referencing it with the -filterProp option.

The format of the file referenced by -filterProp contains a constraint represented as a key=value pair. Each record read from the database is evaluated against this constraints, and only those that satisfy the constraint 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, countries=country_id<>'AR' migrates only those countries with a country_id value that is not equal to 'AR'; this constraint applies only to the countries table.

NOTE: Only one condition can be specified in the -filterProp file.
If a user wants to use mulitple conditions at the same time then can initiate multiple migrations (run multiple instances of MTK) with different prop files, but it may have its own drawbacks (like multiple DB connections etc..).

Sample table from Oracle:

SQL> select * from HIST_TRAN_DTL_TABLE;
--------+---------- 1 31-MAR-10 1 02-APR-10 1 03-JAN-19 2 05-OCT-19 1 10-NOV-19 6 rows selected.

Sample table from EPAS:

edb=# select * from hist_tran_dtl_table ;
bank_id |     tran_date
2 | 12-OCT-19 00:00:00
(1 row)

Contents of the HTD2.prop file to be used with the -filterprop option

[root@localhost Desktop]# cat /tmp/HTD2.prop
HIST_TRAN_DTL_TABLE=TRAN_DATE>to_date('31-JUL-2010','DD-MON-YYYY') and bank_id='01'

The condition defined in the -filterprop option would produce the following output:

SQL> select * from HIST_TRAN_DTL_TABLE where TRAN_DATE>to_date('31-JUL-2010','DD-MON-YYYY') and bank_id='01' ;
1 03-JAN-19 1 10-NOV-19

Migrate the relevant rows with the MTK command:

./ -tables HIST_TRAN_DTL_TABLE -dataOnly -filterProp /tmp/HTD2.prop -targetSchema enterprisedb SURAJ

Output from EPAS after successful migration:

edb=# select * from hist_tran_dtl_table ;
  bank_id   |     tran_date
 2          | 12-OCT-19 00:00:00
 1          | 03-JAN-19 00:00:00
 1          | 10-NOV-19 00:00:00
(3 rows)
Version history
Revision #:
13 of 13
Last update:
2 weeks ago
Updated by: