First of all, I would like to introduce myself. I'm not a Progress developer or dba. I'm not a SQL Server developer or DBA, either. However, I am a business intelligence manager and because my company doesn't have enough of either of the above, I've learned quite bit through the age-old trial by fire method. I just finished reading Progress's "Progress DataServer for MS SQL Server Guide" and it didn't really answer the questions I have. I'm hoping that someone here might have some insight they don't mind sharing with a newbie.
Here's what we're trying to accomplish:
I work for a manufacturing company that uses Progress (running on AIX) as it's shop floor database. This is all well and good, and it's stable as a rock. However, I'm in charge of providing adequate shop floor reporting (via the web) and using ODBC (Merant SequeLink) is really a poor solution for a variety of performance and security reasons I'm sure you can imagine. Historically, we've used ODBC anyway, but only insofar as it was necessary for SQL Server to connect directly to Progress and execute scheduled DTS packages to pull and transform records as necessary. This didn't work very well, either, and because of the ODBC performance we weren't able to get all of the production data -- just a small subset.
Jump forward three years.
Now we're trying to use the Progress DataServer to replicate data from the production Progress database into MS SQL Server, in very close to real time. This shouldn't be a problem at all, even with the volume of data we're looking at (some plants generate >1m records/day).
The problem we're having is that whenever we turn on the replication trigger it creates thousands of page locks in SQL Server for each transaction. Our Progress developer swears he set the NOLOCK (Read Uncommitted) parameter in the ODBC connection, and I believe him. Neither of us is having any luck figuring out what's causing these locks. They're released as soon as the transaction completes, but even having a transaction take as long as .5-.75 seconds is unacceptable.
If necessary I can try to provide more specific information, but since I really don't have a very technical grasp on the actual 4GL, I'll need to be told what you'd like to see.
I'll appreciate any help anyone can provide, and will sing your praises around the world until the end of time... or the next time this breaks, whichever occurs first.
Thanks in advance,
Elliot
Here's what we're trying to accomplish:
I work for a manufacturing company that uses Progress (running on AIX) as it's shop floor database. This is all well and good, and it's stable as a rock. However, I'm in charge of providing adequate shop floor reporting (via the web) and using ODBC (Merant SequeLink) is really a poor solution for a variety of performance and security reasons I'm sure you can imagine. Historically, we've used ODBC anyway, but only insofar as it was necessary for SQL Server to connect directly to Progress and execute scheduled DTS packages to pull and transform records as necessary. This didn't work very well, either, and because of the ODBC performance we weren't able to get all of the production data -- just a small subset.
Jump forward three years.
Now we're trying to use the Progress DataServer to replicate data from the production Progress database into MS SQL Server, in very close to real time. This shouldn't be a problem at all, even with the volume of data we're looking at (some plants generate >1m records/day).
The problem we're having is that whenever we turn on the replication trigger it creates thousands of page locks in SQL Server for each transaction. Our Progress developer swears he set the NOLOCK (Read Uncommitted) parameter in the ODBC connection, and I believe him. Neither of us is having any luck figuring out what's causing these locks. They're released as soon as the transaction completes, but even having a transaction take as long as .5-.75 seconds is unacceptable.
If necessary I can try to provide more specific information, but since I really don't have a very technical grasp on the actual 4GL, I'll need to be told what you'd like to see.
I'll appreciate any help anyone can provide, and will sing your praises around the world until the end of time... or the next time this breaks, whichever occurs first.
Thanks in advance,
Elliot
.