Command Line and IBM SQL/400 Interfaces for the QDBST* (Database File Statistics APIs)

The IBM SQL Query Engine (SQE) makes use of Database File Statistics. Externalized management functions for these Statistics include the Navigator for i GUI and programming APIs. See also the Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS IBM Redbooks and the Documentation Center.

Additional Database File Statistics Tools

The following sample CL commands and SQL interfaces are available.

CL commands:

o	CRTDBFSTC - Create database file statistics
o	DLTDBFSTC - Delete database file statistics
o	UPDDBFSTC - Update database file statistics
o	CNLDBFSTC - Cancel database file statistic requests

SQL UDTF (User-defined Table Function):

o	LSTDBFSTC - List database file statistics

SQL Stored Procedure:

o	LSTSYSSTC - Generate script to replicate system generated database file statistics

Downloading and Installing the Tools
 

Caution: This sample code has not been submitted to any formal testing and is distributed as is. These interfaces are not supported. While the APIs are supported, any and all support for this sample code must come from a consulting agreement, not the IBM Support Line.

1.	Download the save file from  https://public.dhe.ibm.com/services/us/igsc/db/dbsttools.savf to your workstation.
2.	Create an empty save file on the target IBM i system: CRTSAVF FILE(SOMELIB/DBSTTOOLS) Press the Enter key.
3.	Use FTP in binary mode to upload the save file to the target IBM i system, overwriting the newly created save file: ftp> bin 200 Representation type is binary IMAGE. ftp> put dbsttools.savf SOMELIB/DBSTTOOLS
4.	Restore library DBSTCMD from the save file: RSTLIB SAVLIB(DBSTCMD) DEV(*SAVF) SAVF(SOMELIB/DBSTTOOLS) Press the Enter key.
5.	Register the LSTDBFSTC UDTF once with the target system's SQL catalogs. Use, for example, STRSQL or Navigator for i's Run SQL Script to run the CREATE FUNCTION DBSTCMD/LSTDBFSTC SQL statement listed in the Usage Notes section below.
6.	Create the LSTSYSSTC SQL Stored Procedure once on the target system by running the CREATE PROCEDURE DBSTCMD/LSTSYSSTC SQL statement listed in the Usage Notes section below.

Usage Notes for the Tools

CRTDBFSTC - Create database file statistics

o	As interface to the QdbstRequestStatistcs API.
o	Similar to the API, it will not overwrite or update existing statistics. Use UPDDBFSTC instead, or use DLTDBFSTC followed by CRTDBFSTC.
o	For enhanced performance on multiprocessor systems, use CHGQRYA DEGREE(*MAX) in the calling job, to allow the Statistics create process to take full advantage of all system resources.

DLTDBFSTC - Delete database file statistics

o	As interface to the QdbstDeleteStatistics API.

UPDDBFSTC - Update database file statistics

o	As interface to the QdbstUpdateStatistics API.
o	Similar to the API, it only works on already existing Statistics.
o	As an alternative, DLTDBFSTC followed by CRTDBFSTC can be used.

CNLDBFSTC - Cancel database file statistic requests

o	As interface to the QdbstCancelRequestedStatistics API.

LSTDBFSTC - List database file statistics

