Schema Extraction Process

Restriction: This topic applies to Windows environments only.

The Extract Schema tool retrieves information about various z/OS DB2 SQL objects, and then stores this information in an XML file for further processing with HCOSS.

The standard schema extraction method is to first connect to your z/OS DB2 database, and then retrieve the required information directly from the z/OS DB2 catalog.

You can optionally use the offline schema extract solution. With this approach, you first migrate the z/OS DB2 catalog information to SQL Server, and then use the Extract Schema tool connected to SQL Server. This eliminates the need to connect to z/OS DB2 directly. For details on this approach, see Offline Extract Schema Solution.

The Extract Schema tool 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:

Note: To extract CREATE VIEW statements with proper translation when using DB2 for z/OS version 10 and higher, you might need to set the Cast STATEMENT column to VARCHAR during SysViews extract Extract Schema option to True. See the To set HCOSS Options and Options topics for more information.

Connects to a DB2 database directly, or connects to a SQL Server database that contains z/OS DB2 catalog information migrated using the method outlined in Offline Extract Schema Solution.
Requests a list of available schemas
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
Reports results