Importing into SQL-based databases

[BCM]

For the past three weeks I have been seeking the optimal way to import the business data in our Progress database into SQL Server database for reporting. The routine(s) are to be run daily.

There seems to be an absence of expert knowledge providing deep coverage in this area of integration. Based upon my testing, benchmarking, and a brief communication from our vendor here is what I have found:

The Progress broker for the SQL-92 connection serves the same purpose as the Oracle listener. However, the granularity with which the Progress broker listens is at the client level rather than the request level. This is significant from the perspective that multi-threaded clients will exist. The web browser is an example of a multi-threaded client that may open multiple windows, making requests from each window, in parallel. The SQL Server includes a job runner that can run multiple jobs concurrently. I believe that Unix's CRON does the same thing. Concurrent jobs could be requests for data from the Progress database. Unfortunately, Progress will see the concurrent requests as all being from the same client and will not spawn a new server to handle the request. This means that concurrent requests from a single client to the Progress SQL service will be processed serially in the order received. This limitation does not exist with databases like Oracle, SQL Server, DB2, etc.

My other noteworthy finding is that the shortest elapsed time for retrieving large sets of rows from Progress is to breakup the retreival into small groups and put them back together on the destination database. Furthermore, asking the Progress database to provide any additional functionality - casting, min/max functions, and joins - results in much greater processing time, and the best elapsed times will be achieved by simply getting the raw facts from the Progress database and manipulating the results in the destination database.

 

[joey.jeremiah]

maybe peerdirect could do what you're looking for ? be warned it's a progress
company www.peerdirect.com


another thing i'd try is log based replication

using replication triggers to track the changes and data servers to copy the
data into the target database.

store the raw data in a general purpose table and copy at a set interval
( asynchronously ) or synchronously.

never done it before with non-progress database.

 

[BCM]

Joey,
I'm in complete agreement with you. Here is our (my company's) predicament. We have no code and no technical manuals. I must reverse engineer things through lengthy trial and error. The vendor does not want us to look 'under the hood'. They want us to spend money money money with them while they stumble around with a poorly designed system. We tried that and they are unable to deliver. I wanted to write replication triggers 15 months ago, and I proved that it would work on a copy of the application's database. But we our concerned that anything we do to the database directly may have an adverse impact on the integrity of the application as it is. Even if our modification worked now, what guarantee would we have that future releases would work. We're simply trying to attain a level of performance and stability so that we can spend our time writing our own application to replace this one.

 

[joey.jeremiah]

as you probably know besides the create, delete, write, find triggers

there are additional replicate-create, replicate-delete, replicate-write
triggers, for that purpose.


you're right, there are no gaurantees these replication triggers won't be
used for additional business logic.

but usually thats what the create, delete, write triggers are for and
replication triggers are for replication.

and even if they were it could still be dealt with, but it would probably
require some co-operation from you vendor.


maybe even have the replication triggers store the data in a log database

and write a job in visual basic or what ever that reads and copies the
data to the target database, at a set interval.

i would bet it's been done before and with reasonable performance impact.


you would not need any documentation all you need is the schema.

 

[BCM]

Joey -
Again, I completely agree with you. We do not have a level of cooperation from the vendor to give us the confidence we need to pursue this.

By the way, it is not necessary to write any Visual Basic or other application language program to move the data to the target database. All that is necessary is for the replication trigger to execute an OS batch file sending it the identification of the table, record key, and new field values. For SQL Server the batch file would execute a console utility called OSQL that would replicate the data to the target database. I tried this early last year. It works great.

The application we have has been extended for years for many of the vendor's customers. The vendor has never redesigned the application or database to be more robust. It is extremely convoluted and the database design is in disarray with many redundancies. We have no confidence that releases from the Vendor will not break something that was fixed in a previous release. ...and this is the only application that the vendor sells and supports.

I am sorry to say that I have experienced this type of application design twice since 1990, and both times the application was built in Progress 4GL with a Progress database. Any of today's development languages allow the programmer to proceed before design is well thought, but the Progress database makes it easy for the developer to avoid correcting design flaws at the database level. Instead, the Progress model encourages handling these flaws at the application level. The problem with this is that no application is an island unto itself. Most applications today are itegrated with other applications. To interface properly to one of these poorly designed Progress databases would require another entire 4GL application. That is a form of blatant exploitation by the vendor.

 

[joey.jeremiah]

any time you're implementing large scale apps

with many modules that may or may not be used
that account for many possible scenarios you'd get redundancies, right ?

i.e screens, options, tables, fields etc. that aren't being used


i have more experience writing proc then designing architectures

i think service-oriented arch not just sql is a solution for apps that are
madeup of a mesh of diff apps

e.g. some modules implemented in sap, some progress and some in oracle


i do think the replication scheme is doable, atleast worth a shot

it would probably require some thought to go into it, if it's not violating any
acid rules, haven't really thought it thru.


i don't really have a feel on your situation and the relationship with your
vendor and if you can allow for some work to be done.

if it's not replication whats the main issue, reporting ?


you know the saying, nothing ventured nothing gained