RPS Historic Tables and Deletes

[torrie]

We are reporting off a historic RPS feed. We've just noticed that when a object deletion is recorded in the SQL database, all the fields are set to null. It would be preferable for our reporting to have the state of the object immediately prior to deletion.

For example if we have an Owner class and a Child class (with a myOwner property) where the owner has a collection of children, then a SQL query along the lines of

Select * from Child where Child.myOwner = "0123.456"

will not include the delete entries for any child that have been deleted as their myOwner column is set to null.

Has any one encountered this previously. I wonder if we can do a custom RPS mapping, but I'm not sure at what stage the JadeRpsDataPumpIF::updateCallback method is called. I would assume this is called immediately prior to the Commit Transaction in which case accessing the object that will be deleted will result in a Invalid Object Reference. Can anyone confirm this?

Thanks

Torrie

[cnwjd]

Torrie,

The RPS data pump processes the changes made by a transaction after the updates have been applied and committed in the local JADE database. When the data pump replicates a delete operation it only has access to the OID because the deleted object has actually gone. That is the reason why all columns mapped from user properties are null values in delete rows of a historical table.

When the JadeRpsDataPumpIF::updateCallback is called with operation = ObjectDeleted the outputRowList dynamic object parameter is not provided and if the callback attempted to access the deleted object it would encounter an object_not_found exception.

Having said that, there may be another way to retrieve the information you require. The following suggestion requires that you are running with the ‘Bulk Load Historical Tables’ configuration option set using the RPS manager (sets [JadeRps] AutoBulkLoadHistoricalTables=true ). That option results in a snapshot load of the historical table when it is first established or subsequently changed by a reorg and ensures there is at least one row in the table for an object when its delete row is inserted.

Now, as you mentioned in your post, the query: Select * from Child where Child.myOwner = "0123.456"
will not include the delete entries for any child that have been deleted as their myOwner column is set to null.

However, if we alter this query to make use of a nested select as follows:

select * from Child where oid in (SELECT oid FROM Child where myOwner = '00123.0000000456')

The subquery (nested select) returns the oids of non deleted objects with myOwner=123.456 and the outer select will return all child rows with any of those oids including the deleted rows. If you just want the deleted entries, then add an additional filter expression to the where clause as follows.

select * from Child where oid in (SELECT oid FROM Child where myOwner = '00123.0000000456') and _operation = ‘D’

[torrie]

Thanks for the details. I had thought that the delete was processed following the physical delete, hence the record only contained the oid.

I've suggested to the report developers that a query along the lines of

Code: Select all

select ChildA.*, ChildB._operation as deleted from Child as ChildA Left Join Child as ChildB on ChildB.oid = ChildA.oid and ChildB._operation = 'D' where ChildA.myOwner= '03206.0013183009'

would return the rows required with a flag to indicate whether they have been subsequently deleted. I'm assuming that additional filters will be applied to retrieve only those rows that existed when the parent was last updated.

I've also passed on your suggestion for the nested query which may work better.

Thanks

[torrie]

Thinking it through last night, you would probably need to know when the record was deleted or updated, not just if it had been deleted.

Code: Select all

SELECT *, ( Select Min( _timestamp ) from [Child] as ChildB where ChildB.oid = ChildA.oid and ChildB._timestamp > ChildA._timestamp) as _ReplacedTime FROM [Child] as ChildA where ChildA=myOwner= '00123.0000004567'

[cnwjd]

The first nested query I suggested will return a row for each state of a child object that satisfies the search predicate, including a row for the delete (with its timestamp) if it exists.

If the reporting task requires a single row representing the final state of each Child object (and not a history of changes to the object), then either of your proposed queries would be a better starting point.