CAST AIP Extensions
Page tree
Skip to end of metadata
Go to start of metadata

On this page:

Summary: this page explains how to manage XXL and XXS data for Quality Rule purposes.

Introduction

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.: 
xxl_threshold=100000
xxs_threshold=10

CASTPUBS.ORDER_LINE=2000000000
cwmm.ACC=2000000000


Note that when generating a snapshot, the step "Update SQL XXL Table Size for ..." will display "skipped : no Table Size data" even when the analyzer correctly takes into account the tablesize files.

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.
xxl_threshold=100000

CASTPUBS.ORDER_LINE=2000000000
cwmm.ACC=2000000000

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):

xxl_threshold=10

CASTPUBS.ORDER_LINE=2000000000
cwmm.ACC=2000000000


.sqltablesize file generation methods

To generate the XXL table size statistics for use in an .sqltablesize file during the analysis, please see below:

DB2 z/OS

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:

/*
//*------------------------------------------------------
//*
//* 26 - EXTRACTING NUMBER OF ROWS FOR TABLES
//*
//* COLUMN        TYPE     COL-SIZE EXTRACT-SIZE
//* ============= ======== ======== ============
//* DBNAME        VARCHAR  24       24
//* CREATOR       VARCHAR  128      128
//* NAME          VARCHAR  128      128
//* CARDF         FLOAT    11       11
//*------------------------------------------------------
//* Following filters can be inserted in the WHERE clause:
//*   - CREATOR IN ('xxx','yyy', ...)
//*   - CHAR(DBNAME) IN ('xxx','yyy', ...)
//*------------------------------------------------------
//STEP26   EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//*
//SYSREC00 DD DSN=CAST.DB2.TABROWS,DISP=(NEW,CATLG,),
//            SPACE=(TRK,(100,100),RLSE)
//SYSPUNCH DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSTSIN  DD *
  DSN SYSTEM(DBx)
  RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIBxx) -
      LIB('DSNxxx.RUNLIB.LOAD') PARMS('SQL')
//SYSIN    DD *

  SELECT
      CHAR(24),  CHAR(DBNAME),
      CHAR(128), CHAR(CREATOR),
      CHAR(128), CHAR(NAME),
      CHAR(11),  CHAR(CARDF)
  FROM SYSIBM.SYSTABLES
  WHERE TYPE = 'T'
  ORDER BY DBNAME, CREATOR, NAME;

DB2 UDB

Use the following query:

SELECT concat(concat(trim(TABSCHEMA), '.') , trim(TABNAME)) concat '='  concat trim(cast(COLCOUNT as char(50))) TableSize
FROM SYSCAT.TABLES
WHERE type = 'T'
AND TABSCHEMA = 'YOUR SCHEMA NAME'
ORDER BY TableSize

For example:

SELECT concat(concat(trim(TABSCHEMA), '.') , trim(TABNAME)) concat '='  concat trim(cast(COLCOUNT as char(50))) TableSize
FROM SYSCAT.TABLES
WHERE type = 'T'
AND TABSCHEMA = 'TT3'
ORDER BY TableSize

Will generate the following ouput :

TT3.BAN_IM_1=7
TT3.BAN_IM_2=7
TT3.BATCH_REF=4
TT3.BUS_LIST_1=18
....
  • No labels