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

10.2B08: Option auto adjustment sql-width available?

 Forum

10.2B08: Option auto adjustment sql-width available?

This question is answered

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
  • 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
  • Another approach that works much better in my opinion... use the OpenEdge MS SQL dataserver to push the data from the 4GL into MS SQL. You end up writing a lot less code for replication and you get to skip all of the width issues.

    Basically you have a 4GL client that is connected to your Progress DB and the MS SQL database at the same time.


  • On 10/16/2014 12:23 PM, cverbiest wrote:
    Reply by cverbiest

    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.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    Yes, there are drawbacks! This has been tried and has very significant performance impact.
    Also, applications like Crystal make fomat and display decisions based on these sizes.

    The server does buffer allocation based on defined sql widths, and allocates buffers for the number of output rows per network msg (default = 50).
    The numbers multiple out to big values,   giving very large data buffers, mostly unused.
    So,  it leads to a large memory footprint, more paging, less locality of reference, etc.

    So, it might work OK with just a very few fields, but is quite problem prone.

    ....steve pittman [OE sql architect]
  • Well, it is a field in a schema table, but it doesn't impact the schema for ABL.

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

  • Rich, the point is, though, that without the requested option, one can already have a query start in SQL using the current width and have an ABL client update longer than than width and then the client fails.  I am sure that sites who run dbtool every night occasionally encounter a query that fails because a violating update has been made since the last run.  With the requested auto update, the chance would be **much** lower because the ABL update would have to happen during the SQL session.

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

  • Believe me, I completely understand the implications of the current mechanism and the various suggested implementations.
     
    If this feature is important to you, enter an enhancement request.
     
     
     
    From: Thomas Mercer-Hursh [mailto:bounce-tamhas@community.progress.com]
    Sent: Thursday, October 16, 2014 1:34 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

    Rich, the point is, though, that without the requested option, one can already have a query start in SQL using the current width and have an ABL client update longer than than width and then the client fails.  I am sure that sites who run dbtool every night occasionally encounter a query that fails because a violating update has been made since the last run.  With the requested auto update, the chance would be **much** lower because the ABL update would have to happen during the SQL session.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • Ok, so to summarise, automatically increasing the SQL width as soon as a record is created will make pre-existing SQL processes crash. Someone may have to restart the process, which will now run fine. But still, we had the minor inconvenience of a crash...

    That does not sound much better than the status quo? Or does it?

    For me the status quo is this: Once a longer value enter the database, ALL SQL processes, new and old, will crash. They will be restarted automatically and recreashed. Eventually the top brass will notice that the dashboard did not change for 10 minutes, because these SQL processes, where ever I encounter them in the market, are used to populate data warehouses. So the guy at the top jumps on the dashboard people. They investigate why the auto start script is not working and find the problem in the log. First, the blame OpenEdge and the OpenEdge team. Then they call the OpenEdge support team and complain. The Pointy Haired Boss is still screaming. So now the OpenEdge team runs a db maintenance utility that scans the ENTIRE TABLE to locate the offending record, flushing all the buffers, messing around with the disks and slows down all performance. So now the OpenEdge takes the blame for the dead dashboard AND the slowdown in operations. And then everything is fine for a few days and the cycle repeats.

    I would vote for the (often unnoticed) once-off SQL crash ASAP, but I will not waste a vote on improving concurrency. That can be fixed in v. 103.3 circa 2081 for all I care....

    Simon L Prinsloo

    www.vidisolve.com

  • Simon, let me supplement your description by noting that, in order to have a crash with the proposed mechanism in place, one has to have started a SQL session which has accessed the schema for the table in question, put it in its cache, then the ABL session has to update the record with an offending value *and* that SQL session has to access that one specific record.  Start the session after the update and it will get the right width.  Access other records and it won't crash.  Fetch the records before the update, no crash.

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

  • O.k, what about this?

    If the sql driver noticed during the request that the length of a record field is too long it can request the increasing to the needed value and rerun it again, invisible for the users - it will only need the same requst time again.

    No crash - no concurrency protocol needed.

    Or PSC propose a better solution to prevent SQL width errors?

    OT: And please fix the error when runing sql queries on tables without an index - it's not possible in 10.2b08

  • Stefan, that sounds difficult.  For starters, in many cases the SQL connection is going to be read-only ... but you want it to write.  Moreover, it really needs to fail and completely restart in order to get a fresh copy of the schema.  That is a lot of spinning wheels.  I think fixing it on the fly in the ABL is a much sounder idea.

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

  • Ø  OT: And please fix the error when runing sql queries on tables without an index - it's not possible in 10.2b08

    Stefan,
     
    Unless you want to hear that either I just did that and the query runs or simply that running query on a table without an index makes no sense (hence create one), please start a new thread for the above and provide some details. Also please note that 10.2B08 was the last service pack for 10.2B.
     
    Thanks.

  • On 10/16/2014 2:43 PM, Thomas Mercer-Hursh wrote:
    Reply by Thomas Mercer-Hursh

    Stefan, that sounds difficult.  For starters, in many cases the SQL connection is going to be read-only ... but you want it to write.  Moreover, it really needs to fail and completely restart in order to get a fresh copy of the schema.  That is a lot of spinning wheels.  I think fixing it on the fly in the ABL is a much sounder idea.

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.


    Many thanks to everyone for the creative ideas, interesting discussion, and for raising interest  in this problem.
    For  a variety of reasons, including feedback at Exchange and here, this problem is being looked and remedies considered.
    As Rich said, it is  helpful is you file an enhancement request - that does factor into figuring out the priority of the candidate ideas for any release.

    thanks,           .....steve pittman
  • By far, test best idea is for MS, Oracle and company to stop making
    excuses for their inadequacies and fix SQL.

    They have bad implementations. The onus should be on them to fix it.
    They've had 30 years. Time to get off their butts.

    --
    Tom Bascom
    tom@wss.com

  • Hi Libor,

    "please start a new thread for the above and provide some details"

    There is already a  KB availabe for the error which describes that no sql query is possible without any index.

    Some of the current application tables have no indexes, please don't ask me why ,that are very small tables where a "full table scan" isn't a problem.

  • "By far, test best idea is for MS, Oracle and company to stop making

    excuses for their inadequacies and fix SQL."

    Then you have to fix the odbc drivers too because they reserve space in memory for the field widths.

    (you will see when you enable odbc tracing)

    I struggled > 2 years with PSC and MS to get a working ODBC and .NET access.

    Both said very often that it's not their error - i was in the middle - and both had errors which were fixed in the ODBC driver or in RDBMS side or in the .NET framework. It was very funny when MS Germany requested an OpenEdge version ...

    Libor should know about my endless error reports, meanwhile it's stable but still has problems.

  • On 10/16/14, 4:50 PM, Stefan Marquardt wrote:
    Then you have to fix the odbc drivers too because they reserve space in memory for the field widths.

    (you will see when you enable odbc tracing)


    They only do that because the SQL guys have been so lazy for so long...

    Anyhow from a practical perspective the only thing that you can do *today* is to run dbtool or add -checkwidth.

    If you're going to file enhancement requests I just hope that you do so with the SQL providers too.  Because it is really all their fault -- Progress has the much more reasonable approach to data.

    While you're at it you could also ask them to stop being case sensitive by default.

    --
    Tom Bascom
    tom@wss.com