Change Data capture with RMS on OpenVMS

[Vincent Deeney]

Is there any way to do CDC on RMS? I found a company Connx that claims to do it, but I'm not aware of log files they can scrape, so I am not sure how they could be accomplishing it.

[Hoff]

If you're referring to configuration data and related capture of system configuration changes (a couple more sentences and some background on the request can be better when asking a question), that's usually a combination of brute-force directory searches and related and databases of existing files (SHA-1 or MD5 or such) via VMSKITBLD.DAT or VMSKITBLD.XML, etc), and tapping into existing notification constructs such as system security auditing and such, and VMSINSTAL and PCSI databases.

There's no single central API.

[Vincent Deeney]

This is actually or an existing COBOL application that is running on OpenVMS. The data is in RMS and we can do a full load using an Adapter we have, but the requirement is coming for CDC.

My understanding is that there is not a way, but CONNX's website gave me hope. So data will be stored in the RMS system and we will want to take customer data as it changes and propogate it out to an Oracle System.

[Hoff · Accepted solution]

If you have the source code, it's (usually) trivial to add this.

Biggest issue here is usually with the volume of data generated; with the size of what can effectively be the journal file.

If you don't have the source code of the application (or there are prohibitions around changing same), you'll probably have to tap into the system service intercept mechanism, tap into the lock manager processing, or scrape the changes via RMS journaling or such. These approaches are technically feasible, probably (entirely?) unsupported, and rather more work and likely also far more expensive than tweaking the source code.

What I know of CONNX is its ODBC and JDBC pieces, and you can tap into that; I can certainly understand how to tap into that and implement change management as CONNX has control over the API involved.

I don't know of a way to use CONNX locally to access RMS that doesn't also involve re-writing your COBOL code to access the CONNX APIs. And once you do that...

[Robert Gezelter]

Vincent,

There are various ways that this can be accomplished, but as Hoff noted the most general case together with the requirement to "not change code" can be a challenge.

Is it possible to be more specific as to the precise requirements? Also, sometimes it is possible to implement logging facilities by modifying relatively discrete sections of code.

As always, the devil is in the details.

- Bob Gezelter, http://www.rlgsc.com

[Hein van den Heuvel]

Here is what I suspect is happening :

- On initial load, create lookaside table with entry for each record in rms file.
- On (scheduled) update the full rms file is scanned and for each record it is determined whether it is new, update, or deleted using that lookaside table.
- For the full table scan connx does NOT use RMS $GETs private code going much faster.

This is based on how I would solve the problem, as well as:
http://www.connx.com/products/change-data-capture/DataSync.asp
http://www.connx.com/ftpdir/white/Change_Data_Capture_White_Paper.pdf
"By storing the hash keys along with the primary key of each record in
a separate CONNX private data store, the CONNX DataSync tool performs
incremental updates from source to target at a fraction of the usual
full update time."

I have asked a contact at connx for comment on the above, pointing to this topic.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Larry McGhaw]

Hello,

I'm the VP of Engineering at CONNX, so I can explain how our CDC for RMS works.

You are correct that RMS does not have a log file to read from. CONNX CDC works by completely scanning the RMS file(s) in question using blockIO during scheduled intervals. A hash of every record along with the primary key is stored in an intermediate datasource, and the hashs from the last scan are compared to the current hashs. This enables CONNX Datasync to quickly determine which records have been changed, inserted, or deleted since the last sync. Then the changed data is propogated to the target data source. This technique does not require any modification to the existing COBOL applications, nor any modification to the structure of the RMS files. It also does not require a timestamp.

Thanks

lm

[Hoff]

A quite elegant hack, albeit (given it involves polling) still a hack.

[Hein van den Heuvel]

Thanks Larry, that was quick!

For sake of completeness...

Other vendors also offers solutions in this space. For example.
http://www.attunity.com/data/uploads/Data%20Sheets/Attunity%20Stream%20DS.pdf

"I'm not aware of log files they can scrape,"

Yes and No. RMS has an optional product (license included in highest OpenVMS licenses), called Journaling which offers AI (After Image), BI (Before Image) and RU (Run Unit = Transactional) journalling.
Its usage can be transparent to the application functionality, but it certainly has performance and disk usage implications.

