SQL Lengths

[GregTomkins]

I vaguely understand that whereas Progress fields are inherently variable length, in SQL, they're usually fixed length (unless you define them as VARCHAR, which is, I gather, uncommon).

I further believe that if the actual length of a field exceeds the "SQL length", bad things happen. To avoid this, PSC has a tool that will expand all the "SQL length" of every field to be x% larger than the largest data value.

My question is: is this the best we can do? What if down the road, a field is updated to include data that is x+1% larger than the previous maximum? Is there some startup parameter or whatever that will make things work with any data length?

 

[Rob Fitzpatrick]

The SQL width is the field _Field._Width. By default it is twice the ABL display format. It can be adjusted to fit your data, database-wide, by running the dbtool utility (option 2). This compares SQL widths to data values and changes _Width if necessary to help you avoid run-time errors in SQL clients.

You can also specify a padding factor for the increase. For example if a field record has _format of "x(20)" and _width of 40, and its longest field value has a length of 100, with a padding factor of 0, dbtool will set _width for that field to 100. With a padding factor of 10% it will set _width to 110.

This isn't a perfect solution. As you note Greg, I can run dbtool and a minute later someone can add a wide data value and a minute after that someone can query for it and still see an error message. Dbtool is a reactive solution. That said, if you use SQL clients it's a good idea to run dbtool periodically to minimize the possibility of such a problem. (And, off-topic, it's also a good idea to update SQL statistics periodically to help the query optimizer.)

Also, there is a new capability in 11.5.1 that gives you an option to avoid these length errors altogether. It is called "authorized data truncation". Basically you can tell the DB that any data in a result set that exceeds the defined length should just be truncated at the maximum when returned to the client. You can specify this on the SQL broker with the startup parameter "-SQLTruncateTooLarge on". You can also specify this on the level of an individual JDBC or ODBC session connection. See the 11.5.1 New Features doc (gssp1.pdf) for details.

 

[GregTomkins]

Wow. What a superb reply. Thank you very, very much.

Aside: it's just so crazy to me that SQL people think screwing around with field lengths is a good use of time. I like a great many things about SQL and Java, etc. over Progress, but this is one of two or three areas that Progress gets SO right.

 

[mollyfud]

For completeness, should also mention the -checkwidth option that can enforce the SQL-Width on the ABL side of things. Here is a knowledge article on it:
http://bit.ly/1KPJ5cn

Having read your comments above, it doesn't seem the fix for the issue that you are after, but someone else reading this might be interested in it.

HTH
Molly

 

[TheMadDBA]

I know this will brand me as a heretic but... I prefer the SQL style of actually enforcing the field limits. Even though the days of fixed length storage are long gone I prefer having the database enforce the limits, just like it should for any other limit/constraint.

 

[TomBascom]

A scarlet H upon your forehead!

I'd be ok with that IF the 4gl had a concept of an optional length constraint distinct from the suggested default display format.

In the absence of such an option the current behavior is much more to my liking.

 

[TheMadDBA]

I wear it with pride

Changing the 4GL behavior now would cause untold carnage. Getting it right the first time would have prevented quite a bit though

 

[TomBascom]

They did get it right the first time -- it's the rest of the world that is messed up

If such a constraint were added as an *optional* new feature nothing would break. They could even leverage the -checkwidth feature to implement it.

Similar, optional, constraints could be added for other data types -- how about integers that must be between 1 and 9? etc.

It could be sort of like "validation done right".

Not that I am holding my breath or even really having any desire for it

 

[TheMadDBA]

I guess we will have to agree to disagree on who got it right the first time

I am sure PSC will get around to fixing it right after they remove the lock table, add a cost based optimizer with hash joins, remove dirty reads and provide time/transaction consistent views of the DB.

 

[GregTomkins]

Ok, I'll bite. Doesn't everyone have a lock table? What's the alternative? Optional table level locking might be helpful? +1 for dirty reads, this seems so terrible... though in all honesty I have never seen an actual production problem caused by this.

 

[TomBascom]

Zing!

 

[TheMadDBA]

Well other DBs technically have concepts of lock tables but not in the same way as Progress. There is no real concept of lock table overflows or manually setting the lock table size. In Oracle most things are limited only by the amount of resources (memory and then disk) you have available. Oracle hides a lot of this from you by providing transaction based views of the database.

Most of the issues I have with dirty reads are caused by running reports/extracts while updates are happening. The busier the system the more often this happens... or you are forced to exclusive lock the records when reading them.

Don't get me wrong.. there are still a lot of things I like about Progress but the DB just doesn't compare very favorably to Oracle. Once you have done online major maintenance on a multi TB system with thousands of users active... and none of them noticed... it is kind of hard to go back.

 

[GregTomkins]

Not sure what you mean by 'major maintenance' but for us it is certainly true that a lot of terrible, terrible things derive from the hassle of doing schema changes in Progress. If you could change schema the same way you can edit a .p, we would have avoided many atrocities. I know it's better than it used to be, but editing schema is still a 'wait until night time when nobody else is working' sort of an affair.

... and optional named parameters

 

[TheMadDBA]

Imagine doing almost anything you want online without kicking out users or preventing access to the underlying tables....

Adding/dropping/changing columns and tables (you might need to deploy new code first to remove column references if you are dropping)
Adding/rebuilding/dropping indexes (no recompiling of code)
Moving tables between tablespaces
Changing partition/subpartition definitions

For a lot of operations like index adds/rebuilds or table/index moves you can chose how aggressive the operation is (get it done fast or go slow and minimize IO). Not to mention that they are all orders of magnitude faster than the Progress versions.

 

[GregTomkins]

Getting back to the Lengths thing, I have a follow up question.

Does anybody know what would be the implications of setting _width on every field to 32k (in our case, we don't have any LONGCHAR, etc. to worry about). This *sounds* like the worst idea ever, like every single field would get padded out to 32k or something, but I don't know for sure; does anyone?

 

[TheMadDBA]

I don't believe that the data will actually be padded across the wire (certainly not in the DB)... but it is highly likely that you will run into some limits on the SQL engine and/or the SQL clients. Depending on the SQL client you use it might actually reserve 32k for each character field in the query. Better implementations don't but there aren't a lot of those out there.

If you decide to bump the widths up you would want to look at all of the advanced options on the ODBC driver and tweak the various fetch/memory settings.

 

[GregTomkins]

Thank you again.

And yeah ... 'Adding/dropping/changing columns and tables, Adding/rebuilding/dropping indexes (no recompiling of code)', that would be dreamy for sure. The problem isn't so much that doing these things is tedious or expensive directly, but it leads to people doing all kinds of crazy overloading of fields and tables.

I guess you still have issues of keeping track of schema versions, though (unless you have some good tools and/or are blessed with only one production installation to support).

 

[TheMadDBA]

Yeah... adding an index to a table with 400 million rows without kicking anybody out AND having the next query be eligible to use the new index (with no code changes) was a real eye opener for me.

For most Progress shops offline changes are manageable but when you get closer to 24-7 and/or when DB maint starts taking a 12+ hours (or days) it kind of sucks.

Oracle/MS SQL/DB2 still have the same issues of keeping multiple installs in sync. But you have all the same comparison tools that generate incremental diffs... plus being able to do them online makes life a lot simpler.