11.6 sql - Autonomous Schema Update - for sql width problems

Posted by steve pittman on 07-Aug-2015 10:42

As part of OpenEdge 11.6,  OpenEdge SQL will be providing a new feature to help manage
sql width problems. The new feature is called Autonomous Schema Update (ASU). We are providing an early description of ASU so that you can learn about this upcoming feature, and so we can hear from you about this feature. Please let us know about any observations, questions, remarks that you may have on this 11.6 ASU feature.


This new ASU feature builds on the Authorized Data Truncation (ADT) feature delivered in OpenEdge 11.5.1. ADT, when enabled, will truncate column values that are larger than their defined size. ASU adds to this truncation capability by updating the schema, for the columns being truncated, with the actual observed size of the columns' data values. ASU does not add to the user's transaction workload.

Like ADT, ASU must be enabled by a startup parameter. It is not ON by default.

I've attached a document that contains much more detailed description of Autonomous Schema Update. This document is not a "polished" whitepaper. In order to get a timely description to Communities, we've derived a description from our development design documents. At a later stage, there may be a whitepaper, but that is To Be Decided.

Thanks,         .....steve pittman and the OE sql Development team

All Replies

Posted by steve pittman on 07-Aug-2015 11:42

I should have added that the sql Autonomous Schema Update feature is not in past 11.6 ESAP releases and will not be in the next 11.6 ESAP.  When there is an ESAP release with this feature, there will be more details in the ESAP forum. And probably a brief note here.

Thanks.

Posted by cverbiest on 10-Aug-2015 01:54

Looks good, we'll activate this as soon as it becomes available.

Is there a way for the SQL client to determine that the next query will yield different results due to the ASU ?

Not sure if our BI tool could use it to re-do the query but I'm curious if there is a status or something else that could be queried.

Posted by rchundur on 10-Aug-2015 02:28

Hi,

Unfortunately this (determine that the next query will yield different results due to the ASU) is not available in 11.6 release.  We will consider to have this in future releases.

Though there is no direct way to determine that next query will yield different result set, there is an indirect way to do this by enabling Authorized data truncation logging. When this authorized data truncation logging is enabled, for the first time, query is executed, sql engine truncates data and will log related information in truncation log files. When the same query is executed again, as the sql width would have been updated already, sql engine will not do any truncation and hence there won’t be any info in truncation log.

Thanks,

Raja Sekhar

Posted by Stefan Marquardt on 03-Sep-2015 08:42

-A new server startup parameter “-SQLWidthUpdate”

“-SQLWidthUpdateAtOnce" would be better

Isn't it possible to update the width value in the schema during any update which needs to increase it?

We need the correct width to get a valid schema for SQL export which will be done with ABL EXPORT command.

This new feature needs to run a SQL command (odbc/jdbc) for every record to be safe to get the correct width in the schema?

Posted by steve pittman on 03-Sep-2015 09:51

Hi Stefan,
 
Thanks for your comments on the new 11.6 feature Autonomous Schema Update (for OE sql).
 
This feature does not update the schema “at once”.  “at once”  implies that the schema would be updated immediately  when the too-large values are read, and that is not the case.
 
The update occurs after completion of the query which sees the too-large value, and after a small amount of time needed to “send” the needed update to another server management thread that does the update. Waiting until query completion enables sql to get a look at as many too-large values as the query sees. E.g. 50 too-large values for a column “my_field” will result in only one schema update.
Note that the schema update does not affect the application’s transaction.
 
About updating the schema for sql width during “during any update which needs to increase it”,  this would have to be an ABL update, and so this is about ABL runtime execution. One of the problems with this approach would be that the application’s transaction would “expand”, unknown to the application, to include schema updates. That is problematic, to say the least.  I’m sure there are non-trivial ways to make this work, and they might be an interesting enhancement request for future consideration.
 
This feature is designed to help deployed sql applications, or sql tools (like Crystal) that  are used alongside deployed ABL applications.
 
The best way to check all the db data for sql width problems is to use the OE database tool “dbtool”. Dbtool can very rapidly scan large amounts of data, and as an option update the schema.
In pre-deployment situations, dbtool will make sure the exported schema is Ok with respect to sql width issues.
 
Hope this helps,   ……sjp
 
 
 
[collapse]
From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
Sent: Thursday, September 03, 2015 9:43 AM
To: TU.OE.RDBMS@community.progress.com
Subject: RE: [Technical Users - OE RDBMS] 11.6 sql - Autonomous Schema Update - for sql width problems
 
Reply by Stefan Marquardt

-A new server startup parameter “-SQLWidthUpdate”

“-SQLWidthUpdateAtOnce" would be better

Isn't it possible to update the width value in the schema during any update which needs to increase it?

We need the correct width to get a valid schema for SQL export which will be done with ABL EXPORT command.

This new feature needs to run a SQL command (odbc/jdbc) for every record to be safe to get the correct width in the schema?

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by Stefan Marquardt on 03-Sep-2015 10:20

Hi Steve,

my idea would be to let the ABL/SQL clients set a new HWM value in any control table that an update is needed and it's done from a separate process which does the same as your current plans for 11.6

This flag/value could be used by dbtool, then only the tables with the new HWM needs to be updated in the schema and prevents any scans.

Currently we are using dbtool but this needs ~10-15 minutes with multiple threads.

My 2 cents - should be easy.

Posted by gus on 03-Sep-2015 13:20

A better solution would be to set the configuration parameter that makes the 4GL runtime enforce the maximum width. The initial setting of maximum width is based on the default display format, which has nothing to do with anything.

This is the -checkwidth parameter.

