OpenEdge SQL Width Server Parameter - Forum - OpenEdge RDBMS - Progress Community

OpenEdge SQL Width Server Parameter

 Forum

OpenEdge SQL Width Server Parameter

  • 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

    Brian L. Bowman

    bowman@progress.com

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

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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]
     
     
    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.

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