Micro Focus Enterprise Test Server 2.3 Update 2 > Data Access > Database Access > HCO for SQL Server (HCOSS) > Migrating Databases > Extract Schema Tool

Extract Schema Tool

The HCOSS tools used to prepare for a migration rely on a data file known as a schema extract file. This file contains metadata extracted from the DB2 system catalog for database schemas you want to migrate. As the first step in preparing for a DB2 database to SQL Server database migration, use the Extract Schema tool to create this file. You can run the tool either from the HCOSS user interface, or from the command line using the MFExtractSchemaInfo command.

Schema Extraction Process

The Extract Schema tool is the only HCOSS tool that requires access to the mainframe before actual data migration begins. It extracts data for selected schemas from the following DB2 catalog tables in the SYSIBM schema:

Object Type	DB2 System Catalog Tables
tables, aliases, synonyms, columns, sequences	SYSTABLES SYSCOLUMNS SYSSEQUENCES SYSSYNONYMS
indexes	SYSINDEXES SYSKEYS
routines, parameters	SYSROUTINES SYSPARMS
constraints (relational, check, key)	SYSCHECKS SYSCHECKDEP SYSCHECKS2 SYSCONSTDEP SYSTABCONST SYSKEYCOLUSE SYSRELS SYSFOREIGNKEYS SYSSEQUENCESDEP
views	SYSVIEWS SYSVIEWDEP

Note: The user ID and password combination you use to connect to your mainframe must grant you READ access to your DB2 system catalog tables. For a complete list of required tables, see the topic Required DB2 Catalog Tables.

The Extract Schema tool does the following to create the schema extract file:

Connects with the DB2 database
Requests a list of available schemas in the DB2 database
Populates the tool with the list of available schemas
Enables selection of specific schemas for migration
Enables selection of an estimated or actual table row count for each table in the schema
Generates an XML file containing a representation of the objects in the selected schemas, including:
- Dependent objects such as dependent tables or views and tables referenced by a foreign key in a different table
- Parameter values necessary to create the same schema objects in SQL Server 2008
Reports results

Modifying, Exporting, and Importing Schema Extract Files

Once you have created a schema extract file, you can use the Schema Extract tool to modify it, export it, and/or import it. The export and import features enable you to share schema extract files with multiple users, and to assist Micro Focus SupportLine in troubleshooting migration issues.

Security

We offer two methods of implementing security with regard to catalog and mainframe access during the Extract Schema process:

Catalog Security: When catalog security is a concern, consider creating an additional schema containing views of SYSIBM DB2 catalog tables. This enables the user of the Extract Schema tool to extract from the new schema, and therefore does not require query access to the catalog tables themselves. If catalog security is not a concern, then anyone who runs the Extract Schema tool requires query access to the required DB2 catalog tables in the SYSIBM schema.
Mainframe Security: If your aim is to limit mainframe access to a single individual or system, you can use the Export and Import features of the Schema Extract tool to provide additional staff access to schema extract files without granting access to the mainframe.

HCOSS uses the generated schema extract file in the next step of the database migration process - defining lists of specific tables and views to migrate.