Yesterday, one of my colleauges encountered a customer that was getting errors "The total length of the fields in an index exceeds max key size. Index xxxxx of table yyyy (129)". For 4k block size, this is more than 1,000 bytes.

Turned out this was caused by a mistake in the application. The correct data valeus are only 10 to 15'ish bytes long, not 1,000.




> On Sep 3, 2015, at 11:21 AM, Stefan Marquardt wrote:
>
>[collapse] From: Stefan Marquardt
> Post: RE: 11.6 sql - Autonomous Schema Update - for sql width problems
> Posted in: Forum
> Link: https://community.progress.com/community_groups/openedge_rdbms/f/18/p/19534/71030.aspx#71030
>
> Hi Steve,
> my idea would be to let the ABL/SQL clients set a new HWM value in any control table that an update is needed and it's done from a separate process which does the same as your current plans for 11.6
> This flag/value could be used by dbtool, then only the tables with the new HWM needs to be updated in the schema and prevents any scans.
> Currently we are using dbtool but this needs ~10-15 minutes with multiple threads.
> My 2 cents - should be easy.
>
>
> --
> You were sent this email because you opted to receive email notifications when someone created a new thread.
>
> To unsubscribe[collapse] from:
> - ...only this thread, disable notifications at https://community.progress.com/community_groups/openedge_rdbms/f/18/p/19534/71030.aspx#71030.
> - ...all email notifications from Progress Community, navigate to "Settings", click on the "Email" tab, then under the "Email Configuration" section, set Send Notifications to "No".
>[/collapse][/collapse]

Posted by Brian Bowman on 03-Sep-2015 14:42

Hi Stefan –
   You are correct, until the SQL command is run to access the rows we do not have visibility into whether the field is over-stuffed.  Due to the architecture and nature of SQL and SQL engines in general it is not possible to update the width “on the fly”.  SQL requires and very much enforces field widths prior to the select being submitted.  We had explored how to do this in real time but the only way would be to execute each and every query twice changing the schema between queries.  Obviously this would not work.
 
There are database utilities to scan the database and fix this without having to run a SQL command.  This would be the preferred solution if you are concerned with the SQL width for export.

Brian
 
Brian L. Bowman
 
Senior Principal Product Manager
Progress Software Corporation
14 Oak Park, Bedford, MA, USA 01730
 
Phone: +1 (603) 801-8259
Email: bowman@progress.com
 
 
[collapse]
From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
Sent: Thursday, September 03, 2015 9:43 AM
To: TU.OE.RDBMS@community.progress.com
Subject: RE: [Technical Users - OE RDBMS] 11.6 sql - Autonomous Schema Update - for sql width problems
 
Reply by Stefan Marquardt

-A new server startup parameter “-SQLWidthUpdate”

“-SQLWidthUpdateAtOnce" would be better

Isn't it possible to update the width value in the schema during any update which needs to increase it?

We need the correct width to get a valid schema for SQL export which will be done with ABL EXPORT command.

This new feature needs to run a SQL command (odbc/jdbc) for every record to be safe to get the correct width in the schema?

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by mfurgal on 03-Sep-2015 14:50

The way I read the request was having the _width updated when the record was updated.  The only was a field can be overstuffed is from the 4GL, not SQL.  So I thought the proposal was for the _width to be updated for a field when it is grown to exceed the current width from the 4GL.

MikeF
-- 
Mike Furgal
PROGRESS Bravepoint
678-225-6331 (office)
617-803-2870 (cell)
mfurgal@progress.com

[collapse]
From: Brian Bowman <bounce-bowman@community.progress.com>
Reply-To: "TU.OE.RDBMS@community.progress.com" <TU.OE.RDBMS@community.progress.com>
Date: Thursday, September 3, 2015 at 3:43 PM
To: "TU.OE.RDBMS@community.progress.com" <TU.OE.RDBMS@community.progress.com>
Subject: RE: [Technical Users - OE RDBMS] 11.6 sql - Autonomous Schema Update - for sql width problems

Reply by Brian Bowman
Hi Stefan –
   You are correct, until the SQL command is run to access the rows we do not have visibility into whether the field is over-stuffed.  Due to the architecture and nature of SQL and SQL engines in general it is not possible to update the width “on the fly”.  SQL requires and very much enforces field widths prior to the select being submitted.  We had explored how to do this in real time but the only way would be to execute each and every query twice changing the schema between queries.  Obviously this would not work.
 
There are database utilities to scan the database and fix this without having to run a SQL command.  This would be the preferred solution if you are concerned with the SQL width for export.

Brian
 
Brian L. Bowman
 
Senior Principal Product Manager
Progress Software Corporation
14 Oak Park, Bedford, MA, USA 01730
 
Phone: +1 (603) 801-8259
 
 
[collapse]
From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
Sent: Thursday, September 03, 2015 9:43 AM
To: TU.OE.RDBMS@community.progress.com
Subject: RE: [Technical Users - OE RDBMS] 11.6 sql - Autonomous Schema Update - for sql width problems
 
Reply by Stefan Marquardt

-A new server startup parameter “-SQLWidthUpdate”

“-SQLWidthUpdateAtOnce" would be better

Isn't it possible to update the width value in the schema during any update which needs to increase it?

We need the correct width to get a valid schema for SQL export which will be done with ABL EXPORT command.

This new feature needs to run a SQL command (odbc/jdbc) for every record to be safe to get the correct width in the schema?

Stop receiving emails on this subject.

Flag this post as spam/abuse.

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse][/collapse]

Posted by Stefan Marquardt on 07-Sep-2015 03:41

Mike, you are correct, then we get rid of dbtool for width fix.

@Gus: -checkwidth will change the behavior of the application.

This thread is closed