10.2B08: Option auto adjustment sql-width available?

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.

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

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.

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

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

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.

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?

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]

RE: 10.2B08: Option auto adjustment sql-width available?

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.

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.

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.

RE: 10.2B08: Option auto adjustment sql-width available?

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.

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

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.

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

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.

RE: 10.2B08: Option auto adjustment sql-width available?

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.

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.

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.

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)

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

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.

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

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