What’s New in Zen V15: Easily Tracking Data Changes By Linda Anderson September 22, 2021 Data maintenance is an ongoing requirement in every database environment. Archiving historic data, synchronizing data after off-line 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 Linda Anderson Linda Anderson is the Sustaining Engineering Manager for the Actian Zen products, heading the team that provides updates and improvements to the current product line. She has a 25+ year history working with Actian Zen, PSQL, and Btrieve developers and users. Linda is a long-time Cubs fan, grateful for the World Series win in 2016.