Linked Server to Progress Database
-
It appears that we may have to read in data from a progress database into a data warehouse. By preference we would like to do this using a linked server (reasons to convoluted to mention), can anyone advise on what ODBC we would need to do this - does it come with progress or is it necessary to purchase a third party product ? Also has anyone done this before - any tips on things to watch out for.
All help appreciated.
-
What version of Progress are you accessing? We have Progress 9.1d here and use DTS to extract data on a nightly basis.
We've found that using the supplied MERANT 3.60 SQL-92 driver provides acceptable performance. DataDirect and Openlink supply more recent drivers, but we didn't see any performance improvement over the original drivers.
Watch out for the character fields in Progress. When the database is updated via 4GL there is no limit to the text length. However, when access via the ODBC driver there is a property on each field called SQL-WIDTH, this property tells the ODBC driver how long the field is. If the data in the field is longer than the SQL-WIDTH setting then you won't be able to query that field until it's corrected.
Also, if you use any SQL-89 ODBC driver, be aware that character data can be truncated. This is because it uses the field display length to set the data length. If you can make adequate changes to the database schema to eliminate the truncation, the SQL-89 driver does perform much, much quicker.
If at all possible I'd suggest you re-think the linked server idea. We tried using a linked server with each of the drivers and they all caused SQL Server to become non-responsive after 1-2 hours use. Some of the techsupport guys seemed to suggest it was something to do with Progress itself rather than their ODBC drivers, but they would say that wouldn't they.
--------------------
Colt 45 - the original point and click interface
-
Hi all,
We have a similar setup here with using DTS to copy data across to SQL overnight. We have tried the Linked Server scenario as well but we found that to be slow mainly in our system (MFGPro), and in Progress 9.1d, the query works well if it is a straight select * from tablename, if you need it to do anything other than that, it takes forever.
On that note, does anyone have any experience with Progress 9.1d on HPUX - it seems to only allow 2 simetanous connections at a time.
Hope this helps
Thomas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply