How to create summaries based on the HLQ of DSNINDEX records in a CA Disk Datacom FDB using SQL?

Products

• DISK BACKUP AND RESTORE
• Datacom/AD
• Datacom/DB

Issue/Introduction

How to create summaries based on the HLQ of DSNINDEX records in a CA Disk Datacom FDB using SQL, that means without Vantage? 
The SQL command should count all data set index records by HLQ and also summarize the used bytes by HLQ processing all DSNINDEX records in a CA Disk Datacom FDB. 

Environment

CA Disk with CA Datacom Files DB (FDB)

Resolution

Use the new SUBSTRING_INDEX function to extract the HLQ from the dsname.
SUBSTRING_INDEX allows to search a source-string for a specified delimiter and returns a substring of leading or trailing characters.  

Here a sample SQL command where
- SUBSTRING_INDEX is used to extract the HLQ of the data set name 
- SUBSTRING_INDEX used as base for grouping by HLQ 
- COUNT and SUM are used to count the records and to summarize the bytes per HLQ 
                                  
  SELECT SUBSTRING_INDEX(DSNDSNAM,'.',1) as HLQ,      
         COUNT(*)                        as Number,
         SUM(dsnbytes)                   as Bytes                              
  FROM  CADISK.dsnindex_650                            
  WHERE DSNDSNAM LIKE '%'                              
  GROUP BY SUBSTRING_INDEX(DSNDSNAM,'.',1); 

Sample output:

HLQ                                               NUMBER        BYTES
____________________________________________ ___________ ____________
ABC                                                   94     74064138
BCDEF                                                  7        30260
CABBBX2                                                1      2060502
ICF                                                    4      4000164
MB000092                                              67       333190
...                                                  ...          ... 
PSS                                                    1         4872
RRRRR                                                 23    230015085
SAMS                                                   3    105169091
SYS1                                                   2    239995602 

...                                                  ...          ... 




      

           

Additional Information

Note:
The SUBSTRING_INDEX function is new. It has been introduced by CA Datacom SQL 15.1 PTF SO10328.
So above SQL command will fail on with Datacom/AD 14.0 and AD 15.0.
There is no other function in Datacom/AD 14.0 or AD/15.0 to resolve the request.
The SUBSTR function cannot be used as the length of the HLQ can vary.

See: 

New Features in Level Set PTF 15.1.01 - Scalar Functions