Locating and Understanding SQL Data Timestamp
Article ID: 54497
Updated On:
Products
Issue/Introduction
Working with IDMS SQL databases means that timestamps will be used at run-time to validate that the table definition being referenced is the correct one for the data being accessed. To perform this verification, timestamps are stored both in the catalog and in the physical data area where the rows of the SQL table area stored. These timestamps are in sync when a table definition is created. If either the catalog segment or the data area is restored without the other, then these may no longer match and a timestamp validation error may occur. At such times, it is necessary to see what the existing timestamps are so that they can be re-synchronized.
Resolution
Timestamps are recorded in every physical area that contains SQL tables; one timestamp is stored for each area. and one for each table whose data is stored in the area. They are also recorded in the DDLCAT area of the catalog as part of the table definition, in the TIMESTAMP column of SYSTEM.TABLE table in the row corresponding to the table in question. At run-time, these values for each table that is accessed must match each other, or an error is produced. An error indicating a problem with a timestamp will occur if the data area and the catalog area have not been kept in sync. For this or other reasons you may need to research a problem with the timestamp in the data area. As of r16.0, you can do this by running the new SYNCHRONIZE STAMPS utility which lets you compare stamps in the data area and the catalog and optionally to update one from the other. Also on r16, a new option was introduced for the INSTALL STAMPS utility which allows you to replace existing synchronization stamps in an area with the values from the catalog.
As noted above, there are two types of timestamps in the data area: area stamps, and table stamps. Area stamps are called K0 records and are represented physically with an SR5 record-id. Table stamps are called K2 records and are represented physically by an SR9 record-id. These are connected by a user-owned index structure in which the K0 is the owner and the Ks are members. If the area is defined as stamped by area, then the K0 timestamp reflects the timestamp of the most recently updated table K2 within that area. If the area is stamped by table, then the K0 timestamp will always be null.
On R15.0, and on later releases in cases of data corruption or if desired for other reasons, you can view the timestamps that are stored in the physical area for a table by following these steps:
1. The timestamp records and the user-owned index connecting them are all stored on the second page of the area where the rows for this table are stored.
2. In order to find the right timestamp, you will need to know the table-id for the table in question. You can find this and other relevant information by issuing SELECT NAME, TABLEID, SEGMENT, AREA, TIMESTAMP, HEX(TIMESTAMP) FROM SYSTEM.TABLE WHERE NAME = <table-name>; If the table name is not unique in this catalog, you will also need to specify AND SCHEMA = <schema-name>' . We are displaying both the timestamp column and the hex value for the timestamp because all timestamps are stored in SQL TIMESTAMP data type, which means the values will be stored in hex and automatically displayed in EDCBIC format when they are retrieved. The hex value will be needed for comparison to the timestamp stored on the page; the readable format lets us see what that value indicates. The query results look like this example:
3. Using the segment and name in this output, issue a DCMT DIS AREA to see the page range from this area. In this example, we get:
4. Run a PRINT PAGE of the second page of the area. Use the remaining steps to read this output. In our example, a part of the output showing the records on the page follows:
5. As described above, the records on this page with timestamp information are the SR5 (K0), the SR9 (K2), and the SR8s. The SR5 (K0) is the timestamp for the area, the SR9 (K2) records are timestamps for each table, and the SR8s are a user-owned index structure connecting the two.
6. In order to find the correct timestamp for the table in question, you need to find the right SR9. You can do this by walking the SR8 index structure, if you're comfortable with this, or by simply browsing the SR9s on the page. The next two steps outline how to browse the SR9s.
7. The structure for the SR5 & SR9 records is documented in the CA IDMS DSECT manual, under DSECT #D2S2DS. Each SR9 begins with a DBKey which can be ignored for now. At offset x'4' in the SR9 is the table-id, in hex, of the table associated with this SR9. The timestamp is at offset x'8' in each K2 record.
8. Take the table-id for the table in question, and convert it to hex. (1024 will be x'400', 1028 will be x'404', etc.) Use the PRINT PAGE output to find the beginning address of each SR9 and scan each one for the appropriate table-id at offset x'4'. In our example, from step #2 above, we see that the table-id is 1026; converting this to hex yields x'402'. If we browse the output from the Print Page run above, this turns out to be the SR9 starting at offset x'0068' on the page. You can see the detail of that record in the following output.
9. The #D2S2DS DSECT documents that the timestamp in an SR9 for the table in question will follow the table-id in the next 2 words, at offset x'08' into the record layout. In the above example, we can see that the timestamp value here is x'0164BF6886967CC7'. We can see from the output in step #2 above that this matches the value in the catalog, so for this table we would not expect any timestamp errors at run-time when these values are compared.
10. For r16 and higher, this process is simplified by running the SYNCHRONIZE STAMPS utility for this area with the COMPARE parm. In our example, that output looks like this
Remember that keeping the timestamps in sync is much easier than trying to correct a problem when they get out of sync. If you backup and restore your catalog areas and associated data areas as one logical unit, these should always match. In some cases, as when production data is moved to a test CV, it may not be possible to also migrate the catalog area. In situations like this, on r16 and higher the SYNCHRONIZE STAMPS utility can be used to promote the catalog timestamp to the copy of the data area. On R15.0 these stamps must be resynchronized manually, using the PAGE FIX utility to place the correct stamp into the area.
Feedback
