Keeping in SYNC SQL Server database with VMS files

[DDB]

Is there any software that allows to keep in sync the SQl server database tables with live updates in VMS files?

[Arch_Muthiah]

Is it Sybase's SQL Server on VMS or MS SQL Server?

Please give few more env info.

Archunan

[Hein van den Heuvel]

Welcome to the HP ITRC Forum 'DDB'.

That's a rather terse question. It is not even clear to me what is the driver here. Are changes made to a (windows?) SQL(server) databse to be reflected in an OpenVMS RMS (indexed) files? Or the other way around?

You probably not change the application to add code to 'split' the updates to go both local and remote right?

So for both cases you'll need and ODBC driver product for VMS, or a private application protocol. Nothing is built into VMS in this space

In the first case you'll need some sort of 'sql log scraper' which extracts the updates transactions to be transmitted.

the second case is much harder, without application changes. There is no transaction log for RMS. The only thing close is 'After Image Journalling', but it does nor record individual (record) transaction, but logs block changes. IF the application is calling RMS directly (SYS$PUT, SYS$UPDATE,...) then you coudl fairly easily 'intercept' those through a shared library, do the remote work, and call onwards to the actuall RMS task.
But that woudl nto capture say a 'datatrieve' or DCL update to a file outside the application.

This problem may be very hard to solve, and certainly needs much more information about the application and processing conditions than you have shared so far.

Good luck!
Hein.

[Willem Grooters]

If SQLServer on Windows: SQL_Integrator might be used (MS as well, I think).
In your database in on Unix, you can think of Attunity Navigator - which requires licenses on both sides.
If you mean SQLServer as part of RDB, the RDB logminer and JCC's Logmine Loader (www.jcc.com) would do the ttick perfectly.

[Arch_Muthiah]

Hein,

I don't know Mr.DDB wants sync with MS SQL Server Database with our RMS files or any other files. Or He may have SQL Server Database for VMS from Sybase.

Anyway need more info, will easy to give suggestions.

Archunan

[Phillip Thayer]

This sounds like a good use for Attunity Connect that would allow the mapping of the RMS files into metadata structures that can then be accessed in SQL. We used this to map over 200 file structures from a VMS VAX/Alpha cluster (using the Alpha as our Attunity engine) with cluster shared disks. Out VAX applications running on dump terminals and batch queues could update the files on the VAX and the web server could access the data for displaying to the web via SQL. Attunity also include an API library that provides access to standard Windows ODBC calls to access data on VMS as well as outside of VMS that is mapped to Attunity. Al-in-all it works extremely well and is easy to install and get running.

Attunity also has a product called Attunity Stream that allows for "change data capture". We are not using this yet but have plans to possibly use it in the future.

Then there is the Attunity Federate for EII functionality and Attunity Infocus for BI functionality. These two also have the ability to use data on heterogenous systems as long as they are mapped through attunity connect. We are not using either of these but from all I have heard about them they are just as easy to install and use as Connect is.

Phil

P.S. - I Do not get any commissions from Attunity.:) i just really like the product and it has been a great help for us.

[Chris Davis_9]

Hi DDB,

I'd agree with Phillip on this one. We are currently using Attunity connect to extract
data through VB apps from an Adabas database on VMS. We are looking at synchronisation
with SQL Server on a Windows platform using Attunity Stream but this has only just been
developed for Adabas on VMS. Attunity provides access to a large number of different databases on many different platforms.

It all comes down to the speed and timeliness of the synchronisation you require (and the available budget!). If you need active synchronisation with data being updated in real time across the databases, Attunity Stream would achieve this. If you only require an "end of day" type synchronisation, then you may look at a different solution. However, taking the option of active sychronisation now could provide future options for the

business.

Hope this helps!

[Richard J Maher]

Hi,

I honestly don't know what is the matter with the rest of you blokes, and your apparent inability to understand plain English. IMHO Mr DDB (Deutsche-Bahn with a stutter? :-) could not have expressed himself and his requirements more clearly, eloquently or succinctly.

But, for the aurally-challenged let me explain; DDB would like the ACID umbrella of a true Two-Phase-Commit (2PC) protocol to encompass the updates to both his SQL Server database(s) on his NT systems and his RMS files on his VMS systems.

If you're happy that the transaction has to be PUSHed from your .NET W2K/MTS/DTC environment and can't be PULLed from VMS and you're also happy that you'll need RMS Journalling (including encasing your RMS I/Os in standard DECdtm calls) then I just might have the product for you.

Now ITRC protocol, quite understandably, dictates that this is not a forum for gratuitous self-promotion so I can't say too much more than I am soon to be a purveyor of such wares. Please contact me at my MAHER_RJ hotmail account if you would require further information.

