How to find out the last compile date/time of a schema
search cancel

How to find out the last compile date/time of a schema

book

Article ID: 10541

calendar_today

Updated On:

Products

IDMS IDMS - Database IDMS - ADS

Issue/Introduction

A Database administrator might need to know when a schema was modified and compiled (validated) for the last time.

(Note: The information in this document applies only to network-defined schemas and not SQL-defined schemas.) 



Database Administration & Maintenance

Environment

IBM z/OS, z/VSE, z/VM

Resolution

The schema entity in the dictionary contains no "compile" datetimestamp.
It contains the creation date and time of the schema and the date and time of its last update : 
         DATE CREATED IS date      
         TIME CREATED IS time      
         DATE LAST UPDATED IS date   
         TIME LAST UPDATED IS time

If the schema has NEVER been modified after its initial creation, then the date and time of last update are not filled in.
The date and time of last update is only modified after a MODIFY of the schema. The VALIDATE command however does NOT change this timestamp.

You can display the schema information using the online SCHEMA compiler or the batch IDMSCHEM compiler, using the statement

            DISPLAY SCHEMA name.    

This displays the complete schema, with areas, records and sets.  If you only want to display the schema information itself, you can use

            DISPLAY SCHEMA name WITHOUT AREA RECORD SET. 

Example :

DIS SCHEMA EMPLSCH WITHOUT AREA RECORD SET.        
*+   ADD                                            
*+   SCHEMA NAME IS EMPLSCH VERSION IS 1           
*+       DATE CREATED IS      04/14/15              
*+       TIME CREATED IS      10570251              
*+       DATE LAST UPDATED IS 04/16/15              
*+       TIME LAST UPDATED IS 13135544              
*+       PREPARED BY APPLDBA
*+       REVISED  BY APPLDBA
*+       ASSIGN RECORD IDS FROM 1001                
*+       PUBLIC ACCESS IS ALLOWED FOR ALL           
*+       .                                       

The DATE LAST UPDATED and TIME LAST UPDATED values are filled in, which means that this schema has been modified at least once.

A  MODIFY SCHEMA EMPLSCH is issued, followed by another display :

MODIFY SCHEMA EMPLSCH.

DIS SCHEMA EMPLSCH WITHOUT AREA RECORD SET.                   
*+   ADD                                                       
*+   SCHEMA NAME IS EMPLSCH VERSION IS 1                      
*+       DATE CREATED IS      04/14/15                         
*+       TIME CREATED IS      10570251                         
*+       DATE LAST UPDATED IS 07/13/17                         
*+       TIME LAST UPDATED IS 08054273                         
*+       PREPARED BY APPLDBA                                   
*+       REVISED  BY APPLDBA                                   
*+       ASSIGN RECORD IDS FROM 1001                           
*+       PUBLIC ACCESS IS ALLOWED FOR ALL                      
*+       .                                                     
The DATE LAST UPDATED and TIME LAST UPDATED are modified to reflect the datetime stamp of MODIFY.

Here is an example of a schema that has never been changed after its initial creation :

DIS SCHEMA FINADSCH WITHOUT AREA RECORD SET.        
*+   ADD                                            
*+   SCHEMA NAME IS FINADSCH VERSION IS 1           
*+       DATE CREATED IS      08/02/13              
*+       TIME CREATED IS      04472822              
*+       PREPARED BY APPLDBA                        
*+       ASSIGN RECORD IDS FROM 1001                
*+       PUBLIC ACCESS IS ALLOWED FOR ALL           
*+       SUBSCHEMA IS FINADSSC
*+       .                                           
This schema was created on August 2, 2013 and never changed/modified since then.

A modify of this schema was done, followed by another display :

MODIFY SCHEMA FINADSCH.
DIS SCHEMA FINADSCH WITHOUT AREA RECORD SET.        
*+   ADD                                            
*+   SCHEMA NAME IS FINADSCH VERSION IS 1           
*+       DATE CREATED IS      08/02/13              
*+       TIME CREATED IS      04472822              
*+       DATE LAST UPDATED IS 07/13/17              
*+       TIME LAST UPDATED IS 08100399              
*+       PREPARED BY APPLDBA                        
*+       REVISED  BY APPLDBA                        
*+       ASSIGN RECORD IDS FROM 1001                
*+       PUBLIC ACCESS IS ALLOWED FOR ALL           
*+       .

Now, the DATE LAST UPDATED and TIME LAST UPDATED are filled in and shown in the DISPLAY output.

To illustrate the "non-effect" of the VALIDATE statement, a VALIDATE for this FINADSCH schema was done followed by another display :

VALIDATE.