o	Sample calls: o List statistics for the first member of all files in library QSYS2 in the current namespace: Note: Exact spacing must be used. SELECT * FROM TABLE(LSTDBFSTC('*         ',                      'QSYS2     ',                      '*ALL      ',                      '*FIRST    ')) AS A o List statistics for the all members, of all files, in all libraries, in the current namespace: Note: Exact spacing must be used. WITH ALL_LIBS (LIBNAM) AS (SELECT DISTINCT(DBXLIB) FROM QSYS/QADBXREF) SELECT U.* FROM ALL_LIBS AS A,      TABLE(LSTDBFSTC('*         ',                      A.LIBNAM,                      '*ALL      ',                      '*ALL      ')) AS U
o	All four input parameters must be in uppercase and must be exactly 10 characters long, which might require padding the names with blanks.
o	The library DBSTCMD must be on the job's library list (or SQL PATH resp.) when calling the UDTF.
o	When experiencing time-out problems for larger result sets, use a QAQQINI file with option UDF_TIME_OUT set to *MAX.
o	One-time only registration with the system SQL catalogs using: CREATE FUNCTION DBSTCMD/LSTDBFSTC (  ASPDEV VARCHAR(10),  LIBNAM VARCHAR(10),  FILNAM VARCHAR(10),  MBRNAM VARCHAR(10) ) RETURNS TABLE (  ASPDEV CHAR(10),  LIBNAM CHAR(10),  FILNAM CHAR(10),  MBRNAM CHAR(10),  COLNAM CHAR(10),  CRTUSR CHAR(10),  STALE  CHAR(1),  AGEMOD CHAR(10),  BLKOPT CHAR(1),  CARDIN BIGINT,  MFVCNT INTEGER,  HSTCNT INTEGER,  STCID  CHAR(32),  STCSIZ BIGINT,  STCNAM VARCHAR(128) ) RETURNS NULL ON NULL INPUT NO FINAL CALL NO DBINFO EXTERNAL NAME 'DBSTCMD/LSTDBFSTC(LSTDBFSTC)' LANGUAGE C++ SCRATCHPAD PARAMETER STYLE DB2SQL DISALLOW PARALLEL NOT FENCED CARDINALITY 1000

LSTSYSSTC - Generate script to replicate system generated database file statistics

