Concept
When BW queries contain local restrictions for the different cells in the output of the report, they are called “FEMS queries” (FEMS is short for Form EleMent Selection). As of SAP BW 7.5 SP04, a new option became available that controls whether these restrictions are evaluated in the application server or directly in the database.
For a description when it makes sense to push the query execution down to the database, see also this blog post: Pushing the Execution of BW Queries to the Database Level (FEMS Queries). The post was written for IBM Db2 for Linux, UNIX, and Windows, but the general concept applies to Db2 for IBM i as well.
You enable the so-called “FEMS Pushdown” support for a SAP BW system on Db2 for IBM i by following the instructions in SAP notes 2302681 - Pushdown of local restrictions to the database layer and 2301464 - IBM i: BW: FEMS restriction pushdown, additional information.
Please note that currently only FEMS queries on Flat InfoCubes are eligible for FEMS Pushdown on Db2 for IBM i.
Implementation of a FEMS Query
A part of the SQL statement that is created when executing a FEMS query is a matrix, which flags the conditions that need to be fulfilled for each FEMS. On Db2 for IBM i, there are three ways how to implement this matrix.
The default so far was a Common Table Expression (CTE). When the number of local restrictions for the query increases, the number of constants needed to build the CTE increases as well. At some point, this will lead to an SQL error:
SQL0101: SQL statement too long or complex
Message: [SQL0101]. Cause: The SQL statement is longer than the limit allowed
for length or complexity. The reason code is 2. One of the following reason codes
indicates the error:
1 - The total number of subselects combined using UNION, EXCEPT, and INTERSECT
is greater than 256.
2 - The total number of columns, constants, and operators is greater than the SQL limits.
3 - The sum of the lengths of the non-LOB and XML columns ...To avoid this error, the matrix can also be materialized in a Global Temporary Table, which is then joined with the rest of the query. By default, a temporary table is created when the number of constants needed for the matrix exceeds 8000. With RSADMIN parameter DB4_MAX_NUM_CONST, this threshold can be adjusted.
SAP note 2674941 - IBM i: Using Table Function for FEMS Pushdown delivers a third method: The constants are passed to a User-Defined Table Function (UDTF), which returns a result table that can be joined with the rest of the SQL query.
Of course, performance is highly dependent on many variables like the configuration of the underlying system, the query definition, or the amount of processed and selected data. However, our tests have shown that in most cases, the use of the table function is the fastest FEMS Pushdown method. The average query runtime was 10-15% faster compared to the other methods.
Therefore, the new default method for FEMS Pushdown is using the UDTF. The input string for the table function is limited to 32K, which should suffice in most cases. For more complex FEMS queries, or in the case that the table function support is not installed, CTEs are being used. And if the number of constants needed in the matrix exceeds the value of parameter DB4_MAX_NUM_CONST, a Global Temporary Table is created.
BW Sanity Checks
You can review the FEMS pushdown settings of your system in the BW Sanity Checks. Call transaction DBACOCKPIT, follow the path Diagnostics -> BW Sanity Checks -> Flat InfoCubes, and review the output under Check FEMS Pushdown Options. The check displays the status of relevant corrections and parameters and verifies whether table function support for FEMS queries is installed properly.
- SAP Managed Tags:
