WebFOCUS Newsletter February 2005 - Having Cake and Eating It Too: Using SQL With WebFOCUS

Let's face it; most data today is not stored in FOCUS databases. Relational database management systems (known as RDBMS) are very popular and their mother tongue is SQL. Naturally, database administrators and technicians usually are quite comfortable with the SQL language and some of them even swear by it.

Sure, the SQL language is very efficient at extracting and manipulating data from relational databases, but it simply does not hold a candle to WebFOCUS for user-friendly output formatting.

In a perfect computing universe, you would use the efficiency of SQL on relational databases combined with the beautiful reports and graphs produced by WebFOCUS.

The question is, Is this possible? The truth is Information Builders can provide the perfect environment for combining SQL and WebFOCUS. The iWay Reporting Server offers many relational adapters that can either translate WebFOCUS code to SQL or pass SQL code directly to the RDBMS.

When running a WebFOCUS procedure against a RDBMS, the SQL data adapter will translate as much of the WebFOCUS code as possible into SQL and pass it on to the RDBMS for processing. After this, the resulting answer set will be returned for WebFOCUS to finish the job.

In some cases the entire request can be translated easily and WebFOCUS only applies formatting to the answer set. In other cases, such as complex DEFINE-based JOINs and WHEREs, the code won't be completely translated and the answer sets will be much larger than needed. This could severely degrade performance.

RDBMS are typically very efficient at joining, sorting and selecting their own records. It is unfortunate when you cannot take advantage of these strong features. Usually, you can make a simple modification to the original code to greatly optimize a request. For example, a selection based on a defined field will often be more efficient in SQL if you directly test the expression used in defining the field.

Passing SQL Code Directly

Sometimes you just can't make a change in the code. In these cases, executing SQL code directly might be the most efficient thing to do. The WebFOCUS Reporting Server can pass SQL code directly to the RDBMS. This technique is called Direct SQL passthru. The Data Adapter needs to know the RDBMS engine to which it will pass the code and its connection information.

You can include this information in the FOCEXEC or it may already exist in your edasprof.prf configuration file. The answer set is returned in virtual memory and is named SQLOUT. The FOCEXEC will contain the SQL code directly followed by a "TABLE FILE SQLOUT" and the WebFOCUS commands for subsequent processing, as illustrated in the following example:

SET SQLENGINE=SQLMSS
 
SQL SELECT  Plant, Lineprice
FROM ORDNORTH
UNION ALL
SELECT  Plant, Lineprice
FROM ORDSOUTH
;  
 
TABLE FILE SQLOUT
SUM Lineprice
BY Plant
COLUMN-TOTAL
END

This example uses the SQL command UNION ALL, which is equivalent to a MATCH FILE with an OLD-OR-NEW hold file. This processing is much faster when performed by the RDBMS because WebFOCUS does not yet translate the MATCH command into SQL.

In future releases this type of application will become easier to do. The next release of WebFOCUS has a SQL Wizard to assist you in coding Direct SQL passthru for some of the more popular SQL RDBMS. We also will offer a SQL Editor with color-coding. These new features will be discussed in future issues of the WebFOCUS Newsletter.