Space: Single Table Analysis (DB2 UDB for UNIX and Windows) (SAP Library

Space: Single Table Analysis (DB2 UDB for UNIX and Windows)

You can access detailed information on a single table and maintain table statistics by calling the DBA Cockpit and choosing Space ® Single Table Analysis in the navigation frame of the DBA Cockpit. The Space: Tables and Indexes Details screen appears.

The following information is displayed:

Table and Index Details

Field	Description
Name	Name of the table
Schema	Schema of table, that is usually the user who created the table

Table

Field	Description
*Table*
Tablespace	Name of the tablespace to which the table belongs
Pooled, Cluster or Import/Export	This information only applies to SAP systems (ABAP only). Indicates whether this table is defined as a pooled table, a cluster or an import/export table in the ABAP dictionary.
*REORG Check Statistics*
Last REORG Check	Date and time of the last REORG check, for example, the date and time when RUNSTATS ran using program dmdb6srp
Total Size	Size of table in KB
Free Space Reserved	Percentage of free space reserved in the tables’ allocated pages. This free space is taken into account by LOAD and REORG.
F1: Overflow Rows	Overflow rows as a percentage
F2: Table Size / Allocated Space	Table size divided by allocated space as a percentage
F3: Full Pages / Allocated Pages	Full pages divided by allocated pages as a percentage
Last REORG of Table	Date and time when the last REORG ran
Runtime of Last REORG	Runtime of the last REORG
*Catalog Statistics*
Last RUNSTATS	Date and time when the last RUNSTATS ran
Cardinality	Number of data records in the table
Counted Rows	Number of rows that have been counted by a SELECT() statement. This information is only displayed if you choose Count*.
Deviation	Deviation of the number of rows provided by RUNSTATS in the system catalog from the number of rows provided by a SELECT COUNT() statement. This information is only displayed if you choose Count*.
Overflow Records	Number of records that have overflowed. Records overflow when a data record is updated and the new data record is larger than the old one or when a column is added to a table.
No. of Pages with Data	Number of pages containing data
Total Number of Pages	Total number of pages in the table
VOLATILE	Indicates whether the table is flagged as VOLATILE in the system catalog or not. If the table is flagged as VOLATILE, statistics are not used by the optimizer. Newly created tables and tables that were dropped or recreated during an upgrade or a table conversion are always marked as VOLATILE as long as there are not yet valid statistics available.

Indexes

Field	Description
*Index*
Name	Name of the index
Schema	Schema of the index, that is usually the user who created the index
Tablespace	Name of the tablespace to which the index belongs
*REORG Check Statistics*
Last REORG Check	Date and time of the last REORG check, for example, the date and time when RUNSTATS ran using program dmdb6srp
Cardinality	Number of entries in the index
Total size	Size of the index in KB
Free Space Reserved	Percentage of free space reserved in the index pages. This free space is taken into account by LOAD and REORG.
F4: Cluster Ratio	Cluster ratio as a percentage
F5: Index Size / Allocated Space	Index size divided by allocated space as a percentage
F6: No. Entries / No. Poss. Entries	Number of entries divided by the number of possible entries as a percentage
*Catalog Statistics*
Last RUNSTATS	Date and time when the last RUNSTATS ran
Number of Leaves	Number of index leaves
Number of Levels	Number of index levels
Sequential Pages	Number of index leaves physically located on the hard disk sorted by index without large intervals between them
Density	Relative density of the sequential pages as a proportion of the total number of index pages. 100 % is the optimum value.
Cluster Ratio	Degree of fragmentation of the index (100 % means no fragmentation and is the optimum value).
Cluster Factor	Not currently calculated. The value is set to –1.
First Key Cardinality	Number of different values in the first column of the index
First 2 Key Cardinality	Number of different values in the first two columns of the index
First 3 Key Cardinality	Number of different values in the first three columns of the index
First 4 Key Cardinality	Number of different values in the first four columns of the index
Full Key Cardinality	Number of different values in all columns of the index

Note

If the value displayed in field Full Key Cardinality is the same as the one displayed in field Cardinality, the index is a unique index. Every record in the table can be accessed using that index.

If several indexes are defined on a table, you can use the page buttons on Index to navigate between the different indexes.

Table Structure

Column	Description
DB Column No.	Number of the column in the database
DB Column Name	Name of the column in the database
DB Type	Data type of the column in the database
DB Length	Length of the column in the database
The following information is only displayed for RFC-monitored systems and if the table is defined as a transparent table in the ABAP Dictionary:
Column	Description
SAP Column Name	Name of the column defined in the ABAP Dictionary
SAP Key	Column is part of primary key defined in the ABAP Dictionary
SAP Type	Data type of the column defined in the ABAP Dictionary
SAP Length	Length of the column defined in the ABAP Dictionary

Caution

The values displayed on the Space: Tables and Indexes Details screen are sometimes not correct. The reason is that these values are calculated based on DB2 system catalog information. To achieve good results, we recommend that you run RUNSTATSweekly.

If database tables contain VARCHAR or LOB columns, the specification of the size may be too small because the consumed space for VARCHAR and LOB columns can only be estimated.

Caution

The date and time of the last REORGcheck and the last RUNSTATS should be the same. If this is not the case, run program RUNSTATS or REORGCHKusing the DBA Planning Calendar.

Index Structures

Column	Description
Position	Position of the column within the key
Order	Sort order of the column: · A = ascending order · D = descending order
DB Column Number	Number of the column in the database
DB Column Name	Name of the column in the database
DB Type	Data type of the column in the database
DB Length	Length of the column in the database
The following information is only displayed for RFC-monitored systems and if the table is defined as a transparent table in the ABAP Dictionary:
Column	Description
SAP Column Name	Name of the column defined in the ABAP Dictionary
SAP Type	Data type of the column defined in the ABAP Dictionary
SAP Length	Length of the column defined in the ABAP Dictionary

If several indexes are defined on a table, you can use the page buttons on Index Structures to navigate between them.

RUNSTATS Control

Field	Description
*Scheduling*
Automatically	Statistics and REORGCHK calculations are gathered by CCMS jobs that are scheduled in the DBA Planning Calendar.
On User Request	CCMS jobs do not process these tables automatically, that is RUNSTATS and REORGCHK must be explicitly scheduled by the user.
Statistics is out-of-date	Due to the monitored number of update activities, the statistics might be out-of-date. As a consequence, a RUNSTATS is recommended.
Deviation	Deviation of the current size (cardinality) in the table statistics from the size that was estimated based on the monitored number of update activities
Collect Data for Application Monitor	Table is monitored by the application monitor ST07
*Statistics Attributes*
Statistics	A statistics is gathered for this table. As soon as there is a valid statistics the table will be marked as NOT VOLATILE in the system catalog.
No Statistics and Volatile	The table is marked as VOLATILE and has no statistics
*Table Analysis Method*
Basic	Basic statistics for the table
Distributed Statistics	Distributed statistics for the table
None	No statistics for the table Selecting this option does only freeze already existing old table statistics but not delete or invalidate them.
Sampling of [ ] %of entries	Percentage of entries to be used for sampling. This field is only active if you are using DB2 UDB for UNIX and Windows Version 8, FixPak 2 or higher.
System (Page, Sampling)	Data to be sampled is selected page by page. This field is only active if you are using DB2 UDB for UNIX and Windows Version 8, FixPak 2 or higher.
BERNOULLI (Row Sampling)	Data to be sampled is selected row by row. This field is only active if you are using DB2 UDB for UNIX and Windows Version 8, FixPak 2 or higher.
Analyze Key Columns only	Table statistics is gathered only for key columns of the table
*Index Analysis Method*
Basic	Basic statistics for the index
Detailed Statistics	Detailed statistics for the index
Detailed Sampled Statistics	Detailed statistics for the index using sampling
None	No statistics for the index Selecting this option does only freeze already existing old index statistics but not delete or invalidate them.

Checking and Updating the Statistics

You can check the quality of the statistical information in the system catalog by choosing This graphic is explained in the accompanying text Count. This counts the current number of rows in the table. Afterwards two additional fields, Counted Rows and Deviation in %, are displayed on the Table tabstrip. If the deviation is more than 15%, you should perform a RUNSTATS on this table. You can do this by choosing RUNSTATS in dialog or RUNSTATS in the background.

· RUNSTATS in dialog

A dialog box appears to inform you that running RUNSTATSmight have an impact on system performance. To start RUNSTATS, confirm the dialog box.

· RUNSTATS in the background

In this case, you switch to the planning calendar with a planning proposal for a single table RUNSTATS and with all parameters preset according to the RUNSTATS control parameters. For more information, see The DBA Planning Calendar.

Recommendation

For larger tables, we strongly recommend that you run RUNSTATS in the background.

In both cases, the RUNSTATS can be performed based on the changeable parameters that you specified on the RUNTSTATS Control tabstrip. If you have modified any of the control parameters and not chosen Save, the RUNSTATS Execution dialog box appears. You can choose one of the following options:

· Use modified parameters

Caution

Be aware when choosing this option, you have to take into account that the statistics will be overwritten by an automatically triggered RUNSTATSjob if you had previously selected Automatically by CCMS on the RUNSTATS Control tabstrip.

· Use active parameters

· Use modified parameters and save

If you want to reset modified RUNSTATS control parameters to the standard ones, you choose This graphic is explained in the accompanying text Reset RUNSTATS Control.

Caution

The SAP system is shipped with several tables whose settings differ from the standard settings. These tables are tables for which No Statistics and Volatile was chosen.

If you choose Reset RUNSTATS Control, these special settings will be lost. This loss can result in considerable performance problems.

If you want to check the system catalog content related to statistics against the RUNSTATS Control parameters, choose This graphic is explained in the accompanying text Check. The result is displayed in an editor in the lower half of the screen.