Before migrating a third-party database, you must extract information from the database. This information is a representation of the structure of the source database, and it is called the captured model. The process of extracting the information from the database is called capturing the source database.
The capture can be done online or offline:
Online capture is done in a convenient guided sequence within the SQL Developer interface, as explained in Online Capture. You can use online capture with all supported third-party databases.
Offline capture involves creating a script that you run later, as explained in Offline Capture. You can use offline capture with IBM DB2, MySQL, Microsoft SQL Server databases, and Sybase Adaptive Server.
After capturing the source database, you can view the source database information in the captured model in SQL Developer. If necessary, you can modify the captured model and change data type mappings.
|
Note: Oracle recommends that you do not change the default data type mappings unless you are an experienced Oracle database administrator. |
Related Topics
Migrating Third-Party Databases
To perform an online capture of the source database, you can have the capture performed automatically as part of the Quick Migrate option, or you can have it performed as a separate operation by right-clicking the connection name in the Connections navigator and selecting Capture product-name (for example, Capture MySQL, Capture Microsoft Access, Capture Microsoft SQL Server, or Capture Sybase Adaptive Server).
For a Microsoft Access database, selecting Capture product-name automatically invokes the Microsoft Access exporter tool to create XML files for migrating the schema and the table data. However, if you want to run the exporter tool manually (for example, to control certain options), click Migration, then Microsoft Access Exporter, then the item for your version of Microsoft Access. Follow the steps for the exporter tool, which has its own online help.
To perform an offline capture of an IBM DB2, MySQL, Microsoft SQL Server, or Sybase Adaptive Server database, you create a set of offline capture scripts, run these scripts outside SQL Developer to create the script output (a dump of the third party metadata tables), and load the script output (the .ocp file containing the converted model) using SQL Developer.
To create the script file (a Windows .bat file or a Linux or UNIX .sh file) and related files, click Tools, then Migration, then Third Party Database Offline Capture, then Create Database Capture Scripts.
When this operation completes, you are notified that several files (.bat, .sql, .ocp) have been created, one of which is the controlling script. You must run the controlling script (outside SQL Developer) to populate the object capture properties (.ocp) file with information about the converted model.
To load the converted model from the object capture properties (.ocp) file generated by the offline capture controlling script, click Tools, then Migration, then Third Party Database Offline Capture, then Load Database Capture Script Output.
Script files and the db2_x.ocp file are generated in the target folder. The main script is startDump.xxx, which you must execute to produce the schema dump. The script files prompt you for the database name, user name, and password, and they use this information to connect to the local DB2 database. The scripts generate the schema dump for database objects within object-specific folders.
To capture the schema information in offline file format, use a command in the following format (with the db2 executable in the run path):
db2 -x +o -r <file name> <schema query>
To export the schema data in offline file format, use a command in the following format (with the db2 executable in the run path):
For DB2 version 9 data export:
db2 export to <file name> of DEL modified by lobsinsepfiles coldel"#" timestampformat=\"YYYY/MM/DD HH.mm.ss\" datesiso nochardel <select query>
For DB2 version 8 data export:
db2 export to <file name> of DEL modified by coldel"#" timestampformat=\"YYYY/MM/DD HH.mm.ss\" datesiso nochardel <select query>
DB2 version 9 supports LOB data in separate files, which is better for migrating large data sizes. With version 8, to support large LOB data, you must modify the oracle ctl file command and db2 command in unload_script.bat or unload_script.sh.
The table data is exported to files with names in the format <catalog>.<schema>.<table>.dat. The format of file is as follows: data1#<COL_DEL> #data2#<COL_DEL>…<ROW_DEL> where COL_DEL and ROW_DEL come from migration offline preference settings.
Before you execute the DB2 data dump script, you must log in by entering a command in the following format:
db2 connect to <catalog> user <user name> using <password>
You can then execute the script using the logged connection session.