11.6 sql - Autonomous Schema Update - for sql width problems

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.

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.

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

-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?

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.

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]

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

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