Default language.

Space announcement This space provides the same content as before, but the organization of the home page has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

ANALYZE

ANALYZE gathers information about the objects that you are reorganizing and provides estimated data set sizes.

Related topic

Basic REORG PLUS processing options

ANALYZE.png

ANALYZE provides estimates for the following data sets:

  • Unload (SYSREC)
  • Work (SYSUT1)
  • Sort (SORTWK)
  • Full image copy (BCPY, BCPZ, BRCY, and BRCZ)
  • Incremental image copy (BICY, BICZ, BIRY, and BIRZ)

REORG PLUS provides all estimates in both kilobytes and cylinders by device type. REORG PLUS calculates the data set size based on cardinality (the number of rows) and average row length (table space only). REORG PLUS obtains these values by using one of the following methods:

  • Sampling the object (SAMPLE)
  • Scanning the index (SCAN)
  • Retrieving the data from the Db2 catalog (HURBA)
  • Retrieving the data from the DASD MANAGER PLUS statistics tables (BMCSTATS)

REORG PLUS does not stop the table space and associated index spaces that participate in the reorganization during the ANALYZE phase.

The following table shows the combinations of ANALYZE options and the effects that they have on how REORG PLUS determines cardinality and average row length.

ANALYZE keywords

Cardinality

Average row length

ANALYZE not specified)

ANALYZE

ANALYZE PAUSE

ANALYZE ONLY

REORG PLUS decides whether to sample the table space (for a table space reorganization) or the index (for an index reorganization), or to scan the index leaf pages (for either type of reorganization).

REORG PLUS samples the table space.

ANALYZE SAMPLE

ANALYZE PAUSE SAMPLE

ANALYZE ONLY SAMPLE

REORG PLUS samples the table space or index space.

For an XML table space reorganization, REORG PLUS obtains index cardinality from the Db2 real-time statistics tables.

ANALYZE SCAN

ANALYZE PAUSE SCAN

ANALYZE ONLY SCAN

For a table space reorganization, REORG PLUS scans one index for each table that has an index. If a table does not have an index, REORG PLUS uses sampling for that table.

For an index reorganization, REORG PLUS scans the index.

ANALYZE HURBA

REORG PLUS makes an estimate based on the HURBA of the object that you are reorganizing, average row length, page size, and free space.

If you specify SHRLEVEL CHANGE or activate dynamic allocation, REORG PLUS ignores the HURBA keyword.

REORG PLUS estimates the length based on half the length of any VARCHAR columns in the table and the length of any fixed columns. REORG PLUS does not consider compression in the calculations.

ANALYZE PAUSE HURBA

ANALYZE ONLY HURBA

REORG PLUS changes the keywords to ANALYZE PAUSE or ANALYZE ONLY.

ANALYZE BMCSTATS

ANALYZE PAUSE BMCSTATS

ANALYZE ONLY BMCSTATS

REORG PLUS uses cardinality from the DASD MANAGER PLUS statistics table.

REORG PLUS uses average row length from the DASD MANAGER PLUS statistics table.

no value

If you do not specify ANALYZE on the command or you specify ANALYZE with no values (the default), REORG PLUS determines whether to use sampling or scanning. REORG PLUS performs a full analysis and continues processing, using information from the analysis to dynamically allocate data sets.

Additional consideration

When reorganizing a LOB table space, REORG PLUS does not sample or scan to estimate copy data set size but adds HURBAs from all data sets.

PAUSE

If you specify ANALYZE PAUSE, REORG PLUS ends the processing after the ANALYZE phase is complete and displays a report. You can use the output of the ANALYZE phase to specify the number and allocations of the work and copy data sets yourself, or you can have REORG PLUS use dynamic allocation for the data sets when you restart REORG PLUS at the next phase.

Restriction

When invoking DSNUTILB, REORG PLUS ignores this option.

ONLY

ANALYZE ONLY tells REORG PLUS to generate a report that provides estimates of the total space that REORG PLUS will need for the work and copy data sets, and the recommended number of each type of work data set. You can use the output of the ANALYZE phase to specify the number and allocations of the data sets on the REORG job that actually performs the reorganization. If you specify ANALYZE ONLY, REORG PLUS terminates after the ANALYZE phase and cannot be restarted.

Restriction

This option is not valid when REORG PLUS invokes DSNUTILB. REORG PLUS issues message BMC50178E and terminates.

SAMPLE

SAMPLE tells REORG PLUS to read the minimum number of pages needed to determine a reasonable estimate for cardinality.

Restriction

When invoking DSNUTILB, REORG PLUS ignores this option.

Additional considerations

The following considerations apply to ANALYZE SAMPLE:

  • When reorganizing a LOB table space, REORG PLUS does not sample or scan to estimate copy data set size but adds HURBAs from all data sets.
  • When dynamically allocating the SYSUT1 data sets for an XML table space reorganization, REORG PLUS sizes the data sets based on the number of keys in the XML and node ID indexes. During ANALYZE SAMPLE, REORG PLUS obtains these key counts from the Db2 real-time statistics tables.

SCAN

SCAN tells REORG PLUS to read every leaf page in one or more indexes to determine the exact cardinality.

Restrictions

The following restrictions apply to ANALYZE SCAN:

  • When invoking DSNUTILB, REORG PLUS ignores this option.
  • REORG PLUS does not support SCAN for an XML table space reorganization. If you specify ANALYZE SCAN for an XML table space, REORG PLUS changes the value to ANALYZE SAMPLE and continues processing.

Additional consideration

When reorganizing a LOB table space, REORG PLUS does not sample or scan to estimate copy data set size but adds HURBAs from all data sets.

HURBA

HURBA tells REORG PLUS to use information obtained from the Db2 catalogs and the HURBA to determine a gross estimate for cardinality and the average row length. You can save processing time by specifying HURBA, but you might get less accurate numbers than if you specify another value.

Restrictions

The following restrictions apply to ANALYZE HURBA:

  • For the following functions, REORG PLUS changes ANALYZE HURBA to ANALYZE to obtain more accurate numbers:
    • Dynamic allocation
    • SHRLEVEL CHANGE
    • ANALYZE PAUSE HURBA or ANALYZE ONLY HURBA
  • When invoking DSNUTILB, REORG PLUS ignores this option.
  • When you also specify the REBALANCE option, REORG PLUS ignores it.

BMCSTATS

BMCSTATS tells REORG PLUS to use the information contained in the DASD MANAGER PLUS statistics tables to determine cardinality and average row length.

If the information in the DASD MANAGER PLUS statistics tables is current, you can save processing time by specifying ANALYZE BMCSTATS. The results will be as accurate as if you had specified ANALYZE SCAN.

If the information in the DASD MANAGER PLUS statistics tables for the object that you are reorganizing is missing or incomplete, REORG PLUS changes BMCSTATS to SAMPLE and continues with the reorganization. To populate the statistics tables, either run the BMCSTATS component of DASD MANAGER PLUS or run a reorganization with BMCSTATS YES.

Important

The ANALYZE BMCSTATS option is available only if you are using REORG PLUS as a component of the BMC AMI Database Performance for Db2 or Database Performance for DB2 solution. If you are not, REORG PLUS changes BMCSTATS to SAMPLE and continues with the reorganization.

Restrictions

REORG PLUS ignores this option in the following cases:

  • When invoking DSNUTILB
  • When reorganizing LOB table spaces

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*