OpenEdge Databases & SQL
Thinking about enabling SQL access to your OpenEdge database? Read this.
This post is part of the “New DBA Series.” Check wss.com/blog for more posts in the series and other interesting DBA blog posts.
The OpenEdge database supports two distinctly different query languages: the OpenEdge ABL (4GL) and SQL-92. Both interact with the OpenEdge database differently, and while both access the same table and index data, there is very little overlap in their behaviour.
Below are the top four differences between SQL and the ABL.
The most glaring difference between the two query languages is how they optimize queries. The ABL is a rules-based optimizer, meaning that it analyzes the fields in the various indexes when evaluating a where clause and selecting one or more indexes to access the data. SQL is a cost-based optimizer, meaning that it uses table and index statistics to select the most optimal path to the requested data.
To put it another way: the ABL will always select the same indexes regardless of the content of the table. You can compile against an empty table or a 100 GB table and the same index will be selected. SQL, on the other hand, relies on statistics that are calculated based on the data stored in the table.
4GL trigger procedures are executed by the OpenEdge AVM, and are completely ignored by SQL connections. Watch out, especially if you start modifying data from the SQL side.
Another difference is that with the OpenEdge ABL, all character data is variable width. A field format is a default display suggestion used when the coder provides no other input or display format – it is not a constraint. Example: a character field defined as “x(30)” could easily be “overstuffed” with a string 2,000 characters long. This is normal and very common practice within 4GL applications. It is not an error. However, when you access that same field using the SQL engine, the SQL client relies on _field._width (the SQL width) to determine the width of the character field.
SQL and ABL approach security from completely opposite directions. The OpenEdge side assumes that security is handled by the application, and all table data is PUBLIC by default. You must specifically revoke access privileges. SQL is locked down by default except for the owner of the data, and you must specifically GRANT privileges to other users.
You can access the OpenEdge SQL engine with any language or tool that supports ODBC or JDBC. All you need to do is download the SQL Access files, free to any licensed OpenEdge user. And in case you didn’t know, Progress Software Corporation is the owner of DataDirect, the #1 developer of ODBC/JDBC drivers in the world.
All Progress installations provide sqlexp[.exe], a simple character interface that connects to the OpenEdge database via JDBC.
$DLC/bin/sqlexp -user <userName> -password <passWord> -db <dbName> -S <servicePort>
Sqlexp is especially useful for running batch SQL commands via the -infile and -outfile command line parameters.
Another popular SQL client is SQuirrel SQL, available at http://squirrel-sql.sourceforge.net/. Squirrel also uses JDBC to access the OpenEdge and pretty much every other SQL database out there.
The Progress Developer Studio for OpenEdge, an Eclipse (java) based tool, has a built in DB Navigator which also connects to the OpenEdge database via JDBC.
Key DBA Tasks When Using SQL
You must specifically GRANT select, update, etc. permissions to SQL users. For example:
GRANT SELECT on pub.customer to paul;
CAREFUL: Don’t forget to dump and load SQL GRANTs when doing a dump & load of an OpenEdge database!
As mentioned above, the SQL engine is a cost based optimizer, so table statistics should be calculated periodically to provide accurate data to the query optimizer. Check out the Update Statistics page in the ProTop documentation to see how to easily generate the update statistics commands.
WARNING: If you’re still running 10.2B prior to SP8, there was a bug with column statistics.
When a SQL client queries data, it expects the length of each field not to exceed the defined length, otherwise an error “Character string is too long (8184)” is generated. DBTool is a simple utility that, among other things, scans field lengths and adjusts the SQL-WIDTH accordingly.
ASU and ADT
As of version 11.5.1, two new startup parameters are available:
-SQLTruncateTooLarge [ON|OFF|OUTPUT], aka “Automatic Data Truncation” or “ADT”, will truncate data from the OpenEdge database if it is larger than the SQL-WIDTH.
WARNING: If you set -SQLTruncateTooLarge to ON, the truncation will also occur in the context of the where clause. For example, if the city field contains “Montreal” but the SQL-WIDTH of the city field is 5, the following query will not return any data:
select * from address where city = 'Montreal'.
The effective query will be “…where city = ‘Montr'”.
If the value is set to OUTPUT, the query will succeed, but the SQL client will receive ‘Montr’ as the value of the city.
-SQLWidthUpdate [ON|OFF] parameter, aka “Autonomous Schema Update” or “ASU”, will automatically update the SQL-WIDTH when ADT is enabled. The first time the problematic data is accessed, truncated data will be returned to the client and the schema will be adjusted to accommodate the new length.
Note that ASU and ADT only work on VARCHAR (character) data fields.
Watch Out !!
There are a couple of gotchas that you have to think about when enabling SQL access, especially on a production database:
- Consider forbidding user access to the data in an unstructured way, via Excel or MS Access or similar. We see two common problems here: users end up writing insane queries that cause gajillions of reads, affecting all users; and they mostly don’t understand the table relationships, causing them to interpret the data incorrectly.
- Make sure to put the SQL connections on a separate login broker, and set each login broker exclusively for 4GL or SQL connections by using the -ServerType [ 4GL | SQL ] startup parameter.
SQL and OpenEdge have been playing nicely together for decades, so if you need SQL access, don’t hesitate to use it. But do watch out for some of these pitfalls. We see them [and fix them] all the time at OpenEdge sites all around the world!