Database Management Systems

For a consistent closed database backup using OS commands, the database must be shut down cleanly ensuring all data is written to disk. NORMAL waits for users to disconnect, IMMEDIATE/TRANSACTIONAL prevent new connections and rollback active transactions. ABORT is inconsistent - it doesn't roll back transactions, leaving database in an unrecoverable state for backup purposes.

To export tables owned by another user: CREATE SESSION is required to connect to the database, and EXP_FULL_DATABASE privilege grants authority to export objects owned by any user. CREATE USER is for creating users (not exporting), CREATE ANY TABLE is for DDL (not export), IMP_FULL_DATABASE is for import (not export).

Locally managed tablespaces with automatic segment space management require: Oracle Managed Files (OMF) for automatic file handling, and COMPATIBLE parameter set to 9.0.0 or higher for LMT + ASSM support. FET$/UET$ are used in DICTIONARY-managed tablespaces (not locally managed), and freelists are manual segment management (not automatic).

The CONTROL_FILE_RECORD_KEEP_TIME parameter indeed determines how long RMAN records are retained in the control file before they can be overwritten. RMAN can use the control file as its catalog repository instead of a separate recovery catalog database, though this has limitations like shorter retention and no stored scripts. The control file has a fixed size at creation and does not dynamically grow, and RMAN scripts are stored in the recovery catalog, not the control file.

In NOARCHIVELOG mode, database recovery to a specific point in time or incomplete recovery is impossible because redo logs are not archived. Thus, if a data file is lost, the database must be restored from a cold backup (losing all changes since), or the affected tablespace must be dropped, losing all its data.

LOG_ARCHIVE_DEST_n parameters control Data Guard availability through three key attributes: SYNC/ASYNC defines network transmission mode (synchronous for zero data loss, asynchronous for better performance), LGWR/ARCH specifies which primary process sends redo (LGWR for real-time, ARCH for archived logs), and AFFIRM/NOAFFIRM controls whether disk writes are synchronous (AFFIRM) or asynchronous (NOAFFIRM) at the standby. PROTECTED/UNPROTECTED is not a valid parameter - data protection modes are set at the database level, not per destination.

In SMS (Storage Management Subsystem), the STORAGE CLASS is the essential class that must be assigned to create a SMS-managed dataset. The storage class defines the service class and performance characteristics (like response time) that the dataset requires. Other classes like Management Class, Data Class, and Storage Group are optional but Storage Class is mandatory for SMS management.

Storage Class is the SMS class that specifies dataset performance and availability characteristics. It defines attributes like response time, service class, and availability requirements that the system must meet when allocating and accessing the dataset. Management Class handles backup and migration, Data Class deals with multi-volume attributes, and Storage Group specifies volume names.

Data Class is the SMS class that determines multi-volume properties of a dataset, including whether the dataset can span multiple volumes and how it's allocated across them. This is distinct from Storage Class (performance/availability), Management Class (backup/migration), and Storage Group (volume selection). Multi-volume allocation rules are specifically governed by Data Class attributes.

Management Class is the SMS class used to specify dataset backup and migration criteria. It defines when and how datasets should be backed up, when they should be migrated to secondary storage (like tape), and when they should be expired. This is distinct from Data Class (multi-volume properties), Storage Class (performance), and Storage Group (volume selection). Management Class handles all data lifecycle and retention policies.

XML was introduced as a native data type in DB2 9, allowing storage and querying of XML documents using XQuery. BLOB and GRAPHIC existed in earlier versions, while ROWID is a pseudo-column, not a data type.

A clustering index determines the physical order in which data rows are stored on disk. When a clustering index is defined, DB2 attempts to maintain the data sequence matching the index key order. Other index types (unique, partitioning, secondary) only affect logical access paths.

The statement CREATE STOGROUP TEST VOLUMES(ABCD1,ABCD2,ABCD3) VCAT dsn1 creates a Storage Group (STOGROUP) in DB2, mapping logical volumes to a specific catalog identifier. The syntax defines volumes and VCAT, which are not used directly in creating tablespaces, indexes, or views.

RUNSTATS is recommended after REORG because REORG changes the physical data distribution, making existing statistics obsolete. RUNSTATS collects fresh statistics about table data distribution, enabling the optimizer to choose efficient access paths for subsequent queries.

The DB2 Directory contains Database Descriptors (DBDs), which are internal control structures describing database physical structures. The DB2 Catalog stores metadata about database objects, the Optimizer uses statistics for access path selection, and the Data Manager manages actual data access.