The BI journalling would be the easiest path to CDC. However, the before images are of the entire bucket, and they are there for all buckets>. They are not per record, and not just for primary data buckets.
So there is 5 to 100 times more data in the BI stream than strictly needed for CDC. And the data itself is the raw possibly compressed RMS format. While it is no rocket science to decode that and cull data records from there, it woudl certainly be 'tedious' work. Notably unravelling a bucket split cause could be 'fun'.

An other transparent alternative could be trhough SSI (System Service Intercept)... a major undertaking.

Now given that it is a COBOL application, and assuming that any and all data modificaton to be captured is processed by Cobol images there may be a much easier options. Just create an alternative COBRTL
(google: gillings fake rtl) listen careful to what comes by for the dcob$open, dcob$writexxx, dcob$rewrite, dcob$deletexxx and dcob$close calls and package it all up neatly. SMOP!

I have built a Cobol RMS TRACE tool, instrumenting performance of each step, using a much similar technique. Quit useful and a big step into the direction of a CDC tool.

Hope this helps some more,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Larry McGhaw]

One additional note, the attunity product mentioned in the previous post does not support RMS CDC. As far as I am aware, CONNX is the only COTS (Commercial Off The Shelf) product that has some type of CDC support for RMS.

Thanks

lm

[Jon Pinkley]

Journaling would be better if you want to be able to track every change, but for resynchronizing, I don't see a big disadvantage to the CONNX CDC method, other than the need to scan every record for the sync. If the database is large, and only a very small number of records are changed between sync points, then the overhead will be higher than journaling.

But an advantage is that it doesn't have to journal every change. It just detects when the values are different than they were at the last "sync point".

Another advantage is that it detects differences, not the fact that the record has been updated. If someone is changing a flag field but the flag is returned to its original value before the next sync point, the data doesn't need to be copied.

Jon

[Richard J Maher]

Hi Vincent,

In the words of Mark Bradley (an ex-Rdb engineer) "The best way to replicated data is not at all!". Just curious what you're using the CDC for.

My version? Many moons ago I tried putting an RMS-like exec-mode lock on the record(s) I was "watching" hoping to get a blocking-AST when someone touched the record. (A slight problem is that COBOL doesn't honour the i-o-control clauses that tell it to wait for RMS locks (apply lock-wait 255?) and just returns an error from the read/write/delete etc and never triggers the BLAST :-(

Journal-mining seems to be the most feature rich - but there's that code-change again.

Cheers Richard Maher

[Hein van den Heuvel]

A little over 2 years ago, I wrote:

>> An other transparent alternative could be trhough SSI (System Service Intercept)... a major undertaking.

Well, this has now been under undertaken, by Attunity. They now offer an RMS-CDC product in their STREAMS offering. Let me assure you that it was indeed a significant task. But is seems worht while!

In the end SSI was not the right solution because or is process specific and more or less 'voluntarily'.

To do this properly one has to intercept all RMS calls, whether direct, through a Language RTL, Datatrieve, or even DCL.

See also:

http://forums13.itrc.hp.com/service/forums/questionanswer.do?threadId=1452391

Regards,
Hein

[Brian Schenkenberger]

Hi Vincent,

Since I am the party responsible for Attunity's RMS-CDC, I thought I'd expound Hein's comment and explain a bit about the RMS-CDC since others may use this forum as a resource when looking for such a solution.

Hein said: "To do this properly one has to intercept all RMS calls, whether direct, through a Language RTL, Datatrieve, or even DCL."

The Attunity solution does, indeed, intercept the SYS$rms services. This insures that anything writing to a file -- whether it be a direct invocation of an $RMS service or via one of the layer/language support RTLs -- will see the change data. Only a $QIO will not be captured but then, the product *IS* called RMS-CDC.

The system service interception was the simple part (proprietry). There are a great many things going on in the bowels of RMS that needed to be considered -- it's not just a copy of data being passed to the $RMS service. This was a project that consumed about 8 extremely intense months of my life -- 25 hours a day, 8 days a week. The before image was exceptionally onerous. Thus, real-time RMS-CDC is not something that is going to be easily replicated by any other provider.

If you're really looking for a "real-time" RMS-CDC solution, contact Attunity and get a demo of RMS-CDC.