How to find out the last compile date/time of a schema
Article ID: 10541
Updated On:
Products
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
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
Feedback
