Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Panel
Table of Contents

Running the extractor job

Info
The CAST DB2 for z/OS extractor always saves its results in the same data sets. As a result, if you want to save several extractions, then you must make sure you transfer the results of each extraction into different directories prior to carrying out the next extraction. This will avoid overwriting the previous results with new results.

Once the JCL script has been adapted to the execution environment, it can then be submitted.

Required privileges

To execute the JCL script on the DB2 z/OS server, it is necessary to have the following privileges:

  • The user running the JCL script requires WRITE privileges for writing the output files (DSN defined inside the JCL)
  • In addition, the extractor relies on the IBM utility IKJEFT01 (purpose is to use TSO command - here DB2 utility DSNTIAUL - from JCL batch)
    • The extractor uses DSNTIAUL to access DB2 system catalog tables
    • Users require SELECT authority for the following tables:
      • SYSIBM.SYSCOLUMNS
      • SYSIBM. SYSDATATYPES
      • SYSIBM.SYSFOREIGNKEYS
      • SYSIBM.SYSINDEXES
      • SYSIBM.SYSKEYS
      • SYSIBM.SYSPARMS
      • SYSIBM.SYSRELS
      • SYSIBM.SYSROUTINES
      • SYSIBM.SYSROUTINES_SRC
      • SYSIBM.SYSTABLES
      • SYSIBM.SYSTRIGGERS
      • SYSIBM.SYSVIEWDEP
      • SYSIBM.SYSVIEWS
    • Note: to grant SELECT authority to access the DB2 Catalog, use this command: 

      • GRANT AUTHORITY_TYPE ON TABLE TABLENAME TO USER;

      • For example, to grant SELECT authority on the table SYSIBM.SYSTABLES to CASTEXT (user running the Extractor), use the following command:

        • GRANT SELECT ON TABLE SYSIBM.SYSTABLES TO CASTEXT;

Tempspace

STEP15 of the JCL script can require a lot of TEMPSPACE for the ORDER BY clause. This depends on the number of objects and dependencies to extract - please ensure that a large tempspace is allotted.

Output

The extractor will query the DB2 system catalog by connecting to the DB2 subsystem and produce 26 files containing information about database structures and elements. They constitute a coherent group of data and so it is important to save them into the same directory.

These files must then be transferred onto the CAST analysis workstation via FTP so that they can be packaged by the CAST Delivery Manager Tool. They constitute a coherent group of data and so it is important to save them into the same directory. When transferring extraction results from the host server to your Windows platform after using the DB2 zOS Extractor, please ensure that you follow these instructions to ensure that data is correctly transferred to Windows and no trailing spaces are removed.

Transfer generated files to a Windows based network location that can be accessed by the CAST Delivery Manager Tool

There are a variety of methods that can be used to transfer the output of the DB2 z/OS extractor to a Windows based network location ensuring that no trailing spaces are removed. The table below lists the methods that CAST has previous experience of and the options you should add to ensure that the trailing blanks are not removed:

Tool used for file transfer

Options to add

Sample command (generated in case of GUI)

z/OS FTP tool

LOCSITE TRAILINGBLANKS

LOCSIte TRAILingblanks

Windows FTP client via the command line

QUOTE SITE TRAIL 

  • Open an FTP connection to the system on which the DB2 Extractor was run
  • Navigate to the location of the files created by the DB2 z/OS extractor
  • Enter quote site trail in to the command prompt
  • Use the GET option to transfer the files to your your local hard drive

IND$FILE

Manually add NOTRUNC to additional option field.

 
Please note that ASCII CRLF is generated automatically.

IND$FILE GET ‘CAST.DB2.TABLE’ ASCII CRLF NOTRUNC

Sterling Connect:Direct

Manually add STRIP.BLANKS (NO) to SYSOPTS option field. For example:

Please see IBM Sterling Connect:Direct process language documentation for more information.

Validate output

zosXtrChecker.exe

A tool named "zosXtrChecker.exe" is delivered with CAST which allows the consistency of the extraction files to be verified. In addition, it can also compact the data files by removing the unnecessary blank characters. Once you have transferred the extracted files to the CAST Administration workstation you should run this tool.

This tool requires the CAST AIP distribution to be executed. Input parameters are as follows:

  • manifest file/filter file
  • required check mode
  • compact action

Required syntax

The syntax for the zosXtrChecker.exe tool is as follows:

No Format
zosXtrChecker.exe file | /? [mode] [compact]
  • /? will display a small help topic
  • [mode] is optional and corresponds to the validation checks that will be performed. It can accept the following values:
    • 0 (default) - checks that files declared in the MANIFEST are all there.
    • 1 - checks the contents of the following file types:
      • GLOBAL.DATABASES = databases
      • GLOBAL.SCHEMAS = schemas
      • GLOBAL.DTP = system supplied data types
      • GLOBAL.UDT = user supplied data types
      • TABLES.DEF = tables without columns
      • VIEWS.DEF = views without columns
      • GLOBAL.ALIASES = aliases;
    • 2 - checks the contents of the following file types:
      • PROCEDURES.DEF = procedures
      • FUNCTIONS.DEF = functions
      • GLOBAL.IDX = indexes
      • TRIGGERS.DEF = triggers
      • GLOBAL.PARAMETERS = parameters
    • 4 - checks the contents of the following file types:
      • TABLES.COL = tables and views columns
      • GLOBAL.IDXCOL = indexed columns
    • 8 - checks the contents of the following file types:
      • TABLES.DEP = table references for PK / FK constraints
      • VIEWS.DEP = views dependencies
      • TRIGGERS.DDL = triggers DDL script
      • VIEWS.DDL = views DDL script
      • PROCEDURES.DDL = procedures DDL script
      • FUNCTIONS.DDL = functions DDL script;
  • [compact] is optional. It removes unnecessary blank characters in data files. This operation keeps SIZE columns and VALUE coherent. Original files are not altered and compacted files will have the "BCK" extension. The option can accept the following values:
    • 0 (default) - compact action carried out
    • 1 - compact action carried out
  • If you do not declare the [mode] and [compact] options, the default values for both will be used.
  • You can combine multiple [mode] values to carry out mutiple tests. This is done by adding together the required mode values and specifying the total. For example to combine modes 1 and 2, you need to specify "3" in the command line. The default test (mode 0) will always be carried out.

Examples

To run the tool with default settings, please use the following - this will use the MANIFEST file you have specified and will check that all files declared in the MANIFEST are present.

No Format
%CAST_INSTALLATION_LOCATION%\zosXtrChecker.exe  D:\extraction\my.MANIFEST

To specify a particular mode and to run the compact action:

No Format
%CAST_INSTALLATION_LOCATION%\zosXtrChecker.exe  D:\extraction\my.MANIFEST 1 1

To specify multiple modes (modes 0, 1 and 4 = 5) and to run the compact action:

No Format
%CAST_INSTALLATION_LOCATION%\zosXtrChecker.exe  D:\extraction\my.MANIFEST 5 1