IBM DB2 for i

The following stored procedure searches to find column statistics that have been automatically collected by DB2 for i. Once the auto-collected stats have been identified, the procedure generates a CL command script that can be used to manually collect the statistics. Instead of waiting for performance to improve once the auto-collected stats have been collected, manual collection allows you to proactively create statistics that benefit the DB2 for i SQE query optimizer.

This procedure relies on unsupported CL commands and user-defined table functions that are available from IBM Support. More details on column statistics and the SQL Query Engine (SQE) can be found in the SQE Redpiece.

CREATE PROCEDURE stats_script(IN libname VARCHAR(10))
LANGUAGE SQL
RESULT SETS 1
BEGIN

/* Library name has to be fixed-length 10 character string */
DECLARE libname10 CHAR(10);

/* Search System ASP - SYSBAS */
DECLARE asp_id CHAR(10) DEFAULT '*         ';

/* Search all tables & files in the specified library   */
DECLARE table_name CHAR(10) DEFAULT '*ALL      ';

/* Search all members in the specified library   */
DECLARE member_name CHAR(10) DEFAULT '*ALL      ';

DECLARE current_table CHAR(10) DEFAULT NULL;
DECLARE current_mbr CHAR(10);
DECLARE columnlist  CHAR(8000);

DECLARE temp_table_exists CONDITION FOR SQLSTATE '42710';

/* Result set cursor to return stats CL script */
DECLARE statcmds CURSOR WITH RETURN FOR SELECT * FROM qtemp.cmdtable;

DECLARE CONTINUE HANDLER FOR temp_table_exists
  SET current_table=NULL;

/* Create table to temporarily hold the generated stats CL script */
CREATE TABLE qtemp.cmdtable
  (clcmd VARCHAR(10000));

SET libname10 = UPPER(libname);

FOR loopvar AS
  collist CURSOR FOR
  SELECT filnam, mbrnam, colnam
      FROM TABLE(dbstcmd.lstdbfstc(
              asp_id,
                  libname10,
                  table_name,
                  member_name))AS A
      WHERE crtusr='*SYS' ORDER BY 1,2,3
  DO
   IF current_table IS NULL THEN
       SET current_table = filnam;
       SET current_mbr = mbrnam;
       SET columnlist = '('||strip(colnam)||')';
    ELSEIF (current_table=filnam AND current_mbr=mbrnam)
      THEN
         SET columnlist = strip(columnlist) || ' ('||strip(colnam)||')';
      ELSE
        INSERT INTO qtemp.cmdtable VALUES(
         'CRTDBFSTC FILE(' || strip(libname10) || '/' ||
           strip(current_table) || ') MBR(' || strip(current_mbr)||
           ') COLLMODE(*BACKGROUND) STCCOLL(' || strip(columnlist)||')'
          );
        SET current_table = filnam;
        SET current_mbr = mbrnam;
        SET columnlist = '('||strip(colnam)||')';
      END IF;
  END FOR;

  /* Generate cmd string for last table the FOR loop was working on */
  IF current_table IS NOT NULL THEN
    INSERT INTO qtemp.cmdtable VALUES(
      'CRTDBFSTC FILE(' || strip(libname10) || '/' ||
         strip(current_table) || ') MBR(' || strip(current_mbr) ||
         ') COLLMODE(*BACKGROUND) STCCOLL(' || strip(columnlist) || ')'
      );


  END IF;

  /* Pass back result to invoker */
  OPEN statcmds;

END;

NOTE: This examples has not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of these programs. All programs contained herein are provided to you "AS IS". THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED.