Schema Extraction Process

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.