FYI I've attached a brief overview of the functionality available. I have not tested it with RMS Journalling (only Rdb) but it should be doable.

Regards Richard Maher

[Phillip Thayer]

Mr Maher,

Respectfully, I have to disagree with what you said. I personally wrote code on an AlphaServer utilizing MS standard ODBC Calls (infact I used the MS documentation to format my calls in VMS Basic) that PULLS data from an MS SQL Server running on a Windows 2003 Enterprise Server box. As for 2Pc commits, we have VB code running on Windows boxes that update data on or VAX via Attunity that is running on the AlphaServer and it requires a commit to actually update the RMS files in VMS.

I'm really not sure what you did wrong in configuring or using Attunity but tghe 2PC capability was the main reason we used it instead of other products currently on the market. I wouldn't trade it for any other product out there. Ever. Never. No Way.

The capabilities it has given us to access the RMS (home grown straight RMS Indexed files) using MS Access from the desktop and run analysis reports has been invaluable. W couple of weeks ago I was asked to tell the production managers how many orders in the last year had been ordered with a specific product code and had been color corrected by our photo lab where the customers had more than $5,000 in sales last year. Previous to using Attunity it would have taken me at least 3 to 4 days to write a program and test it. With Attunity and MS Access I had the information for them in 30 minutes.

Anyway, no sales pitch here, just stating the facts based on my personal experience.

Phil

[Richard J Maher]

Hi Phil,

I hope everyone realised that my tongue was firmly in my cheek when I referred to DDB's detailed and complete requirements specification?

Anyway it is becoming clear that DDB has either lost interest or has dropped dead so we are doomed never to know what he/she actually wanted? But to address some points that you yourself raised.

[At this juncture I belive that it maybe useful if you told me/us exactly what your definition of 2PC was. In particular, how you perceive that Attunity's offering(s) confirm/adhere to the A.C.I.D. properties of a true Two-Phase Commit protocol.]

To cut to the chase, I'd really appreciate it if you (or anyone else who whishes to advertise to the public that Attunity has a 2PC capability with VMS Resource Managers)could:-

1) create a "Commit Time" evaluated constraint on an Rdb database
2) Update your NT SQL Server database
3) Update the VMS Rdb database (with data that is sure to fail the commit time constraint)
4) Commit the transaction

If the SQL Server and Rdb databases are now not out of sync then I submit that it's just down to "doing the Rdb first" or some other fortuitous event.

I also submit to you (after a 2min look at the Attunity web site) that "Attunity Stream" is yet another Store-and-Forward, Guaranteed-Delivery (ITRC Decorum Button)! Just like RTR or DMQ etc, etc, etc

Maybe I'm just trying to make things harder than they need to be but, for my money, Chpt 7 in the attached document is how to achieve what DDB wanted to achieve. Maybe Phil's right and all he/she wanted to do is get at their legacy RMS files with SQL, in which case Phil's seemingly unboundde enthusiasm for Atunnity Connect would appear justified.

I turn this over to the jury.

Cheers Richard

[DDB]

The client server application that written in VB6 uses MS SQL Server on WIN2K. I need synchronization with VMS for both ways. Some changes that were applied to SQl Server tables thru client application should be reflected in VMS files(or RDB) and changes that made on VMS through VMS application and updates VMS files should be reflected on SQL Server database site. Usually we use ACMS DI middleware that works fine. I am wondering if there any other software that could provide this synchronization. For example I would prefer such software where I could indicate that some field in VMS file has relationship with field in SQL database and no matter which one has be changed it will reflect the change on the other side.
Appriciate your time and advise, ddb

[DDB]

I would like to add that ACMS DI requires significant amount of coding on the Client site as well as on VMS site(ACMS tasks) which I want to avoid.
Thanks again, ddb

[Craig A Berry]

There are multiple ways to move data in both directions. Folks have mentioned Attunity. You may also want to look at CONNX (www.connx.com) for accessing the VMS data from Windows. EasySoft also has products in this area (www.easysoft.com); I believe they have products that go both directions.

For a free way to access the Windows side from VMS, I've successfully used unixODBC (www.unixodbc.org) as an ODBC driver manager with the SQL Server ODBC driver from FreeTDS (www.freetds.org). FreeTDS also has other APIs in addition to ODBC.

One thing to be careful about is that if ACMS is involved there is probably a need for transactional integrity across multiple systems. If you change your implementation to something simpler, you might well lose that capability. In any case you should explicitly state your transaction handling requirements to any vendor you deal with.

[DDB]

Thanks.