Gain insight to your IMS databases

On the skill gap subject, the IMS Database Administrators that knew a system inside out are a rare sight these days. When I am working on an assessment or architectural project for a system that I am not familiar with, I have to ask questions, such as:

- How many Full Function and Fast Path databases do you have?

- What is the most used database organization for this client?

- Are you using x, y or z features?

It is very common that the answer to those are "I am not sure" or "I do not know". That is fair because the people that designed the systems many decades ago have probably retired. The young blood needs to learn both the technology, for IMS that is +50 years of technology to learn, as well as the local system configuration, business requirements, etc.

It does not help with some of the existing limitations on the mainframe, the 8 character limit for naming a database. IVPDB1 is well-known for many of us, but it is far from being intuitive for someone know versed in IMS installation.

How to get a little bit of understanding on existing databases for IMS?

My go to place for information is the RECON dataset. I am aware of some clients that do not register their databases to the RECON, that is subject for another discussion, but the majority do use the RECON, and they should.

There are some considerations for the RECON database. It is heavily used by IMS, there are lots of access requests, updates to data, etc. Some functions are serialized, without getting into parallel access for RECON, and long running requests can delay everything else and cause IMS to get into a very bad contention to the point that it halts processing.

We can use the online commands to interface with the RECON or the batch utility, DSPURX00, to extract information about our databases. The output is very verbose and the filters are quite limited, compared to the level of information that is stored.

Alternative approach to reading data

Something that I like to do so I am free to explore the data is to work on a backup copy of the RECON. Most, if not all, clients that do register their databases will have a procedure to backup, reorganize and clean up the RECON, usually that is weekly.

The most common option that I see on the clients that I supported is to run a query using one of the LIST commands and use REXX to interpret the results to extract the required information. I have seen some using the DBRC API in Assembler to do similar activities.

Knowing that the RECON is a VSAM database, there is another approach that I like to use, which is DFSORT directly on the VSAM database and extract the information. Again, to be clear, I do this on a backup copy of the RECON because I run many type os queries and reports, I do not want to delay the online activity in IMS.

This page on the IMS Diagnosis User Guide contain a list of the records available and how to interpret them:

https://www.ibm.com/docs/en/ims/15.5.0?topic=aids-recon-record-types

If we look at the "Database Header" record for example, the mapping macro is hlq.SDFSMAC(DSPDBHRC). The key for the record is:

DBD: DBD name
DDN: hex zeros
Type: X'18'
Time: hex zeros        

We are looking for records with type equal to hexadecimal 18. A DFSORT like this:

//DFSORTRC EXEC PGM=SORT                         
//SORTIN   DD DISP=SHR,DSN=IMS.RECON.BKP      
//SYSOUT   DD SYSOUT=*                           
//SORTDIAG DD DUMMY                              
//REPORT   DD SYSOUT=*                           
//SYSIN    DD *,SYMBOLS=JCLONLY
 OPTION VLSHRT,VLLONG          
 SORT FIELDS=(1,8,CH,A)        
 OUTFIL FNAMES=REPORT,         
 INCLUDE=(17,1,BI,EQ,X'18'),   
  HEADER2=(1:'Database'),    
  OUTREC=(1:1,8)               
