Database maintainance utilities(3)

-

 The RUNSTATS Utility:

 This utility command can use be used to collect table and index statistics to build efficient access plans. The basic command syntax for RUNSTATS is:

     RUNSTATS ON TABLE [TableName]

    USE PROFILE

    <UTIL_IMPACT_PRIORITY [Priority]>

You could also use this version of the command:

     RUNSTATS ON TABLE [TableName] FOR

    <<SAMPLED> DETAILED>

    [INDEXES | INDEX]

    [[IndexNames] | ALL]

    <EXCLUDING XML COLUMNS>

    <ALLOW READ ACCESS | ALLOW WRITE ACCESS>

    <SET PROFILE NONE | SET PROFILE <ONLY> | UPDATE PROFILE

        <ONLY>>

    <UTIL_IMPACT_PRIORITY [Priority]>

You could also use this version of the command:

 

    RUNSTATS ON TABLE [TableName]

    <ON ALL COLUMNS |

        ON KEY COLUMNS> |

        ON COLUMNS [ColumnNames] |

        ON ALL COLUMNS AND COLUMNS [ColumnNames] |

        ON KEY COLUMNS AND COLUMNS [ColumnNames]>

    <WITH DISTRIBUTION>

    <EXCLUDING XML COLUMNS>

    <AND <<SAMPLED> DETAILED>

        [INDEXES | INDEX]

        [[IndexNames] | ALL]>

    <EXCLUDING XML COLUMNS>

    <ALLOW READ ACCESS | ALLOW WRITE ACCESS>

    <SET PROFILE NONE | SET PROFILE <ONLY> | UPDATE PROFILE

        <ONLY>>

    <UTIL_IMPACT_PRIORITY [Priority]>

Consider the following:

TableName: Identifies the name of the table for which to collect statistical information

IndexNames: Identifies the names indexes for which to collect statistical information

ColumnName: Identifies the column names for which to collect statistical information

Priority: Specifies to throttle the RUNSTATS utility

RUNSTATS command has various options, and a few of them are listed here:

Can be executed with a specific rate (throttled) using the UTIL_IMPACT_PRIORITY setting

Can collect statistics based on the profile created before

Can collect statistics for individual columns in a table

Can collect statistics on all indexes for a table

Can collect statistics with a table and index sample percentage

If you want to collect statistics on the table DB2INST1.EMPLOYEE and associated indexes based on a 25% sampling rate, you can execute the following command:

RUNSTATS ON TABLE db2inst1.employee ON ALL COLUMNS AND INDEXES ALL TABLESAMPLE SYSTEM (25.000000) INDEXSAMPLE SYSTEM (25.000000);

DB20000I  The RUNSTATS command completed successfully.

Similarly, if you want to collect statistics on the table DB2INST1.EMPLOYEE and only two of its indexes, with a sampling rate of 10%, you can execute the following command:

 

RUNSTATS ON TABLE db2inst1.employee AND SAMPLED DETAILED INDEXES db2inst1.ix1_employee, db2inst1.ix2_employee TABLESAMPLE SYSTEM (10.000000) INDEXSAMPLE SYSTEM (10.000000);

DB20000I  The RUNSTATS command completed successfully.

 

Comments

Post a Comment

Popular posts from this blog

Db2 export command example using file format (del , ixf)

-
You want to transfer data from a db2 database table to another db2 database or within the same database, one of the way is to use db2 export / import command. Using db2 export / import you can move your data to excel also.   DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of  del - delimited , wsf - worksheet format , ixf - Integration Exchange Format,asc.   The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data ...
Read more

How to fix DB2 Tablespace OFFLINE state issue?

-
Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...
Read more

How to determine fenced User-ID of a DB2 instance

-
When you create a DB2 instance in Unix, you have the option of specifying a fenced user-id different than instance owner. The purpose of fenced user id is to protect DB2 from untested or malicious code from the external stored procedures, user defined functions etc. When you try to run these external procedures, they run in a separate address space owned by the fenced user id.   You know about the fenced user id if you created a DB2 instance yourself on your Unix platform. But suppose, you are looking at a DB2 instance created by someone and you are wondering as which is the fenced user id, you can look at the user and group id of the file .fenced in ~/sqllib/ctrl directory and this will tell you as which is the fenced user id. For Eg:/home/db2inst1/sqllib/ctrl/.fencedID It will display the fenced user id for the instance db2inst1   Another method to find the fenced user id is   db2pd -fmp  
Read more