10.2B08: Option auto adjustment sql-width available?

Posted by Stefan Marquardt on 16-Oct-2014 02: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.

Posted by Chandra Sekhar on 16-Oct-2014 03:26

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.
 
[collapse]
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.

[/collapse]

All Replies

Posted by Chandra Sekhar on 16-Oct-2014 03:26

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.
 
[collapse]
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.

[/collapse]

Posted by Stefan Marquardt on 16-Oct-2014 03: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.

Posted by ChUIMonster on 16-Oct-2014 05: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

Posted by Stefan Marquardt on 16-Oct-2014 05: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.

Posted by Richard Banville on 16-Oct-2014 07: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.
 
 
[collapse]
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.

[/collapse]

Posted by Richard Banville on 16-Oct-2014 07: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

Posted by Stefan Marquardt on 16-Oct-2014 09: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

Posted by Richard Banville 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
 
 
[collapse]
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.

[/collapse]

Posted by Thomas Mercer-Hursh 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.

Posted by Richard Banville 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
 
 
[collapse]
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.

[/collapse]

Posted by Thomas Mercer-Hursh 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?

Posted by Libor Laubacher on 16-Oct-2014 10:58

This is a “schema” update, not a particular record update.
Or rather this would be. Hypothetically speaking.
 
[collapse]
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.

[/collapse]

Posted by Richard Banville 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.
 
[collapse]
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.

[/collapse]

Posted by steve pittman 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]



[collapse]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.
 
[collapse]
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.


[/collapse][/collapse]

Posted by cverbiest 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.

Posted by TheMadDBA on 16-Oct-2014 11:35

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.

Posted by steve pittman on 16-Oct-2014 11:58


[collapse]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]
[/collapse]

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

Well, it is a field in a schema table, but it doesn't impact the schema for ABL.

Posted by Thomas Mercer-Hursh on 16-Oct-2014 12:33

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.

Posted by Richard Banville on 16-Oct-2014 12:37

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.
 
 
 
[collapse]
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.

[/collapse]

Posted by Simon L. Prinsloo on 16-Oct-2014 12:45

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

Posted by Thomas Mercer-Hursh on 16-Oct-2014 12:57

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.

Posted by Stefan Marquardt on 16-Oct-2014 13:35

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

Posted by Thomas Mercer-Hursh on 16-Oct-2014 13:43

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.

Posted by Libor Laubacher on 16-Oct-2014 14:17

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

Posted by steve pittman on 16-Oct-2014 14:46


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

Posted by ChUIMonster on 16-Oct-2014 15:12

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.

Posted by Stefan Marquardt on 16-Oct-2014 15:41

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.

Posted by Stefan Marquardt on 16-Oct-2014 15:50

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

Posted by ChUIMonster on 16-Oct-2014 16:22

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.

Posted by Simon L. Prinsloo on 17-Oct-2014 00:12

[quote user="ChUIMonster"]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.

[/quote]

That is true, but in practice the OpenEdge team is blamed EVERY TIME the integration falls over, because these vendors succeeded for 30 years to convince their followers not only that it is OK to have these restrictions, but that it MUST be like that.

Although none of the SQL users can tell you WHY is must be like that, they will always tell the powers that be that Progress is "violating the standard" when it saves all the user's data, rather than throwing away some of it. Even sensible managers can Google that and see that the SQL implementations all have this limitation. For some reason, nobody but us ever ask: But why? A futile question, really, because they will tell you: That is the standard. Still, a standard is only useful if it serves a purpose, but if you ask for the purpose the dodge the question or ramble non-sense until everybody is confused. And then everybody conclude that since OpenEdge keeps all their data and other systems do not, OpenEdge is the inferior product.

Posted by Simon L. Prinsloo on 17-Oct-2014 00:15

Another alternative to increasing the WIDTH may be to get the SQL guys what they expect, truncated data. Let the RDBMS truncate the data for the SQL requests at the SQL-WIDTH.

Posted by Mike Fechner on 17-Oct-2014 00:18

Simon, that would basically produce data corruption during update and likely unique key conflicts. No good way, I believe.

Von meinem Windows Phone gesendet

Von: Simon L. Prinsloo
Gesendet: ‎17.‎10.‎2014 07:16
An: TU.OE.RDBMS@community.progress.com
Betreff: RE: [Technical Users - OE RDBMS] 10.2B08: Option auto adjustment sql-width available?

Reply by Simon L. Prinsloo

Another alternative to increasing the WIDTH may be to get the SQL guys what they expect, truncated data. Let the RDBMS truncate the data for the SQL requests at the SQL-WIDTH.

Stop receiving emails on this subject.

Flag this post as spam/abuse.

Posted by Simon L. Prinsloo on 17-Oct-2014 00:32

Oh! Yes, did not think about that. But then again, I've never encountered anybody actually updating through the SQL, most likely because we never allow that.

Posted by Abri Venter on 17-Oct-2014 02:00

Hallo

For some reason my post on this matter went onto another thread.

There is also the OpenAccess driver where you could write ABL code to only expose the data in a way that SQL can handle it.

Thank you

A Venter

Posted by Stefan Marquardt on 17-Oct-2014 07:58

... and i answered in the other thread too, no idea how Mike created the new thread.

I compared the time of using "export" or create it dynamically with abl to create a correct format for sql bcp load.

ABL export method needs currenty for a full dump 30 minutes, the same with ABL code to create the string with some small checks >5 hours.

The string functions in ABL seems to be very slow, in C# there is a stringbuilder with a good performance.

So wie decided to use the "fast" ABL export command (with temp .p files dynamically generated for every table) and fast string conversion function in C# with many threads which use 100% of every CPU.

This thread is closed