Specifying RUNSTATS and REORG table column statistics
This option allows for multiple column-level statistics per table.
Procedure
-
On the Object-Specific Utility Options panel, enter Y in the Include Options and Update Options fields for Runstats Table Column Statistics or Reorg Table Column Statistics and press Enter.
The Table Column Statistics panel is displayed.
Figure 1. Setting table column statistics AUTOXPRT V1R1 ---- Table Column Statistics --- 2023/10/19 21:10:5 Option ===> Scroll ===> CSR Line Commands: V - View U - Update ------------------------------------------------------------------------------- Creator: TWUSR Profile: DB2 V12 User: TWUSR Db2 Subsystem: SS01 Description: Row 1 of 1 ------------------------------------------------------------------------------- Include Stats Cmd Name Creator Sample Defined ACTION_GROUP SAM N 0 ***************************** Bottom of Data **********************************Refer to the help panel for information about the fields on this panel.
- Select the table for which you want to define column statistics by entering U in the Cmd field next to the table. Press Enter. The Runstats Table Column Statistics panel is displayed.
- A C is displayed in the Cmd field; press Enter to create statistics. The Runstats Table Create window is displayed.
- In the Column Statistic Type field, enter C to specify a column list or enter G to specify a column group. Press Enter.
- For the column list, the Runstats Table Column Statistics panel is displayed, as shown in the following figure:
Figure 2. Setting table column statistics on the Runstats Table Column Statistics panel AUTOXPRT V1R1 ---- Runstats Table Column Statistics --- 2023/10/19 21:12:23 Option ===> Scroll ===> CSR Line Commands: S - Select U - Unselect ------------------------------------------------------------------------------- Creator: TWUSR Profile: DB2 V12 User: TWUSR Description: Db2 Subsystem: SS01 Table: SAM.ACTION_GROUP > Row 1 of 13 + ------------------------------------------------------------------------------- Cmd Sel Column Type Length Scale ACTION_ID BIGINT 8 0 SEQUENCE SMALLINT 2 0 ACTION_KEY VARCHAR 64 0 STARTTS TIMESTMP 13 12 ENDTS TIMESTMP 13 12 TRANS_ID TIMESTMP 13 12 STATUS VARCHAR 32 0 RETURN_CODE INTEGER 4 0 ERROR_MESSAGE VARCHAR 1331 0 OUTPUT CLOB 4 0 UTILITY_STMT CLOB 4 0 UTILITYRUN_ID BIGINT 8 0Select the columns that you want to collect statistics on by entering S in the Cmd field next to the column name. Press PF3 when finished.
- For the column group, the Runstats Column Group Options panel is displayed.
Figure 3. Setting column group options AUTOXPRT V1R1 ------ Runstats Column Group Options ------ 2023/10/19 21:12:48 Option ===> Scroll ===> CSR ------------------------------------------------------------------------------- Creator: TWUSR Name: DB2 V12 User: TWUSR Description: Db2 Subsystem: SS01 Table: SAM.ACTION_GROUP > Freqval Count . . . . . . . 1 (blank, Number) Freqval Occurrence . . . . . M (blank, M - Most, L - Least B - Both) Histogram Numquantiles . . . 100 (Number) Row 1 of 13 + ------------------------------------------------------------------------------- Seq Column Type Length Scale ACTION_ID BIGINT 8 0 SEQUENCE SMALLINT 2 0 ACTION_KEY VARCHAR 64 0 STARTTS TIMESTMP 13 12 ENDTS TIMESTMP 13 12 TRANS_ID TIMESTMP 13 12 STATUS VARCHAR 32 0 RETURN_CODE INTEGER 4 0 ERROR_MESSAGE VARCHAR 1331 0 OUTPUT CLOB 4 0Specify the column group for which statistics are collected by entering a numeric value in the Seq column. To clear a column, remove the numeric from the columns.
Refer to the help panel for information about the fields on this panel.
- For the column list, the Runstats Table Column Statistics panel is displayed, as shown in the following figure:
- Press PF3 when you are finished defining statistics.
The Runstats Table Column Statistics panel is displayed. The statistics definitions are listed on this panel, as shown in the following figure:
Figure 4. Statistics definitions on the Runstats Table Column Statistics panel AUTOXPRT V1R1 ---- Runstats Table Column Statistics --- 2023/10/19 21:14:12 Option ===> Scroll ===> CSR Line Commands: V - View C - Create D - Delete U - Update ------------------------------------------------------------------------------- Creator: TWUSR Profile: DB2 V12 User: TWUSR Description: Db2 Subsystem: SS01 Table: SAM.ACTION_GROUP > Row 1 of 1 > ------------------------------------------------------------------------------- Cmd Colgroup Columns Statistic Definition COLGROUP ACTION_ID FREQVAL COUNT 1 MOST HIST ***************************** Bottom of Data ********************************** - Press PF3.
The Table Column Statistics panel is displayed.
- Enter Y in the Include Sample field and press Enter. The Update Runstats Sample Specification window is displayed.
Figure 5. Updating sampling options Update Runstats Sample Specification Sample . . . . . . . . . . (Percent) Table Sample . . . . . . . (Auto, 0.01 - 100.00, None) Repeatable . . . . . . . (Number) - Enter the sampling options for the table space.
Refer to the help panel for information about the fields on this window.
- When finished, press Enter.
What to do next
To create more statistic definitions, enter C in the Cmd field. You can also update, delete, or view statistics definitions by using the appropriate line commands.