DIS SCHEMA FINADSCH WITHOUT AREA RECORD SET.        
*+   ADD                                            
*+   SCHEMA NAME IS FINADSCH VERSION IS 1           
*+       DATE CREATED IS      08/02/13              
*+       TIME CREATED IS      04472822              
*+       DATE LAST UPDATED IS 07/13/17              
*+       TIME LAST UPDATED IS 08100399              
*+       PREPARED BY APPLDBA                        
*+       REVISED  BY APPLDBA                        
*+       ASSIGN RECORD IDS FROM 1001                
*+       PUBLIC ACCESS IS ALLOWED FOR ALL           
*+       SUBSCHEMA IS URSSCH03                      
*+       .                                          
The DATE LAST UPDATED and TIME LAST UPDATED have not been changed even if the VALIDATE happened minutes, hours or days after the MODIFY.

How can you see if a schema has been compiled (validated) ?

As shown above, the DISPLAY SCHEMA statement does not reveal this. Therefore, you need to check the schema record in the dictionary. Schema's are stored in the dictionary as S-010 records.

You can do this by means of OLQ, DMLO, an SQL query or a user written program.

OLQ example :

Start OLQ and enter

SIGNON SS=IDMSNWKA DICTNAME dict1 DBNAME dict2

where dict1 : name of the dictionary containing the IDMSNWKA subschema, usually SYSDIRL

          dict2 : name of the dictionary which contains the schema (S-010 record) you want to display

Assume your schema is called "TESTSCHM'. Read its S-010 record :

GET FIRST S-010 WHERE CALC = 'TESTSCHM'      
The result is

S-010                                       
  S-010-DBKEY      :        0/61076:5       
  S-NAME-010       :                        
   S-NAM-010        : TESTSCHM              
   S-SER-010        :      1                
  S-DT-010         :                        
  S-VERSION-010    :   12.00                
  ERR-010          :  2                      
  PUB-ACCESS-FLAG-010 : B'11111111'         
  DESCR-010        :                        
  DATE-CREATED-010 : 07/14/17               
  TIME-CREATED-010 : 08092333
  DATE-LU-010      :                        
  TIME-LU-010      :                        
  PREP-BY-010      : APPLDBA
  REV-BY-010       :                        
  DERIVED-S-NAME-010 :                      
   DERIVED-S-NAM-010 :                      
   DERIVED-S-SER-010 :      0               
  START-RECORD-IDS-010 :   1001             
  USER-COUNT-010   :      0                 
END OF RECORD                              

This tells us the following about the TESTSCHM schema:

  • it has been created on July 14, 2017 at 08h09.23 by APPLDBA .
  • it was not yet modified (DATE-LU-010 and TIME-LU-010 are empty).
  • it was not compiled/validated or it contains errors (ERR-010 = 2). After a successful compile/validate, ERR-010 will be set to 0.

 

SQL query example (executed in OCF or IDMSBCF) :

You may first need to create an SQL schema (if such one does not yet exist) to access your application dictionary :

CREATE SCHEMA name FOR NONSQL SCHEMA dict1.IDMSNTWK;

where name : the name of your SQL schema 

         dict1 : name of the dictionary containing the IDMSNWKA subschema, usually SYSDIRL

Issue a connect to your application dictionary :

CONNECT TO APPLDICT;

Note : the dbname referring to your application dictionary must contain an SQL catalog :

DBNAME APPLDICT MATCH ON SUBSCHEMA IS OPTIONAL 
        SEGMENT APPLDICT             your dictionary
        SEGMENT SYSSQL                your SQL catalog

Issue the SQL query, assume your SQL schema is called SQLDICT :

SELECT * FROM SQLDICT."S-010" WHERE S_NAM_010 = 'TESTSCHM';

The result is

*+                                                                            
*+ S_NAM_010  S_SER_010  S_DT_010  S_VERSION_010  ERR_010  PUB_ACCESS_FLAG_010
*+ ---------  ---------  --------  -------------  -------  -------------------
*+ TESTSCHM           1                    12.00        2  FF                 
*+                                                                            
*+ DESCR_010                                 DATE_CREATED_010  TIME_CREATED_010
*+ ---------                                 ----------------  ----------------
*+                                           07/14/17          08092333       
*+                                                                            
*+ DATE_LU_010  TIME_LU_010  PREP_BY_010  REV_BY_010  DERIVED_S_NAM_010       
*+ -----------  -----------  -----------  ----------  -----------------       
*+                           APPLDBA                                          
*+                                                                            
*+ DERIVED_S_SER_010  START_RECORD_IDS_010  USER_COUNT_010                    
*+ -----------------  --------------------  --------------                    
*+                 0                  1001               0                    
*+                                                                            
*+ 1 row processed                                                            

 

 

Additional Information

For more information about

  • the SCHEMA compiler, see 

https://docops.ca.com/ca-idms/19/en/administrating/administrating-ca-idms-database/schema-statements

 

  • the S-010 record, see

https://docops.ca.com/ca-idms-ref/19/en/dictionary-structure-reference/record-and-element-descriptions/s-010