o	Will operate on all files of a single library and scans the files for existing, system generated database file statistics.
o	For any file with such statistics, a corresponding CRTDBFSTC command is generated to allow "manually" re-creating those statistics, for example, on other systems proactively without the need to wait for the statistics engine to create those statistics automatically.
o	The optionally generated DLTDBFSTC commands in conjunction with their corresponding CRTDBFSTC command will simulate a "create, if not exist, otherwise force refresh" semantic because the CRTDBFSTC alone will have no effect on existing, possibly stale statistics. Note: The use of this option will delete any existing statistics on columns of such a file, and the corresponding create command might not cover some of those columns.
o	Before using LSTSYSSTC, toggling the system value QDBFSTCCOL off and back on (to a value allowing system generated statistics) and letting the system work on potential additional Statistics will increase the chances of getting a more complete snapshot as generated by LSTSYSSTC. Note: A recent IPL will make the system value toggle most likely ineffective because the IPL clears the (SQL statement run history) input for the auto-stats engine.
o	The collection mode parameter is used as input for the generated CRTDBFSTC command.
o	The library DBSTCMD must be on the job's library list (or SQL PATH resp.) when calling or creating the stored procedure.
o	The LSTDBFSTC UDTF must be already registered (see above).
o	On SQL0204 errors, try running CREATE TABLE QTEMP/CRTSTC_TBL (CRTSTC_CMD VARCHAR(10000)) first in the same job.
o	One-time only registration with the system SQL catalogs using: CREATE PROCEDURE DBSTCMD/LSTSYSSTC (     IN v_search_lib VARCHAR(10),     IN v_collmode   VARCHAR(12),     IN gendelete    INTEGER  /* non-zero == TRUE */ ) LANGUAGE SQL RESULT SETS 1 /*SET OPTION DBGVIEW=*SOURCE*/ BEGIN /* List stats input values */     /* IASP to search - CURRENT namespace */     DECLARE search_asp CHAR(10) DEFAULT '*         ';       /* Library to search (fixed length) */     DECLARE search_lib CHAR(10);     /* Files to search */     DECLARE search_file CHAR(10) DEFAULT '*ALL      ';     /* File members to search */     DECLARE search_mbr CHAR(10) DEFAULT '*ALL      '; /* columns with auto stats */     DECLARE current_columns  CHAR(8000); /* Create stats collection mode (fixed length) */     DECLARE collmode CHAR(12); /* Loop through files and file members of library w/ auto stats */     DECLARE current_file CHAR(10) DEFAULT NULL;     DECLARE current_mbr CHAR(10); /* Temp table for result set of generated CRTDBFSTC commands */     DECLARE CRTSTC_CMDS CURSOR WITH RETURN         FOR SELECT * FROM QTEMP/CRTSTC_TBL;     BEGIN         DECLARE X CHAR(1);         DECLARE CONTINUE HANDLER FOR NOT FOUND SET X='1';         DROP TABLE QTEMP/CRTSTC_TBL;     END;     CREATE TABLE QTEMP/CRTSTC_TBL (CRTSTC_CMD VARCHAR(10000));     SET search_lib = UPPER(v_search_lib);     SET collmode = UPPER(v_collmode);     FOR loopvar AS collist CURSOR FOR     SELECT FILNAM, MBRNAM, COLNAM     FROM TABLE(DBSTCMD/LSTDBFSTC(search_asp,                                  search_lib,                                  search_file,                                  search_mbr))AS A         WHERE CRTUSR = '*SYS' ORDER BY 1,2,3     DO         IF current_file IS NULL THEN             SET current_file = FILNAM;             SET current_mbr = MBRNAM;             SET current_columns = '(' || STRIP(COLNAM) || ')';         ELSEIF (current_file = FILNAM AND current_mbr = MBRNAM)         THEN             SET current_columns = STRIP(current_columns) ||                           ' (' || STRIP(COLNAM) ||')';         ELSE             IF (gendelete <> 0) THEN BEGIN             INSERT INTO QTEMP/CRTSTC_TBL VALUES(             'CL: DBSTCMD/DLTDBFSTC FILE('                 || STRIP(search_lib) || '/'                 || STRIP(current_file) || ') '                 || 'MBR(' || STRIP(current_mbr)                 || ');');                 END;             END IF;               INSERT INTO QTEMP/CRTSTC_TBL VALUES(             'CL: DBSTCMD/CRTDBFSTC FILE('                 || STRIP(search_lib) || '/'                 || STRIP(current_file) || ') '                 || 'MBR(' || STRIP(current_mbr) || ') '                 || 'COLLMODE(' || collmode || ') '                 || 'STCCOLL(' || STRIP(current_columns)                 || ');');             SET current_file = filnam;             SET current_mbr = mbrnam;             SET current_columns = '(' || STRIP(COLNAM) ||')';         END IF;     END FOR;   /* Generate command for last table the FOR loop was working on */   IF current_file IS NOT NULL   THEN         IF (gendelete <> 0) THEN BEGIN         INSERT INTO QTEMP/CRTSTC_TBL VALUES(         'CL: DBSTCMD/DLTDBFSTC FILE('             || STRIP(search_lib) || '/'             || STRIP(current_file) || ') '             || 'MBR(' || STRIP(current_mbr)             || ');');             END;         END IF;           INSERT INTO QTEMP/CRTSTC_TBL VALUES(         'CL: DBSTCMD/CRTDBFSTC FILE('             || STRIP(search_lib) || '/'             || STRIP(current_file) || ') '             || 'MBR(' || STRIP(current_mbr) || ') '             || 'COLLMODE(' || collmode || ') '             || 'STCCOLL(' || STRIP(current_columns)             || ');');   END IF; /* Return temp table with commands as result set */     OPEN CRTSTC_CMDS;     SET RESULT SETS CURSOR CRTSTC_CMDS; END;

Once the tool is installed per the steps above, SQL scripts can be used to generate a script to refresh statistics for a given library, DLTDBFSTC followed by a CRTDBFSTC.

Here are the steps:

1. In a run SQL Script session, use the following script:

-- Replace library_name with the name of the library, making sure that 10 characters are specified
-- Change the session naming convention to *SQL through the connection settings
-- Specify whether the statistics will be collected *IMMEDIATE or *BACKGROUND, making sure that 12 characters are specified
-- Specify 1 for the last parameter on the CALL statement to indicate that the DLTDBFSTC commands should also be generated

CL:addlible DBSTCMD;
CREATE TABLE QTEMP.CRTSTC_TBL (CRTSTC_CMD VARCHAR(10000));
CALL DBSTCMD.LSTSYSSTC ('library_name', '*IMMEDIATE ', 1);

2. When the results are displayed, copy and paste the results into a run SQL script session

3. Run the new script to refresh the statistics. Save the script, if desired.