Ce document a été créé essentiellement par copier/coller depuis la documentation DB2-V6 de BOOKMGR.
Chacun des utilitaires DB2 est repris avec une description succinte et sa syntaxe. En cas de doute, il est impératif de consulter la documentation IBM de base.
The CHECK DATA online utility checks table spaces for violations of referential and table check constraints, and reports information about violations that are detected.
+------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ <-------------------------------------+ ¦ ¦ >>--CHECK--DATA----table space spec----------------------------------------------------------------------> ¦ ¦ +-PART--integer-+ ¦ +-PENDING-+ ¦ ¦ ¦ +-SCOPE--+-AUXONLY-+-+ ¦ ¦ +-ALL-----¦ ¦ ¦ +-REFONLY-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-REPORT-----+ ¦ ¦ ¦ +-AUXERROR----INVALIDATE---+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ <-----------------------------------+ ¦ ¦ ¦ +-FOR--EXCEPTION----IN--table-name1--USE--table-name2------------------------------------------+ ¦ ¦ ¦ +-NO--------------------+ ¦ ¦ ¦ +-DELETE----YES---------------------+ ¦ ¦ ¦ +-YES-+ ¦ ¦ ¦ +-LOG----NO----+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-0-------+ ¦ ¦ +-SYSERR-+ ¦ ¦ +-SYSUT1--+ +-,SORTOUT-+ ¦ ¦ ¦ +-EXCEPTIONS----integer---+ +-ERRDDN----ddname---+ +-WORKDDN----ddname1----+----------+-+ ¦ ¦ +-,ddname2-+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------->< ¦ ¦ +-SORTDEVT--device-type-+ +-SORTNUM--integer-+ ¦ ¦ ¦ +------------------------------------------------------------------------------------------------------------+ ¦ table space spec: ¦ ¦ ¦ ¦ >--TABLESPACE----------------------table-space-name--------------------------------------------> ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +--------------------------------------------------------------------------------------------------+
The CHECK INDEX online utility tests whether indexes are consistent with the data they index, and issues warning messages when an inconsistency is found.
+------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ : >>--CHECK--INDEX-----------------------------------------------------> ¦ ¦ ¦ ¦ <-,-----------------------------+ ¦ ¦ >----(---index-name----------------------)---------------------------> ¦ ¦ ¦ +-PART--integer-+ ¦ ¦ ¦ +-(--ALL--)--table space spec--------------------+ ¦ ¦ +-PART--integer-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------> ¦ ¦ ¦ +-SYSUT1-+ ¦ +-SORTDEVT--device-type-+ ¦ ¦ +-WORKDDN----ddname---+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------->< ¦ ¦ +-SORTNUM--integer-+ ¦ ¦ table space spec: ¦ ¦ ¦ ¦ >--TABLESPACE----------------------table-space-name------------------> ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +------------------------------------------------------------------------+
The COPY online utility creates up to four image copies of any of the following objects Table space, Table space partition, Data set of a linear table space, Index spaand Index space partition.
+--------------------------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ >>--COPY---------------------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ ¦ <-------------------------------------------------------------+ ¦ ¦ >-------table-space spec-----------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-index-name spec-----------------------------data-set spec-+ +-PARALLEL------------------------+ +-CHECKPAGE-+ ¦ ¦ ¦ ¦ ¦ +-ALL--------+ ¦ +-(--num-objects--)-+ ¦ ¦ ¦ ¦ ¦ ¦ (1)¦ ¦ ¦ ¦ ¦ ¦ +-DSNUM----integer------+ ¦ ¦ ¦ ¦ <---------------------------------------------------------------+ ¦ ¦ ¦ +-------ts-name spec----------------------------------data-set spec-------------------------CONCURRENT----------------+ ¦ ¦ ¦ +-index-name spec-+ ¦ +-ALL--------+ ¦ ¦ ¦ ¦ ¦ ¦ ¦ (1)¦ ¦ ¦ ¦ ¦ ¦ +-DSNUM----integer------+ ¦ ¦ ¦ ¦ <------------------------------------------------+ ¦ ¦ ¦ +-----ts-name spec------------------------------------FILTERDDN(ddname)--data-set spec-+ ¦ ¦ +-index-name spec-+ ¦ +-ALL--------+ ¦ ¦ ¦ ¦ ¦ (1)¦ ¦ ¦ ¦ +-DSNUM----integer------+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------------------------->< ¦ ¦ ¦ +-REFERENCE-+ ¦ ¦ ¦ +-SHRLEVEL----CHANGE------+ ¦ ¦ ¦ ¦ Note: ¦ ¦ (1) Not valid for nonpartitioning indexes. ¦ ¦ ¦ ¦ ¦ +--------------------------------------------------------------------------------------------------------------------------------+ +--------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ table-space spec: ¦ ¦ ¦ ¦ >--ts-name spec--------------------------data-set spec-----------------------------------------> ¦ ¦ ¦ +-ALL-----+ ¦ ¦ ¦ +-DSNUM----integer---+ ¦ ¦ ¦ ¦ >----------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-YES-+ ¦ ¦ ¦ +-FULL----NO---------------------------------------------------------------¦ ¦ ¦ +-CHANGELIMIT--------------------------------------------------------------+ ¦ ¦ +-(percent_value1-----------------------)-+ +-REPORTONLY-+ ¦ ¦ +-,--percent_value2-+ ¦ ¦ ¦ +--------------------------------------------------------------------------------------------------+ ¦ ts-name spec: ¦ ¦ ¦ ¦ >--TABLESPACE----------------------table-space-name------------------> ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +------------------------------------------------------------------------+ ¦ index-name spec: ¦ ¦ ¦ ¦ (1) ¦ ¦ >----INDEXSPACE----------------------index-space-name----------------> ¦ ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +-INDEX-------------------index-name---------------+ ¦ ¦ +-creator-id.-+ ¦ ¦ ¦ ¦ Note: ¦ ¦ (1) INDEXSPACE is the preferred specification. ¦ ¦ ¦ ¦ ¦ +------------------------------------------------------------------------+ +--------------------------------------------------------------------------------------------------+ ¦ data-set spec: ¦ ¦ ¦ ¦ (1) ¦ ¦ >----------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-SYSCOPY-+ ¦ +-RECOVERYDDN(ddname3--------------)-+ ¦ ¦ +-COPYDDN(-----ddname1-------------------)-+ +-,ddname4-+ ¦ ¦ ¦ +-,ddname2-+ ¦ ¦ ¦ +-,ddname2------------------+ ¦ ¦ ¦ ¦ Note: ¦ ¦ (1) If you specified a list of objects, only one object in the list can use the default ¦ ¦ (SYSCOPY); you must specify the DD names for the rest of the objects listed. ¦ +--------------------------------------------------------------------------------------------------+
Use LOAD to load one or more tables of a table space. LOAD loads records into the tables and builds or extends any indexes defined on them. If the table space already contains data, you can choose whether you want to add the new data to the existing data or replace the existing data. The loaded data is processed by any edit or validation routine associated with the table, and any field procedure associated with any column of the table.
+------------------------------------------------------------------------------------------------------------+
¦ ¦
¦ +-DATA-+ ¦
¦ >>--LOAD-------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-SYSREC-+ ¦ +-PREFORMAT-+ ¦
¦ +-INDDN---ddname----+ ¦
¦ ¦
¦ +-RESUME--NO-+ ¦
¦ ¦--------------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-REPLACE------------------------------------+ +-statistics-spec-+ ¦ ¦
¦ ¦ +-KEEPDICTIONARY-+ +-copy spec-+ ¦ ¦
¦ +-RESUME--YES---------------------------------------------------------------------------+ ¦
¦ ¦
¦ +-LOG YES-----------------+ ¦
¦ >-------------+-------------------------+----------------------------------------------------------------> ¦
¦ +-REUSE-+ +-LOG--NO-----------------+ ¦ +-SYSUT1--+ +-,SORTOUT-+ ¦ ¦
¦ +-NOCOPYPEND-+ +-WORKDDN(---ddname1----+----------+-)-+ ¦
¦ +-,ddname2-+ ¦
¦ ¦
¦ +-FLOAT(S390)-+ +-EBCDIC-+ ¦
¦ >-------------------------------------------------------+-------------+--+--------+----------------------> ¦
¦ +-0-------+ ¦ +-FORMAT---UNLOAD----+ +-FLOAT(IEEE)-+ +-ASCII--+ ¦
¦ +--SORTKEYS--+---------+--+ +-SQL/DS-+ ¦
¦ +-integer-+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ ¦ <-,-----+ ¦ +-NOSUBS-+ ¦ +-CONSTRAINTS-+ ¦ ¦ +-SYSERR-+ ¦ ¦
¦ +-CCSID(--integer--)-+ +-ENFORCE---NO-------------+ +-ERRDDN---ddname----+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-SYSMAP-+ ¦ ¦ +-SYSDISC-+ ¦ ¦ +-0-------+ ¦ ¦
¦ +-MAPDDN---ddname----+ +-DISCARDDN---ddname-----+ +-DISCARDS---integer----+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ +-SORTDEVT--device-type-+ +-SORTNUM--integer-+ ¦
¦ ¦
¦ <-----------------+ ¦
¦ >--------------------------------------------------------------into table spec-------------------------->< ¦
¦ +-CONTINUEIF(start----------)=---X'byte-string'--------+ ¦
¦ +-:end-+ +-'character-string'-+ ¦
¦ ¦
+------------------------------------------------------------------------------------------------------------+
¦ statistics-spec: ¦
¦ ¦
¦ >--STATISTICS--------------------------------------------------------------------------------------------> ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-(--ALL--)-+ ¦ ¦
¦ +-TABLE---------------------------------------------------------------------------------------¦ ¦
¦ ¦ +-SAMPLE--integer-+ ¦ ¦
¦ ¦ <-----------------------------------------------------------------------------------------+ ¦ ¦
¦ +---TABLE--(--table-name--)-------------------------------------------------------------------+ ¦
¦ +-SAMPLE--integer-+ +-COLUMN--------------------------------+ ¦
¦ ¦ +-ALL-------------+ ¦ ¦
¦ ¦ ¦ <-,-----------+ ¦ ¦ ¦
¦ +-(------column-name------)-+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ +-INDEX-----------------------------------------------------------+ ¦ +-NO--+ ¦ ¦
¦ ¦ +-ALL----------------------------------------+ ¦ +-REPORT----YES---+ ¦
¦ ¦ ¦ +-correlation-stats-spec-+ ¦ ¦ ¦
¦ ¦ ¦ <-,--------------------------------------+ ¦ ¦ ¦
¦ +-(------index-name----------------------------------)-+ ¦
¦ +-correlation-stats-spec-+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-ALL--------+ ¦ ¦
¦ +-UPDATE--+-ACCESSPATH-+-+ ¦
¦ +-SPACE------¦ ¦
¦ +-NONE-------+ ¦
¦ ¦
+------------------------------------------------------------------------------------------------------------+
¦ correlation-stats-spec: ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ +-KEYCARD-+ ¦ <---------------------------------------------------+ ¦ ¦
¦ ¦ +-1-------+ +-10------+ ¦ ¦ ¦
¦ +---FREQVAL--NUMCOLS----integer----COUNT----integer-----+ ¦
¦
+------------------------------------------------------------------------------------------------------------+
¦ copy spec: ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-SYSCOPY-+ ¦ +-RECOVERYDDN(ddname3--------------)-+ ¦
¦ +-COPYDDN(-----ddname1-------------------)-+ +-,ddname4-+ ¦
¦ ¦ +-,ddname2-+ ¦ ¦
¦ +-,ddname2------------------+ ¦
¦ ¦
+------------------------------------------------------------------------------------------------------------+
+--------------------------------------------------------------------------------------------------------------------------------+
¦ ¦
¦ INTO TABLE spec: ¦
¦ ¦
¦ >--INTO--TABLE--table-name---------------------------------------------------------------------------------------------------> ¦
¦ +-PART--integer-----------------resume spec-+ +-WHEN---SQL/DS='table-name'----------+ ¦
¦ +-PREFORMAT-+ +-field selection criterion-+ ¦
¦ ¦
>----------------------------------------------------------------------------------------------------------------------------> ¦
¦ ¦ <-,-------------------+ ¦ ¦
¦ +-(---field specification---)-+ ¦
¦ resume spec: ¦
¦ +-RESUME--NO-+ ¦
¦ >----------------------------------------------------------------------------------------------------------------------------> ¦
¦ ¦ +-REPLACE-----------------------------------------------+ ¦ ¦
¦ ¦ +-KEEPDICTIONARY-+ +-REUSE-+ +-copy spec-+ ¦ ¦
¦ +-RESUME--YES-----------------------------------------------------------------+ ¦
¦ field selection criterion: ¦
¦ ¦
¦ >----field-name-----------=----X'byte-string'--------------------------------------------------------------------------------> ¦
¦ +-(start----------)-+ +-'character-string'-¦ ¦
¦ +-:end-+ +-G'graphic-string'--¦ ¦
¦ +-N'graphic-string'--+ ¦
¦ field specification: ¦
¦ ¦
¦ >--field-name----------------------------------------------------------------------------------------------------------------> ¦
¦ +-POSITION(start----------)-+ +-CHAR--------------------------------------------------¦ ¦
¦ +-:end-+ ¦ +-MIXED-+ +-(length)-+ ¦ ¦
¦ +-VARCHAR-----------------------------------------------¦ ¦
¦ ¦ +-MIXED-+ ¦ ¦
¦ +-GRAPHIC-----------------------------------------------¦ ¦
¦ ¦ +-EXTERNAL-+ +-(length)-+ ¦ ¦
¦ +-VARGRAPHIC--------------------------------------------¦ ¦
¦ +-SMALLINT----------------------------------------------¦ ¦
¦ +-INTEGER-----------------------------------------------¦ ¦
¦ ¦ +-EXTERNAL---------------+ ¦ ¦
¦ ¦ +-(length)-+ ¦ ¦
¦ +-DECIMAL-----------------------------------------------¦ ¦
¦ ¦ +-PACKED-----------------------------------¦ ¦ ¦
¦ ¦ +-ZONED------------------------------------¦ ¦ ¦
¦ ¦ +-EXTERNAL---------------------------------+ ¦ ¦
¦ ¦ +-(length------------------)-+ ¦ ¦
¦ ¦ ¦ +-0-----+ ¦ ¦ ¦
¦ ¦ +-,---scale----+ ¦ ¦
¦ +-FLOAT-------------------------------------------------¦ ¦
¦ ¦ +-EXTERNAL-+ +-(length)-+ ¦ ¦
¦ +-DATE--EXTERNAL----------------------------------------¦ ¦
¦ ¦ +-(length)-+ ¦ ¦
¦ +-TIME--EXTERNAL----------------------------------------¦ ¦
¦ ¦ +-(length)-+ ¦ ¦
+-TIMESTAMP--EXTERNAL-----------------------------------¦ ¦
¦ ¦ +-(length)-+ ¦ ¦
¦ +-ROWID-------------------------------------------------¦ ¦
¦ +-BLOB--------------------------------------------------¦ ¦
¦ +-CLOB--------------------------------------------------¦ ¦
¦ ¦ +-MIXED-+ ¦ ¦
¦ +-DBCLOB------------------------------------------------+ ¦
¦ ¦
¦ >----------------------------------------------------------------------------------------------------------------------------> ¦
+-NULLIF--field selection criterion----¦ ¦
¦ +-DEFAULTIF--field selection criterion-+ ¦
¦ ¦
+--------------------------------------------------------------------------------------------------------------------------------+
The MERGECOPY online utility merges image copies produced by the COPY utility or inline copies produced by the LOAD or REORG utilities. It can merge several incremental copies of a table space to make one incremental copy. It can also merge incremental copies with a full image copy to make a new full image copy.
+------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ >>--MERGECOPY--TABLESPACE----------------------table-space-name------> ¦ ¦ +-database-name.-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------> ¦ ¦ ¦ +-SYSUT1-+ ¦ ¦ +-ALL-----+ ¦ ¦ ¦ +-WORKDDN---ddname----+ +-DSNUM---integer----+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------->< ¦ ¦ ¦ +-NO-+ +-copyddn spec-----+ ¦ ¦ ¦ +-NEWCOPY--------------recoveryddn spec--------------------+ ¦ ¦ +-YES-----------------------------------------+ ¦ ¦ +-copyddn spec-+ +-recoveryddn spec-+ ¦ ¦ ¦ +------------------------------------------------------------------------+ ¦ copyddn spec: ¦ ¦ ¦ ¦ +-SYSCOPY-+ ¦ ¦ >--COPYDDN--(------ddname1--------------------)----------------------> ¦ ¦ ¦ +-,ddname2-+ ¦ ¦ ¦ +-,ddname2------------------+ ¦ ¦ recoveryddn spec: ¦ ¦ ¦ ¦ >--RECOVERYDDN--(--ddname3----------------)--------------------------> ¦ ¦ +-,ddname4-+ ¦ ¦ ¦ +------------------------------------------------------------------------+
The MODIFY online utility with the RECOVERY option deletes records from the SYSIBM.SYSCOPY catalog table, related log records from the SYSIBM.SYSLGRNX directory table, and entries from the DBD. You can remove records that were written before a specific date or you can remove records of a specific age. You can delete records for an entire table space, partition, or data set.
You should run MODIFY regularly to clear outdated information from SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX. These tables, and particularly SYSIBM.SYSLGRNX, can become very large and take up considerable amounts of space. By deleting outdated information from these tables, you can help improve performance for processes that access data from these tables.
+------------------------------------------------------------------------+ ¦ ¦ ¦ >>--MODIFY--RECOVERY--TABLESPACE-------------------------------------> ¦ ¦ ¦ ¦ >----------------------table-space-name------------------------------> ¦ ¦ +-database-name.-+ ¦ +-ALL-----+ ¦ ¦ ¦ +-DSNUM---integer----+ ¦ ¦ ¦ ¦ >--DELETE---AGE---integer------------------------------------------->< ¦ ¦ ¦ +-(*)-----+ ¦ ¦ ¦ +-DATE---integer----+ ¦ ¦ +-(*)-----+ ¦ ¦ ¦ +------------------------------------------------------------------------+
The QUIESCE online utility establishes a quiesce point (the current log RBA or log record sequence number (LRSN)) for a table space, partition, table space set, or list of table spaces and table space sets, and records it in the SYSIBM.SYSCOPY catalog table. A successful QUIESCE improves the probability of a successful RECOVER or COPY. You should run QUIESCE frequently between regular executions of COPY to establish regular recovery points for future point in time recovery.
+------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ <---------------------------------------------------------------------+ ¦ ¦ >>--QUIESCE-----TABLESPACE--------------------table-space-name-------------------------------------------> ¦ ¦ ¦ +-database-name.-+ +-PART--integer-+ ¦ ¦ ¦ +-TABLESPACESET------------------------------------table-space-name-+ ¦ ¦ +-TABLESPACE-+ +-database-name.-+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------->< ¦ ¦ ¦ +-YES-+ ¦ ¦ ¦ +-WRITE---NO-----+ ¦ ¦ ¦ +------------------------------------------------------------------------------------------------------------+
REBUILD INDEX reconstructs indexes from the table that they reference.
+------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ <-,-----------------------------+ ¦ ¦ >>--REBUILD--INDEX----(---index-name----------------------)----------------------------------------------> ¦ ¦ ¦ +-PART--integer-+ ¦ ¦ ¦ +-(ALL)--TABLESPACE----------------------table-space-name--------------------+ ¦ ¦ +-database-name.-+ +-PART--integer-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ +-REUSE-+ ¦ +-SYSUT1-+ ¦ +-SORTDEVT--device-type-+ +-SORTNUM--integer-+ ¦ ¦ +-WORKDDN(---ddname---)-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ +-SORTKEYS-+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------->< ¦ ¦ +-STATISTICS------------------------------------------------------------------------------+ ¦ ¦ ¦ +-NO--+ ¦ ¦ +-ALL--------+ ¦ +-correlation-stats-spec-+ ¦ ¦ +-REPORT----YES---+ +-UPDATE--+-ACCESSPATH-+-+ ¦ ¦ +-SPACE------¦ ¦ ¦ +-NONE-------+ ¦ ¦ correlation-stats-spec: ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ +-KEYCARD-+ ¦ <---------------------------------------------------+ ¦ ¦ ¦ ¦ +-1-------+ +-10------+ ¦ ¦ ¦ +---FREQVAL--NUMCOLS----integer----COUNT----integer-----+ ¦ ¦ ¦ +------------------------------------------------------------------------------------------------------------+
The RECOVER online utility recovers data to the current state or to a previous point in time by restoring a copy, then applying log records.
The largest unit of data recovery is the table space or index space; the smallest is the page.
+----------------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ >>--RECOVER--------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ ¦ <---------------------------------+ ¦ ¦ >------object------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ ¦ +-ALL--------+ ¦ +-TORBA--X'byte-string'------¦ ¦ +-REUSE-+ +-parallel spec-+ ¦ ¦ ¦ ¦ ¦ ¦ ¦ (1)¦ ¦ +-TOLOGPOINT--X'byte-string'-+ +-LOGONLY----------------------+ ¦ ¦ ¦ ¦ +-DSNUM---integer-------+ ¦ ¦ ¦ +-object---------------------------recover options spec---------------------------------------------------¦ ¦ ¦ ¦ ¦ +-ALL--------+ ¦ ¦ ¦ ¦ ¦ ¦ ¦ (1)¦ ¦ ¦ ¦ ¦ ¦ +-DSNUM---integer-------+ ¦ ¦ ¦ +-object--PAGE--page-number-------------------------------------------------------------------------------+ ¦ ¦ +-CONTINUE-+ ¦ ¦ ¦ ¦ >----------------------------------------------------------------------------------------------------------------->< ¦ ¦ +-LOCALSITE----¦ ¦ ¦ +-RECOVERYSITE-+ ¦ ¦ ¦ ¦ Note: ¦ ¦ (1) Not valid for nonpartitioning indexes. ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+ +------------------------------------------------------------------------------+ ¦ ¦ ¦ object: ¦ ¦ ¦ ¦ >----TABLESPACE----------------------table-space-name----------------------> ¦ ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +-INDEXSPACE----------------------index-space-name-¦ ¦ ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +-INDEX-------------------index-name---------------+ ¦ ¦ +-creator-id.-+ ¦ ¦ ¦ +------------------------------------------------------------------------------+ ¦ parallel spec: ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------> ¦ ¦ +-PARALLEL------------------------+ ¦ ¦ +-(--num-objects--)-+ ¦ ¦ ¦ +------------------------------------------------------------------------------+ ¦ recover options spec: ¦ ¦ ¦ ¦ >----TOCOPY--data-set-name---------------------------------------------------------------------> ¦ ¦ ¦ +-TOVOLUME---CATALOG--------------------------+ +-REUSE-+ ¦ ¦ ¦ ¦ +-vol-ser-----------------------+ ¦ ¦ ¦ ¦ +-TOSEQNO--integer-+ ¦ ¦ ¦ +-ERROR--RANGE----------------------------------------------------------------------+ ¦ ¦ ¦ +--------------------------------------------------------------------------------------------------+
The REORG INDEX utility reorganizes an index space to improve access performance and reclaim fragmented space. You can specify the degree of access to your data during reorganization, and collect inline statistics using the STATISTICS keyword.
+------------------------------------------------------------------------------+
¦ ¦
¦ ¦
¦ >>--REORG--INDEX--index-name-----------------------------------------------> ¦
¦ +-REUSE-+ +-PART--integer-+ ¦
¦ ¦
¦ +-SHRLEVEL NONE------------------------------------+ ¦
¦ >--+--------------------------------------------------+--------------------> ¦
¦ +-SHRLEVEL---REFERENCE-deadline spec---------------+ ¦
¦ +-CHANGE-deadline spec--change spec--+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------> ¦
¦ ¦ +-200-----+ ¦ ¦
¦ +-LEAFDISTLIMIT-+---------+------------------+ ¦
¦ +-integer-+ +-REPORTONLY-+ ¦
¦ ¦
¦ >--------------------------------------------------------------------------> ¦
¦ ¦ +-CONTINUE-+ ¦ ¦
¦ +-UNLOAD-----PAUSE-------statistics-spec----+ ¦
¦ +-ONLY--------------------------+ ¦
¦ ¦
¦ >------------------------------------------------------------------------->< ¦
¦ ¦ +-SYSUT1-+ ¦ +-PREFORMAT-+ ¦
¦ +-WORKDDN(---ddname---)-+ ¦
¦ ¦
+------------------------------------------------------------------------------+
¦ deadline spec: ¦
¦ ¦
¦ >--------------------------------------------------------------------> ¦
¦ ¦ +-NONE------------------------+ ¦ ¦
¦ +-DEADLINE-+-timestamp-------------------+--+ ¦
¦ +-labeled-duration-expression-+ ¦
¦ change spec: ¦
¦ ¦
¦ >--------------------------------------------------------------------> ¦
¦ ¦ +-300-----+ ¦ ¦ +-WRITERS-+ ¦ ¦
¦ +-MAXRO-+-integer-+--+ +-DRAIN---ALL--------+ ¦
¦ +-DEFER---+ ¦
¦ ¦
¦ >--------------------------------------------------------------------> ¦
¦ ¦ +-CONTINUE-+ ¦ ¦ +-1200----+ ¦ ¦
¦ +-LONGLOG-+-TERM-----+--+ +-DELAY---integer----+ ¦
¦ +-DRAIN----+ ¦
¦ ¦
¦ >--------------------------------------------------------------------> ¦
¦ ¦ +-ABEND-+ ¦ ¦
¦ +-TIMEOUT---TERM-----+ ¦
¦ ¦
+------------------------------------------------------------------------+
¦ labeled-duration-expression: ¦
¦ ¦
¦ <-------------------------------------+ ¦
¦ >----CURRENT DATE------------- + ----constant----YEAR--------------------------------> ¦
¦ +-CURRENT TIMESTAMP-+ +- - -+ +-YEARS--------¦ ¦
¦ +-MONTH--------¦ ¦
¦ +-MONTHS-------¦ ¦
¦ +-DAY----------¦ ¦
¦ +-DAYS---------¦ ¦
¦ +-HOUR---------¦ ¦
¦ +-HOURS--------¦ ¦
¦ +-MINUTE-------¦ ¦
¦ +-MINUTES------¦ ¦
¦ +-SECOND-------¦ ¦
¦ +-SECONDS------¦ ¦
¦ +-MICROSECOND--¦ ¦
¦ +-MICROSECONDS-+ ¦
¦ ¦
+----------------------------------------------------------------------------------------+
¦ statistics-spec: ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
¦ +-STATISTICS------------------------------------------------------------------------------+ ¦
¦ ¦ +-NO--+ ¦ ¦ +-ALL--------+ ¦ +-correlation-stats-spec-+ ¦
¦ +-REPORT----YES---+ +-UPDATE--+-ACCESSPATH-+-+ ¦
¦ +-SPACE------¦ ¦
¦ +-NONE-------+ ¦
¦ correlation-stats-spec: ¦
¦ ¦
¦ >--------------------------------------------------------------------------------------------------------> ¦
+-KEYCARD-+ ¦ <---------------------------------------------------+ ¦ ¦
¦ ¦ +-1-------+ +-10------+ ¦ ¦ ¦
¦ +---FREQVAL--NUMCOLS----integer----COUNT----integer-----+ ¦
¦ ¦
+------------------------------------------------------------------------------------------------------------+
The REORG TABLESPACE utility reorganizes a table space to improve access performance and reclaim fragmented space. In addition, the utility can reorganize a single partition or range of partitions of a partitioned table space. You can specify the degree of access to your data during reorganization, and collect inline statistics using the STATISTICS keyword. If you specify REORG TABLESPACE UNLOAD EXTERNAL, the data is unloaded in a format that is acceptable to the LOAD utility of any DB2 subsystem. You can also delete rows during the REORG by specifying the DISCARD option.
+----------------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ >>--REORG--TABLESPACE----------------------table-space-name--------------------------------------------------------> ¦ ¦ +-database-name.-+ +-REUSE-+ +-PART---integer--------------+ ¦ ¦ +-integer1:integer2-+ ¦ ¦ ¦ ¦ +-LOG YES-+ ¦ ¦ >--+---------+------------------------------------------------copy spec--------------------------------------------> ¦ ¦ +-LOG NO--+ +-SORTDATA-+ +--NOSYSREC--+ +--SORTKEYS--+ ¦ ¦ ¦ ¦ +-SHRLEVEL NONE--------------------------------------+ ¦ ¦ >--+----------------------------------------------------+----------------------------------------------------------> ¦ ¦ +--SHRLEVEL----REFERENCE--DEADLINE spec--------------+ ¦ ¦ +-CHANGE--DEADLINE spec--change spec-+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-10------+ +-10------+ ¦ ¦ ¦ +--OFFPOSLIMIT--+---------+--INDREFLIMIT--+---------+------------------+ ¦ ¦ +-integer-+ +-integer-+ +-REPORTONLY-+ ¦ ¦ ¦ ¦ +-UNLOAD CONTINUE-+ ¦ ¦ >----+-----------------+-------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-UNLOAD PAUSE----+ +-KEEPDICTIONARY-+ +-statistics-spec-+ ¦ ¦ +-SYSPUNCH-+ ¦ ¦ ¦ +-UNLOAD ONLY--------------------------------------------------¦ +-PUNCHDDN----ddname-----+ ¦ ¦ +-UNLOAD EXTERNAL----------------------------------------------+ ¦ ¦ +-NOPAD-+ ¦ <-----------------+ ¦ ¦ ¦ +---FROM TABLE spec---+ ¦ ¦ ¦ ¦ >----------------------------------------------------------------------------------------------------------------->< ¦ ¦ ¦ +-SYSDISC-+ ¦ +-reorg tablespace options-+ ¦ <-----------------+ ¦ ¦ ¦ +-DISCARDDN----ddname----+ +-DISCARD---------------FROM TABLE spec---+ ¦ ¦ +-NOPAD-+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+ ¦ copy spec: ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-SYSCOPY-+ ¦ +-RECOVERYDDN(ddname3--------------)-+ ¦ ¦ +-COPYDDN(-----ddname1-------------------)-+ +-,ddname4-+ ¦ ¦ ¦ +-,ddname2-+ ¦ ¦ ¦ +-,ddname2------------------+ ¦ ¦ ¦ +------------------------------------------------------------------------------------------------------------+ ¦ DEADLINE spec: ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-NONE------------------------+ ¦ ¦ ¦ +-DEADLINE-+-timestamp-------------------+--+ ¦ ¦ +-labeled-duration-expression-+ ¦ ¦ change spec: ¦ ¦ ¦ ¦ >--MAPPINGTABLE--table-name----------------------------------------------------------------------------------------> ¦ ¦ ¦ +-300-----+ ¦ ¦ +-WRITERS-+ ¦ ¦ +-CONTINUE-+ ¦ ¦ ¦ +-MAXRO-+-integer-+--+ +-DRAIN---ALL--------+ +-LONGLOG-+-TERM-----+--+ ¦ ¦ +-DEFER---+ +-DRAIN----+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-1200----+ ¦ ¦ +-ABEND-+ ¦ ¦ ¦ +-DELAY---integer----+ +-TIMEOUT---TERM-----+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+ ¦ labeled-duration-expression: ¦ ¦ ¦ ¦ <-------------------------------------+ ¦ ¦ >----CURRENT DATE------------- + ----constant----YEAR--------------------------------------------------------------> ¦ ¦ +-CURRENT TIMESTAMP-+ +- - -+ +-YEARS--------¦ ¦ ¦ +-MONTH--------¦ ¦ ¦ +-MONTHS-------¦ ¦ ¦ +-DAY----------¦ ¦ ¦ +-DAYS---------¦ ¦ ¦ +-HOUR---------¦ ¦ ¦ +-HOURS--------¦ ¦ ¦ +-MINUTE-------¦ ¦ ¦ +-MINUTES------¦ ¦ ¦ +-SECOND-------¦ ¦ ¦ +-SECONDS------¦ ¦ ¦ +-MICROSECOND--¦ ¦ ¦ +-MICROSECONDS-+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+ ¦ statistics-spec: ¦ ¦ ¦ ¦ >--STATISTICS------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-(--ALL--)-+ ¦ ¦ ¦ +-TABLE---------------------------------------------------------------------------------------¦ ¦ ¦ ¦ +-SAMPLE--integer-+ ¦ ¦ ¦ ¦ <-----------------------------------------------------------------------------------------+ ¦ ¦ ¦ +---TABLE--(--table-name--)-------------------------------------------------------------------+ ¦ ¦ +-SAMPLE--integer-+ +-COLUMN--------------------------------+ ¦ ¦ ¦ +-ALL-------------+ ¦ ¦ ¦ ¦ ¦ <-,-----------+ ¦ ¦ ¦ ¦ +-(------column-name------)-+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ +-INDEX-----------------------------------------------------------+ ¦ +-NO--+ ¦ ¦ ¦ ¦ +-ALL----------------------------------------+ ¦ +-REPORT----YES---+ ¦ ¦ ¦ ¦ +-correlation-stats-spec-+ ¦ ¦ ¦ ¦ ¦ ¦ <-,--------------------------------------+ ¦ ¦ ¦ ¦ +-(------index-name----------------------------------)-+ ¦ ¦ +-correlation-stats-spec-+ ¦ ¦ ¦ ¦ >-----------------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-ALL--------+ ¦ ¦ ¦ +-UPDATE--+-ACCESSPATH-+-+ ¦ ¦ +-SPACE------¦ ¦ ¦ +-NONE-------+ ¦ ¦ correlation-stats-spec: ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ +-KEYCARD-+ ¦ <---------------------------------------------------+ ¦ ¦ ¦ ¦ +-1-------+ +-10------+ ¦ ¦ ¦ ¦ +---FREQVAL--NUMCOLS----integer----COUNT----integer-----+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+ ¦ FROM TABLE spec: ¦ ¦ ¦ ¦ >--FROM--TABLE--table-name-----------------------------------------------------------------------------------------> ¦ ¦ +-WHEN--(--selection condition spec--)-+ ¦ ¦ selection condition spec: ¦ ¦ ¦ ¦ <------------------------------------------+ ¦ ¦ >----predicate-----------------------------------------------------------------------------------------------------> ¦ ¦ +-(--selection condition--)-+ +---AND------predicate-------------------+ ¦ ¦ +-OR--+ +-(--selection condition--)-+ ¦ ¦ predicate: ¦ ¦ ¦ ¦ >----basic predicate-----------------------------------------------------------------------------------------------> ¦ ¦ +-BETWEEN predicate-¦ ¦ ¦ +-IN predicate------¦ ¦ ¦ +-LIKE predicate----¦ ¦ ¦ +-NULL predicate----+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+ ¦ basic predicate: ¦ ¦ ¦ ¦ Note: An exclamation point (!) is supported in place of the not symbol (¬). Therefore: ¦ ¦ != is equivalent to ¬= ¦ ¦ !> is equivalent to ¬> ¦ ¦ !< is equivalent to ¬< ¦ ¦ ¦ ¦ ¦ ¦ >--column-name---- = -------constant-------------------------------------------------------------------------------> ¦ ¦ +- <> -¦ +-labeled-duration-expression-+ ¦ ¦ +- > --¦ ¦ ¦ +- < --¦ ¦ ¦ +- >= -¦ ¦ ¦ +- <= -¦ ¦ ¦ +- ¬= -¦ ¦ ¦ +- ¬> -¦ ¦ ¦ +- ¬< -+ ¦ ¦ BETWEEN predicate: ¦ ¦ ¦ ¦ >--column-name-----------BETWEEN----constant-----------------------AND----constant---------------------------------> ¦ ¦ +-NOT-+ +-labeled-duration-expression-+ +-labeled-duration-expression-+ ¦ ¦ IN predicate: ¦ ¦ ¦ ¦ <-,--------+ ¦ ¦ >--column-name-----------IN--(---constant---)----------------------------------------------------------------------> ¦ ¦ +-NOT-+ ¦ ¦ LIKE predicate: ¦ ¦ ¦ ¦ >--column-name-----------LIKE--string-constant---------------------------------------------------------------------> ¦ ¦ +-NOT-+ +-ESCAPE--string-constant-+ ¦ ¦ NULL predicate: ¦ ¦ ¦ ¦ >--column-name--IS-----------NULL----------------------------------------------------------------------------------> ¦ ¦ +-NOT-+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+
The REPAIR online utility repairs data. The data can be your own data, or data you would not normally access, such as space map pages and index entries.
REPAIR is intended as a means of replacing invalid data with valid data. Be extremely careful using REPAIR.
Improper use can damage the data even further.
SEE DOC IBM.........................
The REPAIR online utility repairs data. The data can be your own data, or data you would not normally access, such as space map pages and index entries.
REPAIR is intended as a means of replacing invalid data with valid data. Be extremely careful using REPAIR.
Improper use can damage the data even further.
+----------------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ >>--REPORT----RECOVERY----TABLESPACE--table space spec------------------------------------------------------------>< ¦ ¦ ¦ ¦ ¦ +-INDEX NONE-+ ¦ ¦ +-info options-+ ¦ ¦ ¦ ¦ ¦ +---INDEX ALL----+ ¦ ¦ ¦ ¦ ¦ +-index spec---------------------------------------+ ¦ ¦ ¦ +-TABLESPACESET------------------table space spec----------------------------------+ ¦ ¦ +-TABLESPACE-+ ¦ ¦ table space spec: ¦ ¦ ¦ ¦ >----------------------table-space-name----------------------------------------------------------------------------> ¦ ¦ +-database-name.-+ ¦ ¦ index spec: ¦ ¦ ¦ ¦ >----INDEXSPACE----------------------index-space-name--------------------------------------------------------------> ¦ ¦ ¦ +-database-name.-+ ¦ ¦ ¦ +-INDEX-------------------index-name---------------+ ¦ ¦ +-creator-id.-+ ¦ ¦ info options: ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ +-ALL-----+ ¦ +-CURRENT-+ +-SUMMARY-+ +-LOCALSITE-+ +-RECOVERYSITE-+ ¦ +-1---+ ¦ ¦ ¦ +-DSNUM---integer----+ +-ARCHLOG--+-2---+-+ ¦ ¦ +-ALL-+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+
The RUNSTATS online utility gathers summary information about the characteristics of data in table spaces, indexes, and partitions. DB2 records this information in the DB2 catalog and uses it to select access paths to data during the bind process. It is available to the database administrator for evaluating database design and to aid in determining when table spaces or indexes must be reorganized.
Use the STATISTICS keyword with LOAD, REBUILD INDEX, and REORG jobs to eliminate the need to execute RUNSTATS for updating catalog statistics
+------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ >>--RUNSTATS--TABLESPACE----------------------table-space-name-------------------------------------------> ¦ ¦ +-database-name.-+ +-PART--integer-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ ¦ (1) +-ALL-+ ¦ ¦ ¦ +-TABLE---(---------)------------------------------------------------------------------------¦ ¦ ¦ ¦ ¦ +-25------+ ¦ ¦ ¦ ¦ ¦ +--SAMPLE----integer----+ ¦ ¦ ¦ ¦ <----------------------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ (1) ¦ ¦ ¦ ¦ +--TABLE(table-name)-------------------------------------------------------------------------+ ¦ ¦ ¦ +-25------+ ¦ +-COLUMN------------------------------+ ¦ ¦ +--SAMPLE----integer----+ ¦ +-ALL-------------+ ¦ ¦ ¦ ¦ ¦ <-,-----------+ ¦ ¦ ¦ ¦ +-(-----column-name-----)-+ ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ +-INDEX--------------------------------------------------------------------------+ ¦ ¦ ¦ +-ALL---------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ +-correlation-stats-spec-+ ¦ ¦ ¦ ¦ ¦ ¦ <-,-------------------------------------------------------+ ¦ ¦ ¦ ¦ +-(----index-name---------------------------------------------------)-+ ¦ ¦ +-PART--integer-+ +-correlation-stats-spec-+ ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------->< ¦ ¦ ¦ +-REFERENCE-+ ¦ ¦ +-NO--+ ¦ ¦ +-ALL--------+ ¦ ¦ ¦ +-SHRLEVEL---CHANGE-------+ +-REPORT---YES----+ +-UPDATE-+-ACCESSPATH-+--+ ¦ ¦ +-SPACE------¦ ¦ ¦ +-NONE-------+ ¦ ¦ ¦ ¦ Note: ¦ ¦ (1) The TABLE keyword is not valid for a LOB table space. ¦ ¦ ¦ ¦ correlation-stats-spec ¦ ¦ ¦ ¦ >--------------------------------------------------------------------------------------------------------> ¦ ¦ +-KEYCARD-+ ¦ <---------------------------------------------------+ ¦ ¦ ¦ ¦ +-1-------+ +-10------+ ¦ ¦ ¦ ¦ +----FREQVAL--NUMCOLS----integer----COUNT----integer------+ ¦ ¦ ¦ +------------------------------------------------------------------------------------------------------------+
+----------------------------------------------------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ <-,---------------------------------------------------------+ ¦ ¦ >>--RUNSTATS--INDEX----(---index-name--------------------------------------------------)---------------------------> ¦ ¦ ¦ +-PART--integer-+ +-correlation-stats-spec-+ ¦ ¦ ¦ +-(-ALL-)TABLESPACE--------------------tablespace-name-----------------------------+ ¦ ¦ +-database-name.-+ +-correlation-stats-spec-+ ¦ ¦ ¦ ¦ >----------------------------------------------------------------------------------------------------------------->< ¦ ¦ ¦ +-REFERENCE-+ ¦ ¦ +-NO--+ ¦ ¦ +-ALL--------+ ¦ ¦ ¦ +-SHRLEVEL---CHANGE-------+ +-REPORT---YES----+ +-UPDATE-+-ACCESSPATH-+--+ ¦ ¦ +-SPACE------¦ ¦ ¦ +-NONE-------+ ¦ ¦ correlation-stats-spec: ¦ ¦ ¦ ¦ >------------------------------------------------------------------------------------------------------------------> ¦ ¦ +-KEYCARD-+ ¦ <---------------------------------------------------+ ¦ ¦ ¦ ¦ +-1-------+ +-10------+ ¦ ¦ ¦ ¦ +----FREQVAL--NUMCOLS----integer----COUNT----integer------+ ¦ ¦ ¦ +----------------------------------------------------------------------------------------------------------------------+
The STOSPACE online utility updates DB2 catalog columns that indicate how much space is allocated for storage groups and related table spaces and indexes.
+------------------------------------------------------------------------+ ¦ ¦ ¦ ¦ ¦ <-,-------------+ ¦ ¦ >>--STOSPACE--STOGROUP(-----stogroup-name-----)--------------------->< ¦ ¦ +-*-----------------+ ¦ ¦ ¦ +------------------------------------------------------------------------+