CMS Snapshot Analysis - Update SQL XXL Tablesize - DB2 zos - The SQLTABLESIZE is not loaded

 This page provides solution when the XXL Tables are not loaded when included during DB2 z/os analysis. The message in the GUI of CMS during Update SQL XXL Tablesize shows skipped : no Table Size data.

Below is the step-by-step scenario leading to the problem:

When configuring the analysis in the CAST Management Studio, you simply need to input the location of the folder that contains the DB2 z/oS .MANIFEST file into the Table Sizes folder field:

In the example above, the .MANIFEST has been deployed to a location in the Deployment folder (Extraction path field). The Table Sizes folder field has then been set to the same folder that contains the .MANIFEST file.

Run the analysis after adding the Table sizes folder.

The impact of the problem on the analysis or the dashboard is:
No SQL XXL tables are loaded in dashboard

To fix the problem, proceed as follows: 

1. Run the analysis, then you can see the no table size data message on the GUI.
   During this run, the analyzer will generate TABROWS.SQLTABLESIZE file at C:\CAST\V7\7.3.7\Deploy\example_sql\DB2(as per your deployment folder location)
2. Save attached file TableSizeFolders.xml
   Check the path in this file is corresponding to the folder containing the TABROWS.SQLTABLESIZE( C:\CAST\V7\7.3.7\Deploy\example_sql\DB2)
3. Open a command prompt as Run as administrator.Modify the following command (profile, log, TableSizeFolders) then run it
   <CASTInstallationFolder>/XXLTabUpd.exe -CONNECT_PROFILE('<hotname>:<port> on CastStorageService.<knowledge base name>') -LOG('C:\CASTMS\Log\Update SQL XXL Table Size4284966472792236488_CMS.castlog',USELOGFILE) -FOLDER('<Path of saved attached file>\TableSizeFolders.xml') -EXIT
4. Check if data is updated in ObjInf table

   1. You can check this by running the following query:

      select object_name,object_fullname,oi.infval from cdt_objects co ,objinf oi
      where oi.idobj = co.object_id
      and oi.inftyp=115
      and oi.infsubtyp=1

      Select OBJECT_NAME, OBJECT_FULLNAME, OI.InfVal
      From CDT_OBJECTS CO, ObjInf OI
      Where OI.IdObj = CO.OBJECT_ID
      And OI.InfTyp = 115
      And OI.InfSubTyp = 1
      And OI.InfVal >= 100000

5.  Generate snapshot (by skipping analysis).
   The process can be automatized if you want by creating a batch file for the command and use it as a tool after analysis.

Impact of the solution on the analysis or the dashboard is: XXL tables are loaded in dashboard