What’s New in Zen V15: Easily Tracking Data Changes

Data maintenance is an ongoing requirement in every database environment. Archiving historic data, synchronizing data after offline access, or auditing changed data are all issues that typically require customized programming. Most of these solutions often need database design changes or time-consuming processes to complete these tasks. With the release of Zen v15, there is now an easy way to do this for any existing Zen data file without impacting existing applications and data layouts – it’s called “System Data v2”.

System data has been around for a long time; it provides a hidden unique identifier on every record in a data file. It is used in conjunction with transaction logging to provide data integrity and recovery in case of system failure. It is also used by DataExchange (used for data replication between various instances of Zen Windows servers in distributed data environments)  to uniquely identify records in files being replicated between systems. The hidden values can be retrieved via standard Btrieve Get operations by reading along key number 125; however, beyond being unique, the system data does not provide any additional information.

Zen v15 introduces System Data v2, which provides two hidden unique values on every record. These values are actual time stamps which represent when the record was inserted into the file, and when it was last updated. These time stamps are automatically handled by the engine for every insert and update received, regardless of the interface used. So, applications written using Btrieve, Btrieve 2, ODBC, ADO.NET, PDAC, Java, etc. will all cause the system data v2 time stamps to be maintained if the data file has this option enabled. The 13.0 file format is required for system data v2, and the rebuild utility can be used to enable this option on the files you select.

Like the original system data, the new hidden values can be retrieved via standard Btrieve methods by reading along key numbers 125 (insert time) and 124 (update time).  In addition, system data v2 values can be accessed via any SQL interface using the virtual column names sys$create and sys$update. The data in these columns is stored as a Timestamp(7), which is a standard time stamp with septasecond granularity.

Let’s look at an example executed in the Zen Control Center (The Zen Database Management Console):

To create a table including system data v2, add the “SYSDATA_KEY_2” keyword to the CREATE TABLE statement:

create table sensorData SYSDATA_KEY_2
(location varchar(20), temp real);

This keyword can also be used in an ALTER TABLE statement to rebuild an existing file to include the new syskey values.  Both cases will result in a 13.0 version file.

Now, let’s insert a few rows and see what the virtual columns look like:

insert into sensorData values(‘Machine1’, 77.3);
insert into sensorData values(‘Machine2’, 79.8);
insert into sensorData values(‘Machine3’, 65.4);
insert into sensorData values(‘Machine4’, 90.0);

select “sys$create”, “sys$update”, sensorData.* from sensorData;

sys$create                                     sys$update                                     location      temp
===========================   ===========================    ========    =====
2021-09-13 12:49:45.0000000   2021-09-13 12:49:45.0000000      Machine1    77.3
2021-09-13 12:49:45.0000001    2021-09-13 12:49:45.0000001       Machine2    79.8
2021-09-13 12:49:45.0000002   2021-09-13 12:39:45.0000002      Machine3    65.4
2021-09-13 12:49:45.0000003   2021-09-13 12:49:45.0000003      Machine4    90.0

Initially, the create time and the update time are recorded as the same value.  You’ll notice that the syskey data values show the fractional seconds as seven digits.  This portion of the time stamp is used to guarantee uniqueness in the value, as opposed to representing the actual septaseconds of the insert.

After updating a row, you’ll see that only the sys$update value has changed:

–update a row:

update sensorData set temp = 90.1 where location = ‘Machine1’;

–find rows that have been updated:

select “sys$create”, “sys$update”, sensorData.* from sensorData
             where sys$update > sys$create;

sys$create                                      sys$update                                    location      temp
===========================    ===========================    ========    =====
2021-09-13 12:49:45.0000000    2021-09-14 11:57:46.0000000       Machine1    90.1

Other examples of queries:

–find rows inserted or updated in the last 20 minutes:
select “sys$create”, “sys$update”, sensorData.* from sensorData
             where “sys$update” > Timestampadd(SQL_TSI_MINUTE, -20, now());

–return all CHANGED rows, including how many minutes since the last update
select sensorData.*, Timestampdiff(SQL_TSI_MINUTE,”sys$update”,now()) NumMins
             from sensorData where “sys$update” > “sys$create”;

–return the number of rows, inserted in the last 24 hours:
select count(*) as Last24Count from sensorData
            where Timestampdiff(SQL_TSI_hour, “sys$create”, now()) < 24;

The system data v2 indexes are fully optimizable by the SQL engine. So, a query with restrictions or sorting on the virtual columns will use the index when appropriate.

Tracking create time and last update time can now easily be accomplished with Zen v15 and the System Data v2 feature. Download the trial version here and try it out!

About Actian Corporation

Actian makes data easy. We deliver cloud, hybrid, and on-premises data solutions that simplify how people connect, manage, and analyze data. We transform business by enabling customers to make confident, data-driven decisions that accelerate their organization’s growth. Our data platform integrates seamlessly, performs reliably, and delivers at industry-leading speeds.