We have to migrate data from a ****gress 9.1D application database on a
regular basis to a SQL environment and ODBC loads are proving slow and
inefficient. In addition the source tables in Progress from which we
are copying data often do not have any suitable keys or other values
we can use to detect changed or inserted records and we are therefore
having to extract the entire table each time (thankfully the volumes
are modest and the server is meaty enough not to mind). Clearly this
is inefficient and means we are also burdened when we load the target
as we are re-processing a lot of existing data. In addition the
reading of 4GL data using SQL often gives rise to the well-known SQL
field width errors.
We are investigating ways of using Progress code to dump the table
data to a text file but have only an arms-length access to the system
at the moment. We can easily generate .p script files on the fly (from
teh remote environment, based on meta data) which define exactly what
fields in each table to dump out to a text file. The text file
resulting from executing the script (probably under the scheduler)
would then be copied/accessed across the network and processed for CDC
on the SQL side (we've already done all this work and it is quick and
easy).
Before we press forward we really could do with some (even subjective)
feedback on whether we are likely to find the Progress .p processing
of a table dumping into a text file signifcantly faster than remotely
by ODBC accessing the very same data and pulling it over the network?
Right now 50k records is taking 1 minute via ODBC and 400k (150Mb of
data when saved as text) takes just under 10 minutes :(
We're also open to other suggestions of better mechanisms for doing
the change data capture elements and minimising the impact on the
Progress server especially and the SQL server environment too (less
im****tant).
Thanks,
RB
