Question
Yaniv Ben Malka · Nov 6, 2018

Detection of last update timestamp of each class row (DSTIME ?)

#Globals #ODBC #SQL #Caché

Hi,

Most of my classes are mapped from Globals. I want to access Cache classes from a BI software through ODBC connection.

'Last update' information does not exist in most of the classes. My question is whether there is a 'last update' timestamp that is automatically generated for each line in classes I can extract to external systems?

Thanks

0
0 578
Discussion (9)2
Log in or sign up to continue
Chris Thompson · Nov 6, 2018

You may to look into using the DSTIME feature that cache offers, we use it for our BI tools to grab the most recent data.

1 0
Yaniv Ben Malka · Nov 7, 2018

Hi Chris,

Thanks for responding.

I tried adding the DSTIME property to a class accordingly:

Property DSTIME As %Library.Integer;

But I get mapping error (My class has SQL storage map). What am I missing here? Should it be mapped somehow, or is it a special definition to make this field automatically populated?

Thanks

0 0
Yaniv Ben Malka · Nov 11, 2018

Hi Evgeni,

Unfortunately, I can't rely on updating existing globals because our whole system is very complex.

I am looking for a class attribute that automatically detects once a line was created/updated an stores the timestamp.

Is such a thing available?

Thanks!

0 0
Evgeny Shvarov · Nov 12, 2018

What is great about InterSystems objects is that you exactly know what's going on with your data. 

You can look into the generated code for classes with Cache Storage and see where and when class writes the data into ^OBJ.DSTIME when the class has 

Parameter DSTIME As STRING [ Constraint = ",AUTO,MANUAL", Flags = ENUM ] = "AUTO";

So, there is no magic here:  DSTIME=AUTO parameter introduces "sets" into ^OBJ.DSTIME global in the places where records are being updated or created.

It doesn't work for non-standard SQL storage because DSTIME Parameter simply doesn't know about insert/update/delete procedures anything.

In your case you can forget about DSTIME and place this sets in Global (or inserts into some Record.ChangeHistory class) in the places, where your data is being inserted/updated/deleted.

0 0
Chris Thompson · Nov 7, 2018

You implement DSTIME on a class by doing the following in your persistent class:

Parameter DSTIME As STRING [ Constraint = ",AUTO,MANUAL", Flags = ENUM ] = "AUTO";
 

You will need to implement batching as well so you only pick up changes to a class from the last time you queried against DSTIME. I believe there are a few posts in the community that further discuss implementing DSTIME. 

Best of luck. 

1 0
Yaniv Ben Malka · Nov 7, 2018

Thanks!

If you can explain a bit about batching process, or direct to posts you think are relevant, it will be much appreciated.

I am struggling to find relevant information.

0 0
Evgeny Shvarov · Nov 11, 2018

Hi Yaniv!

As @Chris Thompson mentioned you can use DSTIME for automatic records changes tracking which will be stored in a special global. But I doubt if it works for classes with non-standard storage schema just by adding DSTIME=auto;

How do you add/update records in your application? You need to introduce special calls in your "create/update" procedures to track changes. DSTIME=auto; does exactly same adding "tracking" logic in SQL and Object requests for class changes. Maybe you can use some calls with DSTIME=manual; mode.

@Alexander Koblov  do you know if it is possible to use DSTIME somehow in this case?

Another approach which may work in your case is to use AUDIT to track the changes users made to DB and then analyze this data, e.g. like here. But I never did it for non-standard storage schema.

0 0
Evgeny Shvarov · Nov 10, 2018

Hi Yaniv!

I think if your class is mapped to globals the only way to have “last update” field is to store it in a global during update and map then into the appropriate field.

Another approach I see here is to parse transaction journals seeking particular “row” in a particular global to have a “Last update” moment.

0 0
Yaniv Ben Malka · Nov 12, 2018

I think I got a bit closer.

When adding the DSTIME (Auto) parameter to a class according to the below, I indeed get the actions recorded in ^OBJ.DSTIME class.

Parameter DSTIME As STRING [ Constraint = ",AUTO,MANUAL", Flags = ENUM ] = "AUTO";

However, there are two exceptions:

  • The timestamp is not recorded (Highlighted in Red)
  • This works only for classes with Cache storage type. Changes in classes with SQL storage type are not recorded.

@Evgeny Shvarov

1 0