CMS Snapshot Analysis - Run Analyzer - Fatal Error - IBM DB2 ZOS with Core CAST AIP - The required zOS extracted reference file is of unknown format

In some cases, the data extracted from a DB2 ZOS system may not be in the correct format and can lead to missing objects in the analysis and dashboard

Step by Step scenario

1. Extract Data from DB2 ZOS.
2. Run Data through DMT and analysis with no errors.
3. Data from DB2 ZOS is missing on the dashboard

1. Use a new extractor script. If binary columns are used,the DB2 ZOS files may not be properly extracted. NB : This workaround is the best, as it solves the problem for good : the next extraction will be correct.
   1. Use the attached script CAST_JCL_EXTRACT_FOR_DB2_V10 - 1.1.jcl  as an extractor script to replace the existing one in  <InstallFolder>\Extractors\DB2Extractor
   2. Redo the extraction
   3. Redo the analysis.
   4. If it is not possible to re-extract, then proceed to next step.

2. Modify the Extracted Files so that they are properly analyzed.

   1. Manually manipulate the files to correct the extraction to temporarily fix the problem
      1. First, modify the manifest file information to remove binary column fields.  

         1. Find out the section titled 'GENERATED FILES' in Manifest file .  This will provide the list of files that have been extracted that are associated with certain sql objects. 
            For example, the table files may look something like this: Indicating that the file DBASE.TEST.CAST.TABLE.txt will correspond to the definition file for tables (TABLES.DEF).

            TABLES.DEF        = DBASE.TEST.CAST.TABLE.txt

            
            

         2. Find out the section titled 'EXTRACTED COLUMNS'.  This will provide the list of columns and format of those columns in the file.
            An example would be as shown in this line: Which indicates that the columns in the definition file for tables (CTABLES.DEF) will have 9 values with the last being a binary column indicated by the '*' character. 

            CTABLES.DEF        = DBN,C,N,TY,TSN,TC,TBN,LO,R*

         3. Modify the definition to make the column non-binary and then proceed to correct the associated column data for the table in the table file since the current issue with the extractor is that the extraction of the data in these binary columns is not correct.
            So modifying the line in the manifest file is to change from: 

            CTABLES.DEF        = DBN,C,N,TY,TSN,TC,TBN,LO,R*
            to:
            CTABLES.DEF        = DBN,C,N,TY,TSN,TC,TBN,LO,R

      2. Next, modify the data if needed in the file to be consistent so that the extractor can properly parse the file.For the table definition above, the file to be modified would be DBASE.TEST.CAST.TABLE.txt.  It would have lines showing something like this: 

         24         DB123456                128        DEV123456                                                                                                                        128        T_NAME1234                                                                                                                      1          T24         DBD12345                128                                                                                                                                        128                                                                                                                                        128                                                                                                                                        255         Ÿ 
          

         The format of each line is a set of pairs of data for each column of the extracted table definition.  The pair consists of a fixed 11 character length field followed by a field of that length providing the data for that column. 

         In the example above, the database name column (DBN) has a length of 24 with a value of DB123456 (8 characters) followed by 16 spaces.

         The last fields of the line above is the length field followed by a truncated binary data field.  These fields need to be modified in this example.  The changes to be made are to make the binary field non-binary to reflect the changes made in the manifest file and to make the length and following data field consistent so that the extractor can properly parse it.

         In the above example it is (\r\n identify the end of line):

         255         Ÿ \r\n

         To correct the issue, it should be changed to 0 followed by 10 spaces (to maintain the 11 character length field) and then the end of line, so making it now:

         0          \r\n

         There can be sometimes other characters/data in these columns, so if making global changes with a text editor like notepad++, then the file should be examined to make sure that all of the columns were modified correctly.

   2. Verify the analysis results

      1. Check the summary section in the analysis log of the items that were added to the knowledge base, at the end of a DB2 ZOS analysis.  (To find the analysis logs, please see this page:  CAST Management Studio - Information - How to find logs)
         A sample of this is as shown below: 

         Count of objects sent to Knowledge Base saving layer: 
            3  Databases
            74  Schemas
            342  Tables
            351  Views
            1005  Procedures
            150  Functions
            0  Triggers
            468  Indexes

      2. Use the values in this section to validate your results and expectations. 
      3. If they are not what you expect, then you should go back and review the modifications that have been made for the associated extracted file and make sure that they are correct.
3. If still having issues contact CAST Technical Support with the below Relevant input .

Relevant input

CAST Support Tool (CST) - alias Sherlock

DB2 ZOS extracted files

Ticket # 4192, #7288, #8319

Fixed in CAST 7.3.10