Maintaining a data warehouse is an ongoing task to meet users' needs or reflect changes to a database. For a database that does not change except when the entire database is loaded with new data, maintenance tasks are minimal. Only it is to be ensured that enough space is available in the default and named segments to accommodate the current batch of input data. Any needed restoration of the database can be done from the original input data files.
For databases that are modified by incremental load operations or INSERT, UPDATE, or DELETE statements, maintenance includes accommodating growth in the database, as well as adjusting to changes in users' needs or the warehouse environment. For databases that change, backing up the data regularly is an important part of maintenance.
A D V E R T I S E M E N T
The following steps can be adopted as the means of maintaining records in a data warehouse:
Locking Tables and Databases : To preserve consistency within a database, operations that modify data must be allowed to complete without interruption, blocking other read or write operations until the modification is complete. Locking is performed automatically by operations that require it for database consistency. For instance, the TMU automatically locks the tables and database as needed for its operations. Tables that are to be modified are locked with a write lock, and tables that are used for checking referential integrity are locked with a read lock. Other operations, such as cleaning the version log, require a manual lock.
Obtaining Information on Tables and Indexes : CHECK TABLE statement can be userd to
Obtain segment statistics for tables, including row counts
Check for damage to the table storage data structure
Repair damage to the table storage data structure
CHECK INDEX statement to obtain configuration and size information and to check an index for corruption.
Monitoring Growth of Tables and Indexes : If tables and their indexes grow in a database, groth must be accommodated. To prevent the database from running out of space at an inconvenient time, monitor the growth and compare the actual growth with the space available, adding new segments, PSUs, or both as needed. Error messages are issued when a segment is full.
If out-of-space error is received because a segment ran out of space, you can either:
* Specify a larger value for the MAXSIZE value of the last PSU with the ALTER SEGMENT...CHANGE MAXSIZE option * Add a new PSU to the segment with the ALTER SEGMENT...ADD STORAGE option
If no available file system contains additional space, you must make file system space available before you can add more data to the segment.
To monitor the growth of a table or index, use the information in the RBW_SEGMENTS table (TNAME or INAME, NPSUs, and TOTALFREE, rowcount columns) and RBW_STORAGE table (SEGNAME, MAXSIZE, and USED columns). Default segments grow as needed but are limited by file system space.
Altering Segments : Segments can be used to distribute data and indexes over multiple drives and to allow continued use of a table when some segments are offline or removed permanently.
Maintaining a Time-Cyclic Database : Time-cyclic database can be managed with the help of the following two techniques:
Rolling off old segments and reusing them with new data.
Creating new segments and adding them to the database.
Recovering a Damaged Segment : Occasionally an operation on a table or index fails with a message that a segment is damaged, or the ALTER SEGMENT...VERIFY statement might indicate that a segment is damaged. The damage is to a PSU within the segment, causing the PSU, the segment, and the table or index to be placed in a damaged state the first time that PSU is accessed.
A PSU is marked damaged (not intact) in the RBW_STORAGE system table for any of the following reasons:
* File cannot be found.
* Permissions are inadequate.
* I/O errors or other operating-system errors are encountered trying to open and read a PSU. This type of error might indicate hardware failure that has corrupted the database.
NFS errors of a transient nature are caused by network loading. This type of error generally does not indicate database corruption.
If a segment is damaged, the table or index cannot be accessed while the damaged segment is online. To provide users with partial access to a multisegment table or index with a damaged segment, take the segment offline while you fix the problem.
To recover a damaged segment, you must determine which segment is damaged and what the damage is, repair the damage, and then complete the recovery process.
Managing Optical Storage : Optical storage devices provide direct-access secondary storage that is faster than tape and less expensive than disk. Optical storage offers you additional flexibility in determining how much data to store for how long. The data stored on optical devices is accessed just like data on magnetic disks. Although the access time is longer, the cost is significantly less. Optical devices are a good choice for infrequently accessed data that you neither want to relegate to tape archives (because it is needed occasionally) nor want to store on magnetic disks (because they are more expensive).
Because the access time is longer, you can specify whether queries and certain commands should wait for or skip data and indexes in optical storage. You can also specify whether STAR indexes that reside partly or entirely in optical storage should be considered when an index is chosen, just as you do for offline segments.
Altering Tables : The following changes can be made to a table with the ALTER TABLE statement:
Add or drop a column
Change a column name
Change a column default value
Change the specified maximum number of rows (MAXROWS PER SEGMENT and MAXSEGMENTS) in a table
Change the action taken to maintain referential integrity during delete operations that affect a specified table
Add or drop a foreign key
Change the fill factor of a VARCHAR column
Change the start and step value of a SERIAL column
Copying or Moving a Database : To make a copy of a database for training or testing or to move a database to a new location, you can use combinations of SQL statements and operating-system and TMU commands.
The rb_cm utility facilitates the movement of data among databases. Using the rb_cm utility for migrating between different versions of IBM Red Brick Warehouse is supported only if the data is transferred in TMU EXTERNAL format.
It is safer to move a database without a version log. If your database contains a version log, drop it before moving the database, and re-create it after the move is complete.
It is safer to move a database without a version log. If your database contains a version log, drop it before moving the database, and re-create it after the move is complete.
Monitoring and Controlling a Database Server : Some warehouse includes tools to help monitor and control the warehouse daemons and database server processes. You can also monitor queries through the USAGE ROUTINE event in the log file.
Determining Version Information : You can obtain database server version information as follows:
By viewing the rbwapid.log file
The version is at the beginning of the configuration information that follows the startup times.
From the copyright banners displayed when a RISQL Entry Tool or RISQL Reporter session is started
By entering the following SQL statement from any tool that allows direct entry of SQL:
select rbw_version from rbw_tables ;
Deleting Database Objects and Databases : As user requirements evolve, you might need to remove tables from an existing database or delete an entire database from a data warehouse installation.
Dropping database objects (tables, indexes, views, synonyms, segments, macros, roles, and hierarchies) from a database with DROP statements.
Deleting a database.
To remove a user from a database, execute a REVOKE CONNECT statement.
Managing memory requirements for a database catalog : When a warehouse process or thread opens the system catalog for a database, it loads the system catalog file RB_DEFAULT_IDX into memory. The system catalog grows over time as you create more database objects (PSUs, segments, tables, views, and so on). As you drop objects, the database frees space within the system catalog. However, if an active object exists in a block between several free blocks, the system catalog removes only the free blocks from the end to the last active block. The free blocks in between the active blocks are not removed, but they can be reused. You can use the rb_syscompact utility to compact the system catalog file RB_DEFAULT_IDX. Compacting a file rearranges blocks to save space when the catalog is loaded into memory.
