View Attach Info
This is version . It is not the current version, and thus it cannot be edited.
[Back to current version]   [Restore this version]

Indici#


Esempio di creazione di indice

db2 "CREATE INDEX ON . (idrub,idpadre,data1 desc,order) CLUSTER ALLOW REVERSE SCANS"
db2 "RUNSTATS ON TABLE .
WITH DISTRIBUTION AND DETAILED INDEXES ALL"
db2 "COMMIT WORK"

AUTO_RUNSTATS#


Per vedere le configurazioni del db

db2 get db cfg for DBNAME

Tra cui si vede

Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

This hierarchy (shown by the indentation of the parameters in the output from this command) allows a DBA to enable or disable automatic table maintenance features selectively or as a group. For example, if AUTO_MAINT is set to OFF, all other automatic maintenance parameters are considered to have an effective value of OFF, regardless their actual value. Similarly, if AUTO_TBL_MAINT is set to OFF, the AUTO_RUNSTATS, AUTOSTATS_PROF, AUTO_PROF_UPD and AUTO_REORG parameters are considered to have an effective value of OFF. To enable automatic statistics collection, the AUTO_MAINT, AUTO_TBL_MAINT and AUTO_RUNSTATS parameters must all have a value of ON.

You can enable automatic statistics collection by issuing the following command:

CONNECT TO
UPDATE DB CONFIG USING AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON


Configuring the statistics heap STAT_HEAP_SZ#

The database configuration parameter STAT_HEAP_SZ determines the maximum amount of memory that will be used when RUNSTATS is performed. This limit applies whether RUNSTATS is issued manually or driven by automatic statistics collection. Although the default value (set when a database is created) is sufficient in many cases, it is usually too low for wide tables (tables with many columns). Whenever a RUNSTATS command fails to complete due to a low setting of STAT_HEAP_SZ, sqlcode SQL0973N is generated. When automatic statistics collection fails to perform RUNSTATS on a table due to insufficient statistics heap memory, the following logpoints will be written to the db2diag.log.

2007-03-23-23.49.33.246227-240 I1690146A941 LEVEL: Error
PID : 807008 TID : 1 PROC : db2agent (A)
INSTANCE: popivan NODE : 000 DB : A
APPHDL : 0-18 APPID: *LOCAL.popivan.070324034939
AUTHID : POPIVAN
FUNCTION: DB2 UDB, relation data serv, sqlr_init_tstat, probe:160
MESSAGE : ZRC=0x8B120006=-1961754618=SQLR_STATS_HEAP_TOO_SMALL
"Statistics heap size too small to begin with"
DIA8328C No memory available in the statistics heap.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This particular version was published on 14-Apr-2010 14:30 by RaffaelePedrini.