** RUNSTATS utility in DB2**

In IBM Db2, RUNSTATS is a utility used to collect and update statistics about database tables, indexes, and columns. These statistics help the Db2 optimizer make efficient query execution plans.

Syntax:

RUNSTATS ON TABLE schema.table_name
AND INDEXES ALL
WITH DISTRIBUTION
DETAILED INDEXES ALL;

**Key Components**

-> ON TABLE schema.table_name – Specifies the table for which statistics should be collected.
-> AND INDEXES ALL – Updates statistics for all indexes on the table.
-> WITH DISTRIBUTION – Collects frequency distribution statistics for columns, improving cardinality estimation.
-> DETAILED INDEXES ALL – Collects detailed index statistics, which help in index selection.

** Example Usage**

RUNSTATS ON TABLE HR.EMPLOYEES
AND INDEXES ALL
WITH DISTRIBUTION
DETAILED INDEXES ALL;

->This command updates statistics for the HR.EMPLOYEES table and all its indexes.

** When to Run RUNSTATS? **

-> After inserting, deleting, or updating a significant number of rows.
-> After reorganizing (REORG) a table.
-> Before running performance-intensive queries.
-> Periodically, as part of database maintenance.

Subscribe to my newsletter

Read articles from Chanikya A directly inside your inbox. Subscribe to the newsletter, and don't miss out.

Written by

Chanikya A

8+ years of progressive experience in Mainframe Development, Migration, Enhancement, Maintenance and Support projects. • Extensive Knowledge in requirement gathering, analysis, design, development, implementation, testing, integration deployment, production support, documentation and maintenance of IBM Mainframe applications. • Extensively Design, develop and maintain complex software systems in Mainframe ensuring high quality and timely delivery. • Extensively worked on COBOL, JCL, DB2, VSAM, and CICS. • Extensively worked on Changeman, Endevor, File-aid, Git, DB2 File Manager, Connect Direct, Zeke, Jira, CA7, and Servicenow Tools • Familiarity with mainframe tools such as REXX, TSO, ISPF, and SDSF. • Having Experience in Banking and Healthcare domain. • Having Experience in Linux Shell Scripting. • Having Good verbal, written and interpersonal skills. • Having experience in develop documentation throughout the software development life cycle (SDLC) • Strong experience on co-ordination skills to work with offshore/onshore teams throughout all phases. Operating System : Linux, IBM Mainframe Z/OS, IBM Mainframe MVS, Windows 7 & 10 Programming Languages: COBOL, JCL Database & Access: DB2, CICS, VSAM, SQL Tools and Utilities: IBM IDz,TSO, ISPF,GIT,SDSF, REXX, File-aid, File Manager , Sonarqube, Jira , Servicenow, Rally, Endevor, Changeman, CA7, Zeke, Savrs, and Fault Analyzer Domain : Banking and Healthcare Debugging Tool : Xpeditor Version Control Tools : Endevor , Changeman, and Git Reporting Tools : MS Excel Presentation Tools : MS Power Point Deliverable Methods : Agile and Devops