Hi - I am a progress novice and have what is probably a really basic question.
We have a progress database as a standalone application and a whole load of SQl Server databases that rely on teh information in the progress system for more accurate information. We have OpenEdge driver 10.2B. I use a SQL DTS (becasue I am stuck on a SQL 2000 system(!)) to transfer the data.
The DTS normally works brilliantly. However, sometimes it seems that the progress database is allowing a user to enter a larger entry than the field size would suggest and I get error messages that state, for example:
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column X in table Y has value exceeding its max length or precision.[DataDirect][ODBC Progress OpenEdge Wire Protocol driver] Error in row.
Is this a matter of the driver looking at the database and doing DQ checks before pumping the information across? Brilliant but irritating that the Progress system allows the inital entry.
If, in my DTS, I exclude a field, will the OpenEdge driver still look at the field for DQ checks before pumping uout a reduced dataset?
I hope I haven't rambled too much and that someone can answer. I have asked the system supplier and they were unsure.
We have a progress database as a standalone application and a whole load of SQl Server databases that rely on teh information in the progress system for more accurate information. We have OpenEdge driver x.yb. I use a SQL DTS (becasue I am stuck on a SQL 2000 system(!)) to transfer the data.
Flag this post as spam/abuse.
I don't know for sure, but I think this is the SQL Width on the Progress side that is causing the errors. You will need to run DBTool to update this.
Thanks both for responding - coming from a SQL server background (and not wanting to sound like a Microsoft snob) it goes against every DB design principle I have ever been taught that a DB can, basically, ignore table design.
Is there an actual purpose to this? or is it a feature of the software? The reason our supplier is using is that users like the flexibility, but if it makes the DB inefficient then it is really counterproductive...
It isn't ignoring the table design as such. The table is created with a best guess at the width. You are then provided with a way to expand it based on data added subsequently. If you're using SQL to access a Progress database there are a couple of things you should do reasonably regularly. One is to update the SQL widths with DBTool. The other is to update statistics so that the queries run more efficiently.
All Progress CHAR fields are VARCHAR with a max size of 32K. Since SQL absolutely wants a field width, Progress sets that using a special field attribute called sql_width. dbtool is the program provided by Progress to automatically adjust the SQL width attribute. IIRC, dbtool sets sql_width to twice the width of the widest instance if the field in question.
I typically run dbtool and update statistics once per month.
Some would say Update Statistics should be run weekly, but I guess it's a business decision that has to be taken there.
I used to run it weekly and I'm sure it's not a bad thing per se, but I doubt that data distributions change enough from week to week to justify it. There may be a couple of tables that see a lot of ADD/DELETE action that would benefit from frequent UPDATE STATISTICS, but most tables (think customer, item, etc...) would not.
Maybe I'm wrong. I'd be happy to hear someone with a mathematical explanation of how to calculate update statistics frequency requirements.
I'm just talking from the perspective of hearsay as we don't have SQL clients. A couple of folks over on Progresstalk champion the weekly run. Maybe they'll jump the great divide to fill us in here! ;)
The idea that field width ought to be limited for efficiency reasons is one of those ancient premature optimization things that doesn't actually hold water. It's actually kind of surprising (to me anyway) that Codd & Date didn't put up a stink about it because it really doesn't fit with "relational" thinking very well at all.
Progress has had variable width fields from the beginning. Progress also had row level locking from the beginning. Just two of the ways that Progress has always been out ahead of the pack :)
No worries James - my comment was not meant as an attack on you and I apologize if it came across that way.
Hearsay is one of those dangerous things: it's the same hearsay that tells us that variable length database extents are BAAAAAADDDD!!!!!!!! (Hint: they are not 99.9% of the time and I can prove it mathematically)
Oh crumbs no Paul - no offence taken in the slightest! :)
As for hearsay - I completely agree. It depends, though, on whom the advice comes from - and in this case the particular folks on Progresstalk are folks whose advice I would trust wholeheartedly.
Rich has said that you should run UPDATE STATISTICS when 20% of your data has changed. He then asks, rhetorically, how you can know that 20% of your data has changed... (I suppose you could keep track of bi cluster closed?)
Personally, I often suggest weekly because:
1) It does no harm.
2) Most people have a slow time at least once a week when it is convenient to do so.
3) It is easy to script and there are several other things that I like to do weekly (dbtool, truncate logs, run dbanalys).
4) Weekly routines are less likely to be forgotten than monthly or even more irregular tasks.
Not particularly query efficiency, more like storage space efficiency. If I was going to design a SQL database like that I would be using VARCHAR(MAX) field sizes which would increase my file size because of the memory each field would use. If you have ever seen the number of columns in any of the datasets that exist in the NHS, you would realise why sometimes limits in SQL Server have to be set. Integration with other DB systems is also more problematic if the DB is saying one thing but the data is saying another.
Still, Who's counting. They all have their place. Thanks for the help you all gave earlier and sorry if I have started any arguments :)
That 20% is applicable at the table level more than the DB level, I would think. Customer and item are unlikely to change 20% very often but inventory, shipper, invoice, gl...all those tables are constantly being modified.
(Theoretically) I wonder if basing the decision on index operations (_IndexStat) would give a more precise trigger. A table that has x amount of write activity to indexes is potentially in need of an UPDATE STATISTICS.
But you are correct, you script it once and forget about it. I think monthly is more than sufficient but as you say, weekly does no harm and you are probably running other maintenance tasks weekly anyways.
I think that you are assuming that a field is padded out to it's maximum size.
Progress fields are not. So they only take up whatever space they actually need. That's actually pretty efficient from a disk and memory usage perspective. Sure, the "overstuffed" fields use more space -- but the "understuffed" ones conserve a whole lot.
It is one of those reasons that Progress tends to be a lot less demanding of system resources than Oracle or SQL Server.
Arguments are good -- they bring out lots of discussion points :)