ODBC to connect to live not replication

[james fenoulhet]

Hello.

Could do with some advice please.

We have a live system with a replication box where we run all our reports and extracts from, using an odbc connection with the 11.4 driver. The current DB is 10.2B patch 8.

We are looking at doing a biztalk implementation at the moment and wondered if you can grab data from the live box with the ODBC driver rather than from the replication server. Reason being, replication may fall behind or collapse completely (probably rare but a risk) which would then impact pulling data.

Is it possible to do this without locking live up? Is this a supported process or a complete do not do it?

This wouldn’t be massive amounts of data so don’t think it would impact in terms of slowing the live system down, I’m more concerned about locking users out of fields. Just wanted to get some advice or recommendations on how we should get the data from live.

Thank you in advance

 

[Rob Fitzpatrick]

I'd say it depends on how you connect, i.e. the parameters of your DSN. If your transaction isolation level is Read Uncommitted then your SQL client shouldn't lock any records in prod.

 

[cj_brandt]

Does your live system already support ODBC connections ?
Configuring ODBC connections to a live db and then running update stats on a regular basis is a noticeable amount of work.
After that is setup, I haven't seen much of an impact on read only ODBC connections. I have not had to support ODBC connections that had the ability to make updates.

 

[Cringer]

As Rob says, you either need to have the ODBC connection Read Uncommitted, or only GRANT SELECT to the user(s) that will be connecting. In an ideal world you'd do both. That will make it physically impossible for the ODBC clients to update records.
Whilst you're configuring you want to ensure that the primary broker is set for 4gl connections only, and that you start up a secondary broker for SQL connections only for the ODBC to connect to. If you set the primary broker to have a very low ratio of users to servers, and set the secondary broker to have a high ratio of users to servers you will also throttle the ODBC connections so they can't hog resources for the production system.

 

[Chris Hughes]

I'd just add to be careful regarding the queries you throw at your live system. Progress just like many other DBs can consume large amounts of RAM on lots of joins / sorts.
Depending on your business needs Pro2 is a cool solution - which would give you real time data but protect your live database.
(I don't work for Progress )