David Beavon (Software) asked a question.
There is a strange metadata filed called "SQL-WIDTH" in OE which I assume was introduced back in version 9.x for the sake of the SQL engine. I'm assuming it was added to allow better compatibility with the existing "DataDirect" code, so that it could be introduced into the Progress ecosystem (was it "Merant" back then?)
Ever since 9.x we've been fighting SQL-WIDTH . Because the Progress database itself does not restrict character widths, we have to deal with these artificial errors from the SQL engine after the fact.
Finally in 11.6 they tweaked the SQL side of things, to allow us to fix the invalid SQL-WIDTHs on the fly. The feature is called ASU (AUTONOMOUS SCHEMA UPDATE). I was under the impression that this was sort of a harmless, no-brainer approach that would pass all RDBMS data to the client, regardless of the length, while updating internal metadata in "SQL-WIDTH" (metadata that now becomes a moot concern for the user and DBA).
That seemed so straightforward, conceptually, that I didn't think much about it until I found this lengthy whitepaper :
https://www.progress.com/docs/default-source/openedge/autonomous_schema_update_whitepaper2.pdf
Much to my surprise, this stuff is not as straightforward as I thought it would be! Here is the overview.
- As ASU is dependent on truncated events generated by ADT, switching on ASU turns on ADT internally. By default, if the width of the column data being operated on in a query exceeds the defined width of the column, SQL returns an error, and as a result, the query fails. If ASU is enabled, SQL will instead truncate the large value down to the defined size and updates the offending column width to the highest in the result set. Due to column width update, subsequent execution of the query returns complete data instead of truncated data. Thus, ASU will permanently update the schema automatically without any user intervention and at the appropriate time.
Yikes. The way I read this, the burden shifts on the customer, and we have to judge the risks of truncated data. I will almost have to run every SQL query twice and compare results, just in case the first execution of the query returned wrong results.
I understand that ASU builds on ADT and that there is some context that needs to be taken into account to understand when/why we arrived at this point. But even so, the current behavior is a bit scary. There should be not be such a thing as the following approach in the ASU/ADT matrix:
"Truncated data for the first run Complete data from next run"
.. On principal I'd be much more comfortable if there was a failure on the first run and the next run gave me valid data. I don't see that option in the matrix. Nobody who cares about data integrity will want to hear that an RDBMS has certain scenarios where it happily sends you bad data. Am I overreacting here?
Hi @David Beavon (Software)
I faced the same issue ...
Tech Support validated the workaround and updated the article 000297219 about querying the "sysprogress"."syscolumns" before and after executing SQL queries To know if ADT occur.
https://community.progress.com/s/article/how-to-log-the-sql-width-adjustements-done-when-using-the-database-startup-parameters-sqlwidthupdate-on-sqltruncatetoolarge-output
Patrice
Thanks for that. Hopefully you shared strong words about the implementation. lol.
I think many people would rather that the first query would just fail, instead of generating bad data. It wouldn't be the only query failing in a given day. Nowadays there are lots of cloud-hosted databases that fail for "transient" reasons. At least this type of failure would only happen once or twice a year.
In the past we used to have to babysit the SQL-WIDTH, and that was not fun - at least not for anyone with OCD.
And now we have to worry about whether data is being truncated on us, which triggers my OCD even worse.
Thanks for the link. At least we can find out if I received bad data after-the-fact. (Wow it hurt to say it like that.)
"Because the Progress database itself does not restrict character widths, we have to deal with these artificial errors from the SQL engine after the fact."
We use the -checkwidth 2 startup parameter to keep the Progress Clients from overstepping, this since we also support the Oracle & SQL Server DataServers. You lose the "limitless" freedom of the character field, but you get interoperability in return.
Hi, I'm really impressed. I'm intriqued that folks would build an OE-ABL solution that targets multiple databases. Are there more than 10% of your customers running on Oracle or SQL Server?
Are the majority of the products at your company built using OE-ABL?
I remember when Progress eliminated DataServer for ODBC and I thought it was a bad omen for DataServer in general. Maybe the Progress customers that really want to use Oracle and SQL it are paying a premium to keep those versions of DataServer around. We have talked about using DataServer for SQL many times in the past but worry about both the transition and the long-term prospects of the Progress DataServer. Our long-time Progress consultants had kept telling us it was not advisable to make the switch to DataServer.
This is just how things evolve :-) Oracle and DB2/400 DataServers were driven by customer demand / opportunity. Since those two are pretty unforgiving, when the MSS DataServer was introduced it was little effort and that attracted some customers. I think around half our customers are using a DataServer, most are MSS, some are Oracle.
As a best of breed accounting application, not being picky about which database is used helps fit you into the existing landscape.
We stem from a small division within our company, the rest of the products are generally from Microsoft fan boys ;-)
To free those result set oriented databases from the OpenEdge row based straight jacket, we have a few optimizations in our core components that transform queries into (massive) SQL select statements, to fire a single query at the foreign database.
Thanks for the details.
I'm glad to hear that MSS DataServer is still so viable.
I guess the next question will be to find out if it is affordable as well. I'm guessing we would need to purchase by core, if possible. That would be consistent with the licenses that are used for hosting applications in PASOE .
We are about 50% Microsoft as well.