All SQL Adapters
This section provides detailed descriptions of new features for all SQL adapters.
Optimization of Simplified Numeric Functions
The following new simplified numeric functions are optimized by the SQL adapters:
- CEILING. Returns the smallest integer value greater than or equal to a value.
- EXPONENT. Raises the constant e to a power.
- FLOOR. Returns the largest integer less than or equal to a value.
- MOD. Calculates the remainder from a division.
- POWER. Raises a value to a power.
Optimization of Simplified Functions REPLACE, TOKEN, and POSITION
The following simplified character functions are optimized by the SQL adapters.
- REPLACE. Replaces all instances of a string.
- TOKEN. Extracts a token (substring) from a string.
- POSITION. Returns the first position of a substring.
Enhancement to the Optimization of LIKE for Fixed Length Fields
In prior releases, the LIKE operator was not optimized for those RDBMS engines (such as Db2) that count trailing blanks when comparing columns because the FOCUS LIKE operator is not sensitive to trailing blanks. Now, LIKE is optimized by removing trailing blanks in the SQL passed to those RDBMS engines using the RTRIM function. (RTRIM(column) LIKE mask).
LIKE optimization works as long as the mask does not end with an underscore character (_). Optimization does work when the mask starts or ends with a percent sign (%).
Example: Optimizing the LIKE Operator for Fixed Length Fields
The following request creates a fixed-length field named CATEGORY and uses it in a WHERE test with the LIKE operator.
SET TRACEUSER=ON SET TRACEON=STMTRACE//CLIENT SET TRACESTAMP=OFF DEFINE FILE WFLITE CATEGORY/A10 = PRODUCT_SUBCATEG; END TABLE FILE WFLITE SUM COGS_US BY CATEGORY WHERE CATEGORY LIKE '%phone' ON TABLE SET PAGE NOLEAD END
The following SQL is generated, which trims trailing blanks from the CATEGORY column.
AGGREGATION DONE ... SELECT T7."PRODUCT_SUBCATEG", SUM(T1."COGS_US") FROM D999OL29_wf_retail_sales T1, D999OL29_wf_retail_product T7 WHERE (T7."ID_PRODUCT" = T1."ID_PRODUCT") AND (TRIM(TRAILING ' ' FROM T7."PRODUCT_SUBCATEG") LIKE '%phone') GROUP BY T7."PRODUCT_SUBCATEG" ORDER BY T7."PRODUCT_SUBCATEG" FOR FETCH ONLY;
The output is shown in the following image.
PERSISTENCE Option for HOLD FORMAT sqlengine
The PERSISTENCE option has been added to the HOLD command when it is used to create an SQL table.
This new command option allows you to generate intermediate tables of different types that will be used only during UPLOAD and EBL requests to accelerate performance by keeping all processing on the DBMS server instead of downloading data into a HOLD file. The actual type of the intermediate table will be determined at run time, based on specific DBMS-supported features and the data-populating mechanisms being used.
The syntax is:
HOLD FORMAT sqlengine PERSISTENCE {STAGE|PERMANENT}where:
- sqlengine
-
Identifies the relational DBMS in which to create the table.
- STAGE
-
Will create either a Volatile or GLOBAL TEMPORARY table, for a DBMS that supports that functionality, currently HP Vertica, Db2, Oracle, Teradata, MS SQL, and MySQL. For a DBMS that does not support that functionality, a message will display and the table will not be created.
- PERMANENT
- Will create a regular SQL table with a uniquely-generated name that will be used in the request and will be available for further use after the request ends, but will be dropped at the end of the session. This is the default value for PERSISTENCE for HOLD FORMAT sqlengine.
Enhanced BY Clause Optimization
The relational adapters now optimize reports that contain a BY field more efficiently, by no longer passing MAX(field) in the SELECT list to the RDBMS.
Enhanced Messages About SQL Optimization
More descriptive messages have been developed when SQL Optimization is not done for a request. In some cases, a message will contain information about ways to possibly change the syntax so that optimization can be used.
Optimization of Function DTRUNC for First Day of Week
Calls to the DTRUNC function can be optimized when using the new WEEK parameter that returns the first day of the week in which the date occurs.
Optimization of Function DTRUNC for YEAR_END, QUARTER_END, MONTH_END and WEEK_END
Calls to the DTRUNC function can be optimized when using the new YEAR_END, QUARTER_END, MONTH_END and WEEK_END parameters that return the last day of the period in which the date occurs.
CONCAT Function Optimization
Simplified character function CONCAT is passed to the Relational Adapters in the generated SQL.
Optimization of the DT_CURRENT_DATE, DT_CURRENT_DATETIME, and DT_CURRENT_TIME Functions
The simplified functions DT_CURRENT_DATE, DT_CURRENT_DATETIME, and DT_CURRENT_TIME are passed to the Relational Adapters in the generated SQL request.
Example: Optimizing the DT_CURRENT_DATETIME Function
The following request calls the DT_CURRENT_DATETIME function.
SET TRACEUSER=ON SET TRACEON=STMTRACE//CLIENT SET TRACESTAMP=OFF DEFINE FILE WFLITE CURRDT/HYYMDS = DT_CURRENT_DATETIME(SECOND); END TABLE FILE WFLITE SUM QUANTITY_SOLD CURRDT BY PRODUCT_CATEGORY ON TABLE SET PAGE NOLEAD END
The following SQL is generated for Microsoft SQL Server.
SELECT T7."PRODUCT_CATEGORY", SUM(T1."QUANTITY_SOLD"), MAX(SYSDATETIME()) FROM ( wrd_wf_retail_sales T1 LEFT OUTER JOIN wrd_wf_retail_product T7 ON T7."ID_PRODUCT" = T1."ID_PRODUCT" ) GROUP BY T7."PRODUCT_CATEGORY" ORDER BY T7."PRODUCT_CATEGORY";
The output is shown in the following image.
Enhanced Optimization of Selection Tests
In prior releases, all optimized selection tests were passed to the RDBMS and also re-evaluated when the answer set was returned to FOCUS. Starting in this release, individual tests passed to the SQL engines may be excluded from execution. This enhances performance and also allows tests to be optimized that are not totally compatible with how FOCUS evaluates them.
Optimization of the PARTITION_REF Function
Calls to the PARTITION_REF function in an aggregation request with a WHERE TOTAL phrase can be passed to SQL engines that have a LAG function, such as Db2, Oracle, Teradata, and Microsoft SQL Server.
Example: Optimizing the PARTITION_REF Function
The following request using the Adapter for Oracle calls the PARITION_REF function, which displays values from prior rows.
TABLE FILE WFLITE SUM COGS_US COMPUTE COGS1 MISSING ON = PARTITION_REF(COGS_US,TABLE, -1); BY PRODUCT_CATEGORY WHERE TOTAL COGS_US GT 421; ON TABLE SET PAGE NOPAGE END
The following SQL is generated that calls the Oracle LAG function.
SELECT
"SK001_PRODUCT_CATEGORY",
"VB001_SUM_COGS_US",
"LAG002"
FROM (
SELECT
T7."PRODUCT_CATEGORY" AS "SK001_PRODUCT_CATEGORY",
SUM(T1."COGS_US") AS "VB001_SUM_COGS_US", LAG( SUM(T1."COGS_US"), 1) OVER( ORDER BY
T7."PRODUCT_CATEGORY") AS "LAG002"
FROM
( D99964R6_sales T1
LEFT OUTER JOIN
D99964R6_product T7
ON T7."ID_PRODUCT" = T1."ID_PRODUCT" )
GROUP BY
T7."PRODUCT_CATEGORY"
) X
WHERE ("VB001_SUM_COGS_US" > 421); The output is shown in the following image.
New Optimization Controls Using SET FEATOPT
|
How to: |
The adapter SET FEATOPT command can be used to selectively disable optimization of specific FOCUS features when the results may be undesirable. The ability to disable IF-THEN-ELSE and IF TOTAL or WHERE TOTAL optimization has been added. By default, all optimization features are enabled.
FEATOPT settings are displayed in Web Console debug mode.
The SET OPTIFTHENELSE command has been deprecated.
Syntax: How to Control IF TOTAL and WHERE TOTAL Optimization
ENGINE sqlengine SET FEATOPT TOTALTEST {ON|OFF}where:
- sqlengine
-
Specifies the adapter.
- ON
-
Passes the HAVING clause to the DBMS, as well as CASE logic, when applicable. The HAVING clause results from passing an IF TOTAL or WHERE TOTAL phrase from the FOCUS request. ON is the default value.
- OFF
-
Suppresses adding the HAVING clause to the generated SELECT.
Example: Disabling WHERE TOTAL Optimization Using FEATOPT
The following request against SQL Server has a WHERE TOTAL test.
SET TRACEUSER=ON SET TRACEON = STMTRACE//CLIENT SET TRACESTAMP=OFF TABLE FILE WFLITE SUM COGS_US GROSS_PROFIT_US BY PRODUCT_CATEGORY WHERE TOTAL COGS_US GT 400000 END
The default generated SQL follows. The WHERE TOTAL test has been passed to the DBMS as a HAVING clause.
SELECT T7."PRODUCT_CATEGORY", SUM(T1."COGS_US"), SUM(T1."GROSS_PROFIT_US") FROM ( wrd_wf_retail_sales T1 LEFT OUTER JOIN wrd_wf_retail_product T7 ON T7."ID_PRODUCT" = T1."ID_PRODUCT" ) GROUP BY T7."PRODUCT_CATEGORY" HAVING ( SUM(T1."COGS_US") > 400000) ORDER BY T7."PRODUCT_CATEGORY";
Adding the ENGINE SQLMSS SET FEATOPT TOTALTEST OFF command generates the following SQL. The WHERE TOTAL test has not been passed in the generated SQL and will be handled by FOCUS.
SELECT T7."PRODUCT_CATEGORY", SUM(T1."COGS_US"), SUM(T1."GROSS_PROFIT_US") FROM ( wrd_wf_retail_sales T1 LEFT OUTER JOIN wrd_wf_retail_product T7 ON T7."ID_PRODUCT" = T1."ID_PRODUCT" ) GROUP BY T7."PRODUCT_CATEGORY" ORDER BY T7."PRODUCT_CATEGORY";
Syntax: How to Control IF-THEN-ELSE Optimization
ENGINE sqlengine SET FEATOPT IFTHENELSE {ON|OFF}where:
- sqlengine
-
Specifies the adapter.
- ON
-
Passes the IF-THEN-ELSE expressions used to create a DEFINE field to the DBMS as CASE logic, when possible. ON is the default value. This optimization is subject to the limitations on optimizing DEFINE fields. The DEFINE field must be an object of a selection test or an aggregation request.
- OFF
-
Suppresses passing IF-THEN-ELSE expressions in the generated SQL.
Note: The SET OPTIFTHENELSE command has been deprecated.
Example: Disabling IF-THEN-ELSE Optimization Using FEATOPT
The following request against SQL Server has a DEFINE field created using IF-THEN-ELSE syntax.
SET TRACEUSER=ON SET TRACEON = STMTRACE//CLIENT SET TRACESTAMP=OFF DEFINE FILE WFLITE DEF1 = IF (PRODUCT_CATEGORY EQ ' Accessories') AND (BRAND EQ 'Sony ') AND (PRODUCT_SUBCATEG EQ 'Headphones') THEN 1 ELSE 0; END TABLE FILE WFLITE PRINT PRODUCT_CATEGORY PRODUCT_SUBCATEG BRAND WHERE DEF1 EQ 1 END
The default generated SQL follows. The IF-THEN-ELSE syntax has been passed to the DBMS as CASE logic.
SELECT T7."BRAND", T7."PRODUCT_CATEGORY", T7."PRODUCT_SUBCATEG" FROM wrd_wf_retail_product T7 WHERE ((CASE WHEN ((T7."PRODUCT_CATEGORY" = ' Accessories') AND (T7."BRAND" = 'Sony ') AND (T7."PRODUCT_SUBCATEG" = 'Headphones')) THEN 1 ELSE 0 END) = 1);
Adding the ENGINE SQLMSS SET FEATOPT IFTHENELSE OFF command generates the following SQL. The IF-THEN-ELSE syntax has not been passed in the generated SQL and will be handled by FOCUS.
SELECT T7."BRAND", T7."PRODUCT_CATEGORY", T7."PRODUCT_SUBCATEG" FROM wrd_wf_retail_product T7;
Enhancement to SQL Adapter Native Messaging Interface
In prior releases, the messages returned by an SQL adapter were displayed as one long string, making them difficult to read and understand. Starting in this release, native SQL messages will be presented in readable blocks, each as a FOC1500 message on a separate line. For example:
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000 (FOC1500) : Microsoft SQL Server Native Client 10.0: [42000] Syntax error, (FOC1500) : permission violation, or other nonspecific error (FOC1406) SQL OPEN CURSOR ERROR. : __WF_RETAIL_WF_RETAIL_SALES
Optimization of Expressions on Partial Dates
Partial date formats such as YY, Q, D, and M, which are actually integer values, can now be optimized when used in expressions.