10.2B08: Option auto adjustment sql-width available? - Forum - OpenEdge RDBMS - Progress Community
Home » Community Groups » OpenEdge RDBMS » Forum » 10.2B08: Option auto adjustment sql-width available?

10.2B08: Option auto adjustment sql-width available?

 Forum

10.2B08: Option auto adjustment sql-width available?

This question is answered
Stefan Marquardt
Posted by
on 16 Oct 2014 2:38

Is there an option available that increase database sql-width on the fly to the maxium string length in the record(s)?
We run a replication and creates ddl and dump files and load them in  SQL reporting database.
Currently i have to run dbtool every time to prevent length errors in MS SQL server.
Verified Answer
  • Chandra Sekhar
    Posted by
    on 16 Oct 2014 3:26
    Verified Answer
    Verified by
    There is no option to increase SQL-Width on the fly.  While running dbtool there is an option for padding.
    Pad current max length by 10 or 20% extra, so that it will avoid running dbtool tool every time.
     
    From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
    Sent: 16 October 2014 PM 01:10
    To: TU.OE.RDBMS@community.progress.com
    Subject: [Technical Users - OE RDBMS] Option auto adjustment sql-width?
     
    Thread created by Stefan Marquardt

    Is there an 10.2B08 an option available that increase database sql-width on the fly to the maxium string length in the record(s)?
    We run a replication and creates ddl and dump files and load them in  SQL reporting database.
    Currently i have to run dbtool every time to prevent length errors in MS SQL server.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

