# 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

- FORIdentifies the tables or areas for which the UPDATE STATISTICS utility is to update statistics.
- TABLESpecifies one or more SQL-defined tables and non-SQL defined tables for which the UPDATE STATISTICS utility is to update statistics.
- schema-nameSpecifies the name of the schema associated with the named table.
- table-identifierSpecifies the identifier of a base table defined in the dictionary.
- AREAUpdates 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-nameSpecifies the name of the segment associated with the area.
- area-nameSpecifies the name of the area.
- SAMPLESpecifies 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.
- percentSpecifies 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.
- SCHEMAschema-nameIdentifies the schema for which statistics are updated. The identified schema must be SQL-defined and can reference a non-SQL-defined schema.
- AREAarea-nameIdentifies 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 versionor
- 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