New SEQUEL 10: A more detailed look

In April, SEQUEL Software released SEQUEL 10. This new release offers extended SQL statements and run-time SQL translation for remote database access using SEQUEL’s simpler syntax, rich functionality, and querying capabilities. Let’s look at these features in detail.

View size expanded to 20,000 characters
SEQUEL 10 extends the maximum length for an SQL statement in a SEQUEL view from 5,000 to 20,000 characters. With this larger limit, you can create longer and more complex views. This size increase helps you work with database-specific SQL in which named expressions are not allowed.

For example, you may be familiar with creating a derived field or calculation in a view, and then using this new field in another calculation simply by referencing the name you gave the new field. If you are working with the SQL used by Oracle, this isn’t allowed. You have to repeat the first calculation any time you want to use it. Such views become very large, very fast.

Another kind of view that can grow quickly is a UNION view. A UNION view allows you to “merge” data from files that don’t relate or can’t be joined. A single UNION view can contain multiple SELECT-FROM statements and, depending on the number of fields, files, and UNIONs required, these views often exceeded the old 5,000 character limit. In the past, you would have had to build multiple views and send the results to a temporary file, adding data from each view. Then, you created a script to tie the steps together. Now, with the new size, you can create most requests in a single view.

SEQUEL 10 lets you see your SQL character count as you design your views. Select File > Properties on the Design menu to open the View Properties window. On the SQL tab, you can see the count in the lower-left corner (shown below).

Design once—translate to many
The second big SEQUEL 10 enhancement is the ability to translate SEQUEL SQL syntax into standard SQL syntax based on a chosen server. This run-time translation allows you to run your SEQUEL request locally, using the new, faster IBM SQL Query Engine (SQE), or against remote databases such as SQL Server, Oracle, and MySQL using SQL syntax native to these databases. You can leverage your existing knowledge of SEQUEL SQL to access data across your enterprise.

SEQUEL SQL is a variation of standard SQL that runs using the IBM Classic Query Engine (CQE) for DB2 databases on the System i. The CQE includes extensions that allow access to OS-specific features like multi-member and multi-format files. SEQUEL takes advantage of these features and provides several enhancements, such as the JOIN statement, the CVTDATE statement, and column headings. SEQUEL 10 lets you use all of this with the newer SQE so that SEQUEL objects can take advantage of faster query processing speed.

And, you can run SEQUEL statements against other remote databases, such as SQL Server, Oracle, and MySQL. This ability to design views with SEQUEL SQL and translate them "on the fly" to native SQL on the System i, or to server-specific SQL on a remote database, opens up a world of data access opportunity.

Is it magic?
It’s not magic, but it works like magic. You have complete control of how to translate the SQL against a given database through two execution parameters, SERVER and SYNTAX.

With the SERVER parameter, you specify the system (local System i or remote database) where your view should be executed. (The SERVER parameter has been part of SEQUEL and ViewPoint since support for remote databases was added.) SEQUEL 10 adds a new value, *LOCALSYS, to this parameter.

*LOCALSYS and *LOCAL both use the newer SQE, but with an important distinction: *LOCAL uses *SQL naming where qualified names are entered as libname.filename. Unqualified files must be in the library with the same name as the user running the query. *LOCALSYS uses *SYS naming. This means that file names are entered as library/file instead of libname.filename. So, *SYS naming locates unqualified files using the job’s library list.

You can see these values in the view’s properties (see the illustration below). Use File > Properties on the View Design menu to open the View Properties window. On the View tab, you can see the available Database (SERVER) values.

The SYNTAX parameter is new for SEQUEL 10. You use it to specify the SQL in the view. It’s available for any command that works with an SQL statement (such as CRTVIEW, DISPLAY, DSNREPORT, and others).

• If you specify SYNTAX(*SEQUEL), you indicate that the SQL statement is written using extended SEQUEL syntax. SEQUEL converts from *SEQUEL to native SQL as needed.
• If you specify SYNTAX(*SERVER), you indicate that the SQL statement is written in the syntax of the specified database—SEQUEL, MySQL, SQLServer, Oracle, and so forth—on the SERVER parameter. There is no conversion from *SEQUEL to native SQL.

How do these to parameters relate?
The table below gives you an idea of the range of control these parameters provide. It pulls together the values discussed above to help you better understand the relationship between the SERVER parameter and the SYNTAX parameter.
 

What does the new Translation feature mean to you?
If you want to continue to use ViewPoint with System i databases, but want to use the faster SQE, select SERVER(*LOCALSYS) and SYNTAX(*SEQUEL) for your view and SEQUEL does the rest. You can even set these two values as the default for all your new views!

If you want to retrieve data from a remote database, such as Oracle, you don’t have to learn a new language. You design your view using the SEQUEL SQL syntax and functions you know and SEQUEL does the rest. Just make sure that the view properties specify SERVER(server-name) and SYNTAX(*SEQUEL). SEQUEL will translate the SEQUEL SQL into Oracle SQL. It’s that easy.

Visit the downloads page of our Web site at www.sequel-software.com/support/support-downloads and download SEQUEL 10 today!

Contributed by Steven Smith, Technical Consultant