Summary: this page explains how to manage XXL and XXS data for Quality Rule purposes.
XXL/XXS table Quality Rules are performance related Quality Rules that help detect incorrect or poorly performing SQL queries running on XXL/XXS tables. XXL tables can be defined as extremely large tables, containing a large amount of data, while XXS table are extremely small.
Enabling XXL/XXS Quality Rules
To enable these XXL/XXS tables Quality Rules it is necessary to provide the analyzer with table row size information in XML based *.sqltablesize files. *.sqltablesize files are taken into account in the same way as "regular" .SQL source code files for the analysis: put your *.sqltablesize files in the same folder alongside the source code.
You have now two options for *.sqltablesize file format :
- Use the existing CAST AIP format, see the examples here.
- Use a new and simplified format using the pattern: schema_name.table_name=<table row count> ("xxl_threshold" allows a threshold to be set for rules) e.g.:
Quality Rule results with XXL
Each Quality Rule has two versions: one for XXL and another for non-XXL:
- Violations and grades are always calculated for the non XXL rule whatever configuration is provided.
- If a configuration for XXL is provided for one or several schemas, the extra XXL rule is activated on those.
How to configure XXS
The Quality Rule "Avoid SQL queries that no index can support is configured to ignore tables that are too small, simply because adding an index on a very small table is generally considered as useless. You can now set the threshold at which a table will be considered as too small by this Quality Rule. To do so, you need to add the xxs_threshold= parameter to your *.sqltablesize file as follows (default value is 10):
.sqltablesize file generation methods
To generate the XXL table size statistics for use in an .sqltablesize file during the analysis, please see below:
When using the CAST DB2 z/OS extractor for use with the "legacy" DB2 Analyzer embedded in CAST AIP, a JCL statement is included to extract the table size statistics. This statement can be run as is to generate the required statistics:
Use the following query:
Will generate the following ouput :