OpenEdge SQL Width Server Parameter

After SQLTruncateTooLarge is set to yes can DBtool be used to report and fix width issues?

I like the concept that we can avoid errors on the SQL side. What I am getting at is what is recommend to be used to notify users and administrators that a width issue has been introduced in the data?

I like the fact that you can control the settings at server startup and connection time. Not all SQL processes are created equal and some processes would always need to error out but some could safely truncate.

Definitely an excellent first version. An idea for some future release would be the ability to control this by table and column. I could see situations where a comment/notes column could be truncated much more safely than key columns.

To use a sports db example... truncate the customer comments but not the state or salesrep.

The PDF from the link says that summary events will be logged in the database log and detailed logging can be configured at the backend or client level to give you the exact table, column name and rowid for every truncation event.

Seems like a decent balance to me.

Indeed, it seems like this really should be by table and there should be two options ... truncate or bump the width to match.

Nice work guys.  Definitely a good first pass at this.

Example 3 in the document is about the only place I see where you can really get yourself into trouble.  Going into this with eyes wide open, could help a lot of customers.  Appreciate the efforts and I look forward to future versions of this.

Love it

"Indeed, it seems like this really should be by table and there should be two options ... truncate or bump the width to match -"

+1  as discussed and voted for at PUG Düsseldorf

As much as I would like to see the width auto changed... implementing this is going to be problematic.

The SQL client already has expectations on the width of the columns before the data starts returning. Changing it mid stream isn't supported by the clients themselves.

Having this happen during database writes could be an option but I wonder what the performance implications would be.

I completely agree on both accounts.  The last thing I want to see is something impacts performance.

I think this is pretty good and close.  I would like to see the truncation happen on the output only, rather than on the read.  Currently, this is truncating the data when it is read, which is why there may be issues around joins associated with the truncated data.  If the internal processing of the records was left alone, and only the output of the data was truncated, we could avoid the logical "miss-joins".

"Having this happen during database writes could be an option but I wonder what the performance implications would be "

More then data encryption checking toss limits and so on ? I don't think so.

I was told that the main problem could be that the normal user has no rights for schema updates.

IF (big if) you are going to enable truncation I think doing the truncation on the read side is the most reasonable approach. It at least makes it consistent from a user perspective.

If it only happened on the output side you will still have confusion and other issues. Things that were supposed to be grouped together would not be (ABC123, ABC124, ABC125) would probably show up as three lines of ABC.

Either approach is likely to have some nasty issues from time to time. Things like this are the main reason I would rather enable this on certain columns and make sure none of those columns are used in joins.

I am going to go out on a limb here and say it could be much worse than TDE or toss limits. Those are all low level operations that happen at a block level without multiple users trying to update them at the same time.

For TDE nothing is actually encrypted until the block is written to disk which helps to keep the overhead low. Plus as an alpha tester I can personally attest to how much time Progress spent tuning TDE.

When you start talking about row level operations that have to lock the schema and make sure other users know about those updates it gets a little murkier. If the event only happens a few times a week it wouldn't be that big of a deal. If it starts happening on a regular basis life could get ugly quick.

But maybe PSC can come up with something in a future release.

Sql cannot change the defined width for a given query, during query execution, because applications setup for the defined width of data before actual query execution.  So if sql   sent  data larger than defined width to the client application that would cause serious problems like buffer overruns.

Sql can learn from the observed actual data sizes during query execution, and then subsequently used that actual size to update the schema (if larger than defined size).

For OE 11.6, we are developing a new sql feature along these lines.  This will be an ability to update the schema for sql width problem cases, if this new feature is enabled by a startup parameter. The schema update ability implies enabling data truncation because that lets sql  read the data that is larger than its defined size. There are a lot of details needed to give the full picture, and we hope to provide those, to Communities, when this feature becomes part of an ESAP release.  

thanks to all for your feedback, which is invaluable!

...steve pittman  [OE sql architect]