Reorg utility behavior in SHRLEVEL CHANGE and REFERENCE is very interesting.
To know how Reorg helps in maintaining the consistency in DB2 system is very important.
REORG is like running defragmentor in ur windows OS.To bring the data in a sorted manner and to reorganise the TS and IX in such a way that the fragmented space can be reclaimed and optimized for fast retrieval.
Data access during reorg 1) NONE 2) REFERENCE 3) CHANGE
REORG with SHRLEVEL REFERENCE -: Read Only access to the dataset is given.Any other application can read the data from that TS but cannot make any changes....How it is done?
DB2 creates a shadow copy of the original VSAM dataset (TS) and allows the other applications to read the data from the original dataset. DB2 reloads the reorganised data into the Shadow copy datasets.By the end of reorganization,DB2 switches the future access of the application from the original dataset to shadow copy.
While switching,the application does not hold any access on either dataset.
REORG with SHRLEVEL CHANGE -: DB2 reloads the reorganised data in the shadow copy datasets.In SHRLEVEL CHANGE comes the concept of Mapping Table.
When data is reloaded in the shadow dataset then the RID's of the rows in the original dataset changes.To keep the track of those RID's ie. for source and target(shadow copy) DB2 maintains a mapping table.
The structure is like -:
CREATE TABLE mapping-table-name1
(TYPE CHAR(1) NOT NULL,
SOURCE_RID CHAR(5) NOT NULL,
TARGET_XRID CHAR(9) NOT NULL,
LRSN CHAR(6) NOT NULL);
CREATE UNIQUE INDEX index-name1 ON table-name1
(SOURCE_RID ASC, TYPE, TARGET_XRID, LRSN);
For more understanding -:
1) The orginal tablespace is unloaded and the shadow copy is loaded.The indexes are built as the shadow copy is loaded.
2) The mapping table is maintained to map the position of each row(RID) in original TS and the shadow copy.
3) While the pages are loaded,they are written to the imagecopy datatset.
4) During all this time updating and reading was allowed and the changes were logged.
5) When the shadow copy is loaded,all the logged changes are copied to the shadow copy to bring it up to date .Information from the mapping table is used to update the correct rows.
6) Pages changed since original image copy was taken are added to copy dataset.
7) The old tablespaces and indexspaces datasets are then renamed and shadow copies are given the original name.
8) Finally the old objects are deleted.
Note -: If the datasets are not DB2 Managed then u need to preallocate shadow datasets before u execute REORG.
When preallocating the datasets define it as LINEAR and use the SHAREOPTIONS as (3,3).
Cheers!!
To know how Reorg helps in maintaining the consistency in DB2 system is very important.
REORG is like running defragmentor in ur windows OS.To bring the data in a sorted manner and to reorganise the TS and IX in such a way that the fragmented space can be reclaimed and optimized for fast retrieval.
Data access during reorg 1) NONE 2) REFERENCE 3) CHANGE
REORG with SHRLEVEL REFERENCE -: Read Only access to the dataset is given.Any other application can read the data from that TS but cannot make any changes....How it is done?
DB2 creates a shadow copy of the original VSAM dataset (TS) and allows the other applications to read the data from the original dataset. DB2 reloads the reorganised data into the Shadow copy datasets.By the end of reorganization,DB2 switches the future access of the application from the original dataset to shadow copy.
While switching,the application does not hold any access on either dataset.
REORG with SHRLEVEL CHANGE -: DB2 reloads the reorganised data in the shadow copy datasets.In SHRLEVEL CHANGE comes the concept of Mapping Table.
When data is reloaded in the shadow dataset then the RID's of the rows in the original dataset changes.To keep the track of those RID's ie. for source and target(shadow copy) DB2 maintains a mapping table.
The structure is like -:
CREATE TABLE mapping-table-name1
(TYPE CHAR(1) NOT NULL,
SOURCE_RID CHAR(5) NOT NULL,
TARGET_XRID CHAR(9) NOT NULL,
LRSN CHAR(6) NOT NULL);
CREATE UNIQUE INDEX index-name1 ON table-name1
(SOURCE_RID ASC, TYPE, TARGET_XRID, LRSN);
For more understanding -:
1) The orginal tablespace is unloaded and the shadow copy is loaded.The indexes are built as the shadow copy is loaded.
2) The mapping table is maintained to map the position of each row(RID) in original TS and the shadow copy.
3) While the pages are loaded,they are written to the imagecopy datatset.
4) During all this time updating and reading was allowed and the changes were logged.
5) When the shadow copy is loaded,all the logged changes are copied to the shadow copy to bring it up to date .Information from the mapping table is used to update the correct rows.
6) Pages changed since original image copy was taken are added to copy dataset.
7) The old tablespaces and indexspaces datasets are then renamed and shadow copies are given the original name.
8) Finally the old objects are deleted.
Note -: If the datasets are not DB2 Managed then u need to preallocate shadow datasets before u execute REORG.
When preallocating the datasets define it as LINEAR and use the SHAREOPTIONS as (3,3).
Cheers!!
good one, but may i know why the mapping table index is created in different sequence ? - Shiv
ReplyDeleteHi Shiv..
ReplyDeleteThanks!!
The order of columns in indexes defenitely affects the query performance.I'ld always prefer the column which is most frequently used, to be scanned through indexes.In case of Mapping table,every query must be dependent on source_rid.Suppose in a table (col1 col2 col3) i create index accross all columns.Then the queries that access col1 col2 col3 or just col1 or just col1 and col2 will be more efficient.But queries that access col2 and col3 or just col2 or just col3 will not use index.
easy to understand..thanks and post more topics
ReplyDeleteWhat is the situation that a online reorg with SHRLEVEL CHANGE will fail and the mapping table is populated? And how do you recover from the failure, i.e. how to get the data back in a consistent fashion after the failure. I am looking for how the mapping table will be used if the reorg failed especially during the switching mode.
ReplyDeleteThanks, meng