All Replies
  • Chandra Sekhar
    Posted by
    on 16 Oct 2014 3:26
    Verified Answer
    Verified by
    There is no option to increase SQL-Width on the fly.  While running dbtool there is an option for padding.
    Pad current max length by 10 or 20% extra, so that it will avoid running dbtool tool every time.
     
    From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
    Sent: 16 October 2014 PM 01:10
    To: TU.OE.RDBMS@community.progress.com
    Subject: [Technical Users - OE RDBMS] Option auto adjustment sql-width?
     
    Thread created by Stefan Marquardt

    Is there an 10.2B08 an option available that increase database sql-width on the fly to the maxium string length in the record(s)?
    We run a replication and creates ddl and dump files and load them in  SQL reporting database.
    Currently i have to run dbtool every time to prevent length errors in MS SQL server.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Stefan Marquardt
    Posted by
    on 16 Oct 2014 3:41

    I have to insure 100% that all sql-widths are fine. Padding will not help because sometimes 3 increases to 25.

    This would be a big % value for other fields -> no option.

    it would be so easy for the RDBMS because it has the knowledge of everything during the update.
  • ChUIMonster
    Posted by
    on 16 Oct 2014 5:32
    There is an option to tell 4gl clients not to overstuff fields. I
    forget what it is called because I never use it.

    Frankly I think the better solution would be to tell Microsoft, Oracle
    and the rest of the SQL world to stop being silly and move into the
    modern age. OpenEdge has been able to handle variable length data for
    30 years. What's taking them so long?

    --
    Tom Bascom
    603 396 4886
    tom@greenfieldtech.com

    --
    Tom Bascom
    tom@wss.com
  • Stefan Marquardt
    Posted by
    on 16 Oct 2014 5:53
    1. Yes, but this option would change the behaviour of the production system.

    For SQL there is VARCHAR(MAX) but this isn't the same and has limits. (like index)

    "world to stop being silly"
    Are you at EMEA PUG in Stuttgart, i am looking forward to discuss it with my SQL colleague.
  • Richard Banville
    Posted by
    on 16 Oct 2014 7:41
    The way to do this is with the dtool utility.
     
    Option “1. SQL Width & Date Scan w/Report Option” will scan and report violations.
    Option “2. SQL Width Scan w/Fix Option”  will fix any violations and optionally give you the opportunity to pad sqlwidth some % larger than the current maximum field width found in the current data of database.
     
     
    From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
    Sent: Thursday, October 16, 2014 4:42 AM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?
     
    Reply by Stefan Marquardt

    I have to insure 100% that all sql-widths are fine. Padding will not help because sometimes 3 increases to 25.

    This would be a big % value for other fields -> no option.

    it would be so easy for the RDBMS because it has the knowledge of everything during the update.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Richard Banville
    Posted by
    on 16 Oct 2014 7:48

    running the ABL connections with -checkwidth option gives the user the ability to report violations as they occur or to prevent the data from being inserted if the width is in violation of the _sql-width value
  • Stefan Marquardt
    Posted by
    on 16 Oct 2014 9:57

    Hi Richard,

    I request a -checkwidth 3 :

    Store the data and increase the _width field if needed

    I am sure that somebody at PSC must have the same idea ...

    Stefan
  • Richard Banville
    Posted by
    on 16 Oct 2014 10:05
    Yes, but of course it is much more complicated than that to change SQL’s process private allocated structures on the fly.
    SQL has pre-allocation of existing field widths for communication with the drivers and then the drivers to the application.
    You could argue that changing on the fly would work unless the data was in the process of being requested by SQL.
    However, your suggestion is not an absolute solution and is not part of the current product.
    If you feel strongly about it, you should submit an enhancement request through communities enhancement request system
    _________________________________
    Richard Banville
    Fellow, OpenEdge Development

    PROGRESS SOFTWARE CORPORATION
    14 Oak Park | Bedford, MA 01730 | USA
    DIRECT  +1 781 280 4875
    richb@progress.com
     
     
    From: Stefan Marquardt [mailto:bounce-Marquardt@community.progress.com]
    Sent: Thursday, October 16, 2014 10:58 AM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?
     
    Reply by Stefan Marquardt

    Hi Richard,

    I request a -checkwidth 3 :

    Store the data and increase the _width field if needed

    I am sure that somebody at PSC must have the same idea ...

    Stefan

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Thomas Mercer-Hursh
    Posted by
    on 16 Oct 2014 10:29

    Rich, I think he was suggesting it as an ABL parameter, i.e., self-maintaining on the ABL side so that it was ready to go on the SQL side when needed.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com
  • Richard Banville
    Posted by
    on 16 Oct 2014 10:38
    Sure but there are concurrency issues which must be taken into consideration when schema values are changing.  If _sqlwidth  was modified automatically by the ABL runtime, there is still no guarantee that a running SQL user would not fail due to a sql width issue unless a new concurrency protocol were implemented.
     
    _________________________________
    Richard Banville
    Fellow, OpenEdge Development

    PROGRESS SOFTWARE CORPORATION
    14 Oak Park | Bedford, MA 01730 | USA
    DIRECT  +1 781 280 4875
    richb@progress.com
     
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Thursday, October 16, 2014 11:30 AM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?
     
    Reply by Thomas Mercer-Hursh

    Rich, I think he was suggesting it as an ABL parameter, i.e., self-maintaining on the ABL side so that it was ready to go on the SQL side when needed.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Thomas Mercer-Hursh
    Posted by
    on 16 Oct 2014 10:54

    Still, given that one is simply updating a DB field, the chance of an ABL client doing the update and the SQL client having already started a query that will include the offending record would seem to be extraordinarily small, wouldn't it?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com
  • Libor Laubacher
    Posted by
    on 16 Oct 2014 10:58
    This is a “schema” update, not a particular record update.
    Or rather this would be. Hypothetically speaking.
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Thursday, October 16, 2014 5:55 PM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?
     
    Reply by Thomas Mercer-Hursh

    Still, given that one is simply updating a DB field, the chance of an ABL client doing the update and the SQL client having already started a query that will include the offending record would seem to be extraordinarily small, wouldn't it?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Richard Banville
    Posted by
    on 16 Oct 2014 11:07
    There are no guarantees.
     
    SQL: Select * from customer.
    ABL: create customer.  Assign name = fillstr(“x”, 1000).
     
    One should not make an assumption if this is common or not, only what the expected behavior would be.
    The fact is, even if it were rare, it is not protected. 
     
    As I said, an enhancement should be submitted and we will debate the implementation if and when it rises to the top of the list.
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Thursday, October 16, 2014 11:55 AM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?
     
    Reply by Thomas Mercer-Hursh

    Still, given that one is simply updating a DB field, the chance of an ABL client doing the update and the SQL client having already started a query that will include the offending record would seem to be extraordinarily small, wouldn't it?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • steve pittman
    Posted by
    on 16 Oct 2014 11:08

    sql has cached schema data, including the width of columns for tables that have been used by applications.
    Even if the ABL updated the sql width  of a column, the cached metadata would still contain the old sql width.
    As Rich explained, we would need new concurrency protocol support to enable sql to learn about a change in sql width.
    If there were rapid sql width changes, there would be performance implications.

    hope this helps,     .....steve pittman  [OE sql architect]



    On 10/16/2014 11:59 AM, Libor Laubacher wrote:
    Reply by Libor Laubacher
    This is a “schema” update, not a particular record update.
    Or rather this would be. Hypothetically speaking.
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Thursday, October 16, 2014 5:55 PM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?
     
    RE: 10.2B08: Option auto adjustment sql-width available?
    Reply by Thomas Mercer-Hursh

    Still, given that one is simply updating a DB field, the chance of an ABL client doing the update and the SQL client having already started a query that will include the offending record would seem to be extraordinarily small, wouldn't it?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.


  • cverbiest
    Posted by
    on 16 Oct 2014 11:22

    1. Maybe a stupid question : Are there drawbacks to setting _sql-width to 32768 on all character fields ?

    The real maximum is lower so you'd never had to update _sql-width again.