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
|
|
|
routines, parameters
|
|
|
constraints (relational, check, key)
|
- SYSCHECKS
- SYSCHECKDEP
- SYSCHECKS2
- SYSCONSTDEP
- SYSTABCONST
- SYSKEYCOLUSE
- SYSRELS
- SYSFOREIGNKEYS
- SYSSEQUENCESDEP
|
|
views
|
|
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.
In addition, you can use a schema extract file to generate stored procedure definition (SPD) files that can then be used to generate COBOL wrapper programs for your DB2 stored procedures.
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.
