SAP on IBM i – Update week 30, 2016: Improvements in the BW Sanity Checks
Originally created for verifying the correctness of the required settings that enabled EVI stage 2 support, the BW Sanity Checks tool soon evolved into a quick and simple means to check many SAP Business Warehouse configuration settings that are specific for DB2 for IBM i.
With SAP Note 2342139 – IBM i: New tests in BW Sanity Check, the tool has undergone an extensive revision. New tests have been added, and the usability of the tool has been improved.
The BW Sanity Checks tool is integrated into the DBA Cockpit (transaction DBACOCKPIT), and you can find it using the path Diagnostics –> BW Sanity Checks in the left window area.
We encourage all SAP on IBM i BW users to run this tool at your earliest convenience to make sure SAP and DB2 are running BW together as effectively as possible. If you find that you have red traffic lights indicating missing or incorrect settings, we highly advise you to take the suggested actions as soon as you are able to do so.
To align with the look of other screens in the DBA Cockpit, the screen header now shows the name and release of the monitored BW system, and the name and operating system release of the database server.
The check results are displayed as a hierarchical list. While the original (English) messages usually fit into this log, it often happened that translated texts in other languages were longer and became truncated at the end. This is now solved with the support for longer text messages.
Above the check results, a toolbar offers functions for working with the log: expand and collapse subtrees, a search function, and the possibility to print the test results (to a printer or a local file).
Depending on the SAP BW system release, there are up to five different test categories:
This screen shows basic information about the system like installed Support Packages and the current Kernel.
Profile parameters and RSADMIN parameters that influence SAP BW on IBM i-specific system behavior, but do not fit into any of the other categories, are checked here. The log does not only list the parameters and their values (if set), but also additional information, like what is wrong with an incorrect parameter value, or what is the default behavior if a parameter is not set. For each test, there is also a link to an SAP Note that describes the checked parameter or function in more detail. Clicking on this link will open the indicated SAP Note in a browser window.
The check tool determines which QAQQINI file is used for database connections and displays all parameters that are not set to *DEFAULT.
For a local system, the DBA Cockpit is able to compare the PTF status of the IBM i server with the IBM Information APAR automatically. The log displays a pushbutton that directly switches to this screen. A remote check of this PTF status is not supported by the DBA Cockpit. Therefore, in case of monitoring a remote BW system, there is no such button and the log displays a reminder to regularly check the PTF status.
EVI (Encoded Vector Index) Stage 2 Support
The required settings for enabling EVI stage 2 support are verified here. This includes profile parameters and QAQQINI parameters.
You can also check whether the EVIs on the InfoCube fact tables are all in place. The parameters in the profile and QAQQINI have no effect if the EVIs are not there. Because this check can take some time to finish (especially if there are many InfoCubes in the system), this check is not executed immediately. Instead, a pushbutton can start the test when needed.
Parallel Index Build with SMP
If IBM DB2 Symmetric Multiprocessing is installed (this is checked here), radix indexes and EVIs on BW tables can be built faster by taking advantage of parallelism. This feature is activated by a RSADMIN parameter, which is also checked here.
If IBM DB2 Multisystem is installed (this is checked here), various settings relevant for table partitioning are checked.
The test checks if Parallel Alter Tables support or an alternative, the Fair Lock option (as of database release 7.3) are set up correctly. Partitioning of PSA tables is activated by an RSADMIN parameter, which is being checked as well.
Using another pushbutton, you can check whether the partitions of InfoCube fact tables correspond to the partitioning settings in the Data Warehousing Workbench.
A partitioned table on DB2 for IBM i can have up to 256 partitions, and the last check on this screen finds BW tables that are getting close to this limit. This check is also available via a pushbutton in order to execute it only when needed.
For SAP BW systems with release 7.40 or higher, the prerequisites for Flat InfoCube support and the support for SAP BW to allow more than 77 indexes on fact tables are checked. Flat InfoCubes are not the default, but can help to speed up data loads. More information about Flat InfoCubes can be found in SAP Note 2073519 – IBM i: Support for flat InfoCubes for BW for DB2 for IBM i.
The RSADMIN parameters that are relevant for working with Flat InfoCubes on IBM i are checked here as well.
Finally, a pushbutton offers the function to verify the database indexes of all Flat InfoCubes. This can be useful after implementing the support for more than 77 indexes on fact tables in case there are Flat InfoCubes that should have had more than 77 EVIs when they were first created.
A detailed documentation of the BW Sanity Checks tool complete with descriptions of all available tests can be found in SAP Note 541508 – IBM i: Checking the system parameters for BW.