/*                                                               

Will produce:

Database 
DFSCD000 
DFSCD01  
DFSCX000 
DFSCX01  
DI21PART 
IVPDB1  
IVPDB1I 
IVPDB3          

Simple, isn't it? The thing here is that you have to start with something and then add features to it. I go mapping each column that I am interested, add HEADERs, format in columns, etc. It is not something that gets created in a single job, just have some patience and start adding the fields and formatting. I think it is really complicated to work with these offsets, so I like to use symbols for that. This is much better:

//DFSORTRC EXEC PGM=SORT                         
//SORTIN   DD DISP=SHR,DSN=IMS.RECON.BKP      
//SYSOUT   DD SYSOUT=*                           
//SORTDIAG DD DUMMY                              
//REPORT   DD SYSOUT=*                           
//SYMNAMES DD *                        
dbdbd,1,8,CH              Database Name
dbarea,*,8,CH             Area Name    
dbrtype,*,1,BI            Record Type  
dfstsl,*,12,BI            Timestamp    
/*
//SYSIN    DD *,SYMBOLS=JCLONLY
 OPTION VLSHRT,VLLONG         
 SORT FIELDS=(dbdbd,A)        
 OUTFIL FNAMES=REPORT,        
 INCLUDE=(dbrtype,EQ,X'18'),  
  HEADER2=(1:'Database',/),   
  OUTREC=(1:dbdbd)            
/*        

Instead of memorizing the position, length, data type, etc, you define the symbol once. My personal preference is to use the name of the label that is in Assembler DSECT so I know what that means, but sometimes the label is not really meaningful, so I change to something else.

This is an example of the DBDS record, I queried for databases that had DFS in the dataset name, I was looking for the IMS Catalog and the IVP definitions (there are more columns in the report, it just does not fit will in the blog post):

Database   Type       DS Org     HALDB      DB Org     IC Recom.  Recv. Req. IC Req.    Reco. Req.
DFSCD01    PART       OSAM       (P)        HIDAM      No         No         No         No        
DFSCD01    PART       OSAM       (P)        HIDAM      No         No         No         No        
DFSCD01    PART       OSAM       (P)        HIDAM      No         No         No         No        
DFSCD01    PART       OSAM       (P)        HIDAM      No         No         No         No        
DFSCD01    PART       VSAM       (ILE)      INDEX      No         No         No         No        
DFSCD01    PART       VSAM       (PS)       INDEX      No         No         No         No        
DFSCX01    PART       VSAM       (P)        INDEX      No         No         No         No        
IVPDB1     IMS        OSAM                  HIDAM      No         No         No         No        
IVPDB1I    IMS        VSAM                  INDEX      No         No         No         No        
IVPDB3     FP         VSAM                  DEDB       Yes        No         No         No        
IVPDB3     FP         VSAM                  DEDB       Yes        No         No         No                

As I mentioned before, It is also possible to filter with different parameters than the IMS commands. Here is an example where I am looking for any Fast Path database with the Image Copy recommend flag enabled:

INCLUDE COND=(dshrtype,EQ,X'20',AND,
        dshflags,EQ,dshicrec,AND,   
        dshflg2,EQ,dshfpath)                   

The output is:

Database   Type       DS Org     HALDB      DB Org     IC Recom.  Recv. Req. IC Req.    Reco. Req.
IVPDB3     FP         VSAM                  DEDB       Yes        No         No         No                 
IVPDB3     FP         VSAM                  DEDB       Yes        No         No         No                         

Post processing the data

Here is another cool trick that I use. When formatting the report we usually have columns with one or more spaces between them to improve reading. In my DFSORT JCL I code something like this:

//EXPORT   EXPORT SYMLIST=*  
//CSV         SET CSV=' '                  
//*SV         SET CSV=';'                           

Then I export those symbols to use in the SYSIN with:

//SYSIN    DD *,SYMBOLS=JCLONLY        

With that, I have that symbol after each column, for example:

OUTFIL FNAMES=REPORT,REMOVECC,  
HEADER1=(1:'Database  ',C'&CSV',
           'Type      ',C'&CSV',
           'DS Org    ',C'&CSV',        

That way, if I want to run the job and look at the output on the mainframe, I set the symbol to one blank character. If I plan to download the file or post process it using Python or another language, I change the symbol to a semicolon or a comma so it can be interpreted by CSV programs, no need to change anything else in the DFSORT code.

This is the difference:

Database  ;Type      ;DS Org    ;HALDB     ;DB Org    ;IC Recom. ;
IVPDB3    ;FP        ;VSAM      ;          ;DEDB      ;Yes       ;
IVPDB3    ;FP        ;VSAM      ;          ;DEDB      ;Yes       ;        

This opens many possibilities for automating the reports, you can easily use sub-string functions in any programming language to parse the data, you can hook up the report with other automated processes, feed that data to AI platforms, etc.

There are two things to keep in mind though. The first one is that some of the RECON records have variable length fields and that is very difficult to interpret with DFSORT, I think it is much easier to write a program for that so you can loop through that information instead. The second one is that the database organization as described by the IMS macros will get you some extra grey hair, anyone that looked at the DMBORG knows what that means. : )