OpenEdge SQL Width Server Parameter

Posted by Brian Bowman on 29-Jul-2015 12:40

OpenEdge Development is happy to provide you with an additional server startup parameter that will help with managing data within the OpenEdge SQL Engine.  The parameter is SQLTruncateTooLarge and is available in OpenEdge releases 11.5.1 and higher.  OpenEdge Development has written a whitepaper that covers how this parameter works and how to set it. The link to that whitepaper is below.

It is important to note that this feature is not automatically enabled.  When you enable it, the resulting output of your data can be truncated based on the parameter settings.

OpenEdge Development would like to hear from you about this feature.  Please provide comments on what you like and don’t like with this feature through the OpenEdge community.

OpenEdge Development will continue to enhance this feature in the coming releases.  Stay tuned for additional functionality.

Thank you for your feedback!

The OpenEdge Development Team

https://community.progress.com/community_groups/openedge_rdbms/m/documents/2326.aspx

All Replies

Posted by bremmeyr on 29-Jul-2015 13:00

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

Posted by bremmeyr on 29-Jul-2015 13:11

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?

Posted by TheMadDBA on 29-Jul-2015 13:20

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.

Posted by TheMadDBA on 29-Jul-2015 13:22

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.

Posted by Thomas Mercer-Hursh on 29-Jul-2015 13:26

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

Posted by steve pittman on 29-Jul-2015 14:18
Yes, DBtool can be used to report/fix width issues  after SQL TruncateTooLarge is set to “yes”.
 
If the optional logging for SQL TruncateTooLarge is turned on,  if you wish, you can use the logs to apply DBtool to only the actual tables  where sql did one or more truncations (because SQL TruncateTooLarge was turned on).  That is, the logs will tell you what tables got sql truncation, and then you can decide what you want to do about that.
 
Hope this helps,                ….. steve pittman  [OE sql architect]
 
 
[collapse]
From: bremmeyr [mailto:bounce-bremmeyr@community.progress.com]
Sent: Wednesday, July 29, 2015 2:01 PM
To: TU.OE.RDBMS@community.progress.com
Subject: RE: [Technical Users - OE RDBMS] OpenEdge SQL Width Server Parameter
 
Reply by bremmeyr

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

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]
Posted by jefbrown on 29-Jul-2015 14:30

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.

Posted by bremmeyr on 29-Jul-2015 15:15

Love it

Posted by Stefan Marquardt on 30-Jul-2015 09:43

"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

Posted by TheMadDBA on 30-Jul-2015 09:57

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.

Posted by jefbrown on 30-Jul-2015 10:03

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

Posted by jefbrown on 30-Jul-2015 10:05

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".

Posted by Stefan Marquardt on 30-Jul-2015 10:14

"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.

Posted by TheMadDBA on 30-Jul-2015 10:14

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.

Posted by TheMadDBA on 30-Jul-2015 10:20

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.

Posted by steve pittman on 30-Jul-2015 10:58

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]

This thread is closed