UPDATE STATISTICS

The UPDATE STATISTICS utility updates statistical information maintained in the dictionary for one or more tables. CA IDMS/DB uses this information when determining the optimal access strategy for processing SQL statements.
idmscu19
The UPDATE STATISTICS utility updates statistical information maintained in the dictionary for one or more tables. CA IDMS/DB uses this information when determining the optimal access strategy for processing SQL statements.
This article describes the following information:
2
2
Authorization
To update statistics for
You need this privilege
For
A table
ALTER
The table
All tables in an area
DBAREAD
The area
Non-SQL schema
ALTER
All tables processed in the schema
Syntax
  ►►─── UPDATE STATISTICS ──────────────────────────────────────────────────────►                      ┌────────────────── , ──────────────────┐  ►─── FOR ─┬─ TABLE ─▼─┬────────────────┬─ table-identifier ─┴─┬──────────────►            │           └─ schema-name. ─┘                      │            │        ┌─────────── , ────────────┐               │            ├─ AREA ─▼─ segment-name.area-name ─┴───────────────┤            │                                                   │            └─ SCHEMA schema-name ──┬─────────────────────────┬─┘                                    │       ┌───── , ─────┐   │                                    └─ AREA─▼─ area-name ─┴───┘  ►─┬──────────────────┬───────────────────────────────────────────────────────►◄    └─ SAMPLE percent ─┘  
Parameters
  • FOR
    Identifies the tables or areas for which the UPDATE STATISTICS utility is to update statistics.
  • TABLE
    Specifies one or more SQL-defined tables and non-SQL defined tables for which the UPDATE STATISTICS utility is to update statistics.
  • schema-name
    Specifies the name of the schema associated with the named table.
  • table-identifier
    Specifies the identifier of a base table defined in the dictionary.
  • AREA
    Updates statistics for all the tables in one or more SQL-defined areas. If one of the specified areas is non-SQL-defined, error message DB002316 is displayed which indicates that the area is NOT a relational area. In this case, specify the areas through the SCHEMA-clause of the UPDATE STATISTICS utility.
  • segment-name
    Specifies the name of the segment associated with the area.
  • area-name
    Specifies the name of the area.
  • SAMPLE
    Specifies the percentage of the pages in an area that the UPDATE STATISTICS utility is to examine when calculating statistical information about one or more tables in the area.
  • percent
    Specifies an integer in the range 1 through 100.
    By default, if you do not specify a percentage, the UPDATE STATISTICS utility will examine all the pages in the specified area.
  • SCHEMA
    schema-name
    Identifies the schema for which statistics are updated. The identified schema must be SQL-defined and can reference a non-SQL-defined schema.
  • AREA
    area-name
    Identifies one or more areas of the identified schema for which statistics are to be updated.
Usage
How to submit the UPDATE STATISTICS statement
You submit the UPDATE STATISTICS statement to CA IDMS/DB either online or through the batch command facility. If you submit it through the batch command facility, you should execute it through the central version, because the dictionary is updated as part of processing the statement.
Journal in local mode
If you are running CA IDMS/DB in local mode, you can journal while updating statistics.
These statistics are updated
When updating statistics for a table, the UPDATE STATISTICS utility also updates statistics about:
  • The indexes defined on the table
  • The referential constraints in which the table is the referenced table
  • The area associated with the table
Which pages are examined
When you specify a percentage of less than 100 in the SAMPLE parameter, the UPDATE STATISTICS utility selects the pages to be examined from across the entire area. For example, if you specify SAMPLE 50, the UPDATE STATISTICS utility examines every other page throughout the entire area, rather than every page in the first half of the area.
UPDATE STATISTICS extrapolates the statistics
When calculating statistics based on less than 100 percent of the pages in an area, the UPDATE STATISTICS utility extrapolates the values for the entire area from the values based on the percentage examined. For example, if you specify SAMPLE 50, and the 50 percent of the pages that are examined in the area contain 80 rows of the table, the UPDATE STATISTICS utility assumes the table has 160 rows.
The statistics stored in the dictionary reflect the extrapolated values, not the raw data.
UPDATE STATISTICS for IDMSNTWK
When updating statistics for an SQL-defined schema that references the non-SQL-defined schema IDMSNTWK, the UPDATE STATISTICS utility will only process area DDLDML. If an area other than DDLDML is specified using the AREA clause, a warning will be issued indicating there are no tables to process.
UPDATE STATISTICS for native VSAM files
When updating statistics for an SQL-defined schema that references a non-SQL-defined schema containing native VSAM files, the job abends with error-message DB002300 and DBIO Error code 3077. The UPDATE STATISTICS module IDMSCOLS collects statistics of some information found on a BDAM DB page (for example, total space available count) that cannot be found on a native VSAM page. In this case, the DBIO Error code 3077 indicates that an attempt was made to run an invalid CA IDMS utility against a native VSAM file.
UPDATE STATISTICS for a non-SQL-defined schema
When updating statistics for an SQL-defined schema that references a non-SQL-defined schema, statistics are stored in the dictionary (DDLDML) where the non-SQL schema is defined. For a system-owned index key that does not contain group elements, statistics are kept for each index column. If the index key contains a group element, a DB003202 warning message is issued and only the statistics of the first index column are updated.
JCL Considerations
When you submit an UPDATE STATISTICS statement to CA IDMS/DB through the batch command facility, the JCL to execute the facility must include either:
  • A SYSCTL file to direct execution to the central version
    or
  • Statements to define:
    • The areas containing the table(s) being examined
    • The dictionary containing the table definitions
    • The journal files of the DMCL you are using
Example
Updating statistics for specified tables
The following UPDATE STATISTICS statement updates statistics for the MONTHLY_BUDGET and PROPOSED_BUDGET tables in the PROD schema. Since no percentage has been specified, the UPDATE STATISTICS utility will examine all the pages in the area associated with each table when calculating the statistics.
update statistics    for table prod.monthly_budget, prod.proposed_budget;
Updating statistics for all the tables in an area
The following UPDATE STATISTICS statement updates statistics for all the tables in the SQLDEMO.EMPLAREA area.
update statistics    for area sqldemo.emplarea;
Sample Output
After successful completion of the UPDATE STATISTICS statement on the SQLDEMO.EMPLAREA area shown previously, the following listing is produced.
IDMSBCF                                              IDMS Batch Command Facility                            mm/dd/yy   PAGE 1    UPDATE STATISTICS FOR AREA SQLDEMO.EMPLAREA;  Status = 0  AutoCommit will COMMIT transaction  Command Facility ended with no errors or warnings