4.3 Extracting or Importing metadata into MTK

---

4.3 Extracting or Importing metadata into MTK

Once the project is created, the Specify Source screen opens. During this task we will:

• Specify how the metadata is obtained.

• Obtain the metadata.

Figure 4-2 shows the Specify Source tab; on the left side of the screen are the buttons Import and Export.

Figure 4-2: The Specify Source tab

• Import

  If a metadata file already exists (a script from SQL*Plus, or a third-party tool) and no data movement by MTK is required, then Import should be selected. Selecting this option will allow an individual DDL file or multiple DDL files from your file system to be selected and imported into MTK as the conversion source.

  Note 

  When importing objects for conversion, it must be emphasized that some objects will not convert unless all of the objects on which it depends are also available within the imported file or files. For example, if you choose to import the source for an individual procedure, you must also ensure that you include all of the underlying definitions for tables that are referenced within that procedure. Also, you must ensure that the definitions for all dependent objects must precede the objects that rely on them. For example, definitions of tables referenced in stored procedures must precede the definition of the stored procedure.

• Extract

  For our example conversion, we chose Extract. Choosing this option allows us to connect to the Oracle source system and extract all the metadata (DDL). This is accomplished by "reading" the system catalogs and then creating a file that will be imported into the tool. The extracted DDL, converted DDL and generated data movement scripts, and actual extracted data (during data movement) are defaulted to the server where MTK is installed, in the MTK installation subdirectory path.

  Note 

  The Oracle source system to which MTK connects can either be on a local or a remote server. For a valid connection, it is required that the Oracle Client be installed and configured in the CLASSPATH on the local machine except for Windows systems when ODBC drivers are used.

After Extract is chosen, the Connect to Database screen opens, which requires that we complete the following information:

• Service Name: The service name for the local/remote Oracle database

• User ID: The User ID for the schema, which owns the Oracle source

• Password: The Password for the schema, which owns the Oracle source

Figure 4-3 shows the Connect to Database screen completed for the service name oracle; the user ID ora_usr; and the corresponding password for the ora_usr schema.

Figure 4-3: Detail of the Connect to Database screen

4.3.1 Choosing objects to extract

After a successful connection to the database, the Extract screen opens. This screen shows Available objects (on the left side of the screen) and Objects to extract (on the right side of the screen). Once a schema is chosen, the Available objects will expand to show five categories of objects (Figure 4-4), even if the current database does not contain any objects of that category. The categories of objects are:

• Tables

• Views

• Procedures/functions

• Triggers

• Packages

Figure 4-4: The Extract screen with the ORA_USR schema selected

Once a category is selected, if the plus sign (+) is chosen, the category expands to show the individual objects that exist for that category. From here it is possible to make various selections of objects to be extracted from each category. For example, it is possible to choose any of the following for extraction:

• Individual objects from a single category (a single table, view, sequence, etc.)

• Individual objects from a multiple categories (a table, a view, a sequence, etc.)

• All objects from single categories (all tables, all views, all sequences, etc.)

• All objects from multiple categories (all tables and all views, all sequences and all triggers, etc.)

• All objects from all categories (the entire database schema)

Example 4-5 shows that multiple categories tables, views, and sequences are selected for extraction.

Figure 4-5: The Extract screen with tables, views and sequences selected

Figure 4-6 shows the Extract screen with the ORA_USR schema selected with the categories expanded to show individual objects in each category.

Figure 4-6: The Extract screen with schema and categories expanded

Note 

When extracting objects for conversion, it must be emphasized that some objects will not convert correctly (or at all) unless all of the objects on which it depends are also extracted. For example, when selecting an individual procedure for conversion, you must take care to also extract all of the underlying definitions for tables that are referenced by that procedure, Or, choose the include other needed objects option on the Extract screen. When the include other needed objects option is chosen, MTK will automatically include all the pertinent definitions to correctly convert the selected objects. Also, MTK will extract the objects in correct dependent order.

4.3.2 Import or extract strategies

As previously stated, it is possible to choose one single object, or the entire database for extraction. Although it may seem initially attractive to choose the entire database, we recommend that the extraction strategy be dictated by the size of the source database.

Some aspects of database sizing in regard to MTK should be briefly mentioned here. The accurate sizing of a database, from a migration point of view, should entail thorough analysis on several different levels. This analysis yields the information such as the number of lines of code; the complexity of the code; the code conformance/non-conformance to ANSI standards; the number of objects; the types of objects - just to name a few.

One of the best uses of MTK is as an aid in engaging in the type of analysis. MTK can be used to find, in detail, much of the information that is required to successfully analyze the database.

With that said, a general and simplistic approach to sizing the database can be used in the initial stages of analysis. In the initial stage we might not have any information about, for example, complexity or conformance to ANSI standards. In order to gather that information we would have to arrive at some guidelines for beginning the analysis. The guidelines at this stage are usually along these lines:

• Large database

  More than 200 stored procedures and functions (stand-alone or in Oracle Packages), and triggers.

• Small database

  Less than 200 stored procedures and functions (stand-alone or in Oracle Packages), and triggers.

Large database

For large databases, the extraction strategy should focus on creating separate files for each individual category. For example, a separate extraction file should be created for all tables, sequences, views, triggers, procedures, and functions. This strategy will facilitate "tracking-down," analyzing, and perhaps "fixing" possible issues that may arise in the conversion of a particular category. This is usually easier than trying to understand several interrelated issues, which may arise across the whole spectrum of categories.

Small database

For smaller databases, we recommend that the extraction files be grouped according to dependencies and dependents. For example, one file may consist of all tables, views, and sequences; and another file of procedures, functions, packages, and triggers. In this strategy, the first file will allow the objects on which the second file depends to be created and analyzed before converting the second file. In this way, we are also able to contain the messages and the interrelated issues that may occur.

Sample conversion

Considering the size of our database, we choose to follow the recommendation for small databases. For our extraction, the first file will contain all tables, views, and sequences; a second file will contain all procedures, functions, packages, and triggers.

For the first file, all objects in the categories: tables; views; and sequences are selected and moved to the Objects to extract panel. For File name, we choose and entered tabs_views_seqs.src (Figure 4-7).

Figure 4-7: The Extract screen with tables, views and sequences selected

Once this is completed, we clicked the Extract button, and extraction of the metadata began (Figure 4-8).

Figure 4-8: MTK during the Extract process

After the first file is created, we repeated the process, this time selecting all procedures, functions, packages, and triggers. For this file we choose and entered the name: procs_pkgs_trigs (Figure 4-9).

Figure 4-9: The categories for the second Extract file

4.3.3 Viewing Extracted files

When extraction has completed, we are returned to the Specify Source tab. Figure 4-10 shows the Specify Source tab after the extraction that was just completed. In the right hand panel, the files that were created during the extraction of tab_views_seqs.src and procs_pkgs_trgs.src are visible.

Figure 4-10: The Specify Source tab after all extraction files have been created

We can examine the extracted files by choosing one and then clicking the View button located on the lower right side of the panel. In our example, when tabs_views_seqs.src is selected and then viewed, we see the following screen displayed (Figure 4-11).

Figure 4-11: Viewing extracted files in the Text Editor

Note 

You can edit and save this file when it is viewed from this panel. This is sometimes useful for making changes to source code before it is to be converted.

The file was imported, the edited file is a copy in the project directory, and the original file will neither be changed nor damaged, and it can be re-imported in case of user errors during editing.

Note 

You can use the default editor or set up your preferred editor through the menu bar selecting Applications -> User Preferences.

---