简化 DB2 for i5/OS 索引建议

                
 -- Create sample database in CONDENSE schema
 CALL QSYS.CREATE_SQL_SAMPLE('CONDENSE');
 SET SCHEMA Condense;

 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE e.job = 'DESIGNER' AND YEAR(e.birthdate) > 1950 AND e.sex = 'M';
 SELECT e.firstnme, d.deptnmae FROM department d, employee e 
	WHERE YEAR(e.birthdate) = 1953 AND e.job = 'DESIGNER' AND e.sex IN ('M');
 SELECT e.firstnme, d.deptnmae FROM department d, employee e
        ORDER BY e.job, e.sex, e.birthdate;

                
QSYS2.CONDENSEDINDEXADVICE ( 	
TABLE_NAME              FOR COLUMN TABNAME      VARCHAR(258) CCSID 37 NOT NULL , 
TABLE_SCHEMA            FOR COLUMN TABSCHEMA    CHAR(10) CCSID 37 NOT NULL , 
SYSTEM_TABLE_NAME       FOR COLUMN SYS_TNAME    CHAR(10) CCSID 37 NOT NULL , 
PARTITION_NAME          FOR COLUMN TABPART      VARCHAR(128) CCSID 37 DEFAULT NULL , 
KEY_COLUMNS_ADVISED     FOR COLUMN KEYSADV      VARCHAR(16000) CCSID 37 DEFAULT NULL , 
INDEX_TYPE                                      CHAR(14) CCSID 37 DEFAULT NULL , 
LAST_ADVISED            FOR COLUMN LASTADV      TIMESTAMP DEFAULT NULL , 
TIMES_ADVISED           FOR COLUMN TIMESADV     BIGINT DEFAULT NULL , 
ESTIMATED_CREATION_TIME FOR COLUMN ESTTIME      INTEGER DEFAULT NULL , 
LOGICAL_PAGE_SIZE       FOR COLUMN "PAGESIZE"   INTEGER DEFAULT NULL , 
MOST_EXPENSIVE_QUERY    FOR COLUMN QUERYCOST    INTEGER DEFAULT NULL , 
AVERAGE_QUERY_ESTIMATE  FOR COLUMN QUERYEST     INTEGER DEFAULT NULL , 
TABLE_SIZE                                      BIGINT DEFAULT NULL , 
NLSS_TABLE_NAME         FOR COLUMN NLSSNAME     CHAR(10) CCSID 37 DEFAULT NULL , 
NLSS_TABLE_SCHEMA       FOR COLUMN NLSSSCHEMA   CHAR(10) CCSID 37 DEFAULT NULL )

                

-- Condensed index advice for an entire schema, named CONDENSE
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE WHERE table_schema = 'CONDENSE' ;

-- Condensed index advice for a specific table, named EMPLOYEE in CONDENSE schema
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
       WHERE table_name = 'EMPLOYEE'  AND table_schema = 'CONDENSE';

-- Condensed index advice for a range of schemas, where the average 
-- query estimate driving the index advice is greater than 10 seconds
SELECT * FROM QSYS2.CONDENSEDINDEXADVICE 
   WHERE table_schema LIKE 'Q%' AND average_query_estimate > 10 ;

                
CREATE FUNCTION QSYS2.Condense_Advice(TABLE_SCHEMA VARCHAR(128), 
                                      TABLE_NAME VARCHAR(128) ) 
  RETURNS TABLE(TABLE_PARTITION VARCHAR(128), KEY_COLUMNS_ADVISED VARCHAR(16000), 
                INDEX_TYPE CHAR(14), LAST_ADVISED TIMESTAMP, TIMES_ADVISED BIGINT, 
                ESTIMATED_CREATION_TIME INTEGER, LOGICAL_PAGE_SIZE INTEGER,
                MOST_EXPENSIVE_QUERY INTEGER, AVERAGE_QUERY_ESTIMATE INTEGER,
                TABLE_SIZE BIGINT, NLSS_TABLE_NAME CHAR(10),NLSS_TABLE_SCHEMA CHAR(10))
  LANGUAGE C 
  NOT DETERMINISTIC 
  READS SQL DATA 
  CALLED ON NULL INPUT 
  SCRATCHPAD 325064 
  DISALLOW PARALLEL 	
  FINAL CALL 
  CARDINALITY 1 
  EXTERNAL NAME 'QSYS/QDBSSUDF2(CONDENSE_ADVICE)' 
  PARAMETER STYLE DB2SQL;   


-- Query the condenser UDTF directly, providing selection criteria and ordering 
-- the results of the 15 most important condensed entries
     SELECT * FROM TABLE(QSYS2.CONDENSE_ADVICE('CRPDTA','B123456')) AS a
       WHERE a.table_size >(1024*1024) AND MONTH(last_advised)= MONTH(CURRENT TIMESTAMP)
     ORDER BY average_query_estimate DESC 
     FETCH FIRST 15 ROWS ONLY ;