DB2 zOS extractor - installation and configuration

Installation

The first step is to upload the extractor source code onto the z/OS execution environment. Please make sure you use the source code of the extractor that corresponds to the version of the DB2 server you are working on. You then need to adapt the JCL source code to the environment in which it is going to be executed and, perhaps, to the version of DB2. This operation MUST be completed before you run the z/OS job.

Configuration

Specific cards

Configuration changes must be made in the following JCL cards:

• JOB cards
• JOBLIB DD card

In addition, it may be necessary to adapt the SYSTSIN cards of steps STEP02 to STEP26 to the version of DB2. The macros to adapt are as follows:

• SYSTEM
• LIB
• PLAN

//SYSTSIN  DD *
  DSN SYSTEM(DB8G)
  RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIB81) -
    LIB('DSN810.RUNLIB.LOAD') PARMS('SQL')

Dataset names and MANIFEST

If the dataset names must be changed, then it is important to modify the following cards:

• STEP00: SYSIN card
• STEP01: FILEOUT card
• STEP02 to STEP26: SYSREC00 card

The content of the section "GENERATED FILES" of the file "CAST.DB2.MANIFEST" (generated by step STEP01 in FILEIN DD card) must be coherent with the final dataset names used on the Windows server and which are delivered via the CAST Delivery Manager Tool. For instance, if the file containing the database information is named "TSTENV.DB2.DBASE" on the z/OS environment and then "DBASE.TXT" on the Windows server, then the content of the file "CAST.DB2.MANIFEST" must be modified to reference "DBASE.TXT". However, the content of the MANIFEST file can be changed once it is on the Windows server.

Dataset names used in step STEP00 (SYSIN DD card) must be coherent with the dataset names used in steps STEP02-STEP26 (SYSREC00 DD cards).

The other sections of the file "CAST.DB2.MANIFEST" must not be changed.

Size of extracted columns

In the generated files, each extracted column is preceded by its maximum size (the current values come from the DB2 system catalog documentation). For instance, for DB2 v8, a column that is extracted with 128 characters is preceded by the value 128 and this value is different for DB2 v7. Each column containing a size is a 11-character string. Each step contains a header describing the size of columns and the size of extracted fields.

In the case where the JCL must be used with another utility or where the column sizes are different from those which are currently specified in the JCL, it is necessary to adapt the values.

For instance, in the step STEP06, the following query is executed:

SELECT
CHAR(24),  CHAR(DBNAME),
CHAR(128), CHAR(CREATOR),
CHAR(128), CHAR(NAME),
CHAR(1),   TYPE,
CHAR(24),  CHAR(TSNAME),
CHAR(128), CHAR(TBCREATOR),
CHAR(128), CHAR(TBNAME),
CHAR(128), CHAR(LOCATION),
CHAR(255), SUBSTR(REMARKS,1,255)

FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T'
ORDER BY DBNAME, NAME, TYPE;

The column NAME is extracted with 128 characters and so is preceded by the value "128"+ 8 spaces. Here is an example of extracted results:

If the extracted size does not respect this value, then modify the query properly. It is important that the two columns are coherent. Otherwise, the analyzer will generate an error and it will stop the analysis.

Some columns can contain binary characters such as the column TEXT in the SYSVIEWS table. When these columns are extracted, they start with two binary characters corresponding to the size of the string. In this specific case, they are identified by a star ('*') in the file "CAST.DB2.MANIFEST". Thus, the analyzer can process them correctly. On the contrary, if a column does not start with these binary characters, then it must not be followed by a star in the file "CAST.DB2.MANIFEST". For instance, the REMARKS columns are different in DB2 v7 and DB2 v8. On DB2 v7, they do not contain binary characters, while on DB2 v8, they start with these two characters. As such, the MANIFEST files are different.

Relationships between files

It is important to note that generated files are analyzed in a specific order. There are relationships between them and, in specific cases, some records can be ignored. We can separate the generated files into two categories: those which contain object definitions (main files) and those which contain links between objects (secondary files). If an object is referenced in a secondary file and it is not defined in a main file, then the record found in the secondary file will be ignored.

For instance, if an object is referenced in the file "TABLES.COL" but it is not found in the file "TABLES.DEF" or in the file "VIEWS.DEF", then this information will not be taken into account. The same behaviour occurs if a view is referenced in the file "VIEWS.DDL" and if this view is not present in the file "VIEWS.DEF".

Filtering the data extraction

It is possible to adjust the data extraction by filtering the generated information. This can be done by inserting specific predicates in the WHERE clause of SQL queries. These predicates are generally based on either (A) creator (for example: CREATOR IN ('xxx', 'yyy', ...), where xxx and yyy are creator names) or (B) database name (for example: CHAR(DBNAME) IN ('xxx','yyy', ...), where xxx and yyy are database names).

Please note that filtering must be done using the same criteria in all steps to avoid inconsistencies in the information that is generated. The steps which can be changed to insert filters are as follows:

• 02 - EXTRACTING DATABASES (can be filtered by using predicate A or B)
• 06 - EXTRACTING TABLES (can be filtered by using predicate A or B)
• 07 - EXTRACTING VIEWS(can be filtered by using predicate A or B)
• 08 - EXTRACTING ALIASES (can be filtered by using predicate A or B)
• 09 - EXTRACTING MATERIALIZED QUERY TABLE (can be filtered by using predicate A or B)
• 10 - EXTRACTING AUXILIARY TABLES (can be filtered by using predicate A or B)
• 11 - EXTRACTING COLUMNS (can be filtered by using predicate A or B)
• 12 - EXTRACTING INDEX (can be filtered by using predicate A or B)
• 13 - EXTRACTING INDEXED COLUMNS (can be filtered by using predicate A or B)
• 14 - EXTRACTING TRIGGERS (can be filtered by using predicate A)
• 15 - EXTRACTING TABLE REFERENCES (can be filtered by using predicate A or B)
• 16 - EXTRACTING VIEW DEPENDENCIES (can be filtered by using predicate A)
• 17 - EXTRACTING VIEW DDL (can be filtered by using predicate A)
• 18 - EXTRACTING TRIGGER DDL (can be filtered by using predicate A)
• 24 - EXTRACTING MATERIALIZED QUERY TABLE SRC (can be filtered by using predicate A)
• 25 - EXTRACTING CREATED GLOBAL TEMP TABLES (can be filtered by using predicate A or B)
• 26 - EXTRACTING NUMBER OF ROWS FOR TABLES (can be filtered by using predicate A or B)

Please consult the headers of above steps for suggested filters.